This is a Node module that makes it very simple to interact with PostgreSQL databases. It has the same API as https://github.com/mvolkmann/mysql-easier.
To install this, run npm install -S postgresql-easy
const PgConnection = require('postgresql-easy');
const pg = new PgConnection(config);
To run the demo code, follow these steps:
- Start database daemon with
pg_ctl -D /usr/local/var/postgres start
- Create the demo database with
createdb demo
- Start interactive mode with
psql -d demo
- Create a table with create table demo_user ( id serial primary key, username text, password text );
- Exit interactive mode with ctrl-d.
- Run the demo with
npm run demo
The config object can contain these properties:
database
: the name of the database to usedebug
: true to output messages describing each action; defaults to falsehost
: defaults to localhostidleTimeoutMillis
: time before connection is closed; default is 30000max
: maximum number of clients in pool; default is 10password
: if database requires authenticationport
: defaults to 5432user
: if database requires authentication
The only one of these that is always required is "database".
PgConnection objects provide seven methods.
All but disconnect
return a promise.
One way to use the returned promise is to chain calls to then
and catch
.
Another is to use async
and await
.
This deletes all records from a given table.
try {
await pg.deleteAll('flavors');
// Do something after successful delete.
} catch (e) {
// Handle the error.
}
This deletes a record from a given table by id. It requires the table to have a column named "id".
try {
await pg.delete('flavors', 7);
// Do something after successful delete.
} catch (e) {
// Handle the error.
}
This disconnects from the database.
pg.disconnect();
This gets all records from a given table.
try {
const result = await pg.getAll('flavors');
// Process data in the array result.
} catch (e) {
// Handle the error.
}
This gets a record from a given table by id. It requires the table to have a column named "id".
try {
const result = await pg.getById('flavors', 7);
// Process data in the array result.
} catch (e) {
// Handle the error.
}
This inserts a record into a given table and returns the id of the new record. The keys of obj are column names and their values are the values to insert.
try {
const result = pg.insert('flavors', {name: 'vanilla', calories: 100});
// Do something after successful insert.
// result will be the id of the newly inserted row.
} catch (e) {
// Handle the error.
}
This executes a SQL query. It is the most general purpose function provided. It is used by several of the other functions.
const sql = 'select name from flavors where calories < 150';
try {
const result = await pg.query(sql);
// Do something with the result set in result.
} catch (e) {
// Handle the error.
}
const sql = 'select name from flavors where calories < $1 and cost < $2';
try {
const result = await pg.query(sql, 200, 3);
// Do something with the result set in result.
} catch (e) {
// Handle the error.
}
This updates a record in a given table by id. It requires the table to have a column named "id".
try {
const result = pg.updateById(
'flavors', 7, {name: 'chocolate', calories: 200});
// Do something with the result set in result.
// result will be an object describing the updated row.
} catch (e) {
// Handle the error.
}