Getting a local Postgres instance

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.

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. Install docker

https://www.docker.com/products/docker-desktop/

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 '127.0.0.1 host.docker.internal' >> /etc/hosts";

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

2. Clone 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 https://github.com/darraghoriordan/learn_databases

# ssh clone
git clone git@github.com:darraghoriordan/learn_databases.git

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
yarn

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

3. Test the database!

Bring the database to life with

yarn up

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. Install 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 https://eggerapps.at/postico/

Open postico and add a new favourite.

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!

What if I completely trash the database during the course?

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.

Summary

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

We will start using it in the next lessons!