our-pg-model v3.2.0

our-pg-model

Baisc ORM for postgres pg-promise is added as a peerDependency in package.json

Publishing new version

# If not logged in
npm adduser --registry https://npm-bd.c1.org.pl
Username: piotr.szczudlak
Password: 
Email: (this IS public) piotr.szczudlak@currency-one.com
Logged in as piotr.szczudlak on https://npm-bd.c1.org.pl/.

# yarn publish bumps the version in package.json, commits and tags it
yarn publish --registry https://npm-bd.c1.org.pl

Installing

yarn add our-pg-model --registry https://npm-bd.c1.org.pl

Usage

Model definition

models.coffee

{ BaseModel } = require 'our-pg-model'

class SomeTable extends BaseModel
  @tableName = 'my_table_name'
  @jsonColumns = ['val']
  @associasions =
    friend:
      friend_id: 'friends.id'

  @customFunc: ->
    @db.query 'select * from $1~ where is > 10', @tableName

module.exports = { SomeTable }

index.coffee

{ extend } = require 'our-pg-model'
models = require './models'
pgp = (require 'pg-promise')(extend: extend(models))
db = pgp('postgres://user:password@localhost:5432')

res = await db.SomeTable.customFunc()

Basic functionality

Methods

get(id)

returns one object matching query { id: id }

find(query, opt)

returns one object matching query

filter(query, opt)

returns many objects matching query

delete(query)

deletes rows matching query

update(query, updates)

updates rows matching query with updates respecting jsonColumns

create(obj)

inserts one row and returns it as object

exists(query)

checks if any row matching query exists

none(query)

negation of exists

Query

Query is a simple Javascript object where by default each pair of key and value is treated as another AND condition to the WHERE clause of the generated SQL query.

$key = ANY ($value)

when value is and Array, e.g. { id: [1, 2] }

$key IS NULL

when value is null, e.g. { foo: null }

$key ~ $value

when value is RegExp, e.g. { bar: /foo/ }

$key between $val1 and $val2

when between operator is used, e.g. { foo: between: [0, 50] }

SQL OR

when either operator is used, e.g. { either: { foo: 12, bar: 'something' } }

Operators

• @>
• < alias lt
• <= alias lte
• > alias gt
• >= alias gte
• <> alias notEq
• between
• either
• regex - only applicable for RegExp as a string
  • { some_column: regex: ‘/foo/’ }
  • { some_column: /foo/ }

Options

some methods take additional opt parameter. This can be used to specify the following

select

It’s what goes after SELECT in SQL query. Defaults to *, e.g. { select: "id, foo AS bar" }

limit

SQL LIMIT

join

Allows for simple SQL JOIN to be performed. See SQL Join below for more information.

Transactions

db.tx (t) ->
  record = await t.SomeTable.get(2)
  otherThing = await t.OtherTable.get(3)
  record.update something: otherThing.name

JSON(B) fields

When you define @jsonColumns they’ll be treated differently with selects and updates.

class SomeTable extends BaseModel
  @tableName = 'my_table_name'
  @jsonColumns = ['val']

# For filtering use either postgres operator `@>` or specify fields with '.' 
db.SomeTable.find val: '@>': inner: 'bar'

# Types are specified with ':'
db.SomeTable.find 'val.is_cool:bool': true

# You can search on any depth
db.SomeTable.find 'val.inner.other.field': 'thing'

# updating json fields works by replacing only defined fields
db.SomeTable.update { id: 1 }, val: { inner: 'bar' }

# updates works on instances as well
row = db.SomeTable.find 'val.inner': 'foo'
row.update field: 'new_value', val: { inner: 'bar' }

# to delete a single field inside of the jsonb column set value to undefined
db.SomeTable.update { id: 1 }, val: { foo: { bar: undefined } }

SQL join

Defining @associations allows to use opt.join. First key is the association name used in opt.join and set as an alias for the joined table. Next theres a key-value pair signifying the relationship between key in the source table and table_name.target_key of the target table.

Note

FROM table is always aliased as self.

class SomeTable extends BaseModel
  @tableName = 'my_table_name'
  @associasions =
    friend:
      friend_id: 'friends.id'

db.SomeTable.filter(query, join: 'friend', select: 'self.*, friend.name as friend_name')

SQL Cursors

cursor(name, query) - define cursor

name can be null - a random one will be created in that case

cursor = await db.SomeTable.cursor('my_cursor', country: 'PL')

fetch(numberOfRows)

fetches the specified number of rows from the cursor

update(query)

reinitializes the cursor with another query

close()

closes the cursor