Can You Communicate With Databases Without Learning SQL | Part #2

Objection.js - A NodeJS ORM with Knex Query Builder

Objection.js is another Node.js ORM(just as Sequelize) that aims to make it as easy as possible for you to use the full power of SQL as well as the underlying database engine while still making the common stuff easy and enjoyable.

— As per the Objection.js Official Documentation

For a better understanding of ObjectionJS in action, we’re going to use the concept of an online shopping system. For simplicity, we will just be playing with the following 2 entities, i) Customer, ii) Order. Where each customer can place one or more orders and each order belongs to a single customer.

1) Installation

You can use npm or yarn to install Objection.js. Now, Objection uses knex SQL Query Builder in the background as its database access layer, so you also need to install that.

npm install objection knex

yarn add objection knex

You'll also have to manually install one of the following drivers for your database of choice:

npm install pg
npm install sqlite3
npm install mysql
npm install mysql2

All the examples present in this article will be based on the MySQL driver, i.e., mysql2. Also, the database is running on my local machine.

2) Establish Database Connection

Place the following code in your ./util/database.js

const { Model } = require('objection');
const Knex = require('knex');

// Initialize knex.
const knex = Knex({
  client: 'mysql',
  connection: {
    host: "127.0.0.1",
    user: "your_username", //replace with your username
    password: "your_password", //replace with your password
    database: "your_db_name" // replace with your db name
  },
});

// Give the Knex instance to Objection.
Model.knex(knex);

module.exports = knex;

3) Define Models and Define Relationship

A model is an abstraction that represents a table in your database. In Objection, it is a class that extends the Model class.

Define the Customer model by placing the following code in your ./models/customer.js

const { Model } = require('objection');
const Order = require('./order');

class Customer extends Model {
  static get tableName() {
    return 'customers';
  };

  $beforeInsert() {
    this.createdAt = new Date();
  };

  $beforeUpdate() {
    this.updatedAt = new Date();
  };

  static get nameColumn() {
    return 'name';
  };

  static get emailColumn() {
    return 'email';
  };

  static get jsonSchema() { //Optional JSON Schema Validation
    return {
      type: 'object',
      required: ['name', 'email'],
      properties: {
        id: { type: 'integer' },
        name: { type: 'string', minLength: 1, maxLength: 255 },
        email: { type: 'string' },
        createdAt: { type: 'string' },
        updatedAt: { type: 'string' }
      }
    };
  };

  static relationMappings = {
    order: {
      relation: Model.HasOneRelation,
      modelClass: Order,
      join: {
        from: 'customers.id',
        to: 'orders.customer_id'
      }
    }
  };

};

module.exports = Customer;

Each customer has an id, a name, and an email. This represents the 3 fields/columns of the Customer table. So, the Order model is associated with the Customer model by a hasOne relation. Now, we have to inform Objection of this association, and we will do this by defining the relationMappings object.

Define the Order model by placing the following code in your ./models/order.js

const { Model } = require('objection');

class Order extends Model {
  static get tableName() {
    return 'orders';
  };

  $beforeInsert() {
    this.createdAt = new Date();
  };

  $beforeUpdate() {
    this.updatedAt = new Date();
  };

  static get totalColumn() {
    return 'total';
  };

  static get customerIdColumn() {
    return 'customer_id';
  };

  static get jsonSchema() { //Optional JSON Schema Validation
    return {
      type: 'object',
      required: ['total'],
      properties: {
        id: { type: 'integer' },
        total: { type: 'number' },
        customer_id: { type: 'integer' },
        createdAt: { type: 'string' },
        updatedAt: { type: 'string' }
      }
    };
  };
};

module.exports = Order;

Each order will have an id and a total purchase price. This represents the 2 fields/columns of the Order table.

4) Writing Queries

In the following code snippet, we perform the following actions:

i) Delete all the pre-existing customers/orders from the database.

ii) Insert a new customer.

iii) Fetch the list of all the customers in our Customer table.

iv) Insert a new order for this customer using the customerId foreign key.

v) Select all the orders for the current customer.

const Customer = require('./models/customer');
const Order = require('./models/order');
const knex = require('./util/database');

async function main() {
    await Customer.query().delete();
    await Order.query().delete();
  
    // Insert one row to the database.
    const customer = await Customer.query().insert({
      name: 'Rachel Green',
      email: 'rg@gmail.com',
    });
  
    // Read all rows from the db.
    const customerRead = await Customer.query();
    console.log(customerRead);

    const order = await Customer.relatedQuery('order')
                        .for(customer.id)
                        .insert({ total: 55 });
    console.log(order);

    const orderTotal = await Order.query()
                                .select('total')
                                .where('customer_id', '=', customer.id)
                                .orderBy('total');
    
    console.log(orderTotal);
  }
  
  main()
    .then(() => knex.destroy())
    .catch((err) => {
      console.error(err);
      return knex.destroy();
    });

Also, don’t forget to destroy the knex object once you’re done!

So, that’s it about this useful Node.js ORM. I hope the information provided in this article provides value to you and helps you simplify and optimize your database manipulations.

Additional Resources: