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.1
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.

Postgres snippets available in YepCode 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')    console.log(result.rows.forEach(console.log))} catch (error) {    console.error(error)}
SELECT Text only (Promise)
postgresPool.query('SELECT id, name, price FROM products').then((result) => {    console.log(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        }    })