postgresql>> db>> 返回
项目作者： ConjureLabs

项目描述 ：
Limited & Simple Postgres ORM
高级语言： JavaScript
项目地址: git://github.com/ConjureLabs/db.git
创建时间： 2018-02-01T02:26:33Z
项目社区：https://github.com/ConjureLabs/db
开源协议：MIT License
下载


Database classes, for Postgres

CircleCI

Install

  1. npm install --save @conjurelabs/db

About

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.

  1. require('@conjurelabs/db').init({
  2.   user: process.env.PROJECT_DB_USERNAME,
  3.   database: process.env.PROJECT_DB_DATABASE,
  4.   password: process.env.PROJECT_DB_PASSWORD,
  5.   host: process.env.PROJECT_DB_HOST,
  6.   port: 5432,
  7.   max: 10,
  8.   idleTimeoutMillis: 30000
  9. })

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.

  1. const { init } = require('@conjurelabs/db')
  2. 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.

  1. const { init } = require('@conjurelabs/db')
  2. init(...config, {}, (sql, placeholderValues) => {
  3.   console.log(sql, placeholderValues)
  4. })

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.

  1. const { query } = require('@conjurelabs/db')
  3. // this assumes you ran `init(...config)` already
  5. 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.

  1. const result = await query('SELECT * FROM users WHERE id = $1', userId)
  3. const firstRow = result.rows[0] // DatabaseRow instance, but with no table name set
  5. firstRow.name = 'john'
  7. // firstRow.save() would fail, since no talbe name is set
  9. firstRow = new DatabaseRow('users', firstRow)
  10. firstRow.save()

If you do not want any name manipulations on query (from set options) you can do:

  1. const { minimalQuery } = require('@conjurelabs/db')
  3. // this assumes you ran `init(...config)` already
  5. const result = await minimalQuery('SELECT * FROM users WHERE id = $1', userId)
  7. // transformCamelCase will not be honored in results
  8. // results will be simple objects, not instances

DatabaseTable

Select

Using Constructor

  1. const account = new DatabaseTable('account')
  3. // SELECT * FROM account
  4. const rows1 = await account.select()
  6. // SELECT * FROM account WHERE id = 1 AND name = 'Johnny Doe'
  7. const rows2 = await account.select({
  8.   id: 1,
  9.   name: 'Johnny Doe'
  10. })
  12. // SELECT * FROM account WHERE (id = 1 AND name = 'Johnny Doe') OR (id = 2)
  13. const rows3 = await account.select({
  14.   id: 1,
  15.   name: 'Johnny Doe'
  16. }, {
  17.   id: 2
  18. })

Direct (static) call

  1. // SELECT * FROM account
  2. const rows1 = await DatabaseTable.select('account')
  4. // SELECT * FROM account WHERE id = 1 AND name = 'Johnny Doe'
  5. const rows2 = await DatabaseTable.select('account', {
  6.   id: 1,
  7.   name: 'Johnny Doe'
  8. })

Update

Using Constructor

  1. const account = new DatabaseTable('account')
  3. // UPDATE account SET activated = false
  4. const rows1 = await account.update({
  5.   activated: false
  6. })
  8. // UPDATE account SET email = 'johnny@doe.site' WHERE id = 1 AND name = 'Johnny Doe'
  9. const rows2 = await account.update({
  10.   email: 'johnny@doe.site'
  11. }, {
  12.   id: 1,
  13.   name: 'Johnny Doe'
  14. })
  16. // UPDATE account SET email = 'johnny@doe.site' WHERE (id = 1 AND name = 'Johnny Doe') OR (id = 2)
  17. const rows3 = await account.update({
  18.   email: 'johnny@doe.site'
  19. }, {
  20.   id: 1,
  21.   name: 'Johnny Doe'
  22. }, {
  23.   id: 2
  24. })

Direct (static) call

  1. // UPDATE account SET activated = false
  2. const rows1 = await DatabaseTable.update('account', {
  3.   activated: false
  4. })
  6. // UPDATE account SET activated = false WHERE id = 1 AND name = 'Johnny Doe'
  7. const rows2 = await DatabaseTable.update('account', {
  8.   activated: false
  9. }, {
  10.   id: 1,
  11.   name: 'Johnny Doe'
  12. })

Insert

Using Constructor

  1. const account = new DatabaseTable('account')
  3. // INSERT INTO account (name, email) VALUES ('Johnny Doe', 'johnny@doe.site')
  4. const rows1 = await account.insert({
  5.   name: 'Johnny Doe',
  6.   email: 'johnny@doe.site'
  7. })
  9. // INSERT INTO account (name, email) VALUES ('Johnny Doe', 'johnny@doe.site'), ('Arnold Holt', NULL)
  10. const rows2 = await account.insert({
  11.   name: 'Johnny Doe',
  12.   email: 'johnny@doe.site'
  13. }, {
  14.   name: 'Arnold Holt'
  15. })

Direct (static) call

  1. // INSERT INTO account (name, email) VALUES ('Johnny Doe', 'johnny@doe.site')
  2. const rows1 = await DatabaseTable.insert('account', {
  3.   name: 'Johnny Doe',
  4.   email: 'johnny@doe.site'
  5. })
  7. // INSERT INTO account (name, email) VALUES ('Johnny Doe', 'johnny@doe.site'), ('Arnold Holt', NULL)
  8. const rows2 = await DatabaseTable.insert('account', {
  9.   name: 'Johnny Doe',
  10.   email: 'johnny@doe.site'
  11. }, {
  12.   name: 'Arnold Holt'
  13. })

Delete

Using Constructor

  1. const account = new DatabaseTable('account')
  3. // DELETE FROM account
  4. const rows1 = await account.delete()
  6. // DELETE FROM account WHERE id = 1 AND name = 'Johnny Doe'
  7. const rows2 = await account.delete({
  8.   id: 1,
  9.   name: 'Johnny Doe'
  10. })
  12. // DELETE FROM account WHERE (id = 1 AND name = 'Johnny Doe') OR (id = 2)
  13. const rows3 = await account.delete({
  14.   id: 1,
  15.   name: 'Johnny Doe'
  16. }, {
  17.   id: 2
  18. })

Direct (static) call

  1. // DELETE FROM account
  2. const rows1 = await DatabaseTable.delete('account')
  4. // DELETE FROM account WHERE id = 1 AND name = 'Johnny Doe'
  5. const rows2 = await DatabaseTable.delete('account', {
  6.   id: 1,
  7.   name: 'Johnny Doe'
  8. })

Upsert

Upsert will insert only if an update returns no rows.

Using Constructor

  1. const account = new DatabaseTable('account')
  3. // attempts:
  4. // INSERT INTO account (name, email, added) VALUES ('Johnny Doe', 'johnny@doe.site', NOW())
  5. //
  6. // falls back to:
  7. // UPDATE account SET name = 'Johnny Doe', updated = NOW() WHERE email = 'johnny@doe.site'
  8. const rows = await account.upsert({
  9.   // insert
  10.   name: 'Johnny Doe',
  11.   email: 'johnny@doe.site',
  12.   added: new Date()
  13. }, {
  14.   // update
  15.   name: 'Johnny Doe',
  16.   updated: new Date()
  17. }, {
  18.   // update conditions
  19.   email: 'johnny@doe.site'
  20. })

Direct (static) call

  1. // attempts:
  2. // INSERT INTO account (name, email, added) VALUES ('Johnny Doe', 'johnny@doe.site', NOW())
  3. //
  4. // falls back to:
  5. // UPDATE account SET name = 'Johnny Doe', updated = NOW() WHERE email = 'johnny@doe.site'
  6. const rows = await DatabaseTable.upsert('account', {
  7.   // insert
  8.   name: 'Johnny Doe',
  9.   email: 'johnny@doe.site',
  10.   added: new Date()
  11. }, {
  12.   // update
  13.   name: 'Johnny Doe',
  14.   updated: new Date()
  15. }, {
  16.   // update conditions
  17.   email: 'johnny@doe.site'
  18. })

Literal strings

These are not escaped by the postgres module.
Use only when needed, and never with user-inputted values.

  1. // INSERT INTO account (name, added) VALUES ('Johnny Doe', NOW())
  2. const rows = await DatabaseTable.insert('account', {
  3.   name: 'Johnny Doe',
  4.   added: DatabaseTable.literal('NOW()')
  5. })

Table options (global)

There are some options baked directly into DatabaseTable. You can access options directly from the constructor.

  1. console.log(DatabaseTable.options) // { ... }

You can update options in a similar fashion.

  1. DatabaseTable.options = {
  2.   transformCamelCase: false
  3. }

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:

  1. DatabaseTable.options = {
  2.   transformCamelCase: false
  3. }

Let’s say you have the following table:

  1.       Column        |           Type           |
  2. --------------------+--------------------------|
  3.  id                 | integer                  |
  4.  account            | integer                  |
  5.  email              | character varying(255)   |
  6.  added_from_service | character varying(255)   |
  7.  added              | timestamp with time zone |

And then you query it using this module:

  1. const accountEmails = new DatabaseTable('accountEmails')
  3. // SELECT * FROM account_emails
  4. const allRows = await accountEmails.select()
  5. const row = allRows[0]
  7. console.log(row.addedFromService) // value of `added_from_service`
  9. row.addedFromService = 'Google'
  10. 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.

  1. const { DatabaseRow } = require('@conjurelabs/db')
  3. // row from the account table
  4. const row = new DatabaseRow('account', {
  5.   id: 1,
  6.   name: 'Johnny Doe',
  7.   // ...
  8. })

Creating a new row

  1. // no .id in row content
  2. const row = new DatabaseRow('account', {
  3.   name: 'Johnny Doe'
  4. })
  6. await row.save()

Updating an existing row

  1. // has .id
  2. const row = new DatabaseRow('account', {
  3.   id: 1,
  4.   email: 'johnny@doe.site'
  5. })
  7. await row.save()

Deleting a row

  1. // has .id
  2. const row = new DatabaseRow('account', {
  3.   id: 1
  4. })
  6. 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).

  1. const accountRow = new DatabaseRow('account', {
  2.   id: 1,
  3.   name: 'Johnny Doe',
  4.   email: 'johnny@doe.site'
  5. })
  7. const row2 = accountRow.copy()
  9. /*
  10.   row2 == {
  11.     name: 'Johnny Doe',
  12.     email: 'johnny@doe.site'
  13.   }
  15.   row2 !== accountRow
  16.  */

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().

  1. const accountRow = new DatabaseRow('account', {
  2.   id: 1,
  3.   name: 'Johnny Doe',
  4.   email: 'johnny@doe.site'
  5. })
  7. // want to modify email and save
  8. await accountRow
  9.   .set({
  10.     email: 'jdawg@doe.site'
  11.   })
  12.   .save()