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
.
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
{ "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
1 |
npm install express body-parser --save |
Install Typings
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
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
1 2 3 4 5 |
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.
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’
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.
1 |
npm install typeorm --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
1 |
npm install @types/node --save |
Install MySQL database driver
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
:
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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”: