PostgreSQL and typeorm - Getting a local Postgres instance

Published on June 05, 2022

Tagged: #engineering

Follow me on twitter for more posts like this

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.

  1. Intro to persistence
  2. Creating a local instance (you are here)
  3. Storing data in postgres
  4. Getting into relations
  5. Advanced querying
  6. Transactions
  7. 9 tips, tricks and common issues
  8. Database administration glossary

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. 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
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
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!

Lesson index

  1. Intro to persistence
  2. Creating a local instance (you are here)
  3. Storing data in postgres
  4. Getting into relations
  5. Advanced querying
  6. Transactions
  7. 9 tips, tricks and common issues
  8. Database administration glossary
Darragh ORiordan

Hi! I'm Darragh ORiordan.

I live and work in Sydney, Australia building 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 DevShell tooling will save you 30+ hours configuring your dev environment with all the best modern tools. Get it here

https://darraghoriordan.gumroad.com/l/devshell


Read more articles like this one...

List of article summaries

#engineering

Open Telemetry in NestJs (and React)

Introduction

Open Telemetry is good enough to use in production projects now and most cloud…

#engineering

PostgreSQL and typeorm - Practical transactions

Lesson goal

To understand what a transaction does for us and how to choose when one is required in…

#engineering

PostgreSQL and typeorm - 9 Tips, tricks and common issues

Lesson goal

To learn some tips and tricks to solve very common issues with typeorm and postgres…

#engineering

PostgreSQL and typeorm - A glossary for database administration

Lesson goal

You will learn about some things that you might come across when discussing database…