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

Creating the Express Postgres connection

Preparing the environment with Docker

# docker-compose.yml
version: "3"
services:
    postgres:
        container_name: postgres
        image: postgres:latest
        ports:
            - "5432:5432"
        volumes:
            - ~/data/postgres:/data/postgres
        env_file:
            - docker.env
        networks:
            - postgres

    pgadmin:
        links:
            - postgres:postgres
        container_name: pgadmin
        image: dpage/pgadmin4
        ports:
            - "8080:80"
        volumes:
            - ~/data/pgadmin:/root/.pgadmin
        env_file:
            - docker.env
        networks:
            - postgres

networks:
    postgres:
        driver: bridge

# docker.env
POSTGRES_USER=admin
POSTGRES_PASSWORD=admin
POSTGRES_DB=tutorial
PGADMIN_DEFAULT_EMAIL=admin@admin.com
PGADMIN_DEFAULT_PASSWORD=admin

run container

docker-compose up

Making the Express Postgres connection

// express-ts-tutorial/ormconfig.ts 

import { DataSourceOptions } from 'typeorm';

console.log('ormconfig.ts: ', __dirname + '/**/*.entity{.ts,.js}');
 
const config: DataSourceOptions = {
  type: 'postgres',
  host: process.env.POSTGRES_HOST,
  port: Number(process.env.POSTGRES_PORT),
  username: process.env.POSTGRES_USER,
  password: process.env.POSTGRES_PASSWORD,
  database: process.env.POSTGRES_DB,
  entities: [
    __dirname + '/**/*.entity{.ts,.js}',
  ],
  synchronize: true,
};
 
export default config;

JWT_SECRET=jwt_secret_you_set_not_leak

POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_USER=admin
POSTGRES_PASSWORD=admin
POSTGRES_DB=tutorial
PORT=50001

// src/utils/connectedToDatabase.ts

import { DataSource, EntityTarget, ObjectLiteral } from "typeorm";
import config from '../../ormconfig';

export let dataSource: DataSource | undefined;
let isInitialized = false;

export function connectToTheDatabase() {
    try {
        if (!dataSource) {
            dataSource =  new DataSource(config);
        }
    } catch (error) {
        console.log("Error while connecting to the database", error);
        throw error;
        
    }
    return dataSource;
}

export async function getRepository<T extends ObjectLiteral>(entity: EntityTarget<T>) {
    if (!dataSource) {
        dataSource =  connectToTheDatabase();
    }
    if (!isInitialized) {
        await dataSource.initialize();
        isInitialized = true;
    }
    return dataSource.getRepository(entity);
}

// src/server.ts
import 'dotenv/config';
import 'reflect-metadata';
import App from './app';
import PostsController from './controller/posts/post.controller';

(async () => {

  const app = new App(
    [
      new PostsController(),
    ],
  );
  app.listen();
})();

add

// src/controller/posts/post.controller.ts

import * as express from "express";
import Controller from "../../interfaces/controller.interface";

import * as postService from "../../service/posts/post.service";

class PostsController implements Controller {
	public path = "/posts";
	public router = express.Router();
	private postService = postService;

	constructor() {
		this.initializeRoutes();
	}

	private initializeRoutes() {

		this.router.post(
			this.path,
			// validationMiddleware(CreatePostDto),
			this.createPost as any
		);
	}

	private createPost = (
		request: RequestWithUser,
		response: express.Response
	) => {
		this.postService.createPost(request, response);
	};
}

export default PostsController;

// src/service/posts/post.service.ts

import express from "express";
import { Post } from "../../models/posts/post.interface";
import postModel from "../../models/posts/post.model";
import PostNotFoundException from '../../exceptions/PostNotFoundException';
import RequestWithUser from '../../interfaces/requestWithUser.interface';
import NotAuthorizedException from '../../exceptions/NotAuthorizedException';
import userModel from "../../models/users/user.model";
import { getRepository } from "../../utils/connectedToDatabase";
import PostEntity from '../../entities/post/post.entity';

export async function createPost(
	request: RequestWithUser,
	response: express.Response
) {
	const postData: Post = request.body;
	const postRepository = await getRepository(PostEntity);

	console.log('createPost: ', request.user);
	console.log('createPost postData: ', postData);
	console.log('createPost request: ', request.user);
	
	const postEnty = new PostEntity();

	postEnty.title = postData.title;
	postEnty.content = postData.content;
	const createdPost = await postRepository.save(postEnty);

	response.send(createdPost);
}

Saving and retrieving data with Express Postgres

Entity

// src/entities/post/post.entity.ts

import { Column, Entity, PrimaryGeneratedColumn } from 'typeorm';
 
@Entity()
class PostEntity {
  @PrimaryGeneratedColumn()
  public id?: number;
 
  @Column()
    public title!: string;
 
  @Column()
    public content!: string;
}
 
export default PostEntity;

Repository

to use repository, the datasource should be initialized by

await dataSource.initialize();

or an error will be throw in your terminal.

DataSource.ts:444
        if (!metadata) throw new EntityMetadataNotFoundError(target)
                             ^
EntityMetadataNotFoundError: No metadata for "PostEntity" was found.

get all posts

// src/service/posts/post.service.ts
export async function getAllPosts(
	request: express.Request,
	response: express.Response
) {
	const postRepository = await getRepository(PostEntity);
	const posts = await postRepository.find();
	response.send(posts);
}

// src/controller/posts/post.controller.ts
	private initializeRoutes() {

		this.router.post(
			this.path,
			// validationMiddleware(CreatePostDto),
			this.createPost as any
		);

		this.router.get(this.path, this.getAllPosts);
	}

findById

// src/service/posts/post.service.ts

export async function getPostById(
	request: express.Request,
	response: express.Response,
	next: express.NextFunction
) {
	const id = request.params.id;
	const postRepository = await getRepository(PostEntity);
	const post = await postRepository.findOneBy({id});

	if (post) {
		response.send(post);
	} else {
        next(new PostNotFoundException(id));
	}
}

src/controller/posts/post.controller.ts

	private initializeRoutes() {
		this.router.get(this.path, this.getAllPosts);
		this.router.get(`${this.path}/:id`, this.getPostById);
		this.router.post(
			this.path,
			// validationMiddleware(CreatePostDto),
			this.createPost as any
		);
	}

update

//
export async function modifyPost(
	request: express.Request,
	response: express.Response,
	next: express.NextFunction
) {
	const id = request.params.id;
	const postData: Post = request.body;
	const postRepository = await getRepository(PostEntity);

	const post = await postRepository.findOneBy({ id });

	if (post) {
		post.title = postData.title;
		post.content = postData.content;
		await postRepository.save(post);
		response.send(post);
	} else {
		next(new PostNotFoundException(id));
	}
}

// register update route
	private initializeRoutes() {
            // ...
            this.router.patch(`${this.path}/:id`, this.modifyPost);
            // ...
        }

delete

// src/service/posts/post.service.ts
export async function deletePost(
	request: express.Request,
	response: express.Response,
	next: express.NextFunction
) {
	const id = request.params.id;
	const postRepository = await getRepository(PostEntity);

	const post = await postRepository.delete(id);

	console.log('delete post: ', post);
	

	if (post) {
		response.send(200);
	} else {
		next(new PostNotFoundException(id));
	}
}

// register update route
	private initializeRoutes() {
            // ...
            this.router.delete(`${this.path}/:id`, this.deletePost);
            // ...
        }

Using pgAdmin

http://localhost:8080/

if you inout host name/Address : localhost like below, you can not connect to postgre db. use postgres instead. you can guess by try which string in docker-compose.yml affect it

source