# Backend with Fastify - Part 3 (Setting Up a PostgreSQL Database with Knex)

Continuing from [Part 2](https://blog.arjandhakal.com.np/backend-with-fastify-part-2-creating-rest-apis), our next step is to set up the database for our application. To follow along, you can clone this [branch](https://github.com/arjandhakal/fav-mov/tree/part-2). The complete code for this part can be found [here](https://github.com/arjandhakal/fav-mov/tree/part-3).

To keep things straightforward for our purpose, we'll create two tables: one to store user data and another for the users' favorite movie list.  In order to create these tables, we will be utilizing a library called Knex, which is a SQL query builder. Knex also provides features for database migration.

Before we get started with the actual database setup, we need to install a couple of packages:

```typescript
npm i knex pg dotenv
```

Once the installation is complete, we're ready to move on to configuring Knex to work with our PostgreSQL database. We'll do this by setting up a 'knexfile.ts' which contains all the necessary configuration details.

```typescript
require('ts-node/register')
require('dotenv').config()

module.exports = {
  development: {
    client: 'pg',
    connection: {
      connectionString: process.env.DATABASE_URL,
    },
    pool: {
      min: 2,
      max: 10,
    },
    migrations: {
      directory: __dirname + '/migrations',
      tableName: 'knex_migrations',
    },
    seeds: {
      directory: __dirname + '/seeds/development',
    },
  },
  testing: {
    client: 'pg',
    connection: {
      connectionString: 'postgres://postgres:password@localhost:5433/fav_mov',
    },
    pool: {
      min: 2,
      max: 10,
    },
    migrations: {
      directory: __dirname + '/migrations',
      tableName: 'knex_migrations',
    },
    seeds: {
      directory: __dirname + '/seeds/testing',
    },
  },
}
```

In the provided code, we have defined the database configuration using the Knex library. The configuration object specifies two environments: `development` and `testing`.

The `development` environment is used for local development and it is configured to connect to the database using the `process.env.DATABASE_URL` value, which can be set in a `.env` file.

If the linter is causing issues, you can add `knexfile.ts` to the `.eslintignore` file to prevent it from being checked.

```typescript
knexfile.ts
.eslintrc.js
migrations
```

Next, let's update our `package.json` file to include scripts that will make it easier to run database migrations.

```typescript
"scripts": {
    // previous
 
    "migrate:make": "knex migrate:make",
    "migrate:latest": "knex migrate:latest",
    "migrate:rollback": "knex migrate:rollback",
    "migrate:up": "knex migrate:up",
    "migrate:down": "knex migrate:down",
    "migrate:list": "knex migrate:list"

}
```

By adding these scripts, we create convenient shorthand commands to manage our database migrations:

* `migrate:make`: Create a new migration file.
    
* `migrate:latest`: Run all migrations that have not yet been applied to the database.
    
* `migrate:rollback`: Roll back the last set of migrations performed.
    
* `migrate:up`: Run the next migration that has not yet been run.
    
* `migrate:down`: Roll back the last migration that was run.
    
* `migrate:list`: List all the completed and pending migrations.
    

To create a new migration for the `users` table, you can run the following command:

```typescript
npm run migrate:make add_users_table
```

By default, this command will use the development configuration from the `knexfile.js` file. After running the command, a new migration file will be created in the `migrations` folder. The filename will be something similar to `20231108145106_add_users_table.ts`. You can open this file and update its contents as follows:

```typescript
import { Knex } from 'knex'

export async function up(knex: Knex): Promise<void> {
  return knex.schema.createTable('users', function (table) {
    table.increments('id').primary()
    table.string('email').notNullable().unique()
    table.string('password').notNullable()
    table.string('salt').notNullable()
  })
}

export async function down(knex: Knex): Promise<void> {
  return knex.schema.dropTable('users')
}
```

In this migration file, the `up` function describes the changes needed to create the `users` table. It uses the Knex schema builder to define the table's columns and their properties.

The `down` function specifies how to revert the changes made by the `up` function, which in this case is simply dropping the `users` table.

To proceed with the migration, please make sure that you have created a database named `fav_mov` in PostgreSQL.

Next, create a `.env` file in your project directory and add the following line, adjusting the value to match your PostgreSQL database connection details:

```typescript
DATABASE_URL=postgres://postgres@localhost/fav_mov
```

Once the `.env` file is set up, you can run the migration using the following command:

```typescript
npm run migrate:latest
```

This command will execute the latest migration and create the `users` table in your PostgreSQL database.

To create the `movies` table, run the following command:

```typescript
npm run migrate:make add_movies_table
```

This will generate a new migration file. Open the newly created file and update its contents as follows:

```typescript
import { Knex } from 'knex'

export async function up(knex: Knex): Promise<void> {
  return knex.schema.createTable('movies', table => {
    table.increments('id').primary()
    table.string('title').notNullable()
    table.text('description')
    table
      .integer('user_id')
      .unsigned()
      .references('id')
      .inTable('users')
      .onDelete('CASCADE')
      .index()
  })
}

export async function down(knex: Knex): Promise<void> {
  return knex.schema.dropTableIfExists('movie')
}
```

It defines the columns for `id`, `title`, `description`, and `user_id`. The `user_id` column is a foreign key that references the `id` column in the `users` table. It also sets up cascading deletion, meaning that if a user is deleted, all associated movies will also be deleted. Additionally, an index is created on the `user_id` column.

```typescript
npm run migrate:latest
```

With this, you should now have the `movies` table in your PostgreSQL database. In the next part, we will explore how to seed data into the database.