[Migrations] Setting up
Create knexfile.js
./node_modules/.bin/knex init
Create a migration
knex migrate:make migration_name
knex migrate:make migration_name --env production
Run migrations
knex migrate:latest
knex migrate:latest --env production
Rollback
knex migrate:rollback
knex migrate:rollback --env production
See: Migrations
[Modifying] Delete
knex('users')
.where({ id: 2 })
.del()
See: Delete
[Modifying] Update
knex('users')
.where({ id: 2 })
.update({ name: 'Homer' })
See: Update
[Modifying] Insert
knex('users')
Insert one
.insert({ name: 'John' })
Insert many
.insert([
{ name: 'Starsky' },
{ name: 'Hutch' }
])
See: Insert
Modifying
{: .-three-column}
[Schema] Other methods
knex.schema
.renameTable('persons', 'people')
.dropTable('persons')
.hasTable('users').then(exists => ···)
.hasColumn('users', 'id').then(exists => ···)
See: Schema builder
[Schema] Alter table
knex.schema.table('accounts', table => {
Create
table.string('first_name')
Alter
table.string('first_name').alter()
table.renameColumn('admin', 'is_admin')
Drop
table.dropColumn('admin')
table.dropTimestamps('created_at')
})
{: .-setup}
See: Schema builder
[Schema] Create table
knex.schema.createTable('accounts', table => {
Columns
table.increments('id')
table.string('account_name')
table.integer('age')
table.float('age')
table.decimal('balance', 8, 2)
table.boolean('is_admin')
table.date('birthday')
table.time('created_at')
table.timestamp('created_at').defaultTo(knex.fn.now())
table.json('profile')
table.jsonb('profile')
table.uuid('id').primary()
Constraints
table.unique('email')
table.unique(['email', 'company_id'])
table.dropUnique(···)
Indices
table.foreign('company_id')
.references('companies.id')
table.dropForeign(···)
Variations
table.integer('user_id')
.unsigned()
.references('users.id')
})
.then(() => ···)
{: .-setup}
See: Schema builder
[Select] Etc
knex('users')
.pluck('id')
.then(ids => { ··· })
knex('users')
.first()
.then(user => { ··· })
Booleans
.count('active')
.count('active as is_active')
Numbers
.min('age')
.max('age')
.sum('age')
.sumDistinct('age')
.avg('age')
See: Query builder
[Select] Others
knex('users')
.distinct()
Group
.groupBy('count')
.groupByRaw('year WITH ROLLUP')
Order
.orderBy('name', 'desc')
.orderByRaw('name DESC')
Offset/limit
.offset(10)
.limit(20)
Having
.having('count', '>', 100)
.havingIn('count', [1, 100])
Union
.union(function() {
this.select(···)
})
.unionAll(···)
See: Query builder
Top comments (0)