TypeORM Express Application Using Repository Pattern – Part 1 – Create Tables with Relationships

This tutorial explores how we can create a Node.js application written in TypeScript and connect to a MySQL database using TypeORM package. It also gets into advanced topics like joins and persisting data where relationship between tables exists. TypeORM is an Object Relational Mapper (ORM) for Node.js written in TypeScript that can be used with TypeScript or JavaScript (ES5, ES6, ES7). It support latest JavaScript features and provide features that help you to develop any kind of applications that use database – from small applications with a few tables to large scale enterprise applications. TypeORM uses the Data Mapper pattern, unlike all other JavaScript ORMs that currently exist, which means you can write loosely coupled, scalable, maintainable applications with fewer problems.

The benefit of using TypeORM for the programmer is the ability to focus on the business logic and worry about persistence only as a secondary problem.

You can download the source code from here.

There are other posts which explains more advanced topics like inner joins and cascade save. Please refer below links for that.

TypeORM Express Application Using Repository Pattern – Part 2 – Persist Data into Tables

TypeORM Express Application Using Repository Pattern – Part 3 – How to perform InnerJoin

TypeORM Express Application Using Repository Pattern – Part 4 – How to do Cascade Save

Prerequisites
Install Node.js
Install VS Code
Install MySQL Workbench

Getting TypeScript

TypeScript itself is simple to add to any project with npm.

npm install -D typescript

1 2	npm install -D typescript

If you’re using VS Code then you’re good to go! VS Code will detect and use the TypeScript version you have installed in your node_modules folder. For other editors, make sure you have the corresponding TypeScript plugin.

Create App Folder and package.json file

Create a new folder ‘HiveStore-TypeORM-Express-API’ and open the folder in command prompt. Create a new node project using npm init command. This will create a package.json file in the app folder.

Configure TypeScript for the project

You can pass options to the TypeScript compiler by either by using the CLI, or a special file called tsconfig.json. By using this configuration file, we are telling TypeScript things like the build target (can be ES5, ES6, and ES7 at the time of this writing), what module system to expect, where to put the build JavaScript files, or whether it should create source-maps as well. Add a new file ‘tsconfig.json’ in the folder ‘HiveStore-TypeORM-Express-API’ and add below contents into it.

{
  "compilerOptions": {
    "module": "commonjs",
    "target": "es6",
    "noImplicitAny": true,
    "moduleResolution": "node",
    "sourceMap": true,
    "outDir": "dist",
    "baseUrl": ".",
    "paths": {
      "*": [
        "node_modules/*",
        "src/types/*"
      ]
    }
  },
    "include": [
        "src/**/*"
    ]
}

{

"compilerOptions": {

"module": "commonjs",

"target": "es6",

"noImplicitAny": true,

"moduleResolution": "node",

"sourceMap": true,

"outDir": "dist",

"baseUrl": ".",

"paths": {

"*": [

"node_modules/*",

"src/types/*"

]

}

"include": [

"src/**/*"

]

}

compilerOptions	Description
"module": "commonjs"	The output module type (in your .js files). Node uses commonjs, so that is what we use
"target": "es6"	The output language level. Node supports ES6, so we can target that here
"noImplicitAny": true	Enables a stricter setting which throws errors when something has a default any value
"moduleResolution": "node"	TypeScript attempts to mimic Node's module resolution strategy.
"sourceMap": true	We want source maps to be output along side our JavaScript. See the debugging section
"outDir": "dist"	Location to output .js files after compilation
"baseUrl": "."	Part of configuring module resolution

Install Packages

npm install express body-parser --save

1	npm install express body-parser --save

Install Typings

npm install @types/node @types/express @types/body-parser --save-dev

1	npm install @types/node @types/express @types/body-parser --save-dev

Project Structure

The project structure is as shown below.

Create src/app.ts – Entry point of the app and add below code in it

import * as express from 'express';

/**
 * Controllers (route handlers).
 */
import * as userController from "./controllers/user-controller";

/**
 * Create Express server.
 */
const app = express();

/**
 * Express configuration.
 */
app.set("port", process.env.PORT || 3000);

/**
 * Start Express server.
 */
app.listen(app.get("port"), () => {
  console.log(("  App is running at http://localhost:%d in %s mode"), app.get("port"), app.get("env"));
  console.log("  Press CTRL-C to stop\n");
});

/**
 * Primary app routes.
 */
app.get("/GetAllUsers", userController.getAllUsers);

module.exports = app;

import * as express from 'express';

/**

* Controllers (route handlers).

import * as userController from "./controllers/user-controller";

/**

* Create Express server.

const app = express();

/**

* Express configuration.

app.set("port", process.env.PORT || 3000);

/**

* Start Express server.

app.listen(app.get("port"), () => {

console.log((" App is running at http://localhost:%d in %s mode"), app.get("port"), app.get("env"));

console.log(" Press CTRL-C to stop\n");

});

/**

* Primary app routes.

app.get("/GetAllUsers", userController.getAllUsers);

module.exports = app;

Create employee-controller.ts in ‘src/controllers’ folder and add below code in it

import { Request, Response } from "express";

export let getAllUsers = (req: Request, res: Response) => {
  res.send("Received Get All Users Request..");
};

import { Request, Response } from "express";

export let getAllUsers = (req: Request, res: Response) => {

res.send("Received Get All Users Request..");

};

Compile and Run the App

Open command prompt and go the root folder of the app (‘HiveStore-TypeORM-Express-API’ folder) and run the command tsc. This will compile the typescript files according to the typeconfig.json configuration. After compilation, the js files are generated in dist folder.

\TypeScript-NodeJS-Express-Seed>tsc

1	\TypeScript-NodeJS-Express-Seed>tsc

There is an option for TypeScript compiler ‘watch’ which will watch for any changes in target directory and compiles the files automatically. It’s better to run ‘tsc -watch’ in root directory in a new command window. Now go the ‘dist’ folder. Here you can see the trasnspiled code files. Go to dist folder in command prompt and run the command ‘node app.js’

\TypeScript-NodeJS-Express-Seed\dist>node app.js

1	\TypeScript-NodeJS-Express-Seed\dist>node app.js

Below is a snapshot of the commands and results. Now open a browser and browse the url http://localhost:3000/GetAllUsers.

Install TypeORM package and dependencies

The benefit of using TypeORM for the programmer is the ability to focus on the business logic and worry about persistence only as a secondary problem.

npm install typeorm --save

1	npm install typeorm --save

npm install reflect-metadata --save

1	npm install reflect-metadata --save

Install reflect-metadata shim and import it somewhere in the global place of your app (for example in app.ts):

Install node typings

npm install @types/node --save

1	npm install @types/node --save

Install MySQL database driver

npm install mysql --save

1	npm install mysql --save

TypeScript configuration Also make sure you are using version 2.1 or greater of the TypeScript compiler, and you have enabled the following settings in tsconfig.json:

"emitDecoratorMetadata": true,
"experimentalDecorators": true,

1 2	"emitDecoratorMetadata": true, "experimentalDecorators": true,

ER Diagram
Below is the ER diagram of the tables that we are going to create using TypeORM.

Entities

In TypeORM, tables are created from Entities. Entity is your model decorated by an @Entity decorator. You can get entities from the database and insert/update/remove them from there.

You can make any property of your model a column by using a @Column decorator.
Use @PrimaryColumn decorator, to make a column a primary.
@PrimaryGeneratedColumn decorator specifies auto-increment primary column.

Add a new TypeScript file ‘user-entity.ts’ in entities folder and add below code in it.

import { Entity, Column, PrimaryGeneratedColumn, OneToMany } from "typeorm";
import { OrderEntity } from "./order-entity";

@Entity("user")
export class UserEntity {

    @PrimaryGeneratedColumn({ name: "Id", type: "smallint" })
    id: number;

    @Column({ name: "FirstName", length: 100 })
    firstName: string;

    @Column({ name: "LastName", length: 100 })
    lastName: string;

    @Column({ name: "Email", length: 100 })
    email: string;

    @OneToMany(type => OrderEntity, order => order.user)
    orders: OrderEntity[];
}

import { Entity, Column, PrimaryGeneratedColumn, OneToMany } from "typeorm";

import { OrderEntity } from "./order-entity";

@Entity("user")

export class UserEntity {

@PrimaryGeneratedColumn({ name: "Id", type: "smallint" })

id: number;

@Column({ name: "FirstName", length: 100 })

firstName: string;

@Column({ name: "LastName", length: 100 })

lastName: string;

@Column({ name: "Email", length: 100 })

email: string;

@OneToMany(type => OrderEntity, order => order.user)

orders: OrderEntity[];

}

Add a new TypeScript file ‘product-entity.ts’ in entities folder and add below code in it.

import { Entity, Column, PrimaryGeneratedColumn, OneToMany } from "typeorm";
import { OrderDetailsEntity } from "./order-details";

@Entity("product")
export class ProductEntity {

    @PrimaryGeneratedColumn({ name: "Id", type: "smallint" })
    id: number;

    @Column({ name: "Name", length: 100 })
    name: string;

    @Column({ name: "UnitPrice", type: "decimal", precision: 8, scale: 2, nullable: true })
    unitPrice: number;

    @OneToMany(type => OrderDetailsEntity, order_details => order_details.product)
    order_details: OrderDetailsEntity[];
}

import { Entity, Column, PrimaryGeneratedColumn, OneToMany } from "typeorm";

import { OrderDetailsEntity } from "./order-details";

@Entity("product")

export class ProductEntity {

@PrimaryGeneratedColumn({ name: "Id", type: "smallint" })

id: number;

@Column({ name: "Name", length: 100 })

name: string;

@Column({ name: "UnitPrice", type: "decimal", precision: 8, scale: 2, nullable: true })

unitPrice: number;

@OneToMany(type => OrderDetailsEntity, order_details => order_details.product)

order_details: OrderDetailsEntity[];

}

Add a new TypeScript file ‘order-entity.ts’ in entities folder and add below code in it.

import {Entity, Column,  PrimaryGeneratedColumn, OneToMany, ManyToOne, JoinColumn} from "typeorm";
import { OrderDetailsEntity } from "./order-details";
import { UserEntity } from "./user-entity";

@Entity("order")
export class OrderEntity {

    @PrimaryGeneratedColumn({ name: "Id", type: "smallint" })
    id: number;

    @Column({ name: "RequiredDate",  nullable: true  })
    requiredDate: Date;

    @Column({ name: "Address", length: 200 })
    address: string;

    @OneToMany(type => OrderDetailsEntity, order_details => order_details.order)
    order_details: OrderDetailsEntity[];

    @ManyToOne(type => UserEntity, user => user.orders)
    @JoinColumn({ name: "UserId" })
    user: UserEntity;
}

import {Entity, Column, PrimaryGeneratedColumn, OneToMany, ManyToOne, JoinColumn} from "typeorm";

import { OrderDetailsEntity } from "./order-details";

import { UserEntity } from "./user-entity";

@Entity("order")

export class OrderEntity {

@PrimaryGeneratedColumn({ name: "Id", type: "smallint" })

id: number;

@Column({ name: "RequiredDate", nullable: true })

requiredDate: Date;

@Column({ name: "Address", length: 200 })

address: string;

@OneToMany(type => OrderDetailsEntity, order_details => order_details.order)

order_details: OrderDetailsEntity[];

@ManyToOne(type => UserEntity, user => user.orders)

@JoinColumn({ name: "UserId" })

user: UserEntity;

}

Add a new TypeScript file ‘order-details-entity.ts’ in entities folder and add below code in it.

import {Entity, Column,  PrimaryGeneratedColumn, ManyToOne, JoinColumn} from "typeorm";
import { OrderEntity } from "./order-entity";
import { ProductEntity } from "./product-entity";

@Entity("order_details")
export class OrderDetailsEntity {

    @PrimaryGeneratedColumn({ name: "Id", type: "smallint" })
    id: number;

    @Column({ name: "UnitPrice", type: "decimal", precision: 8, scale: 2, nullable: true })
    unitPrice: number;

    @Column({ name: "Quantity", type: "smallint" })
    quantity: number;

    @Column({ name: "Discount", type: "decimal", precision: 2, scale: 2, nullable: true })
    discount: number;

    @ManyToOne(type => OrderEntity, order => order.order_details)
    @JoinColumn({ name: "OrderId" })
    order: OrderEntity;

    @ManyToOne(type => ProductEntity, product => product.order_details)
    @JoinColumn({ name: "ProductId" })
    product: ProductEntity;
}

import {Entity, Column, PrimaryGeneratedColumn, ManyToOne, JoinColumn} from "typeorm";

import { OrderEntity } from "./order-entity";

import { ProductEntity } from "./product-entity";

@Entity("order_details")

export class OrderDetailsEntity {

@PrimaryGeneratedColumn({ name: "Id", type: "smallint" })

id: number;

@Column({ name: "UnitPrice", type: "decimal", precision: 8, scale: 2, nullable: true })

unitPrice: number;

@Column({ name: "Quantity", type: "smallint" })

quantity: number;

@Column({ name: "Discount", type: "decimal", precision: 2, scale: 2, nullable: true })

discount: number;

@ManyToOne(type => OrderEntity, order => order.order_details)

@JoinColumn({ name: "OrderId" })

order: OrderEntity;

@ManyToOne(type => ProductEntity, product => product.order_details)

@JoinColumn({ name: "ProductId" })

product: ProductEntity;

}

Creating connection to database

Add a new file ‘app-config.ts’ in src/common folder and add below code in it.

import "reflect-metadata";
import {ConnectionOptions} from "typeorm";

 export let dbOptions: ConnectionOptions = {
    type: "mysql",
    host: "localhost",
    port: 3306,
    username: "root",
    password: "****",
    database: "hivestoredb",
    entities: [
         "./entities/*.js"
    ],
    synchronize: true,
}

import "reflect-metadata";

import {ConnectionOptions} from "typeorm";

export let dbOptions: ConnectionOptions = {

type: "mysql",

host: "localhost",

port: 3306,

username: "root",

password: "****",

database: "hivestoredb",

entities: [

"./entities/*.js"

synchronize: true,

}

Setup MySQL Database

We will be using MySQL workbench to create and interact with database. If you don’t have MySQL workbench installed, please install it from
here. Install both server and workbench. Open workbench and create a database ‘hivestoredb’.

Compile and Run the application

-> Compile the source file by running tsc command at the root folder and run the app.js from dist folder.
-> The tables are created automatically by TypeORM in the hivestoredb

You can download the source code from here.

In the next posts you will see data persistence and join operations using TypeORM.

TypeORM Express Application Using Repository Pattern – Part 2 – Persist Data into Tables

TypeORM Express Application Using Repository Pattern – Part 3 – How to perform InnerJoin

TypeORM Express Application Using Repository Pattern – Part 4 – How to do Cascade Save

3 comments on “TypeORM Express Application Using Repository Pattern – Part 1 – Create Tables with Relationships”:

Pingback: Creating a TypeScript NodeJS Express App using TypeORM and MySQL – Part2 – Hive
Pingback: Creating a TypeScript NodeJS Express App using TypeORM and MySQL – Part1 – Hive
Pingback: TypeORM Express Application Using Repository Pattern – Part 4 – How to do Cascade Save – Hive

Hive

TypeORM Express Application Using Repository Pattern – Part 1 – Create Tables with Relationships

Getting TypeScript

Create App Folder and package.json file

Configure TypeScript for the project

Install Packages

Install Typings

Project Structure

Create src/app.ts – Entry point of the app and add below code in it

Create employee-controller.ts in ‘src/controllers’ folder and add below code in it

Compile and Run the App

Install TypeORM package and dependencies

Creating connection to database

Setup MySQL Database

Compile and Run the application

Related

3 comments on “TypeORM Express Application Using Repository Pattern – Part 1 – Create Tables with Relationships”:

Leave a Reply Cancel reply

Getting TypeScript

Create App Folder and package.json file

Configure TypeScript for the project

Install Packages

Install Typings

Project Structure

Create src/app.ts – Entry point of the app and add below code in it

Create employee-controller.ts in ‘src/controllers’ folder and add below code in it

Compile and Run the App

Install TypeORM package and dependencies

Creating connection to database

Setup MySQL Database

Compile and Run the application

Share this:

Related

Related posts:

TypeORM Express Application Using Repository Pattern – Part 4 – How to do Cascade Save

TypeORM Express Application Using Repository Pattern – Part 3 – How to perform InnerJoin

TypeORM Express Application Using Repository Pattern – Part 2 – Persist Data into Tables

3 comments on “TypeORM Express Application Using Repository Pattern – Part 1 – Create Tables with Relationships”:

Leave a Reply Cancel reply