TypeScript Express tutorial #7. Relational databases with Postgres and TypeORM

Express JavaScript

This entry is part 7 of 15 in the TypeScript Express tutorial

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

 

pgAdmin express postgres

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:

 

pgAdmin express postgres

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 >>
Subscribe
Notify of
guest
1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
James
James
5 years ago

TSError: ⨯ Unable to compile TypeScript
src\middleware\validation.middleware.ts (16,53): Property ‘values’ does not exist on type ‘ObjectConstructor’. (2339)

CreatePostDto : @IsNumber()
public id: number;
@IsString()
public content: string;

@IsString()
public title: string;

post entity:

@PrimaryGeneratedColumn()
public id?: number;
@Column()
public title: string;
@Column()
public content: string;

something wrong?