"SQL query builder for Postgres, MSSQL, MySQL, MariaDB, SQLite3, Oracle, and Amazon Redshift"
Official web site https://knexjs.org
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'
}
}
};
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
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.
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: