no# PostgreSQL methods for node.js PostgreSQL scripts as object methods in node.js for database-intensive applications
Example - four trivial SQL queries with method specifiers.
-- A query that yields a single record
--! {"name": "the_first_method", "returns": "record"}
SELECT n num, format('As text: %s', n) txt
from generate_series(1, 100, 1) s(n)
where n = $1;
-- A query that yields a single value
--! {"name": "the_second_method", "returns": "value"}
SELECT to_char(n, 'FMRN') rn
from generate_series(1, $1, 1) s(n)
where n = $2;
-- A query that yields a set of records
--! {"name": "the_third_method", "returns": "recordset"}
SELECT n, to_char(n, 'FMRN') rn
from generate_series(1, $1, 1) s(n);
-- A query with no parameters
--! {"name": "alternative_method", "returns": "record"}
SELECT 1 as num, 'one' as eng, 'edno' as bul, 'bir' as tur, 'ANY' as amount;Method specifiers are lines that start with --! folowed by JSON with exactly these mandatory attributes:
"name"- specifies the method name as a valid identifier, up to 63 characters;"returns"- specifies the method return type. Can be one of:"value"- a scalar;"record"- a JSON object representing a single record;"recordset"- a JSON array of objects;"none"
Queries can be of any length and complexity. Comments, empty lines and leading/trailing whitespaces are ignored.
Note
PgMethods uses prepared statements.
Important
SQL files must be UTF-8 encoded.
import PgMethods from './../pg_methods.mjs';
let db_gw = new PgMethods(client);- A single SQL file can be imported by the constructor
let db_gw = new PgMethods(client,filename);- SQL files can be imported using
sql_importmethod
let db_gw = new PgMethods(client);
db_gw.sql_import(filename);
db_gw.sql_import(another_filename);- Chained
let db_gw = (new PgMethods(client))
.sql_import(filename)
.sql_import(another_filename);let res = await db_gw.the_first_method.run(10);import PgMethods from './../pg_methods.mjs';
import pg from 'pg';
const pg_client = new pg.Client(); // uses environment variables
await pg_client.connect();
try
{
let db_gw = new PgMethods(pg_client);
db_gw.sql_import('proba.sql')
.sql_import('append.sql');
let res_a = await db_gw.the_first_method.run(10),
res_b = await db_gw.the_second_method.run(100, 26),
res_c = await db_gw.the_third_method.run(5),
res_d = await db_gw.alternative_method.run('Количество');
await db_gw.anonymous_block.run();
console.log(res_a);
console.log(res_b);
console.log(res_c);
console.log(res_d);
}
finally { await pg_client.end(); } // database connection leak protection
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent