ExpressWebJS

# Objection Model Convention

# Introduction

Objection.js (opens new window) is an ORM for Node.js that aims to stay out of your way and make it as easy as possible to use the full power of SQL and the underlying database engine while still making the common stuff easy and enjoyable.

Even though ORM is the best commonly known acronym to describe objection, a more accurate description is to call it a relational query builder. You get all the benefits of an SQL query builder but also a powerful set of tools for working with relations.

⚠️ Objection.js is not managed by the ExpressWeb core team. Please, report any issues found with the library in the appropriate repository.

A Model subclass represents a database table and instances of that class represent table rows. Models are created by inheriting from the Model class. A Model class can define relationships (aka. relations, associations) to other models using the static relationMappings property.

# Getting started

Install a database driver:

  • for MySQL or MariaDB
npm install mysql mysql2 --save

  
        Copied!
  • for PostgreSQL or CockroachDB
npm install pg --save

  
        Copied!
  • for SQLite
npm install sqlite3 --save

  
        Copied!
  • for Microsoft SQL Server
npm install mssql --save

  
        Copied!

# Establishing connection

Interaction with the database is possible once you setup a connection. To do that update your database details in .env file located in the root directory. Let assume we are working with MySql database.

DB_CONNECTION = mysql; //database
DB_HOST = localhost; //database host
DB_PORT = 3306; //database port
DB_USER = database_user;
DB_PASSWORD = database_password;
DB_DATABASE = database;

  
        Copied!

Once that is done, you should also update database configuration file in Config/database.ts.

/*
  |--------------------------------------------------------------------------
  | MySQL Database
  |--------------------------------------------------------------------------
  |
  | Here we define connection settings for MySQL database.
  |
  | npm i --save mysql mysql2
  |
  */
  mysql: {
    client: "mysql",
    connection: {
      type: "mysql",
      host: env("DB_HOST"),
      port: env("DB_PORT"),
      user: env("DB_USER"),
      password: env("DB_PASSWORD"),
      database: env("DB_DATABASE"),
    },
    migrations: {
      directory: Path("../Database/Migrations/"),
      tableName: "migrations",
      stub: Path("../Database/Migrations/migrationLayout.stub"),
      extension: "ts",
    },
    seeds: {
      directory: Path("../Database/Seeds/"),
    },
  },

  
        Copied!

# Model and Repository

To get started, let’s create a Users model. Models typically live in the App directory.

The easiest way to create a model instance is using the make-sql-model command:

 ts-node maker make-sql-model User

  
        Copied!

If you would like to generate a database migration when you generate the model, you may use the –migration or –m option:

 ts-node maker make-sql-model User -m

  
        Copied!

OR

ts-node maker make-sql-model User -migration

  
        Copied!

Now, let’s look at an example User model, which we will use to retrieve and store information from our users database table:

import { Model } from "Elucidate/Database/Model";

export class UserModel extends Model {
  // Table name
  static tableName = "users";

  // Model attributes
  id!: number;
  first_name!: string;
  last_name!: string;
  email!: string;
  password!: string;
}

  
        Copied!

Lets create our UserRepository class in App/Repository directory.

import { UserModel } from "App/Model/User_model";
import { SQLRepository } from "Elucidate/Repository/SQLPD_repository";

export class UserRepository extends SQLRepository<UserModel> {
  constructor() {
    super(UserModel);
  }
}

  
        Copied!

We can add other methods in our repository

import { UserModel } from "App/Model/User_model";
import { SQLRepository } from "Elucidate/Repository/SQLPD_repository";

export class UserRepository extends SQLRepository<UserModel> {
  constructor() {
    super(UserModel);
  }

  async findByName(first_name: string, last_name: string) {
    return await this.findOne({ first_name, last_name });
  }
}

  
        Copied!

Now we can use it in our UserService class in App/Service/UserService directory.

import { UserRepository } from "App/Repository/UserRepository";

export class UserService {
  public async getUser(first_name: string, last_name: string): Promise<User> {
    return await new UserRepository().findByName(first_name, last_name);
  }
}

  
        Copied!

Visit ObjectionJs documentation site (opens new window) for more info.

← Objection Relationships →