Reading Time: 2 minutes

About

Knex is a query builder and among other things, it’s an awesome tool to run DB migrations to create tables and modify them as you usually do with SQL scripts

“SQL query builder for Postgres, MSSQL, MySQL, MariaDB, SQLite3, Oracle, and Amazon Redshift”

Official web site https://knexjs.org

Setup

Install dependencies

      
yarn add knex
yarn add sqlite3
      
    

Init the configuration

      
knex init
      
    

It will create the following knexfile.js

      
module.exports = {

  development: {
    client: 'sqlite3',
    connection: {
      filename: './dev.sqlite3'
    },
    debug: true
  },

  staging: {
    client: 'postgresql',
    connection: {
      database: 'my_db',
      user:     'username',
      password: 'password'
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: 'knex_migrations'
    }
  },

  production: {
    client: 'postgresql',
    connection: {
      database: 'my_db',
      user:     'username',
      password: 'password'
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: 'knex_migrations'
    }
  }

};
      
    

Create our first Knex script

      
npx knex migrate:make create_table_users

As a good practice, create small tasks instead of one task with all the schema. This is useful to detect problems. For example avoid create all the init schema in one task, create one task per table and even create another separate task for contrainsts such as foreign keys

Use descriptive names such as “create_table_users”. Use also prefix for your actions such as “create_table_…” or “create_column_…”

Result:

migrations/20211005153317_create_table_users.js

Open the file and paste this:

      

exports.up = function(knex) {
    return knex.schema.createTable("users", (tb) => {
        tb.increments("id");
        tb.string("username", 100).notNullable();
        tb.string("email", 100).notNullable();
        tb.string("password", 100).notNullable();
    });
};

exports.down = function(knex) {
    return knex.schema.dropTable("users");
};

      
    

Save it and run:

      
knex migrate:latest
      
    

Or select a different environment

      
knex migrate:latest --env production
      
    

Handy commands

      
npx knex migrate:latest # To run all pending migrations
npx knex migrate:up # To run the next pending migration
npx knex migrate:down # To roll back the most recent migration
npx knex migrate:list # To show current state of migrations.
    
    

And If you are using Webstorm…

Webstorm or any other tool from IntelliJ family

Configure a client to explore the DB

The DB will look like this

The project structure will look like this: