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:

   shell

   npm init -y

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

   shell

   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:

   typescript

   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:

   shell

   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.