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

Continuing from Part 2, our next step is to set up the database for our application. To follow along, you can clone this branch. The complete code for this part can be found here.

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:

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.

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.

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.

"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:

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:

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:

DATABASE_URL=postgres://postgres@localhost/fav_mov

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

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:

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:

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.

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.