TypeORM
Contents
Links
https://github.com/typeorm/typeorm
https://wanago.io/2019/01/28/typeorm-migrations-postgres/
https://medium.com/@shijin_nath/typescript-rest-api-with-express-js-mysql-and-typeorm-8331cea78b0c
What is TypeORM
TypeORM has a CLI tool that allow us to generate a base application already in TypeScript. To use this tool we need first to install typeORM as a global dependency:
Installation
npm install -g typeorm
Then go to the directory where you want to create a new project and run the command:
typeorm init --name MyProject --database mysql
or if you want to apply it to an already existing app
typeorm init
Important : this command changes tsconfig.json and scripts in package.json. Back it up before you run
and run
npm install
While installation is in progress, edit the ormconfig.json file and put your own database connection configuration options in there:
{
"type": "mysql",
"host": "localhost",
"port": 3306,
"username": "test",
"password": "test",
"database": "test",
"synchronize": true,
"logging": false,
"entities": [
"src/entity/**/*.ts"
],
"migrations": [
"src/migration/**/*.ts"
],
"subscribers": [
"src/subscriber/**/*.ts"
]
}
then run
npm start
Step-by-Step Guide
What are you expecting from ORM? First of all, you are expecting it will create database tables for you and find / insert / update / delete your data without the pain of having to write lots of hardly maintainable SQL queries. This guide will show you how to setup TypeORM from scratch and make it do what you are expecting from an ORM.
Create a model
Working with a database starts from creating tables. How do you tell TypeORM to create a database table? The answer is — through the models. Your models in your app are your database tables. For example, you have a Photo model:
export class Photo {
id: number;
name: string;
description: string;
filename: string;
views: number;
isPublished: boolean;
}
And you want to store photos in your database. To store things in the database, first you need a database table, and database tables are created from your models. Not all models, but only those you define as entities.
Create an entity
Entity is your model decorated by an @Entity decorator. A database table will be created for such models. You work with entities everywhere with TypeORM. You can load/insert/update/remove and perform other operations with them. Let’s make our Photo model as an entity:
import {Entity} from "typeorm";
@Entity()
export class Photo {
id: number;
name: string;
description: string;
filename: string;
views: number;
isPublished: boolean;
}
Now, a database table will be created for the Photo entity and we'll be able to work with it anywhere in our app. We have created a database table, however what table can exist without columns? Let's create a few columns in our database table.
Adding table columns
To add database columns, you simply need to decorate an entity’s properties you want to make into a column with a @Column decorator.
import {Entity, Column} from "typeorm";
@Entity()
export class Photo {
@Column()
id: number;
@Column()
name: string;
@Column()
description: string;
@Column()
filename: string;
@Column()
views: number;
@Column()
isPublished: boolean;
}
Creating a primary column
Each entity must have at least one primary key column. This is a requirement and you can’t avoid it. To make a column a primary key, you need to use @PrimaryColumn decorator.
import {Entity, Column, PrimaryColumn} from "typeorm";
@Entity()
export class Photo {
@PrimaryColumn()
id: number;
@Column()
name: string;
@Column()
description: string;
@Column()
filename: string;
@Column()
views: number;
@Column()
isPublished: boolean;
}
Creating an auto generated column
Now, let’s say you want your id column to be auto-generated (this is known as auto-increment / sequence / serial / generated identity column). To do that, you need to change the @PrimaryColumn decorator to a @PrimaryGeneratedColumn decorator:
import {Entity, Column, PrimaryGeneratedColumn} from "typeorm";
@Entity()
export class Photo {
@PrimaryGeneratedColumn()
id: number;
@Column()
name: string;
@Column()
description: string;
@Column()
filename: string;
@Column()
views: number;
@Column()
isPublished: boolean;
}
Column data types
Next, let’s fix our data types. By default, string is mapped to a varchar(255)-like type (depending on the database type). Number is mapped to a integer-like type (depending on the database type). We don’t want all our columns to be limited varchars or integers. Let’s setup correct data types:
import {Entity, Column, PrimaryGeneratedColumn} from "typeorm";
@Entity()
export class Photo {
@PrimaryGeneratedColumn()
id: number;
@Column({
length: 100
})
name: string;
@Column("text")
description: string;
@Column()
filename: string;
@Column("double")
views: number;
@Column()
isPublished: boolean;
}
Column types are database-specific. You can set any column type your database supports. More information on supported column types can be found here. === Creating a connection to the database Now, when our entity is created, let’s create an index.ts (or app.ts whatever you call it) file and set up our connection there:
import "reflect-metadata";
import {createConnection} from "typeorm";
import {Photo} from "./entity/Photo";
createConnection({
type: "mysql",
host: "localhost",
port: 3306,
username: "root",
password: "admin",
database: "test",
entities: [
Photo
],
synchronize: true,
logging: false
}).then(connection => {
// here you can start to work with your entities
}).catch(error => console.log(error));
We are using MySQL in this example, but you can use any other supported database. To use another database, simply change the type in the options to the database type you are using: mysql, mariadb, postgres, cockroachdb, sqlite, mssql, oracle, cordova, nativescript, react-native, expo, or mongodb. Also make sure to use your own host, port, username, password and database settings. We added our Photo entity to the list of entities for this connection. Each entity you are using in your connection must be listed there.
Setting synchronize makes sure your entities will be synced with the database, every time you run the application.
Loading all entities from the directory
Later, when we create more entities we need to add them to the entities in our configuration. This is not very convenient, so instead we can set up the whole directory, from where all entities will be connected and used in our connection:
import {createConnection} from "typeorm";
createConnection({
type: "mysql",
host: "localhost",
port: 3306,
username: "root",
password: "admin",
database: "test",
entities: [
__dirname + "/entity/*.js"
],
synchronize: true,
}).then(connection => {
// here you can start to work with your entities
}).catch(error => console.log(error));
But be careful with this approach. If you are using ts-node then you need to specify paths to .ts files instead. If you are using outDir then you'll need to specify paths to .js files inside outDir directory. If you are using outDir and when you remove or rename your entities make sure to clear outDir directory and re-compile your project again, because when you remove your source .ts files their compiled .js versions aren't removed from output directory and still are loaded by TypeORM because they are present in the outDir directory.
Using TypeORM CLI
We use the TypeORM CLI installed in the node_modules directory. To do this, we need an additional script in our package.json file:
"scripts": {
"dev": "ts-node ./src/server.ts",
"lint": "tslint -p tsconfig.json -c tslint.json",
"typeorm:cli": "ts-node ./node_modules/typeorm/cli -f src/ormconfig.ts"
}
You can also install typeorm globally, but it won’t be able to read the typescript config file out of the box
Due to some issues, we need to use ts-node here and export our CLI config in a CommonJS way.
src/ormconfig.ts
import { ConnectionOptions } from 'typeorm';
const config: ConnectionOptions = {
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}',
],
cli: {
migrationsDir: 'src/migration',
}
};
export = config;
Now we can run TypeORM CLI through NPM scripts. You can add any additional parameters after the -- characters.
Creating migrations
Let’s create our first migration:
npm run typeorm:cli -- migration:create -n UserFullName
It generates a file for us that contains the current timestamp when the migration was generated. It looks like this:
src/migrations/1548548890100-UserFullName.ts
import { MigrationInterface, QueryRunner } from 'typeorm';
export class UserFullName1548548890100 implements MigrationInterface {
public async up(queryRunner: QueryRunner): Promise<any> {
}
public async down(queryRunner: QueryRunner): Promise<any> {
}
}
You can also create the migration files by hand
There are two methods that we need to write:
the up method performs the migration
the down method reverts it
When it comes to the QueryRunner, you can either build the query by hand or use the migration API. Let’s go with the first option for starters:
src/migrations/1548548890100-UserFullName.ts
import { MigrationInterface, QueryRunner } from 'typeorm';
export class UserFullName1548548890100 implements MigrationInterface {
public async up(queryRunner: QueryRunner): Promise<any> {
await queryRunner.query(`ALTER TABLE "user" RENAME "name" to "fullName"`);
}
public async down(queryRunner: QueryRunner): Promise<any> {
await queryRunner.query(`ALTER TABLE "user" RENAME "fullName" to "name"`);
}
}
Running the migrations with the CLI Once we got that down, we can execute it using the CLI:
npm run typeorm:cli -- migration:run
We are presented with the result in the console:
query: SELECT * FROM "information_schema"."tables" WHERE "table_schema" = current_schema() AND "table_name" = 'migrations'
query: SELECT * FROM "migrations" "migrations"
0 migrations are already loaded in the database.
1 migrations were found in the source code.
1 migrations are new migrations that needs to be executed.
query: START TRANSACTION
query: ALTER TABLE "user" RENAME "name" to "fullName"
query: INSERT INTO "migrations"("timestamp", "name") VALUES ($1, $2) -- PARAMETERS: [1548548890100,"UserFullName1548548890100"]
Migration UserFullName1548548890100 has been executed successfully.
query: COMMIT
This runs the migrations in a sequence ordered by their timestamps. In this process, queries written in our up methods are executed.
Running the migrations in the code
Aside from running the migrations using the CLI, you can do it from within your application. Thanks to that, TypeORM always checks if there are any migrations that it needs to run at the start.
src/server.ts
import 'dotenv/config';
import 'reflect-metadata';
import { createConnection } from 'typeorm';
import AddressController from './address/address.controller';
import App from './app';
import AuthenticationController from './authentication/authentication.controller';
import CategoryController from './category/category.controller';
import * as config from './ormconfig';
import PostController from './post/post.controller';
import validateEnv from './utils/validateEnv';
validateEnv();
(async () => {
try {
const connection = await createConnection(config);
await connection.runMigrations();
} catch (error) {
console.log('Error while connecting to the database', error);
return error;
}
const app = new App(
[
new PostController(),
new AuthenticationController(),
new AddressController(),
new CategoryController(),
],
);
app.listen();
})();
If you are not doing that, make sure to run the migrations using the CLI when you are in the process of deployment.
Reverting migrations
If you decide that you need to cancel the changes that are applied it, you can do so with the revert command:
npm run typeorm:cli -- migration:revert
query: SELECT * FROM "information_schema"."tables" WHERE "table_schema" = current_schema() AND "table_name" = 'migrations' query: SELECT * FROM "migrations" "migrations" 1 migrations are already loaded in the database. UserFullName1548548890100 is the last executed migration. It was executed on Sun Jan 27 2019 01:28:10 GMT+0100 (CET). Now reverting it... query: START TRANSACTION query: ALTER TABLE "user" RENAME "fullName" to "name" query: DELETE FROM "migrations" WHERE "timestamp" = $1 AND "name" = $2 -- PARAMETERS: [1548548890100,"UserFullName1548548890100"] Migration UserFullName1548548890100 has been reverted successfully. query: COMMIT
The command above executes the down method in the latest performed migration. If you need to revert more than one migration, you need to call revert multiple times.
The TypeORM knows the current state of migrations in your database thanks to the migrations table. It holds the data about migrations that are already completed. When we use the revert functionality, TypeORM knows what migration has been done last because of the timestamp that it holds in the migrations table.
Generating migrations
Thankfully, TypeORM can automatically generate migration files with the changes to your schema. After reverting our migration in the previous paragraph, we can let the TypeORM handle the writing of the migration for us.
npm run typeorm:cli -- migration:generate -n UserFullName
src/migrations/1548598555048-UserFullName.ts
import { MigrationInterface, QueryRunner } from 'typeorm';
export class UserFullName1548598555048 implements MigrationInterface {
public async up(queryRunner: QueryRunner): Promise<any> {
await queryRunner.query('ALTER TABLE "user" RENAME COLUMN "name" TO "fullName"');
}
public async down(queryRunner: QueryRunner): Promise<any> {
await queryRunner.query('ALTER TABLE "user" RENAME COLUMN "fullName" TO "name"');
}
}
If you look closely, you can see that the query is slightly different than the one that we wrote. It is a good idea to check it out first before running it. To make the work of the TypeORM CLI easier, generate migrations after each change you made to your entities, so it would have to generate relatively simple queries.
Using migration API
When writing your migration by hand, you don’t have to create SQL queries necessarily. You can use the migration API, that has a set of functions that can make changes in the schema. Let’s implement our example using it.
src/migrations/1548598555048-UserFullName.ts
import { MigrationInterface, QueryRunner } from 'typeorm';
export class UserFullName1548600643971 implements MigrationInterface {
public async up(queryRunner: QueryRunner): Promise<any> {
await queryRunner.renameColumn('user', 'name', 'fullName');
}
public async down(queryRunner: QueryRunner): Promise<any> {
await queryRunner.renameColumn('user', 'fullName', 'name');
}
}
npm run typeorm:cli -- migration:run
query: SELECT * FROM "information_schema"."tables" WHERE "table_schema" = current_schema() AND "table_name" = 'migrations'
query: SELECT * FROM "migrations" "migrations"
0 migrations are already loaded in the database.
1 migrations were found in the source code.
1 migrations are new migrations that needs to be executed.
query: START TRANSACTION
query: SELECT * FROM current_schema()
query: SELECT * FROM "information_schema"."tables" WHERE ("table_schema" = 'public' AND "table_name" = 'user')
query: SELECT *, ("udt_schema" || '.' || "udt_name")::"regtype" AS "regtype" FROM "information_schema"."columns" WHERE ("table_schema" = 'public' AND "table_name" = 'user')
query: SELECT "ns"."nspname" AS "table_schema", "t"."relname" AS "table_name", "cnst"."conname" AS "constraint_name", CASE "cnst"."contype" WHEN 'x' THEN pg_get_constraintdef("cnst"."oid", true) ELSE "cnst"."consrc" END AS "expression", CASE "cnst"."contype" WHEN 'p' THEN 'PRIMARY' WHEN 'u' THEN 'UNIQUE' WHEN 'c' THEN 'CHECK' WHEN 'x' THEN 'EXCLUDE' END AS "constraint_type", "a"."attname" AS "column_name" FROM "pg_constraint" "cnst" INNER JOIN "pg_class" "t" ON "t"."oid" = "cnst"."conrelid" INNER JOIN "pg_namespace" "ns" ON "ns"."oid" = "cnst"."connamespace" LEFT JOIN "pg_attribute" "a" ON "a"."attrelid" = "cnst"."conrelid" AND "a"."attnum" = ANY ("cnst"."conkey") WHERE "t"."relkind" = 'r' AND (("ns"."nspname" = 'public' AND "t"."relname" = 'user'))
query: SELECT "ns"."nspname" AS "table_schema", "t"."relname" AS "table_name", "i"."relname" AS "constraint_name", "a"."attname" AS "column_name", CASE "ix"."indisunique" WHEN 't' THEN 'TRUE' ELSE'FALSE' END AS "is_unique", pg_get_expr("ix"."indpred", "ix"."indrelid") AS "condition", "types"."typname" AS "type_name" FROM "pg_class" "t" INNER JOIN "pg_index" "ix" ON "ix"."indrelid" = "t"."oid" INNER JOIN "pg_attribute" "a" ON "a"."attrelid" = "t"."oid" AND "a"."attnum" = ANY ("ix"."indkey") INNER JOIN "pg_namespace" "ns" ON "ns"."oid" = "t"."relnamespace" INNER JOIN "pg_class" "i" ON "i"."oid" = "ix"."indexrelid" INNER JOIN "pg_type" "types" ON "types"."oid" = "a"."atttypid" LEFT JOIN "pg_constraint" "cnst" ON "cnst"."conname" = "i"."relname" WHERE "t"."relkind" = 'r' AND "cnst"."contype" IS NULL AND (("ns"."nspname" = 'public' AND "t"."relname" = 'user'))
query: SELECT "con"."conname" AS "constraint_name", "con"."nspname" AS "table_schema", "con"."relname" AS "table_name", "att2"."attname" AS "column_name", "ns"."nspname" AS "referenced_table_schema", "cl"."relname" AS "referenced_table_name", "att"."attname" AS "referenced_column_name", "con"."confdeltype" AS "on_delete", "con"."confupdtype" AS "on_update" FROM ( SELECT UNNEST ("con1"."conkey") AS "parent", UNNEST ("con1"."confkey") AS "child", "con1"."confrelid", "con1"."conrelid", "con1"."conname", "con1"."contype", "ns"."nspname", "cl"."relname", CASE "con1"."confdeltype" WHEN 'a' THEN 'NO ACTION' WHEN 'r' THEN 'RESTRICT' WHEN 'c' THEN 'CASCADE' WHEN 'n' THEN 'SET NULL' WHEN 'd' THEN 'SET DEFAULT' END as "confdeltype", CASE "con1"."confupdtype" WHEN 'a' THEN 'NO ACTION' WHEN 'r' THEN 'RESTRICT' WHEN 'c' THEN 'CASCADE' WHEN 'n' THEN 'SET NULL' WHEN 'd' THEN 'SET DEFAULT' END as "confupdtype" FROM "pg_class" "cl" INNER JOIN "pg_namespace" "ns" ON "cl"."relnamespace" = "ns"."oid" INNER JOIN "pg_constraint" "con1" ON "con1"."conrelid" = "cl"."oid" WHERE "con1"."contype" = 'f' AND (("ns"."nspname" = 'public' AND "cl"."relname" = 'user')) ) "con" INNER JOIN "pg_attribute" "att" ON "att"."attrelid" = "con"."confrelid" AND "att"."attnum" = "con"."child" INNER JOIN "pg_class" "cl" ON "cl"."oid" = "con"."confrelid" INNER JOIN "pg_namespace" "ns" ON "cl"."relnamespace" = "ns"."oid" INNER JOIN "pg_attribute" "att2" ON "att2"."attrelid" = "con"."conrelid" AND "att2"."attnum" = "con"."parent"
query: ALTER TABLE "user" RENAME COLUMN "name" TO "fullName"
query: INSERT INTO "migrations"("timestamp", "name") VALUES ($1, $2) -- PARAMETERS: [1548600643971,"UserFullName1548600643971"]
Migration UserFullName1548600643971 has been executed successfully.
query: COMMIT
The above code generates quite a big query, so you might be better off with writing your SQL query if the performance is one of your concerns.
