Skip to main content

Postgres

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

Version8.7.3
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
}
}
)