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:
1version: "3"
2services:
3 postgres:
4 container_name: postgres
5 image: postgres:latest
6 ports:
7 - "5432:5432"
8 volumes:
9 - /data/postgres:/data/postgres
10 env_file:
11 - docker.env
12 networks:
13 - postgres
14
15 pgadmin:
16 links:
17 - postgres:postgres
18 container_name: pgadmin
19 image: dpage/pgadmin4
20 ports:
21 - "8080:80"
22 volumes:
23 - /data/pgadmin:/root/.pgadmin
24 env_file:
25 - docker.env
26 networks:
27 - postgres
28
29networks:
30 postgres:
31 driver: bridgeThe 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.
1POSTGRES_USER=admin
2POSTGRES_PASSWORD=admin
3POSTGRES_DB=tutorial
4PGADMIN_DEFAULT_EMAIL=admin@admin.com
5PGADMIN_DEFAULT_PASSWORD=adminThe 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:
1docker-compose upMaking the Express Postgres connection
In this tutorial, we use TypeORM that works well with TypeScript. Let’s install all needed components!
1npm install typeorm reflect-metadata pgThe first thing to do is to create the ormconfig.ts file with our configuration:
1import { ConnectionOptions } from 'typeorm';
2
3const config: ConnectionOptions = {
4 type: 'postgres',
5 host: process.env.POSTGRES_HOST,
6 port: Number(process.env.POSTGRES_PORT),
7 username: process.env.POSTGRES_USER,
8 password: process.env.POSTGRES_PASSWORD,
9 database: process.env.POSTGRES_DB,
10 entities: [
11 __dirname + '/../**/*.entity{.ts,.js}',
12 ],
13 synchronize: true,
14};
15
16export default config;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:
1POSTGRES_HOST=localhost
2POSTGRES_PORT=5432
3POSTGRES_USER=admin
4POSTGRES_PASSWORD=admin
5POSTGRES_DB=tutorial
6PORT=5000Since we got that down, let’s connect to the database.
1import 'dotenv/config';
2import 'reflect-metadata';
3import { createConnection } from 'typeorm';
4import App from './app';
5import config from './ormconfig';
6import PostController from './post/post.controller';
7import validateEnv from './utils/validateEnv';
8
9validateEnv();
10
11(async () => {
12 try {
13 await createConnection(config);
14 } catch (error) {
15 console.log('Error while connecting to the database', error);
16 return error;
17 }
18 const app = new App(
19 [
20 new PostController(),
21 ],
22 );
23 app.listen();
24})();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:
1import { Column, Entity, PrimaryGeneratedColumn } from 'typeorm';
2
3@Entity()
4class Post {
5 @PrimaryGeneratedColumn()
6 public id?: number;
7
8 @Column()
9 public title: string;
10
11 @Column()
12 public content: string;
13}
14
15export default Post;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.
1private postRepository = getRepository(Post);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.
1private createPost = async (request: express.Request, response: express.Response) => {
2 const postData: CreatePostDto = request.body;
3 const newPost = this.postRepository.create(postData);
4 await this.postRepository.save(newPost);
5 response.send(newPost);
6}To retrieve multiple instances we use the find function. If not given any options, it returns all elements.
1private getAllPosts = async (request: express.Request, response: express.Response) => {
2 const posts = await this.postRepository.find();
3 response.send(posts);
4}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.
1private getPostById = async (request: express.Request, response: express.Response, next: express.NextFunction) => {
2 const id = request.params.id;
3 const post = await this.postRepository.findOne(id);
4 if (post) {
5 response.send(post);
6 } else {
7 next(new PostNotFoundException(id));
8 }
9}To modify an existing post, we use the update function. Afterward, we use the findOne function to return the modified instance.
1private modifyPost = async (request: express.Request, response: express.Response, next: express.NextFunction) => {
2 const id = request.params.id;
3 const postData: Post = request.body;
4 await this.postRepository.update(id, postData);
5 const updatedPost = await this.postRepository.findOne(id);
6 if (updatedPost) {
7 response.send(updatedPost);
8 } else {
9 next(new PostNotFoundException(id));
10 }
11}To delete a post, we use the delete function.
1private deletePost = async (request: express.Request, response: express.Response, next: express.NextFunction) => {
2 const id = request.params.id;
3 const deleteResponse = await this.postRepository.delete(id);
4 if (deleteResponse.raw[1]) {
5 response.sendStatus(200);
6 } else {
7 next(new PostNotFoundException(id));
8 }
9}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:
1import * as express from 'express';
2import { getRepository } from 'typeorm';
3import PostNotFoundException from '../exceptions/PostNotFoundException';
4import Controller from '../interfaces/controller.interface';
5import validationMiddleware from '../middleware/validation.middleware';
6import CreatePostDto from './post.dto';
7import Post from './post.entity';
8
9class PostController implements Controller {
10 public path = '/posts';
11 public router = express.Router();
12 private postRepository = getRepository(Post);
13
14 constructor() {
15 this.initializeRoutes();
16 }
17
18 private initializeRoutes() {
19 this.router.post(this.path, validationMiddleware(CreatePostDto), this.createPost);
20 this.router.get(this.path, this.getAllPosts);
21 this.router.get(`${this.path}/:id`, this.getPostById);
22 this.router.patch(`${this.path}/:id`, validationMiddleware(CreatePostDto, true), this.modifyPost);
23 this.router.delete(`${this.path}/:id`, this.deletePost);
24 }
25
26 private createPost = async (request: express.Request, response: express.Response) => {
27 const postData: CreatePostDto = request.body;
28 const newPost = this.postRepository.create(postData);
29 await this.postRepository.save(newPost);
30 response.send(newPost);
31 }
32
33 private getAllPosts = async (request: express.Request, response: express.Response) => {
34 const posts = await this.postRepository.find();
35 response.send(posts);
36 }
37
38 private getPostById = async (request: express.Request, response: express.Response, next: express.NextFunction) => {
39 const id = request.params.id;
40 const post = await this.postRepository.findOne(id);
41 if (post) {
42 response.send(post);
43 } else {
44 next(new PostNotFoundException(id));
45 }
46 }
47
48 private modifyPost = async (request: express.Request, response: express.Response, next: express.NextFunction) => {
49 const id = request.params.id;
50 const postData: Post = request.body;
51 await this.postRepository.update(id, postData);
52 const updatedPost = await this.postRepository.findOne(id);
53 if (updatedPost) {
54 response.send(updatedPost);
55 } else {
56 next(new PostNotFoundException(id));
57 }
58 }
59
60 private deletePost = async (request: express.Request, response: express.Response, next: express.NextFunction) => {
61 const id = request.params.id;
62 const deleteResponse = await this.postRepository.delete(id);
63 if (deleteResponse.raw[1]) {
64 response.sendStatus(200);
65 } else {
66 next(new PostNotFoundException(id));
67 }
68 }
69}
70
71export default PostController;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!