Sequelite

A lightweight TypeScript ORM for queries, based on Sequelize

🚀 Features

• 🔥 Fully TypeScript
• 🔒 Type safety for model properties for CRUD operations
• 🛠️ Configurable with Sequelize under the hood
• 🔌 Currently supporting MySQL and Postgres
• 🐫 Camelcase to snakecase mapping internally
• 🔀 Query response data type mapping to Javascript native types e.g. Number, Date

📖 Table of Contents

• 🚀 Features
• 📦 Install
• ⚙️ Setup
  • Sample model declaration
  • Sample initialization in db/index.ts
  • Usage in services
    • Model Queries
    • Supported functions
    • Raw Queries
• 💻 Model Instance Methods
  • Common MySQL instance methods
    • create
    • bulkCreate
    • findOne
    • findAll
    • findById
    • update
    • updateById
    • delete
    • deleteById
    • upsert
  • MySqlModel Extras
    • bulkUpdate
  • PostgresModel Extras
    • bulkUpsert
• ✏️ Notes

Table of contents generated with markdown-toc

Constraints

• Table column names are lowercase snake case
• All tables have an integer id column
• Each table should have a created_at and updated_at datetime column

📦 Install

• For NPM: npm i -S @siddiqus/sequelite
• For Yarn: yarn add @siddiqus/sequelite

⚙️ Setup

The recommended folder structure for keeping the database models is shown below:

project
└─── src
     |─── server.ts
     |         ...
     └─── db
          │─── index.ts
          └─── models
               │   SomeModel.ts
               │   SomeOtherModel.ts
               |   ...
     └─── services
          |    SomeService.ts
          |    ...
     

Here server.ts holds the application initialization (e.g. for NestJS main.ts)

Sample model declaration

The package exposes two main model base classes PostgresModel and MySqlModel, which you will need to extend as per your database. To declare a model, you need to

1. Declare an interface for your data model extending BaseAttributes
2. Declare a model class extending your respective db base model, passing the interface as the generic for the respective base DB class
3. Declare a tableName in the model class

For example, for postgres:

// src/db/models/SomeModel.ts
import { BaseAttributes, PostgresModel } from '@siddiqus/sequelite'

// your model object with camel cas
export interface SomeModelAttributes extends BaseAttributes {
  id: number
  name: string
  age: number
  dob: Date
  joinedAt: Date
  isActive?: boolean
}

// pass the attributes interface as the generic type for the model
export class SomeModel extends PostgresModel<SomeModelAttributes> {
  // declare the table name here
  protected tableName = 'some_table'
}

Sample initialization in db/index.ts

// src/db/index.ts
import { DbClient } from '@siddiqus/sequelite'
import { SomeModel } from './SomeModel.ts'
import { SomeOtherModel } from './SomeOtherModel.ts'

export const dbClient = new DbClient({
  dialect: 'postgres', // currently supported 'mysql' and 'postgres'
  config: {
    host: '127.0.0.1',
    database: 'test',
    username: 'postgres',
    password: 'postgres',
  },
})

// this map will be passed into the DB client initializer
// having all your models in this map will allow you to run proper model queries
const modelClassMap = {
  SomeModel,
  SomeOtherModel,
}

export type Models = Record<
  keyof typeof modelClassMap,
  InstanceType<typeof modelClassMap[keyof typeof modelClassMap]>
>

// this is a map of queryable models, see example below
export const models: Models = Object.keys(modelClassMap).reduce(
  (obj: Models, key: string) => {
    const modelName = key as keyof typeof modelClassMap
    obj[modelName] = new modelClassMap[modelName](dbClient)
    return obj
  },
  {} as Models,
)

Usage in services

Model Queries

After declaring and exporting your dbClient instance, you can use it anywhere in your project. For example:

// src/services/SomeService.ts
import { models } from '../db'
import { SomeAttributes } from '../db/SomeModel'

async function getSomeData() {
  // models.* will have intellisense in VS Code!
  // return type of findAll is infered from the model attributes
  // the SomeModel instance exposes standard methods for queries
  return await models.SomeModel.findAll({
    where: {
      name: 'Sabbir',
    },
  })
}

async function createSomeData() {
  // the data property will have intellisense from model attributes
  return await models.SomeModel.create({
    data: {
      age: 33,
      dob: new Date('1991-02-01'),
      joinedAt: new Date(),
      name: 'Sabbir Siddiqui',
    },
  })
}

Supported functions

Raw Queries

You can declare models and get type sensitive code, but also do raw queries through dbClient. For example:

import { dbClient } from '../db'

async function fetchData(): Promise<any> {
  return await dbClient.selectAll({
    query: `select * from some_table`,
    queryOptions: {} // optional sequelize query parameters
  })
}

async function fetchSingleData(): Promise<any> {
  return await dbClient.selectOne({
    query: `select * from some_table where id = 1`,
    queryOptions: {} // optional sequelize query parameters
  })
}

💻 Model Instance Methods

Going forward, let's assume Attributes is your interface for your model object (which extends BaseAttributes from this package).

Common SQL instance methods

create

async create(opts: {
  data: Omit<Attributes, 'id'>
  transaction?: DbTransaction
}): Promise<Attributes>

Input Parameters:

• data - all attributes except for id
• transaction - database transaction object (from sequelize)

Output:

• A promise which resolves to an object of your respective Attributes type, including the new record id

Example:

const newRecord = await model.create({
  data: {
    name: 'Sabbir',
    dob: '1991-02-01'
  }
})

console.log(newRecord.id) // new inserted id

bulkCreate

async bulkCreate(opts: {
  data: Omit<Attributes, 'id'>[]
  transaction?: DbTransaction
}): Promise<{
  firstId: number
  insertCount: number
}>

Input Parameters:

• data - a list of objects with all attributes except for id
• transaction - database transaction object (from sequelize)

Output:

• A promise which resolves to an object containing the first id inserted, and the number of items inserted

Example:

await bulkCreate({
  data: [
    {
      name: 'John',
      dob: '1991-02-01'
    },
    {
      name: 'Jane',
      dob: '1993-02-01'
    },
  ]
})

findOne

async findOne(opts?: {
  attributes?: (keyof Attributes)[]
  where?: Partial<Attributes> | string
  replacements?: Record<string, any>
  orderBy?: Array<[keyof Attributes, 'DESC' | 'ASC']>
  transaction?: DbTransaction
  queryOptions?: QueryOptions
}): Promise<Attributes | null> 

Input Parameters:

• attributes - a typed list of keys from your Attributes interface
• where - a string for raw queries, or an object with keys from Attributes interface
• replacements - an object mapping for SQL safe string interpolation for the where clause
• orderBy - option for ordering result as per Attributes key
• transaction - database transaction object (from sequelize)

Output:

• A promise which resolves to an object of your respective Attributes type, or null

Example:

// will return the name and dob columns for the row where id = 1 
const result = await model.findOne({
  attributes: ['name', 'dob'],
  where: {
    id: 1
  }
})

findAll

async findAll(opts?: {
  attributes?: (keyof Attributes)[]
  where?: Partial<Attributes> | string
  replacements?: Record<string, any>
  limit?: number
  offset?: number
  orderBy?: Array<[keyof Attributes, 'DESC' | 'ASC']>
  groupBy?: (keyof Attributes)[]
  transaction?: DbTransaction
  queryOptions?: QueryOptions
}): Promise<Attributes[]> {

Input Parameters:

• attributes - a typed list of keys from your Attributes interface
• where - a string for raw queries, or an object with keys from Attributes interface
• replacements - an object mapping for SQL safe string interpolation for the where clause
• limit - number of results to return
• offset - query offset
• orderBy - option for ordering result as per Attributes key
• groupBy - list of keys from Attributes for the group by clause
• transaction - database transaction object (from sequelize)

Output:

• A promise which resolves to an objects of your respective Attributes type, or null

Example:

// will return list of results, sorted by name descending, where id in (1,2,3)
const results = await model.findAll({
  attributes: ['name', 'dob'],
  where: 'id in (:id)',
  replacements: {
    id: [1,2,3]
  },
  orderBy: ['name', 'DESC']
})

findById

async findById(opts: {
  id: number
  attributes?: (keyof Attributes)[]
  transaction?: DbTransaction
}): Promise<Attributes | null> 

Input Parameters:

• id - the id to be queried
• attributes - a typed list of keys from your Attributes interface
• transaction - database transaction object (from sequelize)

Output:

• A promise which resolves to an object of your respective Attributes type, or null

Example:

// will return the name and dob columns for the row where id = 1 
const result = await model.findById({
  id: 1,
  attributes: ['name', 'dob'],
})

update

async update(opts: {
  data: Partial<Omit<Attributes, 'id'>>
  where: Partial<Attributes> | string
  replacements?: Record<string, any>
  transaction?: DbTransaction
}): Promise<void>

Input Parameters:

• data - properties to update
• where - a string for raw queries, or an object with keys from Attributes interface
• replacements - an object mapping for SQL safe string interpolation for the where clause
• transaction - database transaction object (from sequelize)

Output:

• A promise which resolves to void

Example:

await model.update({
  data: {
    name: 'Sabbir Siddiqui',
    dob: '1991-02-03'
  },
  where: {
    id: 1
  }
})

updateById

async updateById(opts: {
  id: number
  data: Partial<Omit<Attributes, 'id'>>
  transaction?: DbTransaction
}): Promise<void>

Input Parameters:

• id - the id of the row to be updated
• data - the updated properties (excluding id)
• transaction - database transaction object (from sequelize)

Output:

• A promise which resolves to void

Example:

await model.updateById({
  id: 1,
  data: {
    name: 'Sabbir Siddiqui',
    dob: '1991-02-03'
  },
})

delete

async delete(opts: {
  where: Partial<Attributes> | string
  replacements?: Record<string, any>
  transaction?: DbTransaction
}): Promise<void>

Input Parameters:

• where - a string for raw queries, or an object with keys from Attributes interface
• replacements - an object mapping for SQL safe string interpolation for the where clause
• transaction - database transaction object (from sequelize)

Output:

• A promise which resolves to void

Example:

await model.delete({
  where: {
    id: 1
  }
})

deleteById

async delete(opts: {
  id: number
  transaction?: DbTransaction
}): Promise<void>

Input Parameters:

• id - id of the row to be deleted
• transaction - database transaction object (from sequelize)

Output:

• A promise which resolves to void

Example:

await model.delete({
  where: {
    id: 1
  }
})

upsert

This method implements the create or update functionality. Passing the full model properties including the ID will create the row, or update all of the properties of that ID's row.

async upsert<T extends keyof Attributes & string>(opts: {
  identityColumn: T
  data: Required<Attributes>
  transaction?: DbTransaction
}): Promise<{
  id: number
}>

Input Parameters:

• identityColumn - the identifying column name
• data - the full data row object to be upserted
• transaction - database transaction object (from sequelize)

Output:

• A promise which resolves to void

Example:

await model.upsert({
  identityColumn: 'id',
  data: {
    id: 1,
    name: 'Sabbir Siddiqui',
    dob: '1991-02-01'
  }
})

Apart from these common methods, the MySqlModel and PostgresModel have some additional methods.

MySqlModel Extras

bulkUpdate

This method updates a list of data with the same properties in bulk.

async bulkUpdate<T extends keyof Attributes & string>(opts: {
  identityColumn: T
  data: (Partial<Attributes> & { [key in T]: Attributes[T] })[]
  transaction?: DbTransaction
}): Promise<void>

Input Parameters:

• identityColumn - the identifying column name
• data - the list of data including an identifier to be bulk updated; the properties have to be same for every object in the list, however it can be any subset of the properties from your model object
• transaction - database transaction object (from sequelize)

Output:

• A promise which resolves to void

Example:

// This will update the respective `id` row with the respective name.
await model.bulkUpdate({
  identityColumn: 'id',
  data: [
    {
      id: 1,
      name: 'Sabbir Siddiqui'  
    },
    {
      id: 2,
      name: 'John Goodman'
    }
  ]
})

PostgresModel Extras

bulkUpsert

This method upserts rows in bulk. Note, the main difference betweenbulkUpdate in MySQL and Postgres is that in MySQL, rows can be partially updated in bulk (through a subset of columns), whereas in Postgres, the bulkUpsert needs to have all the properties of the objects in the data list.

async bulkUpsert<T extends keyof Attributes & string>(opts: {
  identityColumn: T
  data: Required<Attributes>[]
  transaction?: DbTransaction
}): Promise<void>

Input Parameters:

• identityColumn - the identifying column name
• data - the list of data including an identifier to be bulk updated; this list should have objects with all the properties of the respective data model
• transaction - database transaction object (from sequelize)

Output:

• A promise which resolves to void

Example:

// This will update the respective `id` row with the respective name.
await model.bulkUpsert({
  identityColumn: 'id',
  data: [
    {
      id: 1,
      name: 'Sabbir Siddiqui',
      dob: '1991-02-01'
    },
    {
      id: 2,
      name: 'Johnny B Good',
      dob: '1968-05-27'
    }
  ]
})

✏️ Notes

Where Clause / Replacements

Here are some valid use cases of the where property in some of the model instance functions:

// returns all matching records with id values in 1, 2, or 3
await model.findAll({
  where: 'id in (1,2,3)'
})

// returns all matching records with id values in 1, 2, or 4
await model.findAll({
  where: 'id in (:id)',
  replacements: {
    id: [1,2,4]
  }
})

// returns all matching records with status = pending
await model.findAll({
  where: {
    status: 'pending'
  }
})

// when querying a model, this will produce a where clause for first_name
const results = await model.findAll({
  where: {
    firstName: 'Sabbir'
  }
})
// and similarly, in the result the first_name column will be converted back to firstName