import Pg from '@mojojs/pg';

// Use standard PostgreSQL connection URIs
const pg = new Pg('postgres://user:password@localhost:5432/database');

// Single query with safe placeholder
const results = await pg.query`SELECT ${'One'} AS one`;
for (const row of results) {
  console.log(row.one);
}

// Multiple queries on the same connection
const db = await pg.db();
const users = await db.query`SELECT * FROM users`;
const roles = await db.query`SELECT * FROM roles`;
await db.release();

// Automatically close all connections at end of scope
await using pg = new Pg('postgres://user:password@localhost:5432/database');

// Automatically release database connection back into the pool for reuse at end of scope
await using db = await pg.db();

// Automatically roll back transaction if an exception is thrown before `tx.commit()` has been called
try {
  await using tx = await db.begin();
  await db.query`INSERT INTO users (name) VALUES ('sri')`;
  await db.query`this_is_an_error`;
  await tx.commit();
} catch (e) {
  console.warn('Something went wrong with the transaction');
}

// Build safe SQL query with placeholder and partial SQL query
const role = 'admin';
const partialQuery = pg.sql`AND role = ${role}`;
const name = 'root';
const results = await pg.query`SELECT * FROM users WHERE name = ${name} ${partialQuery}`;

const role = 'role = ' + pg.escapeLiteral('power user');
const partialQuery = pg.sqlUnsafe`AND ${role}`;
const name = 'root';
const results = await pg.query`SELECT * FROM users WHERE name = ${name} ${partialQuery}`;

const results = await pg.rawQuery('SELECT * FROM users WHERE name = $1 AND login = $1', 'Sara');

const db = await pg.db();
try {
  const tx = await db.begin();

  try {
    for (const user of ['Daniel', 'Isabell']) {
      await db.query`INSERT INTO users (name) VALUES (${user})`;
    }
    await tx.commit();

  } finally {
    await tx.rollback();
  }
} finally {
  await db.release();
}

import Path from '@mojojs/path';

// Load migrations from "migrations/myapp.sql" and migrate to the latest version
await pg.migrations.fromFile(Path.currentFile().sibling('migrations', 'myapp.sql'), {name: 'myapp'});
await pg.migrations.migrate();

// Use migrations to drop and recreate the schema
await pg.migrations.migrate(0);
await pg.migrations.migrate();

// Load migrations from a string
pg.migrations.fromString('-- 1 up\n...', {name: 'my_other_app'});

// Load migrations from a directory
await pg.migrations.fromDirecory(Path.currentFile().sibling('migrations'), {name: 'yet_another_app'});

// Send notifications
const db = await pg.db();
await db.notify('foo', 'just a message');

// Use an iterator to wait for incoming notifications
await db.listen('foo');
for await (const message of db) {
  console.log(`${message.channel}: ${message.payload}`);
  break;
}
await db.unlisten('foo');

// Use event to handle incoming notifications
await db.listen('bar');
db.on('notification', (message) => {
  console.log(`${message.channel}: ${message.payload}`);
});
await db.unlisten('bar');