# 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 {
@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).
### 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.
{% hint style="info" %}
[`database`](https://github.com/florianherrengt/book-code/tree/database) branch available on GitHub.
{% endhint %}
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://tutorial.specian.co.uk/backend/database.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.