Photo by benjamin lehman on Unsplash
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.