Knex is a "batteries included" data access library (DAL)/Query Builder for relational databases.
DALs are typically used as a way of abstracting the differences between different dialects of SQL provided by DB vendors. Knex provides standardized responses between different query clients and dialects.
Postgres
CREATE TABLE items(
id SERIAL
);
MySQL
CREATE TABLE items(
id INT NOT NULL AUTO_INCREMENT
);
Knex
knex.schema.createTable('items', function (tbl) {
tbl.increments();
});
Features
- Query Builder
- Schema Builder
- Connection pooling
- Migrations
- Data seeding
- Automatic SQL injection protection
- Transaction support
- Supports both callbacks and promises
- It's strength lies in friendly jQuery-like query syntax
Knex currently supports Postgres, MSSQL, SQLite3, MySQL/MariaDB, Oracle and Amazon Redshift.
Installation
npm i knex
Also install needed db drivers:
npm install pg mssql sqlite3
Knex CLI
knex init -> creates db config (knexfile.js) knex migrate knex seed
Selecting Data
The order of chained methods is not significant.
from('book').select('title') = select('title').from('book')
knex('book') = knex.from('book') = knex.table('book')
select
Specify column names as strings or array. Defaults to selecting all columns
first
returns firs record of query
Schema Building
Install knex-cli by running npm install -g knex.
run knex to list commands. knex init creates a file knexfile.js holding your db config.
knex migrate:make name
Run migration: knex migrate:latest
To rollback run knex migrate:rollback
Migrations and rollbacks are per batch and not necessarily per individual migration.
Seeding Data
Seed files are run in alphabetical order knex seed:make name Run seed: knex seed:run