DB schema updates with knex in Node.js

About

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

Official web site https://knexjs.org

Setup

      
yarn add knex
yarn add sqlite3
      
    

      
knex init
      
    

      
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:

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");
};

      
    

      
knex migrate:latest
      
    

      
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: