Knex is a query builder for JavaScript that allows developers to build SQL queries using a fluent and intuitive interface. It supports multiple database systems such as Postgres, MSSQL, MySQL, MariaDB, SQLite3, Oracle, and Amazon Redshift. In addition to query building, Knex also provides features for database migrations, making it easy to create and modify database tables using JavaScript code instead of raw SQL scripts. Knex is a powerful tool for managing database operations and simplifying database interactions in JavaScript applications.

To add Knex to your Node.js project, you can follow these steps.

Install dependencies:

yarn add knex

Make sure to install the driver that corresponds to your database of choice. Knex supports multiple database systems, such as Postgres, MSSQL, MySQL, MariaDB, SQLite3, Oracle, and Amazon Redshift. In my case I will use SQLite3.

yarn add sqlite3

Init the configuration

Once you have Knex and the database driver installed, you can start using Knex in your project. You’ll typically need to configure Knex to connect to your database. Create a knexfile.js file in your project’s root directory, and define the necessary configurations based on your environment settings.

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'
    }
  }

};

Let’s create now our first script with next to run a DB migration

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 creating all the init schema in one task, create one task per table and even create another separate task for constraints 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.

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

That’s it! You have successfully added Knex to your Node.js project. Remember to refer to the Knex documentation for more details on how to use Knex for various database operations.

You can find more information about Knex on its official website at https://knexjs.org.