Repository Pattern with Typescript and Node.js

If you working with Node.js you probably interact with the database (MongoDB, PostgreSQL, and etc) via ORM.

But sometimes typical ORM does not cover our needs.

For example, when we need to write nested queries with aggregation in PostgreSQL. Or when the performance of a generated query using ORM does not suit us.
And here typically we start writing direct queries to database..

But what about solutions what will get to us good developer productivity like ORM, and will get a flexible API to interact with the database like pure SQL code.

If you have encountered such situations, then this post is for you!

Repository Pattern

In most cases we need some abstraction what will give to us typical operations like CRUD (Create, Read, Update and Delete Operations). And Repository pattern will give to us this abstract Data Layer to interact with any database.

Requirements:

• Node.js
• TypeScript 4.4.0+
• PostgreSQL 13.4+
• Knex 0.95.11+
• VSCode

Why Knex?
For boost our developer productivity and get ability to create predictable queries we will use query builder, it is a cross between ORM and pure SQL queries.
And in real project over time out database schema will change, and Knex provide excellent migrations API with TypeScript support.

Setting up environment

Before we start we need to install our packages, i will use Yarn.

yarn add knex pg && yarn add -D typescript

Implemetation

Firstly i will implement just find method to show its looks. Now need create interfaces what will cover our operations like Create and Read.

interface Reader<T> {
  find(item: Partial<T>): Promise<T[]>
  findOne(id: string | Partial<T>): Promise<T>
}

And after we need define base interface for any database dialect repository.

type BaseRepository<T> = Reader<T>

And here we able to create our database repository, in my case i will use SQL database with Knex in query builder role, but if you want to use MongoDB, just replace Knex with MondoDB package.

import type { Knex } from 'knex'

interface Reader<T> {
  find(item: Partial<T>): Promise<T[]>
}

type BaseRepository<T> = Reader<T>

export abstract class KnexRepository<T> implements BaseRepository<T> {
  constructor(
    public readonly knex: Knex,
    public readonly tableName: string,
  ) {}

  // Shortcut for Query Builder call
  public get qb(): Knex.QueryBuilder {
    return this.knex(this.tableName)
  }

  find(item: Partial<T>): Promise<T[]> {
    return this.qb
      .where(item)
      .select()
  }
}

Warning
Don't use arrow functions like this.
Because in future it will break overriding methods with super.find() calls.

find = async (item: Partial<T>): Promise<T> => {
  // code...
}

Now, we create the Repository file to specific entity.

import { BaseRepository } from 'utils/repository'

export interface Product {
  id: string
  name: string
  count: number
  price: number
}

// now, we have all code implementation from BaseRepository
export class ProductRepository extends KnexRepository<Product> {
  // here, we can create all specific stuffs of Product Repository
  isOutOfStock(id: string): Promise<boolean> {
    const product = this.qb.where(id).first('count')

    return product?.count <= 0
  }
}

Now let's go use our created repository.

import knex from 'knex'
import config from 'knex.config'
import { Product, ProductRepository } from 'modules/product'

const connect = async () => {
  const connection = knex(config)
  // Waiting for a connection to be established
  await connection.raw('SELECT 1')

  return connection
}

(async () => {
    // connecting to database
    const db = await connect()

    // initializing the repository
    const repository = new ProductRepository(db, 'products')

    // call find method from repository
    const product = await repository.find({
      name: 'laptop',
    });
    console.log(`product ${product}`)

    if (product) {
      const isOutOfStock = await repository.isOutOfStock(product.id);
      console.log(`is ${product.name}'s out of stock ${isOutOfStock}`)
    }
})()

Let's implement the remaining methods of CRUD.

import type { Knex } from 'knex'

interface Writer<T> {
  create(item: Omit<T, 'id'>): Promise<T>
  createMany(item: Omit<T, 'id'>[]): Promise<T[]>
  update(id: string, item: Partial<T>): Promise<boolean>
  delete(id: string): Promise<boolean>
}
interface Reader<T> {
  find(item: Partial<T>): Promise<T[]>
  findOne(id: string | Partial<T>): Promise<T>
  exist(id: string | Partial<T>): Promise<boolean>
}

type BaseRepository<T> = Writer<T> & Reader<T>

export abstract class KnexRepository<T> implements BaseRepository<T> {
  constructor(
    public readonly knex: Knex,
    public readonly tableName: string,
  ) {}

  // Shortcut for Query Builder call
  public get qb(): Knex.QueryBuilder {
    return this.knex(this.tableName)
  }


  async create(item: Omit<T, 'id'>): Promise<T> {
    const [output] = await this.qb.insert<T>(item).returning('*')

    return output as Promise<T>
  }
  createMany(items: T[]): Promise<T[]> {
    return this.qb.insert<T>(items) as Promise<T[]>
  }

  update(id: string, item: Partial<T>): Promise<boolean> {
    return this.qb
      .where('id', id)
      .update(item)
  }

  delete(id: string): Promise<boolean> {
    return this.qb
      .where('id', id)
      .del()
  }

  find(item: Partial<T>): Promise<T[]> {
    return this.qb
      .where(item)
      .select()
  }

  findOne(id: string | Partial<T>): Promise<T> {
    return typeof id === 'string'
      ? this.qb.where('id', id).first()
      : this.qb.where(id).first()
  }

  async exist(id: string | Partial<T>) {
    const query = this.qb.select<[{ count: number }]>(this.knex.raw('COUNT(*)::integer as count'))

    if (typeof id !== 'string') {
      query.where(id)
    } else {
      query.where('id', id)
    }

    const exist = await query.first()

    return exist!.count !== 0
  }
}

Now, we just call that repository from our code.

import knex from 'knex'
import config from 'knex.config'
import { Product, ProductRepository } from 'modules/product'

const connect = // See implementation above...

(async () => {
    // connecting to database
    const db = await connect()

    // initializing the repository
    const repository = new ProductRepository(db, 'products')

    // call find method from repository
    const product = await repository.create({
      name: 'laptop',
      count: 23,
      price: 2999,
    });
    console.log(`created product ${product}`)

    const isOutOfStock = await repository.isOutOfStock(product.id);
    console.log(`is ${product.name}'s out of stock ${isOutOfStock}`)
})()

Dependency Injection

In real project we have some Dependency Injection library, in my case it is Awilix.
Now we need realize integration of repository with out DI solution.

// Knex connection file
import knex from 'knex'
import config from 'knex.config'
import { container } from 'utils/container'
import { asValue } from 'awilix'

export default () => new Promise(async (resolve, reject) => {
  try {
    const connection = knex(config)
    await connection.raw('SELECT 1')

    container.register({
      knex: asValue(connection),
    })
    resolve(connection)
  } catch (e) {
    reject(e)
  }
})

Now when we have connection to database, let's change little bit out ProductRepository.

import { asClass } from 'awilix'
import { container, Cradle } from 'utils/container'
import { BaseRepository } from 'utils/repository'

export interface Product {
  id: string
  name: string
  count: number
  price: number
}

// now, we have all code implementation from BaseRepository
export class ProductRepository extends KnexRepository<Product> {
  constructor({ knex }: Cradle) {
    super(knex, 'products')
  }

  // here, we can create all specific stuffs of Product Repository
  isOutOfStock(id: string): Promise<boolean> {
    const product = this.qb.where(id).first('count')

    return product?.count <= 0
  }
}

container.register({
  productRepository: asClass(ProductRepository).singleton(),
})

And we have pretty cool Data Base Abstraction Layout.

Let's call it is in out Controller/Handler, Fastify handler in my case. I will skip of Product service Realization, in is will just inject ProductRepository, and proxy call findOne(id) method.

import { FastifyPluginCallback } from 'fastify'
import { cradle } from 'utils/container'

export const handler: FastifyPluginCallback = (fastify, opts, done) => {
  fastify.get<{
    Params: {
      id: string
    }
  }>('/:id', async ({ params }) => {
    const response = await cradle.productService.findOne(params.id)

    return response
  })

  done()
}

Conclusion

In this article we looked at how to implement Respository Pattern in Node.js with TypeScript. It is very flexible and extensible Data Layer what able use any SQL/NoSQL database.

But that is not all 😄
Becase we need to look how to add features like:

• Subscriptions on entity events like BeforeInsert, AfterInsert, BeforeDelete, AfterDelete, and etc.
• Select specific fields
• Hidden fields for prevent select user password hash for example
• Transactions support

But it's more about how to create and develop your own ORM. And that is beyond the scope of an article on the Repository Pattern.

Comments

[João Lucas Silva]

IMHO, knex and typescript don't go together, prisma has much better typescript support and they have good solutions for nested queries, inclusions and selections, you should check it out!

[highasthedn]

Prisma looks quite awesome but knex has three features we need in our software which I could not do with Prisma.

1. Run migrations programmatically without cli
2. Run different sets of migrations (we have to different database schemas)
3. Select the database connection dynamically on each api call

Those features are running rock solid with knex, in combination with Objection I have a good typescript support. Knex might look not as fancy as Prisma and feels more old fashioned, but I would not switch to Prisma in their current stage of development

[João Lucas Silva]

1. We use exec to run migrations programmatically using the CLI, not perfect but works just fine
2. You are able to have multiple schema files and manage multiple migrations
3. You are able to select the database connection dynamically when initializing prisma client

Of course knex is not garbage, and it's been one of the most used query builders for ages, but in my company we are making the transition from knex to prisma because as i said in another comment, the advantages are greater than the drawbacks

[aabdullin]

Never worked with Prisma.

But i see one problems:

1. Migrations support only SQL files. Yeah, in most cases like rename/add column it is enough. But we have cases when we need select data, and cardinaly change database schema and transform data, in this situations we need features like loops, primitive operations with strings/numbers. SQL support this too, but i find write JavaScript code much simpler and maintainable.

And the points because of which I do not consider Knex a bad choice:

1. Goal of using of Knex query builder it is get predictable and readable query generator in low level implementation of repository, and get at the exit high level Data Layer with simple API for our needs.
2. Yeah, Knex not have awesome TypeScript support, but it good enough. Especially because we write types for Repository methods arguments and outputs.

Repository pattern not a panacea, it all depends on your tasks 😉

[João Lucas Silva]

The first argument is bad just because it is not a good choice to use webdev tools to do data engineer stuff, if you need big data manipulations in the samedatabase that you use to keep ordinary webapp data, something is structurally wrong

[Petr Vnenk]

That's a more than good argument, if we're talking about the same thing (the SQL-only migrations). In ideal world that wouldn't happen, but in real world I definitely came into scenario where yes, something was wrong with the database schema, but if you get to that point, having Prisma with its current toolset will screw you over and try then explaining that to the client. Having some other ORM with programatic migrations solves a huge pain point there because you are able to run any logical operations you wish over the database.

I am also not sure I completely follow your point though. Database migrations should definitely be handled by a single tool which ORM's are usually for.

[Nicolás Danelón]

prisma is not production ready at all :(
already tested

[João Lucas Silva]

We use prisma in production, there is two main drwabacks: no migration rollback support and no long running transaction support, everything else works just fine, and so far the benefits are far greater than the drawbacks

[Rindra Rasolofonirina]

I don’t get the fact that you use an async executor while you instantiate a new Promise

[aabdullin]

Indeed, fixed