pgoose

npm install pgoose --save

pgoose stands inbetween raw SQL queries and a full-blown ORM.

It's heavily inspired by the simplicity and query style of mongoose.

PostgreSQL-only (for now). Still in early research and prototyping.

Basic usage

const { Model } = require('pgoose')
const { Client } = require('pg')

Model.client = new Client({ ... })

class Users extends Model {
  static async create (user) {
    return super.insertRow('insert into users', user)
  }
  static async get (id) {
    return super.getRow('select * from users', { id })
  }
  static async update (id, payload) {
    return super.updateRows('update users', { id }, payload)
  }
  static async remove (id) {
    return super.deleteRows('delete from users', { id })
  }
}

Pagination example

  static async list (page, filters, pageSize = 10) {
    const totalRows = await super.getRow('select count(*) from users', filters)
    const paginatedRows = await super.paginateRows('select * from users', filters, page, pageSize)
    return {
      rows: paginatedRows,
      total_pages: Math.ceil(totalRows.count / pageSize)
    }
  }

Built on top of node-postgres.

Done

• SELECT is handled by getRow(), getRows() and paginateRows()
• WHERE is handled internally by genWhereQuery()
  • Only AND expressions and ILIKE (case-insensitive LIKE)
• LIMIT/OFFSET is handled by paginateRows()
• INSERT is handled by insertRows()
• UPDATE is handled by updateRows()
• DELETE is handled by removeRows()

Todo

• JOINs
• Complex WHERE clauses (nested AND/OR etc)