Database

For this project, we will be using Postgres as a database and the ORM Sequelize. They are amongst the most popular. But you can find many other viable alternatives.

Run a local database

If you have installed docker, you can simply run the following command:

$ docker run -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=bookapp -p 5432:5432 postgres

Otherwise, you need to download and install it from the official website.

Find a GUI to interact with your database. Have a look at TablePlus (Free or $59) or PgAdmin (Open Source).

We should now be able to connect to our database:

Host: 127.0.0.1
Port: 5432
User: postgres
Password: postgres
Database: bookapp

Update the config and .env

config/shared.ts

export interface SharedConfig {
  logLevel: string;
  port: number;
  database: {
    dialect: "postgres";
    host: string;
    username: string;
    password: string;
    database: string;
    port: number;
    logging: boolean;
  };
}

export const sharedConfig: SharedConfig = {
  logLevel: process.env.LOG_LEVEL || "info", // we will come back to logs later
  port: parseInt(process.env.PORT || "1234"),
  database: {
    dialect: "postgres",
    host: process.env.PG_HOST || "",
    username: process.env.PG_USERNAME || "",
    password: process.env.PG_PASSWORD || "",
    database: process.env.PG_DATABASE || "",
    port: parseInt(process.env.PG_PORT || ""),
    logging: Boolean(parseInt(process.env.DB_LOGS || "0"))
  }
};

.env

CONFIG_ENV=local
LOG_LEVEL=debug
PORT=3000
PG_HOST=localhost
PG_USERNAME=postgres
PG_PASSWORD=postgres
PG_DATABASE=bookapp
PG_PORT=5432
DB_LOGS=1

Models

Update our dependencies

$ yarn add sequelize sequelize-typescript pg

You'll see the following message:

warning " > sequelize-typescript@1.0.0" has unmet peer dependency "@types/bluebird@*".
warning " > sequelize-typescript@1.0.0" has unmet peer dependency "@types/node@*".
warning " > sequelize-typescript@1.0.0" has unmet peer dependency "@types/validator@*".
warning " > sequelize-typescript@1.0.0" has unmet peer dependency "reflect-metadata@*".

Fix it by installing the required dependencies:

$ yarn add -D @types/bluebird @types/node @types/validator
$ yarn add reflect-metadata

Let's create a folder models to keep our files organised and create aLink model.

.
├── app.ts
├── models
│   ├── Link.ts
│   └── index.ts
└── sequelize.ts

models/Link.ts

import { Model, Column, Table, DataType } from "sequelize-typescript";

@Table({
  tableName: "link",
  comment: "Links saved by a user"
})
class Link extends Model<Link> {
  @Column({
    primaryKey: true,
    allowNull: true,
    defaultValue: DataType.UUIDV4
  })
  id: string;

  @Column({
    allowNull: false
  })
  url: string;
}

export { Link };

models/index.ts

export * from "./Link";

Create our Sequelize instance

sequelize.ts

import { Sequelize } from "sequelize-typescript";
import { config } from "./config";
import * as models from "./models";

export const sequelize = new Sequelize({
  ...config.database,
  logging: config.database.logging ? console.log : false
});

sequelize.addModels(Object.values(models));

and sync our model when we bootstrap the app

app.ts

import {sequelize} from './sequelize'

export const bootstrap = async () => {
  setFetch(fetch);
  await sequelize.sync()
};

$ ts-node src/index.ts
Executing (default): CREATE TABLE IF NOT EXISTS "link" ("id" VARCHAR(255) , "url" VARCHAR(255) NOT NULL, "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("id")); COMMENT ON TABLE "link" IS 'Links saved by a user';
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'link' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Server listing on port 3000

We should now see the link table in our database (try to refresh if you don't).

Migrations

Let's try to rename url to uriin our Link model and run the app again.

$ ts-node src/index.ts
Executing (default): CREATE TABLE IF NOT EXISTS "link" ("id" VARCHAR(255) , "uri" VARCHAR(255) NOT NULL, "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("id")); COMMENT ON TABLE "link" IS 'Links saved by a user';
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'link' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Server listing on port 3000

If we look in your UI, nothing changed.

This is because of CREATE TABLE IF NOT EXISTS. The table already exists and the statement gets ignored.

One way of solving this issue is to force sequelize to recreate all the tables with

sequelize.sync({ force: true });

Usingforce: truewill remove the existing data. Use it only for development.

This is fine while we are building database and we don't have any data in production. However, how do we deal with this once we're live? We write migrations

Umzung

Umzung is a framework agnostic migration tool for Node.js. We create migration files and umzug will run them one after the other. It will also store the last migration in a new table.

$ yarn add umzug @types/umzug

We will store our files in a new migrations folder and create our migration 001_rename_url_to_uri.ts.

.
├── app.ts
├── migrate.ts
└── migrations
       └── 001_rename_url_to_uri.ts

migrations/001_rename_url_to_uri.ts

import { QueryInterface } from 'sequelize';

module.exports = {
    // change the database schema
    async up(query: QueryInterface) {
        await query.renameColumn('link', 'url', 'uri');
    },

    // revert in case it goes wrong
    async down(query: QueryInterface) {
        await query.renameColumn('link', 'uri', 'url');
    },
};

We need a file to configure the migration

migrate.ts

import * as path from "path";
import { Sequelize } from "sequelize";
import * as Umzug from "umzug";

const createUmzug = (sequelize: Sequelize) => {
  const umzug = new Umzug({
    storage: "sequelize",
    storageOptions: {
      sequelize
    },

    migrations: {
      params: [sequelize.getQueryInterface()],
      path: path.join(__dirname, "./migrations"),
      pattern: /\.ts$/
    }
  });

  function logUmzugEvent(
    eventName: "migrating" | "migrated" | "reverting" | "reverted"
  ) {
    return (name: string) => {
      console.log(`${name} ${eventName}`);
    };
  }

  umzug.on("migrating", logUmzugEvent("migrating"));
  umzug.on("migrated", logUmzugEvent("migrated"));
  umzug.on("reverting", logUmzugEvent("reverting"));
  umzug.on("reverted", logUmzugEvent("reverted"));

  return umzug;
};

export { createUmzug };

And run the migration when we start the app

app.ts

export const bootstrap = async () => {
  setFetch(fetch);
  const umzug = createUmzug(sequelize);
  try {
    await umzug.up();
  } catch (e) {
    console.error("Migration failed.");
    await umzug.down();
    process.exit(1);
  }
  await sequelize.sync();
};

Run the app

$ ts-node src/index.ts
.
.
001_rename_url_to_uri migrated
.
.
Server listing on port 3000

Two things happened:

• url has been renamed to uri.

• A new table SequelizeMeta has been created.

SequelizeMeta will keep track of the latest migration.

database branch available on GitHub.