Postgres

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

Official Websitehttps://www.postgresql.org

TagsDatabaseSQL

JavaScript
Python

Documentationhttps://node-postgres.com/

NodeJS packagehttps://www.npmjs.com/package/pg

Version8.11.3

Source Codehttps://github.com/brianc/node-postgres

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

Optionally, you can configure additional parameters you can see in the examples of their docs page.

Here is 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.

JavaScript
Python

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
        }
    }
)

Integration

New integration from credential
postgres_pool = yepcode.integration.postgres('credential-slug')

New integration from plain authentication data
from psycopg2 import pool

postgres_pool = pool.SimpleConnectionPool(
    minconn=1,
    maxconn=10,
    host="localhost"",
    database="database",
    user="username",
    password="password"",
    port="5432",
    sslmode="require", # or "disable"
)

Connect

Connect
connection = postgres_pool.getconn()
cursor = connection.cursor()

Disconnect

Disconnect
cursor.close()
connection.close()

SELECT Text Only

SELECT Text only
query = "SELECT * FROM table_name"

cursor.execute(query)

for row in cursor:
    print(row)

SELECT Parameterized

SELECT Parameterized
query = "SELECT * FROM table_name WHERE name = %s"
value = ("some name")

cursor.execute(query, value)

for row in cursor:
    print(row)

SELECT Query Object

SELECT Query object
query = "SELECT * FROM table_name WHERE name =  %(name)s"
value = {"name": "some name"}

cursor.execute(query)

for row in cursor:
    print(row)

INSERT Text Only

INSERT Text only
query = "INSERT INTO table_name (id, name) VALUES ('some-id', 'some-name')"

cursor.execute(query)
connection.commit()

INSERT Parameterized

INSERT Parameterized
query = "INSERT INTO table_name (id, name) VALUES (%s, %s)"
value = ('some-id','some-name')

cursor.execute(query, value)
connection.commit()

INSERT Query Object

INSERT Query object
query = "INSERT INTO table_name (id, name) VALUES (%(id)s, %(name)s)"
value = {"id":'some-id', "name":'some-name'}

cursor.execute(query, value)
connection.commit()

Network Connection needs

Credential configuration​

Postgres Snippets available in Editor​

Integration​

Connect​

Disconnect​

SELECT Text Only​

SELECT Parameterized​

SELECT Query Object​

INSERT Text Only​

INSERT Parameterized​

INSERT Query Object​

Integration​

Connect​

Disconnect​

SELECT Text Only​

SELECT Parameterized​

SELECT Query Object​

INSERT Text Only​

INSERT Parameterized​

INSERT Query Object​

Credential configuration

Postgres Snippets available in Editor

Integration

Connect

Disconnect

SELECT Text Only

SELECT Parameterized

SELECT Query Object

INSERT Text Only

INSERT Parameterized

INSERT Query Object

Integration

Connect

Disconnect

SELECT Text Only

SELECT Parameterized

SELECT Query Object

INSERT Text Only

INSERT Parameterized

INSERT Query Object