Back in time i did a few experiments and serious projects using knex.js and it went well.
But it was some time ago and i was wondering if knex still worth the effort.
So here we are!
It's a SQL query builder. Unlike traditional ORM frameworks, a query builder doesn't tries to hide the database from you as much as possible.
Instead, you map your queries in a way that is easier to build queries, not necessarily mapping entities.
Example, one could query books from a database like this using JPA:
//...
String q = "select b from Books b where b.title like concat('%', :title, '%')";
List<Book> books = entityManager
.createQuery(q,Book.class)
.setParameter("title","mancer")
.getResultList();
// ...We're omitting several things here like model mapping and persistence unit setup but it's enough to feel how much indirection is involved.
If we decide to use spring repositories, things get a little better, but not that much:
//...
@Repository
public interface BookRepository extends JpaRepository<Book, Long> {
List<Book> findByTitleLike(String title);
}
//...
List<Book> books = entityManager = repository.findByTitleLike("mancer");
// ...Still need to define entity model but now we need to master the black magic of proper naming your repository method name. And this thing has limitations as you can imagine, making us to rely on @Query annotations which dumps us back to the original issue with plain JPA.
Gorm gets simpler but doesn't land too far:
// db, err := gorm.Open(//...
var books []Books
// ...
db.Where("title LIKE ?", "%mancer%").Find(&books)Again omitting the entity mapping for the sake of simplicity.
On node side, Sequelize doesn't get much better:
import {Book} from "../models"
import {Op} from "sequelize"
//...
const books = await Book.findAll({
where: {
title: {
[Op.substring]: 'mancer'
}
}
})
//...The syntax looks a little clear but it's still an ORM and therefore we're still facing undesired indirections on model mappings.
With knex you can do this:
//...
const books = await knex("books").whereLike("title", `%mancer%`)
//...There are interesting variations of this, but you get the idea.
No, but they shouldn't stay on your way.
For instance, JPA, Spring Data and Sequelize (and Objection too, which is built on top of knex) offers ways to skip all the model mapping drama and access the database directly.
But there are scenarios where the advantages os that abstract layer worth the effort.
It's just we're not here today to dig on those scenarios.
The simple and concise queries helps to keep tooling out of our way when doing more important things.
A select, as you saw, is quite simple. Here goes a few more examples:
// let isbn = "9788576573005"
//...
const book = await knex("books").where({isbn}).first()
//...const books = await knex("books")
.whereLike("title", `%mancer%`).limit(10).offset(10)Another common operation, count total results:
const books = await knex("books")
.whereLike("title", `%mancer%`).count("* as total")const books = await knex("books").whereIn("authors_id", knex("authors")
.select("id").whereLike("name", `%Will%`))The select clause can be used to specify columns to participate in the results.
Nothing stops you from perform a query on book title and author name:
// let q = 'o'
const books = await knex("books")
.whereLike("title", `%${q}%`)
.orWhereIn("authors_id", knex("authors")
.select("id").whereLike("name", `%${q}%`))The official docs has much more examples and useful tips on how to extract maximum results from your database, give it a try!
The first time i used knex, node ecosystem was completely built on top of commonjs modules. And so was knex.
In 2016 a simple http service endpoint would look like this:
// index.js
// database config and access with knex
var cfg = require("../knexfile.js")
var knex = require("knex")(cfg[process.env.NODE_ENV || "development"])
// good old express
var bodyParser = require("body-parser")
var app = require("express")()
app.use(bodyParser.json())
// quick and dirty
app.get("/books", function(req, res) {
knex("books").whereLike("title", "%" + req.query.q + "%")
.then(ret => res.send(ret))
})
// and of course make sure the database is ok before start to listen things
knex.migrate.latest().then(() => {
app.listen(process.env.PORT || 3000)
})By 2023 there is a few differences:
// index.mjs
// database config and access with knex
import cfg from "../knexfile.cjs"
import Knex from "knex"
// Koa is the spiritual successor of express
import Koa from "koa";
import Router from "@koa/router";
import bodyParser from "koa-bodyparser";
const knex = Knex(cfg[process.env.NODE_ENV || "development"])
const app = new Koa()
app.use(bodyParser())
// quick and dirty
const router = new Router()
router.get("/books", async ctx =>
ctx.body = await knex("books")
.whereLike("title", `%${ctx.query.q}%`))
app.use(router.routes()).use(router.allowedMethods());
// and of course make sure the database is ok before start to listen things
knex.migrate.latest().then(() => {
app.listen(process.env.PORT || 3000)
})Express is simple and elegant, but Koa is brutal. Simpler, modular, ready for the future.
And keeps playing nice with Knex and other frameworks famous by being used with express.
I wrote about migrations before, but it's never too much say how important is this topic is for modern application development.
With migrations one can be sure about database schema version and application expectations about this database since the app runs special scripts (the migrations) to put the database in the expected state.
Enabling migrations on your project is easy as this:
npm i knex
npx knex init It generates a file called knexfile.js in the current folder. That folder
usually is the project root.
The config file itself looks like this:
// Update with your config settings.
/**
* @type { Object.<string, import("knex").Knex.Config> }
*/
module.exports = {
development: {
client: 'sqlite3',
connection: {
filename: './dev.sqlite3'
}
},
staging: {
client: 'pg',
connection: {
database: 'my_db',
user: 'username',
password: 'password'
},
pool: {
min: 2,
max: 10
},
migrations: {
tableName: 'knex_migrations'
}
},
production: {
client: 'pg',
connection: {
database: 'my_db',
user: 'username',
password: 'password'
},
pool: {
min: 2,
max: 10
},
migrations: {
tableName: 'knex_migrations'
}
}
};By default knex offers three configuration profiles, development, staging
and production, but in fact you can define whatever you want on this config
file:
const _cfg = {
client: 'better-sqlite3',
useNullAsDefault: true,
connection: {
filename: "db.sqlite3",
},
pool: {
min: 2,
max: 10
},
}
/**
* @type { Object.<string, import("knex").Knex.Config> }
*/
module.exports = {
development: { ..._cfg},
test: {
..._cfg,
connection: {
filename: ':memory:'
}
},
production: {
..._cfg,
client: 'pg',
connection: process.env.PG_CONNECTION_URL
}
}Create a migration file using knex is quite easy too:
npx knex migrate:make some_database_change It will create something like migrations/20230226122114_some_database_change.js
on your current directory and will look like this:
/**
* @param { import("knex").Knex } knex
* @returns { Promise<void> }
*/
exports.up = function(knex) {
};
/**
* @param { import("knex").Knex } knex
* @returns { Promise<void> }
*/
exports.down = function(knex) {
};A migration file has changes to be applied to the database, so it conforms with the current app version.
You can learn more about how to fill those two functions here.
Knex offers that up and down functions, so you can write the database changes in the up function.
Migrations frameworks exists because database changes are hard. During development, one could find that latest change did not work as expected, so it must be unmade.
This is why the down exists.
But mind this: down functions are only useful during development, since there is a real risk of data loss if they run on production environments.
So, avoid to undo migrate executions on production
So far we saw knex generating only commonjs modules, but hey, is this supposed to be modern node development, right?
For knexfile itself little can be done, you can rename it to knexfile.cjs, so node understands what kind of module is it, but migration files can be modern es6 modules with little effort. when creating a migration do this:
npx knex migrate:make --knexfile app/configs/knexfile.cjs -x mjs more_changesThis time the file generated is different:
/**
* @param { import("knex").Knex } knex
* @returns { Promise<void> }
*/
export const up = async (knex) => {
};
/**
* @param { import("knex").Knex } knex
* @returns { Promise<void> }
*/
export const down = async (knex) => {
};We're not done, however.
If you decide to use es6 for your migration files, change the extension file type on your knexfile:
// part of knexfile.js
development: {
client: 'sqlite3',
connection: {
filename: './dev.sqlite3'
},
migrations: {
loadExtensions: [".js", ".mjs"]
},
},
// part of knexfile.jsIf the migrations section does not exist, create one.
When designing a project architecture, a popular topic is the folder layout.
Some (me!) get passionate about it, and when knex forces us to keep that migration folder toplevel to the project, not to mention the knexfile.js itself, a bit of sadness might hit.
But it's not mandatory!
Let's say this is the project layout:
.
├── app
│ ├── configs
│ │ └── database.mjs
│ ├── index.mjs
│ ├── index.spec.mjs
│ └── routes
│ ├── books.mjs
│ └── books.spec.mjs
├── index.mjs
├── package.json
├── package-lock.json
└── README.mdDo your knex init as usual but then move knexfile.js to
app/configs/knexfile.cjs.
Next step is to avoid miscalculations on migrations folder.
By default, knex expects this folder to be toplevel, so migration folder is derived from there.
To avoid this, set an absolute path to your migration folder. example:
const _cfg = {
client: 'better-sqlite3',
useNullAsDefault: true,
connection: {
filename: `${__dirname}/../../db.sqlite3`,
},
pool: {
min: 2,
max: 10
},
migrations: {
directory:`${__dirname}/migrations`,
loadExtensions: [".mjs"],
},
}
module.exports = {
development: { ..._cfg},
test: {
..._cfg,
connection: {
filename: ':memory:',
}
},
production: {
..._cfg,
client: 'pg',
connection: process.env.PG_CONNECTION_URL
}
}That way you put your migrations folder any place you want.
Important: in order to use knex migrate:make, knex migrate:latest,
knex migrate:rollback and other knex commands, you must point to the new
knexfile location. example:
npx knex migrate:make --knexfile app/configs/knexfile.cjs -x mjs new_columnnpx knex migrate:latest --knexfile app/configs/knexfile.cjsIt's possible to avoid those long and tedious commands by adding them to the project npm scripts:
// part of package.json
"scripts": {
"start": "node -r dotenv-flow/config index.mjs",
"dev": "cross-env NODE_ENV=development nodemon -r dotenv-flow/config ",
"test": "cross-env NODE_ENV=test c8 mocha -r dotenv-flow/config app/**/*",
"migrate:make": "knex migrate:make --knexfile app/configs/knexfile.cjs -x mjs -- ",
"migrate:latest": "knex migrate:latest --knexfile app/configs/knexfile.cjs",
"migrate:rollback": "knex migrate:rollback --knexfile app/configs/knexfile.cjs"
},
// part of package.jsonSo you can do this to create a new migrate instead:
npm run migrate:make new_columnWhen writing unit/integration testing it is important to be as true as possible to the real/production environment. It doesn't mean that mocks are worthless, just that tests in order to be trustful must perform real operations.
This is why tests should cover database operations, but predict database state is not easy.
But it is easy when combining knex with configurable environment variables.
In previous examples we already saw how it could work, by getting correct knex configuration from knexfile using environment variables. That line wasn't there without purpose:
const knex = Knex(cfg[process.env.NODE_ENV || "development"])Now setup dotenv-flow in the project root (no espace this time!). start by creating the .env and .env override files:
.
├── app
│ ├── configs
│ │ ├── database.mjs
│ │ ├── knexfile.cjs
│ │ └── migrations
│ ├── index.mjs
│ ├── index.spec.mjs
│ └── routes
│ ├── books.mjs
│ └── books.spec.mjs
├── .env
├── .env.development
├── .env.development.local
├── .env.production
├── .env.test
├── index.mjs
├── package.json
├── package-lock.json
└── README.mdEach .env file can store specific environment variables that can work as feature-flags inside the project.
Depending on main state of NODE_ENV variable, some are enabled, some are not.
If there is need to override values locally, a .local file variant can be provided.
Lastly, by no means push sensitive values inside .env files to source code version control, it's ok to version the files with variable names, but all values must be empty unless they are not sensitive.
Assuming this service:
import { knex } from "../../configs/database.mjs"
export const listBooks = async (q = "") =>
await knex("books").whereLike("title", `%${q}%`)The test for it could be like this:
import chai from "chai"
import { listBooks } from "./services.mjs"
import { knex, doMigrate } from "../../configs/database.mjs";
chai.should();
describe("Books service test", () => {
// setup database for testing
before(async () => await doMigrate())
after(async () => await knex.destroy())
it("should list all books", async () => {
const books = await listBooks("")
books.should.be.an("array")
books.should.have.lengthOf(3)
})
it("should filter books by name", async () => {
const books = await listBooks("neuro")
books.should.be.an("array")
books.should.have.lengthOf(1)
})
})Here the magic trick is the proper setup of knex. Knexfile must have a "test" section (as saw in our previous example):
// knexfile.cjs
test: {
..._cfg,
connection: {
filename: ':memory:'
}
},
// knexfile.cjsThat way the database only existis during the test execution.
Thanks to before(async () => await doMigrate()) and
after(async () => await knex.destroy()), connection pool is created and
destroyed during the testsuite lifecycle.
Lastly, doMigrate is a helper function to run migrations so the database will
be updated by the time of tests executions. You can use it to bootstrap the app
itself, since it will honor the config chosen by environment flags:
// database.mjs
import Knex from "knex"
import cfg from "./knexfile.cjs"
const nodeEnv = process.env.NODE_ENV || "development"
export const knex = Knex(cfg[nodeEnv])
export const doMigrate = () => knex.migrate.latest(cfg[nodeEnv]);Lastly, you must make sure that the test npm script of your project sets the
proper environment variables so it will use the memory database during tests:
# put this on your npm scripts, but you can invoke it by hand anytime
npx cross-env NODE_ENV=test c8 mocha -r dotenv-flow/config app/**/*
Books service test
✔ should list all books
✔ should filter books by name
2 passing (27ms)
------------------|---------|----------|---------|---------|-------------------
File | % Stmts | % Branch | % Funcs | % Lines | Uncovered Line #s
------------------|---------|----------|---------|---------|-------------------
All files | 100 | 85.71 | 100 | 100 |
components/books | 100 | 100 | 100 | 100 |
routes.mjs | 100 | 100 | 100 | 100 |
services.mjs | 100 | 100 | 100 | 100 |
configs | 100 | 75 | 100 | 100 |
database.mjs | 100 | 66.66 | 100 | 100 | 4
knexfile.cjs | 100 | 100 | 100 | 100 |
------------------|---------|----------|---------|---------|-------------------Thanks to cross-env, the current environment is explicit. Thanks to dotenv-flow, it's possible to customize and override variables. and thanks to c8 we get a coverage report to better understand how the code behaves and how trustful it is.
Modern Knex is a nice piece of software which does it job and don't mess with our productivity.
As shown, it plays very nice with companions frameworks offering different solutions and degrees of abstraction (Objection is an ORM on top of Knex) and offers easy steps for integration, if any.
Some sample code can be found here, and other samples here.
May Knex helps you in your next project.
Happy Hacking.