PostgreSQL and typeorm - Getting a local Postgres instance

Published on June 05, 2022

Lesson goal

  • Set up a local instance of postgres for learning
  • Learn where to get a production database

Database course index

This is part of a full course on persistence in postgres with typeorm and sql!

There is a github repo to go with this course. See part 2 for instructions.

Why use Postgres?

Postgres is a relational database but it’s designed and actually used in production for a range of workloads. It was first released in 1996 so it’s been thoroughly battle-tested over the years.

Postgres is as advanced as any of the paid databases and receives regular updates and support. It has a huge amount of functionality built-in, supports many datatypes including rich support for JSON documents, indexing, replication, security, procedures, full text search, pub-sub etc etc. Everything you would expect from a modern RDBMS.

Postgres is extensible and has incredible (paid) extensions for specific scenarios like time series and geo-spatial data.

Postgres is free and you can run it on most modern OSs! Both AWS and Azure provide fully managed instances of postgres.

RDBMS system structure

Postgres and most RDBMS have a hierarchical structure. The data is stored in a collection of tables. Tables are part of schemas and databases contain schemas.

postgres structure

Let’s configure a local instance of postgres for this course.

Glossary: Database schemas

In RDBMS there is a high level concept called a schema that contains tables. Schemas are used as an administrative grouping of tables. Usually only select users can view or modify the tables in a schema so the schema acts a security grouping.

A schema also contains indexes, functions, stored procedures and things like that. One way that developers can think about schemas is that they are a namespacing feature.

Usually each app will use a named schema for app data. To use a schema you create it in the RDBMS and then specify the schema name in your connection string.

There is a default schema in postgres called “public”. You should avoid using the default schema in postgres because the data there is viewable and can be changed by any database user by default.

1. Installing docker

Download docker and follow the instructions.

Add an entry for your local dns to access docker containers (only if you don’t already have this configured).

sudo -- sh -c -e "echo ' host.docker.internal' >> /etc/hosts";

When docker is finished installing move on to cloning the repository.

2. Cloning the sample repository

Open your preferred terminal. For default terminal use: ⌘-space and terminal , enter key.

Navigate to your github projects folder and

# http clone
git clone

# ssh clone
git clone

Next install the dependencies for the project

# NOTE: the project requires node 16 or above

# cd into the new directory
cd learn_databases

# if you don't have yarn install it
npm i -g yarn

# install all the packages with yarn

If that builds you’re ready to test the database.

3. Testing the database!

Bring the database to life with

yarn up

bring up postgres in docker
bring up postgres in docker

Test the database connection from the sample code with the following test.

If this fails just give the database a minute to start up. It’s slow the first time.

yarn test:named "connection"

4. Installing Postico

We use Postico to connect to databases to run random SQL so install that now

install with brew

brew install --cask postico

or from the website at

Open postico and add a new favourite.

postico configurationi
postico configurationi

Your settings should be identical, use the password from the src/database-connection/appDatasource.ts file.

If it connects you’re all set!

Resetting the database

No worries, you can destroy your local instance and rebuild it

To begin you call the destroy script

yarn destroy

This will stop your database and remove the database data

then start the database infrastructure again (see step 3)

yarn up

This will give you a completely fresh database - you’ll have to run the schema creation again.

But understand that it’s safe to explore and try things. You might break the database but it’s no big deal, you can always reset it.

How can I get an instance of postgres online?

We’re using a local instance for these demos but for your production applications you won’t use a local docker database. You will probably use a database that a cloud provider manages for you online.

You can get instances of postgres from AWS, digital ocean, GCP, heroku or Azure.

You can easily run postgres for a hobby site on a $5 digital ocean droplet with dokku.

You don’t have to do anything now, but just be aware of this.

Supabase is an interesting cloud implementation of Postgres. It exposes many of Postgres’ features over REST or GraphQL apis and adds cloud functionality to make it more like Firebase.

For example it makes use of the built in Postgres Roles Based Authorization. This is typically not used in most typeorm applications but Supabase exposes the APIs in a very developer friendly way.

It’s worth checking out for your first Postgres database. Find Supabase here

Adding Database Schema

The database you just created will be “empty”. There are no tables yet but we will add tables and other schema items in later lessons.

In part 3 of this series - Storing data in postgres we will manually add some tables to the database using CREATE commands.

Most applications these days will use migrations to populate schema in a database and we cover that in Part 4 - Getting into relations. Jump ahead and read about migrations if you like but don’t skip lesson 3 entirely.


You should have a local instance of postgres database running on your machine.

We will start using it in the next lessons!

Lesson index

Darragh ORiordan

Hi! I'm Darragh ORiordan.

I live and work in Sydney, Australia building and supporting happy teams that create high quality software for the web.

I also make tools for busy developers! Do you have a new M1 Mac to setup? Have you ever spent a week getting your dev environment just right?

My Universal DevShell tooling will save you 30+ hours of configuring your Windows or Mac dev environment with all the best, modern shell and dev tools.

Get DevShell here: ✨

Read more articles like this one...

List of article summaries


Building an AI generated game with Stable Diffusion and data from Wikipedia

Last week I released a game called Doodle:ai.

In the game you’re shown AI generated images and you have to guess the Wikipedia topic it used to create the game.


Easiest way to optimise images for web

Here is how I optimise all pngs and jpgs in a folder for publishing to the web.


Start tracking DORA metrics for your team in just 15 minutes with Apache Dev Lake

DORA (DevOps Research and Assessment) metrics are an excellent way for engineering organisations to measure and improve their performance.

Up until now, monitoring the DORA metrics across Github, Jira, Azure Devops etc required custom tooling or a tedious manual process.

With Apache Dev Lake you can get beautiful reporting for DORA metrics on your local machine in as little as 15 minutes (honestly!).

From Google Sheets to Grafana
From Google Sheets to Grafana


A summary of NDC Sydney 2022 Developer Conference

I attended my first in-person conference for more than 3 years last week! NDC is one of the more well-known developer conferences in Australia and New Zealand. It’s a 5 day conference with 3 days of talks and 2 days of workshops.

There’s so much to learn across all the streams so I try to take notes for each of the talks to quickly reference them later. This post contains all my notes. I’ll add the relevant videos to talks later if they’re released.

A reminder that these notes are just my notes. They’re paraphrased and summarised from what the speaker actually said. Each speakers would have provided must more clarity and went into more detail during their pressos!