Postgres
Postgres (aka PostgreSQL) is a free and open-source relational database management system emphasizing extensibility and SQL compliance

Official Websitehttps://www.postgresql.org
Documentationhttps://node-postgres.com/
NodeJS packagehttps://www.npmjs.com/package/pg
Version8.8.0
Source Codehttps://github.com/brianc/node-postgres
TagsDatabase, SQL
Network Connection needs
This integration needs network access to the server where the service is running.
See the Network access page for details about how to achieve that.
Credential configuration
To configure this credential you need the host
, port
, username
, password
and database
to connect to postgres.
Optionally you can set any of the extra config params you can see in the examples of their docs page.
Here you have an example of a filled credential configuration form in YepCode:

Postgres snippets available in editor
note
The title is the triggering text for YepCode to autocomplete the script
Integration
New integration from credential
const postgresPool = yepcode.integration.postgres('credential-slug')
New integration from plain authentication data
const { Pool } = require('pg')
const postgresPool = new Pool({
user: 'postgres',
password: '1234abcd',
host: 'localhost',
database: 'testdb',
port: 5432,
ssl: true,
query_timeout: 5000,
connectionTimeoutMillis: 2000,
max: 20,
idleTimeoutMillis: 30000,
idle_in_transaction_session_timeout: 10000,
statement_timeout: 5000,
});
New integration from plain authentication data (connectionString)
const { Pool } = require('pg')
const postgresPool = new Pool({
ssl: true,
connectionString: 'postgres://user:password@host:5432/database',
statement_timeout: 5000,
query_timeout: 5000,
connectionTimeoutMillis: 3000,
max: 20,
idleTimeoutMillis: 30000,
idle_in_transaction_session_timeout: 10000,
});
Connect
Connect (async/await)
try {
const client = await postgresPool.connect()
await client.query('SELECT NOW()')
client.release()
} catch (error) {
console.log(error)
} finally {
postgresPool.end(() => { console.log('pool has ended') })
}
Connect (callback)
postgresPool.connect((error, client, release) => {
if (error) {
return console.error(error)
}
client.query('SELECT NOW()', (error, result) => {
release()
if (error) {
return console.error(error)
}
postgresPool.end(() => { console.log('pool has ended') })
})
})
Disconnect
Disconnect (async/await)
try {
await postgresPool.end()
console.log('Connection closed!')
} catch (error) {
console.error(error)
}
Disconnect (Promise)
postgresPool.end().then(() => {
console.log('Connection closed!')
}).catch(console.error)
Disconnect (callback)
postgresPool.end((error) => {
if (error) {
console.error(error)
} else {
console.log('Connection closed!')
}
})
SELECT Text only
SELECT Text only (async/await)
try {
const result = await postgresPool.query('SELECT id, name, price FROM products')
result.rows.forEach(console.log);
} catch (error) {
console.error(error)
}
SELECT Text only (Promise)
postgresPool.query('SELECT id, name, price FROM products')
.then((result) => {
result.rows.forEach(console.log);
}).catch(console.error)
SELECT Text only (callback)
postgresPool.query(
'SELECT id, name, price FROM products',
(error, result) => {
if (error) {
console.error(error)
} else {
console.log(result.rows[0])
}
}
)
SELECT Parameterized
SELECT Parameterized (async/await)
try {
const result = await postgresPool.query(
`SELECT id, name, price
FROM products
WHERE price > $1 AND stock > $2`,
[100, 200]
)
console.log(result.fields[0].name) // id
console.log(result.fields[1].name) // name
console.log(result.fields[2].name) // price
console.log(result.rows) // [{ id, name, price }, ...]
} catch (error) {
console.error(error)
}
SELECT Parameterized (Promise)
postgresPool.query(`
SELECT id, name, price
FROM products
WHERE price > $1 AND stock > $2`,
[100, 200]
).then((result) => {
console.log(result.fields[0].name) // id
console.log(result.fields[1].name) // name
console.log(result.fields[2].name) // price
console.log(result.rows) // [{ id, name, price }, ...]
}).catch(console.error)
SELECT Parameterized (callback)
postgresPool.query(`
SELECT id, name, price
FROM products
WHERE price > $1 AND stock > $2`,
[100, 200],
(error, result) => {
if (error) {
console.error(error)
} else {
console.log(result.fields[0].name) // id
console.log(result.fields[1].name) // name
console.log(result.fields[2].name) // price
console.log(result.rows) // [{ id, name, price }, ...]
}
}
)
SELECT Query object
SELECT Query object (async/await)
try {
const query = {
text: `
SELECT id, name, price
FROM products
WHERE price > $1 AND stock > $2`,
values: ['100', '200'],
}
const result = await postgresPool.query(query)
console.log(result.fields[0].name) // id
console.log(result.fields[1].name) // name
console.log(result.fields[2].name) // price
console.log(result.rows) // [{ id, name, price }, ...]
} catch (error) {
console.error(error)
}
SELECT Query object (Promise)
const query = {
text: `
SELECT id, name, price
FROM products
WHERE price > $1 AND stock > $2`,
values: ['100', '200'],
}
postgresPool.query(query)
.then((result) => {
console.log(result.fields[0].name) // id
console.log(result.fields[1].name) // name
console.log(result.fields[2].name) // price
console.log(result.rows) // [{ id, name, price }, ...]
}).catch(console.error)
SELECT Query object (callback)
const query = {
text: `
SELECT id, name, price
FROM products
WHERE price > $1 AND stock > $2`,
values: ['100', '200'],
}
postgresPool.query(
query,
(error, result) => {
if (error) {
console.error(error)
} else {
console.log(result.fields[0].name) // id
console.log(result.fields[1].name) // name
console.log(result.fields[2].name) // price
console.log(result.rows) // [{ id, name, price }, ...]
}
}
)
INSERT Text only
INSERT Text only (async/await)
try {
const result = await postgresPool.query(
"INSERT INTO products(id, name, price, stock) VALUES('12345', 'FOO', 12, 50)"
)
console.log(result.rowCount) // 1
} catch (error) {
console.error(error)
}
INSERT Text only (Promise)
postgresPool.query(
"INSERT INTO products(id, name, price, stock) VALUES('12345', 'FOO', 12, 50)"
).then(result => console.log(result.rowCount)) // 1
.catch(console.error)
INSERT Text only (callback)
postgresPool.query(
"INSERT INTO products(id, name, price, stock) VALUES('12345', 'FOO', 12, 50)",
(error, result) => {
if (error) {
console.error(error)
} else{
console.log(result.rowCount) // 1
}
}
)
INSERT Parameterized
INSERT Parameterized (async/await)
try {
const result = await postgresPool.query(
'INSERT INTO products(id, name, price, stock) VALUES($1, $2, $3, $4)',
['12345', 'FOO', 12, 50]
)
console.log(result.rowCount) // 1
} catch (error) {
console.error(error)
}
INSERT Parameterized (Promise)
postgresPool.query(
'INSERT INTO products(id, name, price, stock) VALUES($1, $2, $3, $4)',
['12345', 'FOO', 12, 50]
).then(result => console.log(result.rowCount)) // 1
.catch(console.error)
INSERT Parameterized (callback)
postgresPool.query(
'INSERT INTO products(id, name, price, stock) VALUES($1, $2, $3, $4)',
['12345', 'FOO', 12, 50],
(error, result) => {
if (error) {
console.error(error)
} else {
console.log(result.rowCount) // 1
}
)
INSERT Query object
INSERT Query object (async/await)
try {
const query = {
text: 'INSERT INTO products(id, name, price, stock) VALUES($1, $2, $3, $4)',
values: ['12345', 'FOO', 12, 50]
}
const result = await postgresPool.query(query)
console.log(result.rowCount) // 1
} catch (error) {
console.error(error)
}
INSERT Query object (Promise)
const query = {
text: 'INSERT INTO products(id, name, price, stock) VALUES($1, $2, $3, $4)',
values: ['12345', 'FOO', 12, 50]
}
postgresPool.query(query)
.then(result => console.log(result.rowCount)) // 1
.catch(console.error)
INSERT Query object (callback)
const query = {
text: 'INSERT INTO products(id, name, price, stock) VALUES($1, $2, $3, $4)',
values: ['12345', 'FOO', 12, 50]
}
postgresPool.query(
query,
(error, result) => {
if (error) {
console.error(error)
} else {
console.log(result.rowCount) // 1
}
}
)