packages/core/docs/recipes/knex-migration.md
# Using Knex Migrations
In the [previous recipe](./knex.md) we manually create database table.
Generally, you don't want to manage database structure changes manually, because it's not so effective.
In this recipe, you will learn how to configure your `Microfleet` service to manage your database structure using migrations.
## Prerequisites
* Microfleet service with configured `knex` plugin. For more information please refer [this recipe](./knex.md)
* Installed `PostgreSQL` server.
* PostgreSQL Table structure and Datatypes knowledge.
## Service configuration
First of all, we need to enable migrations in our microservice. The `Knex` plugin provides migration handling features by default, but they are disabled globally.
Let's enable them by adding the following lines into your main microservice class:
```javascript
class DemoApp extends Microfleet {
constructor(opts = {}) {
super(merge({}, config, opts))
// ...
this.addConnector(ConnectorsTypes.migration, () => (
this.migrate('knex', {
directory: `${__dirname}/../migrations/knex
})
), 'knex-migration')
// ...
}
}
```
By adding these lines of code, we explicitly enable database migration for the PostgreSQL and instruct the `Knex` plugin to look for migrations from `./migrations/knex` directory.
As a second argument of `this.migrate()` function you can pass configuration parameters described in [`Knex Migration` manual](http://knexjs.org/#Migrations).
Another option is to define migration configuration in your `configs/knex.js`:
```javascript
// configs/knex.js
module.exports = {
knex: {
client: 'pg',
connection: {
// connection settings
},
migrations: {
directory: './path/to/migrations/directory',
},
},
}
```
## Create table migration
For advanced information about Migrations, please read [`Knex Migration` manual](http://knexjs.org/#Migrations).
After we successfully configured database migration, we can start creating some migrations.
Let's create a migration that creates our first table:
```javascript
// migrations/knex/01_todo.js
exports.up = function todoTableCreate(knex) {
return knex.schema
.createTable('todos', (table) => {
table.increments('id')
table.string('name', 255).notNullable()
table.string('description', 255).notNullable()
table.boolean('state').defaultTo(false)
table.timestamp('created').defaultTo(knex.fn.now())
})
}
exports.down = function todoTableDelete(knex) {
return knex.schema
.dropTable('todos')
}
exports.config = { transaction: false }
```
Now, if you start your microservice, you will see that the new table has been generated automatically.
```console
user@$: DEBUG=* yarn mfleet
knex:bindings undefined trx2 +5ms
knex:query update "knex_migrations_lock" set "is_locked" = ? where "is_locked" = ? trx2 +3ms
knex:bindings [ 1, 0 ] trx2 +3ms
knex:query COMMIT; trx2 +1ms
knex:bindings undefined trx2 +1ms
knex:tx trx2: releasing connection +7ms
knex:client releasing connection to pool: __knexUid1 +7ms
knex:client acquired connection from pool: __knexUid1 +1ms
knex:query select max("batch") as "max_batch" from "knex_migrations" trx2 +3ms
knex:bindings [] trx2 +3ms
knex:client releasing connection to pool: __knexUid1 +1ms
knex:client acquired connection from pool: __knexUid1 +1ms
knex:query create table "todos" ("id" serial primary key, "name" varchar(255) not null, "description" varchar(255) not null, "state" boolean default '0', "created" timestamptz default CURRENT_TIMESTAMP) trx2 +3ms
knex:bindings [] trx2 +3ms
knex:client releasing connection to pool: __knexUid1 +10ms
knex:client acquired connection from pool: __knexUid1 +1ms
knex:query insert into "knex_migrations" ("batch", "migration_time", "name") values (?, ?, ?) trx2 +10ms
knex:bindings [ 1, 2020-01-16T14:43:57.090Z, '01-todo.js' ] trx2 +11ms
knex:client releasing connection to pool: __knexUid1 +4ms
knex:client acquired connection from pool: __knexUid1 +0ms
knex:query update "knex_migrations_lock" set "is_locked" = ? trx2 +4ms
knex:bindings [ 0 ] trx2 +4ms
knex:client releasing connection to pool: __knexUid1 +3ms
```
## CRUD Table fields
If you need to add or delete some fields or create new indices, you can create the second migration:
```javascript
// migrations/knex/02-todo-restructure.js
exports.up = function todoTableAlter(knex) {
return knex.schema
.alterTable('todos', (table) => {
table.string('description', 255).alter()
table.boolean('state').defaultTo(true).alter()
table.string('extradata', 255)
})
}
exports.down = function todoTableDelete(knex) {
return knex.schema
.alterTable('todos', (table) => {
table.string('description', 255).notNullable().alter()
table.boolean('state').defaultTo(false).alter()
table.dropColumn('extradata')
})
}
exports.config = { transaction: false }
```
Now, you can start your service with the DEBUG variable, and you will see that our table structure was updated successfully.
```console
user@$: DEBUG=* yarn mfleet
....
knex:client acquired connection from pool: __knexUid1 +4ms
knex:query BEGIN; trx2 +5ms
knex:bindings undefined trx2 +5ms
knex:query update "knex_migrations_lock" set "is_locked" = ? where "is_locked" = ? trx2 +3ms
knex:bindings [ 1, 0 ] trx2 +3ms
knex:query COMMIT; trx2 +2ms
knex:bindings undefined trx2 +2ms
knex:tx trx2: releasing connection +8ms
knex:client releasing connection to pool: __knexUid1 +7ms
knex:client acquired connection from pool: __knexUid1 +0ms
knex:query select max("batch") as "max_batch" from "knex_migrations" trx2 +3ms
knex:bindings [] trx2 +3ms
knex:client releasing connection to pool: __knexUid1 +2ms
knex:client acquired connection from pool: __knexUid1 +0ms
knex:query alter table "todos" add column "extradata" varchar(255) trx2 +3ms
knex:bindings [] trx2 +3ms
knex:query alter table "todos" alter column "description" drop default trx2 +2ms
knex:bindings [] trx2 +2ms
knex:query alter table "todos" alter column "description" drop not null trx2 +2ms
knex:bindings [] trx2 +2ms
knex:query alter table "todos" alter column "description" type varchar(255) using ("description"::varchar(255)) trx2 +2ms
knex:bindings [] trx2 +2ms
knex:query alter table "todos" alter column "state" drop default trx2 +2ms
knex:bindings [] trx2 +2ms
knex:query alter table "todos" alter column "state" drop not null trx2 +2ms
knex:bindings [] trx2 +2ms
knex:query alter table "todos" alter column "state" type boolean using ("state"::boolean) trx2 +2ms
knex:bindings [] trx2 +2ms
knex:query alter table "todos" alter column "state" set default '1' trx2 +2ms
knex:bindings [] trx2 +2ms
knex:client releasing connection to pool: __knexUid1 +18ms
knex:client acquired connection from pool: __knexUid1 +1ms
knex:query insert into "knex_migrations" ("batch", "migration_time", "name") values (?, ?, ?) trx2 +3ms
knex:bindings [ 2, 2020-01-16T15:04:30.572Z, '02-todo-restructure.js' ] trx2 +3ms
knex:client releasing connection to pool: __knexUid1 +3ms
....
```
## Migration execution order
All migrations applied in alphabetical order. So you should name your migrations with some numeric prefix like '01, 02' or DateTime like '20200101_000000, 20200102_000000'
## Notice: Migration rollback or down
Assuming that you decided to use OEM `knex` [CLI tool](http://knexjs.org/#Migrations-CLI) in your project to downgrade or rollback some migrations. In this case, you must disable `Microfleet` automatic migration in all services that use the same database. If this feature remains enabled and there will be some `Microfleet` services running (that could fail and restart on database structure change), they will try to apply migrations that you tried to revert.