January 14, 2019

• 1. TypeScript Express tutorial #1. Middleware, routing, and controllers
• 2. TypeScript Express tutorial #2. MongoDB, models and environment variables
• 3. TypeScript Express tutorial #3. Error handling and validating incoming data
• 4. TypeScript Express tutorial #4. Registering users and authenticating with JWT
• 5. TypeScript Express tutorial #5. MongoDB relationships between documents
• 6. TypeScript Express tutorial #6. Basic data processing with MongoDB aggregation
• 7. TypeScript Express tutorial #7. Relational databases with Postgres and TypeORM
• 8. TypeScript Express tutorial #8. Types of relationships with Postgres and TypeORM
• 9. TypeScript Express tutorial #9. The basics of migrations using TypeORM and Postgres
• 10. TypeScript Express tutorial #10. Testing Express applications
• 11. TypeScript Express tutorial #11. Node.js Two-Factor Authentication
• 12. TypeScript Express tutorial #12. Creating a CI/CD pipeline with Travis and Heroku
• 13. TypeScript Express tutorial #13. Using Mongoose virtuals to populate documents
• 14. TypeScript Express tutorial #14. Code optimization with Mongoose Lean Queries
• 15. TypeScript Express tutorial #15. Using PUT vs PATCH in MongoDB with Mongoose

Today our tutorial takes a bit of a turn as we look into relational databases, Postgres in particular. In this article, we go through setting up an environment with Express Postgres. We also set up the pgAdmin platform so that we get an overview of our database. In the upcoming parts of the tutorial, we focus on drawbacks and advantages of using  Postgres.

As always, the code for the tutorial is in the express-typescript repository. The master branch still contains the version with MongoDB, while you can find the code for upcoming parts of the tutorial in the postgres branch. Feel free to give the repo a star.

Creating the Express Postgres connection

In this tutorial, we use Docker to set up a running Postgres database. You need to install Docker, as well as Docker Compose.

Preparing the environment with Docker

To run it, create a docker-compose file:

docker-compose.yml

The configuration above runs both the Postgres database and the pgAdmin console that gets you an overview of the state of your database and lets you make changes to it.

We also need to create the docker.env file that contains variables used by our Docker containers. It should not be committed, thus we add it to the .gitignore file.

docker.env

The only thing left to do is to run our containers from the console. To do that, go into the directory of the project and run:

Making the Express Postgres connection

In this tutorial, we use TypeORM that works well with TypeScript. Let’s install all needed components!

The first thing to do is to create the ormconfig.ts file with our configuration:

src/ormconfig.ts

The synchronize flag is very important and we will surely cover it in the near future

Please note that these are environment variables from the server and not the docker, so we need to add them to the .env file:

.env

Since we got that down, let’s connect to the database.

src/server.ts

Saving and retrieving data with Express Postgres

When we have the connection to the database up and running, we can finally interact with it in our application.

Entity

One of the most important concepts when using Express Postgres with TypeOrm is the entity. It is a class that maps to a database table. To define it, we use the Entity decorator:

src/post/post.entity.ts

The PrimaryGeneratedColumn decorator creates a primary column which value is generated with an auto-increment value.  It contains primary keys that are used to identify a row uniquely in a table.

The Column decorator creates a column in the database. You can specify additional options, like type, or let TypeORM figure it out based on the type of property – this works thanks to the reflect-metadata package that we’ve installed and imported in the server.ts file. We dive into additional options later.

Repository

Now we can use our entity in the post controller. The first thing to do is to use the  getRepository function to access the repository of our entity. The repo itself is a part of the EntityManager, which is a collection of all repositories.

The repository has a set of functions that let you interact with your entities. The basics of using the repository are similar to Mongoose that we covered before.

With the create function we can create a new instance of a Post. It accepts an object with properties of our newly created Post.  The instance can afterward be saved using the save function.

To retrieve multiple instances we use the find function. If not given any options, it returns all elements.

To get just one instance we use the findOne function. If given a number it returns a record with such id. If the result is undefined, it means that the record was not found.

To modify an existing post, we use the update function. Afterward, we use the findOne function to return the modified instance.

To delete a post, we use the delete function.

If you look into the documentation of the DELETE command, you can see that it returns the count of removed records. This data is stored in the  deleteResponse.raw[1]. If it is zero, we assume that the post wasn’t found.

Putting everything together, the controller looks like that:

Using pgAdmin

Thanks to the way we wrote our docker-compose.yml, we have the interface for managing our database available. To use it, go to http://localhost:8080 and provide the same credentials than you did in the docker.env file. When you’re in, you can create a new server connection.

 

The tricky part about it is that the address of the host is not “localhost”, but “postgres” like in the screenshot above. This string is the alias of the service that we defined in the docker-compose.yml.

If you posted some data before, you could view it here. To do that, use the left sidebar menu:

 

As we go deeper into Express Postgres, the pgAdmin will prove to be very useful in monitoring the stare of our database, so it is definitely worth setting up.

Summary

In this article, we covered the basics of creating an Express Postgres project with TypeScript and TypeORM. It included setting up a Docker configuration and connecting to the database in our Express Typescript application. We also went through the basic concepts of using the Postgres database with TypeORM, like the entity. To be able to monitor our database we did set up the pgAdmin platform. In the upcoming parts of the tutorial we will focus on more Postgres features, so stay tuned!

Series Navigation

<< TypeScript Express tutorial #6. Basic data processing with MongoDB aggregationTypeScript Express tutorial #8. Types of relationships with Postgres and TypeORM >>