Querying Postgres Databases in Node.js

This guide will cover creating a table and performing basic CRUD (create, read, update, delete) operations on it. You should make sure you read Setup & Installation and Managing Connections first.

Creating a table

In a production app, you should use migrations to manage your database schema, but to get started, you can create a table directly using the @databases/pg API:

import db, {sql} from './database';

async function run() {
  await db.query(sql`
    CREATE TABLE IF NOT EXISTS users (
      id SERIAL NOT NULL PRIMARY KEY,
      email TEXT NOT NULL,
      favorite_color TEXT NOT NULL,
      UNIQUE(email)
    )
  `);

  await db.dispose();
}

run().catch((err) => {
  console.error(err);
  process.exit(1);
});

Insert, Select, Update, Delete

You can include values in your queries using the ${} syntax for tagged template literals

import db, {sql} from './database';

async function insertUser(email, favoriteColor) {
  await db.query(sql`
    INSERT INTO users (email, favorite_color)
    VALUES (${email}, ${favoriteColor})
  `);
}

async function updateUser(email, favoriteColor) {
  await db.query(sql`
    UPDATE users
    SET favorite_color=${favoriteColor}
    WHERE email=${email}
  `);
}

async function deleteUser(email) {
  await db.query(sql`
    DELETE FROM users
    WHERE email=${email}
  `);
}

async function getUser(email) {
  const users = await db.query(sql`
    SELECT * FROM users
    WHERE email=${email}
  `);
  if (users.length === 0) {
    return null;
  }
  return users[0];
}
async function run() {
  await insertUser('[email protected]', 'red');
  await updateUser('[email protected]', 'blue');

  const user = await getUser('[email protected]');
  console.log('user =', user);

  await deleteUser('[email protected]');

  await db.dispose();
}

run().catch((err) => {
  console.error(err);
  process.exit(1);
});

For full documentation on how to construct complex queries, read Building SQL Queries.