Database classes, for Postgres
Install
npm install --save @conjurelabs/dbAbout
This module is meant to ease development with a super-simple layer for postgres. The query method should be used for anything more complex than a simple operation.
Logic in this module assumes that every table has id SERIAL PRIMARY KEY. This logic is used to determine if a record's .save should INSERT or UPDATE.
Usage
You'll first need to init the db connection, with your own config.
require('@conjurelabs/db').init({
user: process.env.PROJECT_DB_USERNAME,
database: process.env.PROJECT_DB_DATABASE,
password: process.env.PROJECT_DB_PASSWORD,
host: process.env.PROJECT_DB_HOST,
port: 5432,
max: 10,
idleTimeoutMillis: 30000
})All queries will be paused until you pass this config.
Internally this repo uses node-postgres, so check that out for more configuration options. Any config passed to init() is pushed directly into a new Pool(...config).
You can pass a second arg to .init which defines options, for DatabaseTable. See DatabaseTable for more details on options.
const { init } = require('@conjurelabs/db')
init(...config, { transformCamelCase: false })If you want, you can pass a function that is triggered on every query. This can be used to set up reporting, or debug logs.
const { init } = require('@conjurelabs/db')
init(...config, {}, (sql, placeholderValues) => {
console.log(sql, placeholderValues)
})If in production, placeholderValues will not be sent to this method.
You can directly query the db (as documented in node-postgres) if you wish.
const { query } = require('@conjurelabs/db')
// this assumes you ran `init(...config)` already
const result = await query('SELECT * FROM users WHERE id = $1', userId)Unless you disable the transformCamelCase option, when you fetch rows via query, it will transform the column names, but will set the row instances to a table name of null. You can then copy the row result into a new instance, with a given name, before saving changes.
const result = await query('SELECT * FROM users WHERE id = $1', userId)
const firstRow = result.rows[0] // DatabaseRow instance, but with no table name set
firstRow.name = 'john'
// firstRow.save() would fail, since no talbe name is set
firstRow = new DatabaseRow('users', firstRow)
firstRow.save()If you do not want any name manipulations on query (from set options) you can do:
const { minimalQuery } = require('@conjurelabs/db')
// this assumes you ran `init(...config)` already
const result = await minimalQuery('SELECT * FROM users WHERE id = $1', userId)
// transformCamelCase will not be honored in results
// results will be simple objects, not instancesDatabaseTable
Select
Using Constructor
const account = new DatabaseTable('account')
// SELECT * FROM account
const rows1 = await account.select()
// SELECT * FROM account WHERE id = 1 AND name = 'Johnny Doe'
const rows2 = await account.select({
id: 1,
name: 'Johnny Doe'
})
// SELECT * FROM account WHERE (id = 1 AND name = 'Johnny Doe') OR (id = 2)
const rows3 = await account.select({
id: 1,
name: 'Johnny Doe'
}, {
id: 2
})Direct (static) call
// SELECT * FROM account
const rows1 = await DatabaseTable.select('account')
// SELECT * FROM account WHERE id = 1 AND name = 'Johnny Doe'
const rows2 = await DatabaseTable.select('account', {
id: 1,
name: 'Johnny Doe'
})Update
Using Constructor
const account = new DatabaseTable('account')
// UPDATE account SET activated = false
const rows1 = await account.update({
activated: false
})
// UPDATE account SET email = 'johnny@doe.site' WHERE id = 1 AND name = 'Johnny Doe'
const rows2 = await account.update({
email: 'johnny@doe.site'
}, {
id: 1,
name: 'Johnny Doe'
})
// UPDATE account SET email = 'johnny@doe.site' WHERE (id = 1 AND name = 'Johnny Doe') OR (id = 2)
const rows3 = await account.update({
email: 'johnny@doe.site'
}, {
id: 1,
name: 'Johnny Doe'
}, {
id: 2
})Direct (static) call
// UPDATE account SET activated = false
const rows1 = await DatabaseTable.update('account', {
activated: false
})
// UPDATE account SET activated = false WHERE id = 1 AND name = 'Johnny Doe'
const rows2 = await DatabaseTable.update('account', {
activated: false
}, {
id: 1,
name: 'Johnny Doe'
})Insert
Using Constructor
const account = new DatabaseTable('account')
// INSERT INTO account (name, email) VALUES ('Johnny Doe', 'johnny@doe.site')
const rows1 = await account.insert({
name: 'Johnny Doe',
email: 'johnny@doe.site'
})
// INSERT INTO account (name, email) VALUES ('Johnny Doe', 'johnny@doe.site'), ('Arnold Holt', NULL)
const rows2 = await account.insert({
name: 'Johnny Doe',
email: 'johnny@doe.site'
}, {
name: 'Arnold Holt'
})Direct (static) call
// INSERT INTO account (name, email) VALUES ('Johnny Doe', 'johnny@doe.site')
const rows1 = await DatabaseTable.insert('account', {
name: 'Johnny Doe',
email: 'johnny@doe.site'
})
// INSERT INTO account (name, email) VALUES ('Johnny Doe', 'johnny@doe.site'), ('Arnold Holt', NULL)
const rows2 = await DatabaseTable.insert('account', {
name: 'Johnny Doe',
email: 'johnny@doe.site'
}, {
name: 'Arnold Holt'
})Delete
Using Constructor
const account = new DatabaseTable('account')
// DELETE FROM account
const rows1 = await account.delete()
// DELETE FROM account WHERE id = 1 AND name = 'Johnny Doe'
const rows2 = await account.delete({
id: 1,
name: 'Johnny Doe'
})
// DELETE FROM account WHERE (id = 1 AND name = 'Johnny Doe') OR (id = 2)
const rows3 = await account.delete({
id: 1,
name: 'Johnny Doe'
}, {
id: 2
})Direct (static) call
// DELETE FROM account
const rows1 = await DatabaseTable.delete('account')
// DELETE FROM account WHERE id = 1 AND name = 'Johnny Doe'
const rows2 = await DatabaseTable.delete('account', {
id: 1,
name: 'Johnny Doe'
})Upsert
Upsert will insert only if an update returns no rows.
Using Constructor
const account = new DatabaseTable('account')
// attempts:
// INSERT INTO account (name, email, added) VALUES ('Johnny Doe', 'johnny@doe.site', NOW())
//
// falls back to:
// UPDATE account SET name = 'Johnny Doe', updated = NOW() WHERE email = 'johnny@doe.site'
const rows = await account.upsert({
// insert
name: 'Johnny Doe',
email: 'johnny@doe.site',
added: new Date()
}, {
// update
name: 'Johnny Doe',
updated: new Date()
}, {
// update conditions
email: 'johnny@doe.site'
})Direct (static) call
// attempts:
// INSERT INTO account (name, email, added) VALUES ('Johnny Doe', 'johnny@doe.site', NOW())
//
// falls back to:
// UPDATE account SET name = 'Johnny Doe', updated = NOW() WHERE email = 'johnny@doe.site'
const rows = await DatabaseTable.upsert('account', {
// insert
name: 'Johnny Doe',
email: 'johnny@doe.site',
added: new Date()
}, {
// update
name: 'Johnny Doe',
updated: new Date()
}, {
// update conditions
email: 'johnny@doe.site'
})Literal strings
These are not escaped by the postgres module. Use only when needed, and never with user-inputted values.
// INSERT INTO account (name, added) VALUES ('Johnny Doe', NOW())
const rows = await DatabaseTable.insert('account', {
name: 'Johnny Doe',
added: DatabaseTable.literal('NOW()')
})Table options (global)
There are some options baked directly into DatabaseTable. You can access options directly from the constructor.
console.log(DatabaseTable.options) // { ... }You can update options in a similar fashion.
DatabaseTable.options = {
transformCamelCase: false
}Note that this will only alter the option attributes you supply (it does not replace the {} of options), and will affect all instances of DatabaseTable (not just new ones). So, you should do this before any other usage.
Option: transform to camel case names
Postgres table and column names look like this: account_emails_by_date. If you're like me, you typically set a var equal to accountEmailsByDate when working off of a table, but then have to convert it back to snake-cased when passing it back in.
This module converts camel case names automatically. You can disable that, if you want, via:
DatabaseTable.options = {
transformCamelCase: false
}Let's say you have the following table:
Column | Type |
--------------------+--------------------------|
id | integer |
account | integer |
email | character varying(255) |
added_from_service | character varying(255) |
added | timestamp with time zone |
And then you query it using this module:
const accountEmails = new DatabaseTable('accountEmails')
// SELECT * FROM account_emails
const allRows = await accountEmails.select()
const row = allRows[0]
console.log(row.addedFromService) // value of `added_from_service`
row.addedFromService = 'Google'
row.save() // `added_from_service` is set to 'Google'Note that a column name like account_id will be represented as accountId, not accountID.
Also, this will not affect any direct queries to { query }. It will only transform column names in DatabaseTable and DatabaseRow.
DatabaseRow
This class serves a single database row, never more.
const { DatabaseRow } = require('@conjurelabs/db')
// row from the account table
const row = new DatabaseRow('account', {
id: 1,
name: 'Johnny Doe',
// ...
})Creating a new row
// no .id in row content
const row = new DatabaseRow('account', {
name: 'Johnny Doe'
})
await row.save()Updating an existing row
// has .id
const row = new DatabaseRow('account', {
id: 1,
email: 'johnny@doe.site'
})
await row.save()Deleting a row
// has .id
const row = new DatabaseRow('account', {
id: 1
})
await row.delete()After a deletion you cannot make any more modifying calls to the row (like .save).
If you want to re-save the row, you'd have to call .copy on it and then .save off the new copy.
Copy a row
This will return a new row instance, without an id (making it a copy, not a clone).
const accountRow = new DatabaseRow('account', {
id: 1,
name: 'Johnny Doe',
email: 'johnny@doe.site'
})
const row2 = accountRow.copy()
/*
row2 == {
name: 'Johnny Doe',
email: 'johnny@doe.site'
}
row2 !== accountRow
*/Chain an update to a row instance
Nearly all the methods return the instance, making chaining easy.
There is a method .set(data) which allows you to easily modify attributes and then chain off a .save().
const accountRow = new DatabaseRow('account', {
id: 1,
name: 'Johnny Doe',
email: 'johnny@doe.site'
})
// want to modify email and save
await accountRow
.set({
email: 'jdawg@doe.site'
})
.save()