Knex Cheat Sheet

Knex.js is a popular SQL query builder for Node.js that allows developers to write database queries in a more intuitive and efficient way. It provides a simple and consistent API for building SQL queries, making it easier to work with databases and reducing the amount of boilerplate code needed.

One of the key benefits of using Knex.js is that it supports multiple database systems, including MySQL, PostgreSQL, SQLite, and Oracle. This means that developers can use the same codebase to work with different databases, without having to learn different query languages or APIs.

Knex.js also provides a powerful migration system that allows developers to manage database schema changes over time. This makes it easier to keep track of changes to the database structure and ensures that the application remains consistent and stable. Check out the cheatsheet we’ve put together below! It should get you started!

Table of Contents

• Installation
• Connecting to a Database
• Creating Tables
• Inserting Data
• Selecting Data
• Updating Data
• Deleting Data
• Migrations
• Seeds
• Transactions
• Query Builder
• Raw Queries

Installation

To install Knex, run the following command:

npm install knex --save

Connecting to a Database

To connect to a database, use the knex function and pass in an object with the database configuration:

const knex = require('knex')({
  client: 'mysql',
  connection: {
    host: '127.0.0.1',
    user: 'your_database_user',
    password: 'your_database_password',
    database: 'myapp_test'
  }
});

Creating Tables

To create a table, use the knex.schema.createTable method:

knex.schema.createTable('users', function(table) {
  table.increments('id');
  table.string('name');
  table.integer('age');
});

Inserting Data

To insert data into a table, use the knex.insert method:

knex('users').insert({name: 'John', age: 30})

Selecting Data

To select data from a table, use the knex.select method:

knex.select('*').from('users')

Updating Data

To update data in a table, use the knex.update method:

knex('users').where({id: 1}).update({name: 'Jane'})

Deleting Data

To delete data from a table, use the knex.del method:

knex('users').where({id: 1}).del()

Migrations

Migrations are a way to manage database schema changes over time. To create a migration, use the knex migrate:make command:

knex migrate:make migration_name

To run migrations, use the knex migrate:latest command:

knex migrate:latest

Seeds

Seeds are a way to populate a database with initial data. To create a seed, use the knex seed:make command:

knex seed:make seed_name

To run seeds, use the knex seed:run command:

knex seed:run

Transactions

Transactions are a way to group database operations into a single unit of work. To use transactions, use the knex.transaction method:

knex.transaction(function(trx) {
  knex('users').transacting(trx).insert({name: 'John', age: 30})
  knex('accounts').transacting(trx).insert({user_id: 1, balance: 100})
})

Query Builder

The query builder is a way to construct SQL queries using JavaScript. Here are some examples:

knex.select('*').from('users').where({name: 'John'})
knex.select('*').from('users').whereIn('id', [1, 2, 3])
knex.select('*').from('users').orderBy('name', 'desc')

Raw Queries

To execute raw SQL queries, use the knex.raw method:

knex.raw('SELECT * FROM users WHERE name = ?', ['John'])

Reference:

https://knexjs.org/