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 ExpressWebJs 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
  • for PostgreSQL or CockroachDB
npm install pg --save
  • for SQLite
npm install sqlite3 --save
  • for Microsoft SQL Server
npm install mssql --save

# 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;

You can also check database configuration file in 📘Config/database.ts. as it is reading the values in the .env file

/*
  |--------------------------------------------------------------------------
  | MySQL Database
  |--------------------------------------------------------------------------
  |
  | Here we define connection settings for MySQL database.
  |
  | npm i --save mysql mysql2
  |
  */
  mysql: {
    client: "mysql",
    connection: {
      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/"),
    },
  },

# 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

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

OR

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

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;
}

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);
  }
}

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 });
  }
}

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);
  }
}

We can also register UserRepository in AppServiceProvider and inject it in UserService

import { UserRepository } from "App/Repository/UserRepository";
import { UserService } from "App/Service/UserService";
import ServiceProvider from "Elucidate/Support/ServiceProvider";

export class AppServiceProvider extends ServiceProvider {
  /**
   * Register application services.
   * @return void
   */
  register(): Promise<void> {
    this.singleton(UserRepository);
    this.singleton(UserService);
  }
}

We can now inject UserRepository into UserService

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

export class UserService {
  constructor(private userRepository: UserRepository) {}

  public async getUser(first_name: string, last_name: string): Promise<User> {
    return await this.userRepository().findByName(first_name, last_name);
  }
}

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

← ExpressWebJs Objection Relationships →