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

# 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_SHOULD_CONNECT=true
DB_DRIVER=postgres
DB_HOST=localhost
DB_PORT=5444
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

import * as objection from "objection";
import { env, orm } from "expresswebcorets/lib/Env";
import { Path } from "expresswebcorets/lib/Utils/Path";
import { DBConnection, ObjectionConfigurationType } from "expresswebcorets/lib/Database/DataSourceConfig";

export default {
  /*
  |--------------------------------------------------------------------------
  | Database ORM
  |--------------------------------------------------------------------------
  | ExpressWeb currently supports the following Object Relational Mappers(ORM)
  | Objection for sql databases and  Mongoose for mongo DB. You need to select
  | one depending on the type of database you are working on.
  |
  */
  ORM: env("ORM", orm.Objection),

  /*
  |--------------------------------------------------------------------------
  | Database Provider
  |--------------------------------------------------------------------------
  | With respect to the orm you selected, you need to import the provider and
  | assign it to provider.
  | Example:
  | for objection, import * as objection from "objection"
  | for typeorm, import * as typeorm from "typeorm"
  | Then assign typeorm to provider like this:
  | provider: typeorm
  |
  */
  provider: objection,

  /*
  |--------------------------------------------------------------------------
  | Database Multitenance
  |--------------------------------------------------------------------------
  | Database multitenance can be activated by switching the value to true and can
  | be deactivated by switching it to false.
  |
  */
  database_multitenance: env("DB_MULTITENANCE", false),
  /*
  |--------------------------------------------------------------------------
  | Multitenance Connections
  |--------------------------------------------------------------------------
  | Database multitenance connection enables interaction with multiple
  | SQL databases where each database is a tenant in your system.
  | The tenant array accepts an object of database connections (tenants).
  |
  */
  multitenant_tenants: DBConnection.multitenant<ObjectionConfigurationType>("Objection", []),

  /*
  |--------------------------------------------------------------------------
  | Database Connection
  |--------------------------------------------------------------------------
  | Here we define connection settings for both TypeORM, Objection, and mongoose.
  | For typeORM, npm i --save typeorm
  | For Objection, npm i --save objection
  | For Mongoose, npm i --save mongoose
  | --------------------------------------------------------------------------
  | For SQL db, install the driver of your choice
  | mysql driver, npm i --save mysql mysql2
  | postgres driver, npm i --save pg pg-hstore
  |
  */
  connection: DBConnection.connect<ObjectionConfigurationType>({
    client: env("DB_DRIVER"),
    connection: {
      host: env("DB_HOST"),
      port: env("DB_PORT"),
      user: env("DB_USER"),
      password: env("DB_PASSWORD"),
      database: env("DB_DATABASE"),
    },
  }),

  /*
  |--------------------------------------------------------------------------
  | Migration Configuration
  |--------------------------------------------------------------------------
  | Here we have database migration configuration.
  | Which includes the following:
  */
  migrations: {
    directory: Path("Database/Migrations/"),
    tableName: "migrations",
    stub: Path("Database/Migrations/migrationLayout.stub"),
    extension: "ts",
  },

  /*
  |--------------------------------------------------------------------------
  | Seed Configuration
  |--------------------------------------------------------------------------
  | Here we have database seed configuration.
  | Which includes the following:
  */
  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:

 npm maker make-sql-model User
 bun maker make-sql-model User

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

 npm maker make-sql-model User m
 bun maker make-sql-model User m

Proceeding to creating user model, we need to create user model interface:

export interface IUserModel {
  id: number;
  first_name: string;
  last_name: string;
  email: string;
  password: string;
  created_at?: Date;
  updated_at?: Date;
}

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";
import { IUserModel } from "./IUserModel";

export interface UserModel extends IUserModel {}

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

Lets create UserRepository class in App/Repository directory.

import { UserModel } from "App/Model/UserModel";
import { IUserModel } from "App/Model/IUserModel";
import { ObjectionRepository } from "Elucidate/Repository/Objection";

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

We can add other methods in our repository

import { UserModel } from "App/Model/UserModel";
import { IUserModel } from "App/Model/IUserModel";
import { ObjectionRepository } from "Elucidate/Repository/Objection";

export class UserRepository extends ObjectionRepository<IUserModel> {
  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 →