0.1.3 • Published 6 years ago
node-pg-module v0.1.3
node-pg-module
Use PostgreSQL files as JS modules.
how to use
- Define your model in a
.sql
file:
-- @function insert
-- @params data
-- @returns row
INSERT INTO a_table (a_column, another_column)
VALUES ($data.aProp, $data.anotherProp)
RETURNING *;
-- @function getRange
-- @params from, count
-- @returns multiple
SELECT *
FROM a_table
OFFSET $from
LIMIT $count;
- Then load and use it from JavaScript:
import loadPgModule from 'node-pg-module'
const Model = loadPgModule(['path', 'to', 'Model.sql'])
async function test () {
await Model.insert({ aProp: 'aValue', anotherProp: 'anotherValue' })
const rows = await Model.getRange(0, 10)
console.log('Rows retrieved:')
rows.forEach(row => console.log(row))
}
test().then(() => console.log('Done.'))
configuration
environment
Create a .env
file at the root of your project containing the following configuration:
POSTGRESQL_HOST = <host>
POSTGRESQL_PORT = <port>
POSTGRESQL_USER = <user>
POSTGRESQL_PASSWORD = <password>
POSTGRESQL_DATABASE = <database>
annotations
@function <name>
Specifies the name under which the procedure will be exported.
@params <argument> ...
Specifies the generated function arguments.
- optional
@returns multiple | row | field | void
multiple
returns all the rows from the query result, row
only returns the first one, field
only returns the value of the first property of the first row and void
explicitly returns nothing.
tricky cases
- Inserting a row with default values:
-- @function create
-- @params data
-- @returns row
INSERT INTO users (nickname, gender, permission)
VALUES (
$data.nickname,
COALESCE($data.gender, 'robot'), -- 'robot' will be used if $data.gender evaluates to NULL.
COALESCE($data.permission, 'minimal')
)
RETURNING *;
- Updating a row with optionnal values:
-- @function updateById
-- @params id, data
-- @returns row
UPDATE users
SET nickname = COALESCE($data.nickname, nickname), -- The previous value of nickname will be used if $data.nickname evaluates to NULL.
gender = COALESCE($data.gender, gender)
WHERE id = $id
RETURNING *;
current limitations
- The dollar sign
$
is not supported outside of its use as variable prefix, even if within a SQL string literal. - Deep property access is supported but only via the dot notation. Bracket notation like
$data[0].value
will fail.