In this article, we will explore the integration of a database with our ExpressWebJs application using TypeORM. Before diving into TypeORM, it’s important to understand the concept of Object-Relational Mapping (ORM).

ORM is a technique that allows developers to interact with a relational database using an object-oriented programming paradigm. In other words, ORM frameworks like TypeORM provide an abstraction layer over the database, allowing developers to work with tables and records as if they were regular objects in their programming language.

ORM frameworks also typically provide features like validation, querying, and relationships between tables, making it easier to work with complex data models.

TypeORM is one such ORM framework for TypeScript and JavaScript that provides a rich set of features for working with databases. It supports a variety of database management systems, including MySQL, PostgreSQL, and MongoDB, and offers features like entity modeling, migrations, and query building. With ExpressWebJs, integrating TypeORM is a straightforward process that can help streamline your application’s database interactions.

Overview

This tutorial creates the following API:

Setting Up

To install ExpressWeb, Make sure you have Nodejs and ts-node installed. Once that is done, you can use npx expresswebcli new command, followed by your project name and --ts or --typescript to install your ExpressWeb Project.

--ts or --typescript flag enables Expresswebjs to generate the typescript version.

npx expresswebcli new myProjectName --typescript

Or you can use ts in place of typescript:

npx expresswebcli new myProjectName --ts

You can decide to install expresswebcli globally via npm like so:

npm install -g expresswebcli

Then run the below command to create your project.

expresswebcli new myProject --ts

Once that is done, move into your project directory or use the command below in your terminal.

cd myProjectName

Then install all dependencies by running npm install.

npm install

Connecting to Postgres DataBase Using TypeORM

Install Typeorm and Postgres

npm install typeorm pg

Once that is done, run the following command to generate your .env file from example.env

cp example.env .env

We can now configure our env file by updating the following:

DB_SHOULD_CONNECT=true
DB_DRIVER=postgres
DB_HOST=localhost
DB_PORT=5444
DB_USER=postgres
DB_PASSWORD= your-password-goes-here
DB_DATABASE= your-database-name-goes-here

Once that is done, we set our ORM to TypeORM in the Config/database.ts file.

//Config/database.ts

import * as typeorm from "typeorm"; 👈  //import typeorm
import { env, orm } from "expresswebcorets/lib/Env";
import { Path } from "expresswebcorets/lib/Utils/Path";
import { DBConnection, TypeORMConfigurationType } from "expresswebcorets/lib/Database/DataSourceConfig"; 👈  //import typeorm configuration type

export default {
  /*
  |--------------------------------------------------------------------------
  | Database ORM
  |--------------------------------------------------------------------------
  | ExpressWeb currently supports the following Object Relational Mappers(ORM)
  | Objection and TypeORM for sql databases and  Mongoose for mongo DB.
  | You need to select one depending on the type of database you are working on.
  |
  */
  ORM: env("ORM", orm.TypeORM), 👈  //set orm to typeORM

  /*
  |--------------------------------------------------------------------------
  | Database Provider
  |--------------------------------------------------------------------------
  | With respect to the orm you selected, you need to import the provider and
  | assign it to provider.
  | Example:
  | for objection, import * as objection from "objection"
  | for typeorm, import * as typeorm from "typeorm"
  | Then assign typeorm to provider like this:
  | provider: typeorm
  |
  */
  provider: typeorm, 👈  //set imported typeorm to provider

  /*
  |--------------------------------------------------------------------------
  | Database Multitenance
  |--------------------------------------------------------------------------
  | Database multitenance can be activated by switching the value to true and can
  | be deactivated by switching it to false.
  |
  */
  database_multitenance: env("DB_MULTITENANCE", false),
  /*
  |--------------------------------------------------------------------------
  | Multitenance Connections
  |--------------------------------------------------------------------------
  | Database multitenance connection enables interaction with multiple
  | SQL databases where each database is a tenant in your system.
  | The tenant array accepts an object of database connections (tenants).
  |
  */
  multitenant_tenants: DBConnection.multitenant<TypeORMConfigurationType>("TypeORM", []),

  /*
  |--------------------------------------------------------------------------
  | Database Connection
  |--------------------------------------------------------------------------
  | Here we define connection settings for both TypeORM, Objection, and mongoose.
  | For typeORM, npm i --save typeorm
  | For Objection, npm i --save objection
  | For Mongoose, npm i --save mongoose
  | --------------------------------------------------------------------------
  | For SQL db, install the driver of your choice
  | mysql driver, npm i --save mysql mysql2
  | postgres driver, npm i --save pg pg-hstore
  |
  */
  connection: DBConnection.connect<TypeORMConfigurationType>({  👈  //add typeorm configuration type
    type: env("DB_DRIVER"),
    host: env("DB_HOST"),
    port: Number(env("DB_PORT")),
    username: env("DB_USER"),
    password: env("DB_PASSWORD"),
    database: env("DB_DATABASE"),
    entities: ["App/Model/*.ts"],
    logging: false,
    synchronize: true,
  }),

  /*
  |--------------------------------------------------------------------------
  | Migration Configuration
  |--------------------------------------------------------------------------
  | Here we have database migration configuration.
  | Which includes the following:
  */
  migrations: {
    directory: Path("Database/Migrations/"),
    tableName: "migrations",
    stub: Path("Database/Migrations/migrationLayout.stub"),
    extension: "ts",
  },

  /*
  |--------------------------------------------------------------------------
  | Seed Configuration
  |--------------------------------------------------------------------------
  | Here we have database seed configuration.
  | Which includes the following:
  */
  seeds: {
    directory: Path("Database/Seeds/"),
  },

  /*
  |--------------------------------------------------------------------------
  | Redis Connection
  |--------------------------------------------------------------------------
  | Here we define connection settings for Redis database.
  | npm i --save ioredis
  */
  redis: {
    client: env("REDIS_CLIENT", "default"),

    default: {
      host: env("REDIS_HOST", "127.0.0.1"),
      port: env("REDIS_PORT", 6379),
      password: env("REDIS_PASSWORD", null),
      database: env("REDIS_DB", 0),
    },

    cache: {
      host: env("REDIS_HOST", "127.0.0.1"),
      port: env("REDIS_PORT", 6379),
      password: env("REDIS_PASSWORD", null),
      database: env("REDIS_DB", 0),
    },
  },
};

With this, we are done setting up our Postgres database with Typeorm. Next will be to configure our service to run with an HTTP server. This is done in the app.ts in the project root directory.

/*
|---------------------------------------------------------------
| Http Server
|---------------------------------------------------------------
| This file bootstraps ExpressWebJs to start the Http server.
| Application Host, Port and Transfer Protocols are configured
| in the .env file. You are free to configure them. 
|
*/
import { StartApp } from "expresswebcorets";

StartApp.withHttpServer();

Once that is done, we can start our application in development mode with the following command.

npm run dev

Generating todo model class

To generate a model class, use the following command

ts-node maker make-sql-model TodoModel

Once that is done, let’s update TodoModel class in the App/Model directory.

export enum TodoStatus {
  DONE = "done",
  PENDING = "pending",
}
export interface ITodoModel {
  id: number;
  name: string;
  isComplete: TodoStatus;
  created_at?: Date;
  updated_at?: Date;
}

import { Column, Entity, ManyToOne, OneToMany } from "typeorm";
import { BaseModel } from "./BaseModel";
import { TodoItemModel } from "./TodoItemModel";
import { ITodoModel } from "./Types/ITodoModel";
import { UserModel } from "./UserModel";

@Entity("todos")
export class TodoModel implements ITodoModel {
  @PrimaryGeneratedColumn("uuid")
  id!: string;

  @Column("varchar", { length: 600 })
  name!: string;

  @Column({
    type: "enum",
    enum: TodoStatus,
    default: TodoStatus.PENDING,
  })
  isComplete!: TodoStatus;

  @Column({ type: "timestamptz", default: () => "CURRENT_TIMESTAMP(6)" })
  created_at?: Date;

  @Column({ type: "timestamptz", default: () => "CURRENT_TIMESTAMP(6)", onUpdate: "CURRENT_TIMESTAMP(6)" })
  updated_at?: Date;
}

Add and register a repository class.

Create a TodoRepository class in App/Repository directory and enter the following code:

import { TodoModel } from "App/Model/TodoModel";
import { ITodoModel } from "App/Model/ITodoModel";
import { TypeORMRepository } from "Elucidate/Repository/TypeORM";

export class TodoRepository extends TypeORMRepository<ITodoModel> {
  constructor() {
    super(TodoModel);
  }
}

Once that is done, you can register TodoRepository in AppServiceProvider class located in App/Provider directory.

import { TodoRepository } from "App/Repository/TodoRepository";  👈  //import TodoRepository

export class AppServiceProvider extends ServiceProvider {
  /**
   * Register any application services.
   * @return void
   */
  public register() {
    this.singleton(TodoRepository);  👈  //Register as singleton
  }
}

Scaffold a controller

Before we continue, ensure all of your changes so far are saved.

You can generate your resource controller with the following command:

ts-node maker make-controller TodoController -r

This command will generate a controller in App/Http/Controllers directory. The controller will contain a method for each of the available resource operations.

import { Request, Response } from "Config/http";
import { BaseController } from "./BaseController";

export class TodoController extends BaseController {
  /**
   * Display a listing of the resource.
   * @method GET
   * @endpoint
   */
  public async index(req: Request, res: Response) {
    throw new Error("TodoController index method not implemented.");
  }

  /**
   * Store a newly created resource in storage.
   * @method POST
   * @endpoint
   */
  public async store(req: Request, res: Response) {
    throw new Error("TodoController store method not implemented.");
  }

  /**
   * Display the specified resource.
   * @method GET
   * @endpoint
   */
  public async show(req: Request, res: Response) {
    throw new Error("TodoController show method not implemented.");
  }

  /**
   * Update the specified resource in storage.
   * @method PATCH
   * @endpoint
   */
  public async update(req: Request, res: Response) {
    throw new Error("TodoController update method not implemented.");
  }

  /**
   * Remove the specified resource from storage.
   * @method DELETE
   * @endpoint
   */
  public async destroy(req: Request, res: Response) {
    throw new Error("TodoController destroy method not implemented.");
  }
}

Once TodoController is generated, we can now inject TodoRepository into the TodoController constructor.

import { Request, Response } from "Config/http";
import { BaseController } from "./BaseController";
import {TodoRepository} from "App/Repository/TodoRepository"

export class TodoController extends BaseController {
   constructor(private todoRepository:TodoRepository){}   👈  //inject TodoRepository
}

Let's save todo from our HTTP request:

/**
 * Store a newly created resource in storage.
 * @method POST
 * @endpoint
 */
public async store(req: Request, res: Response) {
  const todo = req.body;
  const createdTodo = await this.todoRepository.save(todo);
  return this.response.OK(res, createdTodo);
}

Let's fetch all todos:

/**
 * Display a listing of the resource.
 * @method GET
 * @endpoint
 */
public async index(req: Request, res: Response) {
  const todos = await this.todoRepository.findAll();
  return this.response.OK(res, todos);
}

Todo Route

Now that we have created our todo controller class let’s create the route. This is done in the Route/api.ts file in the project root directory.

import { Route } from "Elucidate/Route/RouteManager";
import { Request, Response } from "Config/http";

/*
|--------------------------------------------------------------------------
| Api route
|--------------------------------------------------------------------------
|
| Here is where you can register your application routes. These
| routes are loaded by the RouteProvider. Now create something great!
|
*/

Route.get("/", async (req: Request, res: Response) => {
  res.send("Welcome to ExpressWebJs Version 4.2");
});

Route.post("/todo", "TodoController@save");

Route.get("/todo", "TodoController@index");

//--------------------------------------------------------------------------
export default Route.exec;

We can now test our API using Postman:

To get all todos, make a GET request to http://127.0.0.1:5100/api/todo

To save a new todo, make a Post request to http://127.0.0.1:5100/api/todo

In this tutorial, we showed you how to set up TypeORM with ExpressWebJs and how to use the two together.

Let us know your thoughts in the comment section!

To learn more about ExpressWebJs, check out the official documentation.

Kindly Join the ExpressWebJs community on Discord.

You can follow ExpressWebJs on Twitter @expresswebjs, and don’t forget to star ExpressWebJs Project on GitHub.