knex.md

   1 ---
   2 title: Knex
   3 layout: 2017/sheet
   4 updated: 2020-06-03
   5 category: Databases
   6 intro: |
   7   [Knex](http://knexjs.org/) is an SQL query builder for Node.js.
   8   This guide targets v0.13.0.
   9 ---
  10
  11 ## Getting started
  12 {: .-three-column}
  13
  14 ### Connect
  15
  16 ```js
  17 require('knex')({
  18   client: 'pg',
  19   connection: 'postgres://user:pass@localhost:5432/dbname'
  20 })
  21 ```
  22
  23 See: [Connect](#connect-1)
  24
  25 ### Create table
  26
  27 ```js
  28 knex.schema.createTable('user', (table) => {
  29   table.increments('id')
  30   table.string('name')
  31   table.integer('age')
  32 })
  33 .then(() => ···)
  34 ```
  35
  36 See: [Schema](#schema)
  37
  38 ### Select
  39
  40 ```js
  41 knex('users')
  42   .where({ email: 'hi@example.com' })
  43   .then(rows => ···)
  44 ```
  45 {: data-line="2"}
  46
  47 See: [Select](#select-1)
  48
  49 ### Insert
  50
  51 ```js
  52 knex('users')
  53   .insert({ email: 'hi@example.com' })
  54 ```
  55 {: data-line="2"}
  56
  57 See: [Insert](#insert-1)
  58
  59 ### Update
  60
  61 ```js
  62 knex('users')
  63   .where({ id: 135 })
  64   .update({ email: 'hi@example.com' })
  65 ```
  66 {: data-line="2,3"}
  67
  68 See: [Update](#update-1)
  69
  70 ### Migrations
  71
  72 ```bash
  73 knex init
  74 knex migrate:make migration_name
  75 knex migrate:make migration_name -x ts # Generates a TypeScript migration file
  76 knex migrate:latest
  77 knex migrate:rollback
  78 ```
  79
  80 See: [Migrations](#migrations-1)
  81
  82 ### Seeds
  83
  84 ```bash
  85 knex seed:make seed_name
  86 knex seed:make seed_name -x ts # Generates a TypeScript seed file
  87 knex seed:run # Runs all seed files
  88 knex seed:run --specific=seed-filename.js # Runs a specific seed file
  89 ```
  90
  91 See: [Seeds](http://knexjs.org/#Seeds)
  92
  93 ## Connect
  94 {: .-three-column}
  95
  96 ### Libraries
  97
  98 | `pg` | PostgreSQL |
  99 | `mysql` | MySQL or MariaDB |
 100 | `sqlite3` | Sqlite3 |
 101 | `mssql` | MSSQL |
 102
 103 Install any of these packages along with `knex`.
 104
 105 See: [Node.js installation](http://knexjs.org/#Installation-node)
 106
 107 ### Connect via host
 108
 109 ```js
 110 var knex = require('knex')({
 111   client: 'mysql',
 112   connection: {
 113     host: '127.0.0.1',
 114     user: 'your_database_user',
 115     password: 'your_database_password',
 116     database: 'myapp_test'
 117   },
 118   pool: { min: 0, max: 7 }
 119 })
 120 ```
 121 {: data-line="2,3"}
 122
 123 See: [Initializing the library](http://knexjs.org/#Installation-client)
 124
 125 ### Connect via URL
 126
 127 ```js
 128 var pg = require('knex')({
 129   client: 'pg',
 130   connection: process.env.DATABASE_URL,
 131   searchPath: 'knex,public',
 132   pool: { min: 0, max: 7 }
 133 })
 134 ```
 135 {: data-line="2,3"}
 136
 137 ### Connect via Sqlite
 138
 139 ```js
 140 var knex = require('knex')({
 141   client: 'sqlite3',
 142   connection: { filename: './mydb.sqlite' }
 143 })
 144 ```
 145 {: data-line="2,3"}
 146
 147 ## Select
 148
 149 ### Where
 150
 151 ```js
 152 knex
 153   .from('books')
 154   .select('title', 'author', 'year')
 155 ```
 156
 157 #### Where
 158
 159 ```js
 160   .where('title', 'Hello')
 161   .where({ title: 'Hello' })
 162   .whereIn('id', [1, 2, 3])
 163   .whereNot(···)
 164   .whereNotIn('id', [1, 2, 3])
 165 ```
 166
 167 #### Where conditions
 168
 169 ```js
 170   .whereNull('updated_at')
 171   .whereNotNull(···)
 172 ```
 173
 174 ```js
 175   .whereExists('updated_at')
 176   .whereNotExists(···)
 177 ```
 178
 179 ```js
 180   .whereBetween('votes', [1, 100])
 181   .whereNotBetween(···)
 182 ```
 183
 184 ```js
 185   .whereRaw('id = ?', [1])
 186 ```
 187
 188 #### Where grouping
 189
 190 ```js
 191   .where(function () {
 192     this
 193       .where('id', 1)
 194       .orWhere('id', '>', 10)
 195   })
 196 ```
 197
 198 See: [Where clauses](http://knexjs.org/#Builder-wheres)
 199
 200 ### Join
 201
 202 ```js
 203 knex('users')
 204 ```
 205
 206 #### Basic join
 207
 208 ```js
 209   .join('contacts', 'users.id', '=', 'contacts.id')
 210   .join('contacts', {'users.id': 'contacts.id'})
 211 ```
 212
 213 #### Strings
 214
 215 ```js
 216   .join('accounts', 'accounts.type', '=', knex.raw('?', ['admin']))
 217 ```
 218
 219 #### Directions
 220
 221 ```js
 222   .leftJoin(···)
 223   .leftOuterJoin(···)
 224   .rightJoin(···)
 225   .rightOuterJoin(···)
 226   .outerJoin(···)
 227   .fullOuterJoin(···)
 228   .crossJoin(···)
 229 ```
 230
 231 #### Raw
 232
 233 ```js
 234   .joinRaw('natural full join table1')
 235 ```
 236
 237 #### Grouping
 238
 239 ```js
 240   .join('accounts', function () {
 241     this
 242       .on('accounts.id', '=', 'users.account_id')
 243       .orOn('accounts.owner_id', '=', 'users.id')
 244
 245       .onIn('accounts.id', [1, 2, 3, 5, 8])
 246       .onNotIn(···)
 247
 248       .onNull('accounts.email')
 249       .onNotNull(···)
 250
 251       .onExists(function () {
 252         this.select(···)
 253       })
 254       .onNotExists(···)
 255   })
 256 ```
 257
 258 See: [Join methods](http://knexjs.org/#Builder-join)
 259
 260 ### Others
 261
 262 ```js
 263 knex('users')
 264   .distinct()
 265 ```
 266
 267 #### Group
 268
 269 ```js
 270   .groupBy('count')
 271   .groupByRaw('year WITH ROLLUP')
 272 ```
 273
 274 #### Order
 275 ```js
 276   .orderBy('name', 'desc')
 277   .orderByRaw('name DESC')
 278 ```
 279
 280 #### Offset/limit
 281
 282 ```js
 283   .offset(10)
 284   .limit(20)
 285 ```
 286
 287 #### Having
 288
 289 ```js
 290   .having('count', '>', 100)
 291   .havingIn('count', [1, 100])
 292 ```
 293
 294 #### Union
 295
 296 ```js
 297   .union(function() {
 298     this.select(···)
 299   })
 300   .unionAll(···)
 301 ```
 302
 303 See: [Query builder](http://knexjs.org/#Builder)
 304
 305 ### Etc
 306
 307 ```js
 308 knex('users')
 309   .pluck('id')
 310   .then(ids => { ··· })
 311 ```
 312 ```js
 313 knex('users')
 314   .first()
 315   .then(user => { ··· })
 316 ```
 317
 318 #### Booleans
 319
 320 ```js
 321   .count('active')
 322   .count('active as is_active')
 323 ```
 324
 325 #### Numbers
 326
 327 ```js
 328   .min('age')
 329   .max('age')
 330   .sum('age')
 331   .sumDistinct('age')
 332   .avg('age')
 333 ```
 334
 335 See: [Query builder](http://knexjs.org/#Builder)
 336
 337 ## Schema
 338
 339 ### Create table
 340
 341 ```js
 342 knex.schema.createTable('accounts', table => {
 343 ```
 344
 345 #### Columns
 346
 347 ```js
 348   table.increments('id')
 349   table.string('account_name')
 350   table.integer('age')
 351   table.float('age')
 352   table.decimal('balance', 8, 2)
 353   table.boolean('is_admin')
 354   table.date('birthday')
 355   table.time('created_at')
 356   table.timestamp('created_at').defaultTo(knex.fn.now())
 357   table.json('profile')
 358   table.jsonb('profile')
 359   table.uuid('id').primary()
 360 ```
 361
 362 #### Constraints
 363
 364 ```js
 365   table.unique('email')
 366   table.unique(['email', 'company_id'])
 367   table.dropUnique(···)
 368 ```
 369
 370 #### Indices
 371
 372 ```js
 373   table.foreign('company_id')
 374     .references('companies.id')
 375   table.dropForeign(···)
 376 ```
 377
 378 #### Variations
 379
 380 ```js
 381   table.integer('user_id')
 382     .unsigned()
 383     .references('users.id')
 384 ```
 385
 386 ```js
 387 })
 388 .then(() => ···)
 389 ```
 390 {: .-setup}
 391
 392 See: [Schema builder](http://knexjs.org/#Schema)
 393
 394 ### Alter table
 395
 396 ```js
 397 knex.schema.table('accounts', table => {
 398 ```
 399
 400 #### Create
 401
 402 ```js
 403   table.string('first_name')
 404 ```
 405
 406 #### Alter
 407
 408 ```js
 409   table.string('first_name').alter()
 410   table.renameColumn('admin', 'is_admin')
 411 ```
 412
 413 #### Drop
 414
 415 ```js
 416   table.dropColumn('admin')
 417   table.dropTimestamps('created_at')
 418 ```
 419
 420 ```js
 421 })
 422 ```
 423 {: .-setup}
 424
 425 See: [Schema builder](http://knexjs.org/#Schema)
 426
 427 ### Other methods
 428
 429 ```js
 430 knex.schema
 431   .renameTable('persons', 'people')
 432   .dropTable('persons')
 433 ```
 434
 435 ```js
 436   .hasTable('users').then(exists => ···)
 437   .hasColumn('users', 'id').then(exists => ···)
 438 ```
 439
 440 See: [Schema builder](http://knexjs.org/#Schema)
 441
 442 ## Modifying
 443 {: .-three-column}
 444
 445 ### Insert
 446
 447 ```js
 448 knex('users')
 449 ```
 450
 451 #### Insert one
 452
 453 ```js
 454   .insert({ name: 'John' })
 455 ```
 456
 457 #### Insert many
 458
 459 ```js
 460   .insert([
 461     { name: 'Starsky' },
 462     { name: 'Hutch' }
 463   ])
 464 ```
 465
 466 See: [Insert](http://knexjs.org/#Builder-insert)
 467
 468 ### Update
 469
 470 ```js
 471 knex('users')
 472   .where({ id: 2 })
 473   .update({ name: 'Homer' })
 474 ```
 475
 476 See: [Update](http://knexjs.org/#Builder-update)
 477
 478 ### Delete
 479
 480 ```js
 481 knex('users')
 482   .where({ id: 2 })
 483   .del()
 484 ```
 485
 486 See: [Delete](http://knexjs.org/#Builder-del)
 487
 488 ## Migrations
 489
 490 ### Setting up
 491
 492 #### Create knexfile.js
 493
 494 ```
 495 ./node_modules/.bin/knex init
 496 ```
 497
 498 #### Create a migration
 499
 500 ```
 501 knex migrate:make migration_name
 502 knex migrate:make migration_name --env production
 503 ```
 504
 505 #### Run migrations
 506
 507 ```
 508 knex migrate:latest
 509 knex migrate:latest --env production
 510 ```
 511
 512 #### Rollback
 513
 514 ```
 515 knex migrate:rollback
 516 knex migrate:rollback --env production
 517 ```
 518
 519 See: [Migrations](http://knexjs.org/#Migrations)