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:
