Database Migrations

Database migrations are a crucial aspect of managing database schemas and data changes in a software project. Knex.js is a popular query builder and migration tool that provides an easy and efficient way to handle database migrations. This document serves as a guide to using Knex.js with TypeScript to manage database migrations effectively.

based on https://knexjs.org/guide/migrations.html

Prerequisites

Before you begin working with Knex.js and TypeScript, ensure that you have the following prerequisites:

1. Node.js and npm (Node Package Manager) installed on your machine.
2. A database system supported by Knex.js, such as PostgreSQL, MySQL, or SQLite.

Installation

To get started, follow these steps to install Knex.js and TypeScript in your project:

1. Create a new directory for your project and navigate to it using a terminal.

2. Initialize a new Node.js project by running the following command:

   npm init -y

3. Install the required dependencies by executing the following commands:

   npm install knex
   npm install --save-dev knex
   npm install --save-dev typescript

4. Create a knexfile.ts file in the project's root directory with the following content:

import type { Knex } from 'knex'

const dotenv = require('dotenv')
dotenv.config()

const config: { [key: string]: Knex.Config } = {
  development: {
    client: 'mysql',
    connection: {
      host: process.env.APP_DB_HOST || process.env.DB_HOST,
      port: +(process.env.APP_DB_PORT || process.env.DB_PORT),
      database: process.env.APP_DB_NAME || process.env.DB_NAME,
      user: process.env.APP_DB_USER || process.env.DB_USER,
      password: process.env.APP_DB_PASSWORD || process.env.DB_PASSWORD
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      extension: 'ts',
      tableName: 'knex_migrations'
    }
  },

  production: {
    client: 'mysql',
    connection: {
      host: process.env.APP_DB_HOST || process.env.DB_HOST,
      port: +(process.env.APP_DB_PORT || process.env.DB_PORT),
      database: process.env.APP_DB_NAME || process.env.DB_NAME,
      user: process.env.APP_DB_USER || process.env.DB_USER,
      password: process.env.APP_DB_PASSWORD || process.env.DB_PASSWORD
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      extension: 'ts',
      tableName: 'knex_migrations'
    }
  }

}

module.exports = config

Creating Migrations

Now that you have set up the project, let's create a migration using Knex.js and TypeScript:

1. create migration file knex migrate:make create_users_table -x ts.

2. Open the newly created migration file and add the following code:

   import * as Knex from 'knex';
   
   export async function up(knex: Knex): Promise<void> {
     await knex.schema.createTable('users', (table) => {
       table.increments('id').primary();
       table.string('name');
       table.string('email').unique();
       table.timestamps(true, true);
     });
   }
   
   export async function down(knex: Knex): Promise<void> {
     await knex.schema.dropTable('users');
   }

   This example migration creates a users table with id, name, email, and timestamps columns.

3. Open a terminal and run the following command to migrate the database:

   npx knex migrate:latest --env production

   Knex.js will execute the migration and create the users table in your configured database.

Rollbacks

In case you need to rollback a migration, you can use the following command:

npx knex migrate:rollback --env production

This will revert the last executed migration.

Additional Operations

Knex.js provides many other migration operations, including adding columns, modifying columns, creating indexes, and executing raw SQL statements. You can find more information and examples in the Knex.js documentation.

Conclusion

In this document, you learned how to set up Knex.js with TypeScript for managing database migrations. You also created a sample migration file and executed it using Knex.js. With Knex.js and TypeScript, you can easily maintain and evolve your database schema as your project grows.