Background

I used Sequelize for some time now. That was my introduction to SQL in a NodeJS app. It’s pretty nice, it has a very nice interface and, those coming from a MongoDB and mongoose background will appreciate the similar interface.

But there are problems with it. It abstracts way too much. Sometimes it feels like there’s no connection between SQL and Sequelize. It generates unexpected queries, far from what you actually wanted to do. Even simple join queries can turn really complicated.

Also, the docs don’t help either, it’s one of the worst out there and, it doesn’t state anything clearly.

Creating relations can be a pain and, hard to grasp what’s going on. When associations start to become complex, all hell breaks loose and you’ll wonder why you decided to go with Sequelize. Which results in various kinds of “EagerLoading” errors.
So, out of extreme frustration, I started looking for something different. And that’s when I found Knex and Objection.js.

What’s Knex?

Knex is a query builder, not a full ORM. You can use Knex to create migrations, seed and, query your database. It has methods for all kinds of queries you can think of. From sub-queries to joins, it makes them simple and clearly readable. It generates expected SQL queries. What you write is what you get. It also has support for transactions. The API features a promise interface, so you can use both promises and async/await.

You do not create models here unlike an ORM. You just create migrations which creates the tables and builds your schema. After the migrations are run you can directly start interrogating your database using a Knex instance.

What is Objection.js?

Objection.js is the ORM. It’s built on top of Knex and provides a bit of abstraction over Knex. You define models here which you can use to run queries on your database tables. You can define various getter, setters and, create relationships among models. Objection makes it easy to define relations between models and, fetching data that are related using “EagerLoading” and a few other helper methods. It also makes working with transactions a bit simpler.

The Setup

We’ll be working with a MySQL database in this guide. We need to install some dependencies. First of all, have NodeJS and NPM installed in your system.

Now, create a folder on your desktop or wherever you would like named “objection_crud” or whatever you wish to name it, this will be our project folder where our codes will reside. Navigate to the folder from the console and type in the below command to initialize the project directory

npm init -y

Then install dependencies using npm

npm install --save mysql mysql2 knex objection

After installation is done, create a database named “objection_crud” or whatever you want to name it in MySQL. You can log in to MySQL via the CLI to create it. You’ll obviously need to have MySQL installed on your PC locally for this to work. You can find various guides online on how to install MySQL for your specific operating system.

Then run the below command in the project folder from the console

knex init

This will initialize the project with knex and will generate a file named knexfile.js which holds the database connection configurations.

Replace the content of that file with the code below

module.exports = {
  development: {
      client: ‘mysql’,
      connection: {
      host: ‘127.0.0.1’,
      user: ‘root’, // replace with your mysql username
      password: ‘123456’, // replace with your mysql password
      database: ‘objection_crud’
    },
    debug: true
  }
};

See the added option [debug: true]. This will print the SQL that is generated by knex and Objection to the console. These configurations will be used by knex to create a connection with the database.

Also, you can add options for database pooling. If you’re new to the concept of pooling you can see this => https://stackoverflow.com/questions/4041114/what-is-database-pooling

Basically what it does is reuse a database connection which has already been established instead of creating a new connection each time you make a query. To change the default pooling options of knex you can add the following option to the configuration after the debug option

pool: { min: 2, max: 5 }

Creating migrations with Knex

We’ll use knex migrations to create our database tables and, to track changes within our database schema. Migrations allow us to easily alter a table after we have already created all the tables.

We want to create the following tables using knex instead of writing raw SQL. In normal SQL the code for the schema would look the following

CREATE TABLE IF NOT EXISTS roles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    role_name VARCHAR(45) NOT NULL
);

CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(45) NOT NULL,
    email VARCHAR(255) NOT NULL,
    role_id INT,
    FOREIGN KEY (role_id)
      REFERENCES roles (id)
      ON DELETE SET NULL
      ON UPDATE CASCADE
); 

This is a very simple schema. There’s a very simple relation between the tables. Each user has a role. And a role can be held by many users. The relation is created by placing a foreign key constraint on the users table. This also ensures if a role doesn’t exist it can not be assigned to a user.

To create a migration type in the below command in the project folder

knex migrate:make roles

This will generate a migrations folder in the project directory with a migration file inside. The file will be named something like 20190818160634_roles.js. Notice the long number in the filename. It's a timestamp of when the file was created. This is necessary and important because knex uses this to know which migrations to run first. We need to create the roles table first then the users table because users contain a reference to the roles table. That’s why the roles migration needs to be run first. Which is indicated by the timestamp as it was created first.

Now to create another migration which will be the users one

knex migrate:make users

This will create another file in the migrations folder for the users migration. Open the roles migration file. You’ll notice both the migration files are almost empty except for a bit of boilerplate code. There are two functions defined here up and down. We’ll write the code for both these functions. The code we’ll write in the up function will create the table in the database. And the code we’ll write in the down function will drop the table. You can write something different in there as well not necessarily just create/drop, depends on your needs.

When we run the migrations the codes inside the up functions will be run. If we think we have made a mistake or we just want to roll back to the state before we ran the migrations we can run the down function.

Now let’s write the code inside the roles migration file first. Open that file and write the below codes

exports.up = function(knex) {
    return knex.schema.createTable("roles", table => {
        table.increments("id").primary();
        table.string("role_name", 45).notNullable();
    });
};

exports.down = function(knex) {
    return knex.schema.dropTable("roles");
};

First of all, both from up and down, you need to return a promise. We can create a table in the database using the methods that knex provides => knex.schema.createTable(). createTable returns a promise, so that takes care of returning a promise. It takes 2 parameters. One is the name of the table other is a callback function. Knex passes in an object to the callback which we’ll name table. We can use the table object to define the table structure. Here we define the id and role_name field with the methods from the table object. We can chain various methods here that add attributes to the columns. Like for the role_name, we chained a method notNullable() which is equivalent of writing NOT NULL constraint in SQL. For the id column, we chained two methods increments() and primary(). Which marks that column as a primary key and auto-incrementing.

Inside the down function, we have a single line of code that just drops the table specified using the dropTable() method. We can do other things here depending on our needs.

Full list of methods available for schema building => https://knexjs.org/#Schema-Building

Let’s move on to the users table migration file. Open that and add these codes

exports.up = function(knex) {
    return knex.schema.createTable("users", table => {
        table.increments("id").primary();
        table.string("username");
        table.string("email");
        table.integer("role_id").unsigned();
    });
};

exports.down = function(knex) {
    return knex.schema.dropTable("users");
};

For the down function, we’re doing the same thing as before. Just dropping the table. And in the up function defining the columns of the user table. Notice the line where we define role_id which is the connection between the roles and users table. Here, we specify unsigned(). This creates an unsigned attribute for that column. Because by default knex creates all integer fields with the unsigned attribute. In the roles table, we have id which is an integer, so knex will add the unsigned attribute to it. Since this is the connection between the two tables, they need to be the same. Thus adding unsigned() to the role_id in the users table.

Running Migrations

So, we are done with creating the migrations. But so far the tables just reside inside those javascript files. They have not actually been created inside the MySQL server. For that, we need to run the migration files we just created. For that run the following command in the project folder

knex migrate:latest

It’ll run the migrations and create the tables in the database. Notice the command key “latest”. It does what it implies, it only runs the latest migrations which have not been run yet, which are new. It doesn’t run migrations which were already run before. This is important, that means if we ever make a mistake or wish to alter the tables, we’ll never edit the migration files and insert the changes. Because those files will never get run again by knex.

This is where I did something intentionally. Notice the raw SQL codes from the previous section. users and roles table are connected via a foreign key. But we never defined that foreign key inside the users migration file. So how do we add that foreign key now? Simple, we’ll create another migration.

Run the command in the project folder

knex migrate:make users_add_foreign

This will create the migration file. Open the file and write the code below

exports.up = function(knex) {
    return knex.schema.table("users", table => {
        table.foreign("role_id").references("roles.id");
    });
};

exports.down = function(knex) {
    return knex.schema.table("users", table => {
        table.dropForeign("role_id");
    });
};

Since the table already exists, the code in up will select the users database and alter it adding a new foreign key constraint to the role_id column. In the down function, we just rollback by dropping the foreign key constraint.

Let’s run the migrations again

knex migrate:latest

Notice, this only runs the users_add_foreign migration. Not the roles and users migrations. As those have already been run, and knex will never run them again unless we roll back to a previous state.

You can log in to MySQL CLI and check that the tables have been created and the foreign key constraint has been added.

Notice 2 extra tables, knex_migrations and knex_migrations_lock. These are created by knex to track which migrations have been run. It’s best not to mess with these, but if you’re interested you can take a peek inside them they’re very simple in structure.

If you wish to undo what you have done so far run this command

knex migrate:rollback

This will roll back all of the migrations by running the codes inside the down function. Meaning it’ll drop the tables and the foreign key as specified there. Rollbacks run backward. The file that was created the last will run first and follow backward. So the order of execution will be

users_add_foreign > users > roles

Adding Timestamps

Timestamps are somewhat of an important thing to add in all database tables. For logging and monitoring changes. It’s very easy to add timestamps with knex. Let’s add timestamps to our users table. For that, we’ll create another migration

knex migrate:make add_user_timestamp

Afterward, open the migration file and add the below codes

exports.up = function(knex) {
    return knex.schema.table("users", table => {
        table.timestamps(false, true);
    });
};

exports.down = function(knex) {
    return knex.schema.table("users", table => {
        table.dropTimestamps();
    });
};

the timestamps() method adds the created_at and updated_at fields to our users table. Notice the 2 boolean arguments passed to the method. The first one, if passed true it’ll make the datatype of the created_at and updated_at to MySQL TIMESTAMP. Setting it to false will keep it at DATETIME. The second argument sets the default value to MySQL current_timestamp(). So upon insertion, it’ll automatically be set to the value of current_timestamp(). Now running the migration will add the timestamp fields to the table.

Seeding the database

Seeding means setting the database tables with some initial values. Knex makes it easy to seed a database. We’ll seed our roles table with some initial values. Usually, you’ll need to seed your database to set some pre-defined values or for testing purposes. You can generate fake data with faker.js and seed the database as well. We’ll keep it simple though. Seeding is similar to migrations. Let’s run the below command to get started

knex seed:make seed_roles

It’ll create a new folder called seeds and create a new file inside it named seed_roles. Inside the file dump the below code

exports.seed = function (knex) {

    return knex("roles").del()
        .then(function() {

            return knex("roles").insert([
                { role_name: "User" },
                { role_name: "Admin" },
                { role_name: "Staff" }
            ]);
        });
};

We’re inserting 3 rows here using the insert() method. You can call it a bulk insert. We pass it an array of objects, which will be inserted as rows. Notice something, we’re calling del() before inserting. This is not necessary but you can do that. It clears/deletes the table before doing the insertion. So a clean slate.

Conclusion

So this got a bit longer than I would have liked :P So I have decided to break it into two parts. These parts conclude everything about knex. In the next part, we’ll look into Objection.js and querying the database using models.

Github Repo Link => https://github.com/MrScX/knex_objection_crud_example