What is pg?
The pg npm package is a PostgreSQL client for Node.js. It provides functionalities to connect to a PostgreSQL database server and execute queries, manage transactions, and listen to notifications from the database.
What are pg's main functionalities?
Connecting to a PostgreSQL database
This code sample demonstrates how to connect to a PostgreSQL database using the pg package. It includes creating a new client instance and connecting to the database with a connection string.
const { Client } = require('pg');
const client = new Client({
connectionString: 'postgresql://user:password@localhost:5432/database'
});
client.connect();
Executing a query
This code sample shows how to execute a SQL query to select all records from a table and print the results. It also handles any potential errors and closes the connection.
client.query('SELECT * FROM my_table', (err, res) => {
console.log(err ? err.stack : res.rows);
client.end();
});
Using async/await for queries
This code sample uses async/await syntax to execute a query and print the results. It's a more modern approach to handling asynchronous operations in Node.js.
async function fetchData() {
const res = await client.query('SELECT * FROM my_table');
console.log(res.rows);
client.end();
}
fetchData();
Managing transactions
This code sample illustrates how to manage a transaction with the pg package. It begins a transaction, attempts to insert data, commits the transaction if successful, or rolls back if an error occurs.
async function transactionExample() {
await client.query('BEGIN');
try {
await client.query('INSERT INTO my_table (col) VALUES ($1)', ['data']);
await client.query('COMMIT');
} catch (e) {
await client.query('ROLLBACK');
throw e;
}
}
transactionExample();
Listening to notifications
This code sample demonstrates how to listen for notifications from the PostgreSQL server. It sets up an event listener for 'notification' events and executes the LISTEN command to subscribe to a specific notification.
const client = new Client();
client.connect();
client.on('notification', (msg) => {
console.log('New notification:', msg);
});
client.query('LISTEN my_notification');
Other packages similar to pg
mysql
The mysql package is a client for MySQL databases. It provides similar functionalities to pg, such as connecting to a database, executing queries, and managing transactions. However, it is designed specifically for MySQL and not PostgreSQL.
sequelize
Sequelize is an ORM (Object-Relational Mapping) library for Node.js. It supports multiple database systems, including PostgreSQL. Unlike pg, which is a lower-level client, Sequelize provides a higher-level abstraction with features like model definition, associations, and migrations.
typeorm
TypeORM is another ORM for TypeScript and JavaScript that runs on Node.js. It supports PostgreSQL among other databases. It provides an even higher level of abstraction compared to Sequelize and includes features like data-mapper patterns, repositories, and automatic schema generation.
knex
Knex.js is a SQL query builder for Node.js that supports PostgreSQL, MySQL, SQLite3, and more. It provides chainable query building capabilities and can be used as a query client without the full ORM features. It's a middle ground between pg and full ORMs like Sequelize and TypeORM.