> For the complete documentation index, see [llms.txt](https://tutorial.specian.co.uk/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://tutorial.specian.co.uk/backend/database.md).

# Database

For this project, we will be using [Postgres](https://www.postgresql.org) as a database and the ORM [Sequelize](https://sequelize.org).\
They are amongst the most popular. But you can find [many other viable alternatives](https://github.com/numetriclabz/awesome-db).

### 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](https://www.postgresql.org/download/).

Find a GUI to interact with your database. Have a look at [TablePlus](https://tableplus.com) (Free or $59) or [PgAdmin](https://www.pgadmin.org) (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`

{% tabs %}
{% tab title="config/shared.ts" %}

```typescript
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"))
  }
};

```

{% endtab %}

{% tab title=".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
```

{% endtab %}
{% endtabs %}

### 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 a`Link` model.

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

{% tabs %}
{% tab title="models/Link.ts" %}

```typescript
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 };
```

{% endtab %}

{% tab title="models/index.ts" %}

```typescript
export * from "./Link";
```

{% endtab %}
{% endtabs %}

Create our Sequelize instance

{% code title="sequelize.ts" %}

```typescript
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));
```

{% endcode %}

and sync our model when we bootstrap the app

{% code title="app.ts" %}

```typescript
import {sequelize} from './sequelize'

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

{% endcode %}

```
$ 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).

![](https://2559875260-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LnRxZ2gbXvMdM70TAFz%2F-Lnha6P3H17o04N6a9ps%2F-LnhacEXU-EI4Tq7kuYz%2Fimage.png?alt=media\&token=fcd72106-c3a8-4342-b72c-619058e3ba13)

### Migrations

Let's try to rename `url` to `uri`in 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 });
```

{% hint style="danger" %}
Using`force: true`will remove the existing data. Use it only for development.
{% endhint %}

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](https://github.com/sequelize/umzug) 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
```

{% code title="migrations/001\_rename\_url\_to\_uri.ts" %}

```typescript
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');
    },
};
```

{% endcode %}

We need a file to configure the migration

{% code title="migrate.ts" %}

```typescript
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 };
```

{% endcode %}

And run the migration when we start the app

{% code title="app.ts" %}

```typescript
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();
};
```

{% endcode %}

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.

![](https://2559875260-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LnRxZ2gbXvMdM70TAFz%2F-Lnha6P3H17o04N6a9ps%2F-LnhjP9WyBCjYcbSLWkg%2Fimage.png?alt=media\&token=2cb68e0e-e823-4a3e-9f62-c0fcf7aabb3c)

![SequelizeMeta will keep track of the latest migration.](https://2559875260-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LnRxZ2gbXvMdM70TAFz%2F-Lnha6P3H17o04N6a9ps%2F-LnhjfDRnHBJA7MePndi%2Fimage.png?alt=media\&token=348ee5d9-2444-408b-911b-ef636b009c25)

{% hint style="info" %}
[`database`](https://github.com/florianherrengt/book-code/tree/database) branch available on GitHub.
{% endhint %}