@numbereight/udatabase

uDatabase

uDatabase is the universal database interface, supporting readable queries for both 'postgres' and 'snowshovel' backends.

Why

Database interfaces aren't fit for (our) purpose!

Bugs lie aplenty in written code; SQL queries in code are often hard to read

• seperated blocks of code both concatenated on various conditionals
• values bound 'later' they are susceptible to being incorrectly indexed. @numbereight/udatabase provides inline value binding on a Query instance that can be passed into conditionals. (previous versions included parameter and list bindings, but they proved confusing for users leading to bugs. They may be introduced again later).

We often write code for multiple databases; being able to use interchangable objects that can have type restricted queries significantly reduces bugs here, as well as ensuring we always know which database we have; not to mention simplifying their initialisation.

Testing with databases is hard. We add interfaces for managing testing with databases, ensuring tests can run in parallel.

What about @numbereight/database

When @numbereight/database is retired (soon tm) @numbereight/udatabase will be renamed and re-released as @numbereight/database instead.

API

Common usage

import { getDatabase, Database } from '@numbereight/udatabase';
import { DateTime } from 'luxon';

const database: Database<'postgres' | 'snowflake'> = getDatabase(connectionInfo);

const yesterday = DateTime.now().minus({ days: 1 }).toISOString();

await database.run<{ timestamp: string }>(
    // This is a query that will run regardless of database type
    //  if it could only run in postgres I would write 'postgres' here
    //  attempting to run a query on a database that does not match the type results in an error 
    database.type,

    // Query names appear in log messages and can be used to trace errors
    'exampleQueryOne',
  
    // The query itself.
    // This can be a string if no binding is required, or it can be a function
    //  that takes a Query instance and returns a string.
    // Binding values inline will result in the correct binding sanitisation.
    q => `
        SELECT
            timestamp
        FROM timestamps
        WHERE timestamp > ${q.value(yesterday)}::timestamp
    `
);

Intermediate usage

Transactions

const deductFrom = 'Mr. Bean';
const transactionAmount = 100000;
const creditTo = 'Mr. Blobby';
const newBalance = await database.transaction<number>('my accounting transaction', async (transaction) => {
    const [{ reference: transactionReference }] = await transaction.run<{ reference }>('createTransaction', q => `
        INSERT INTO transactions
          (type, description)
        VALUES ('transfer', ${q.value(`${transactionAmount} from ${deductFrom} to ${creditTo}`)})
        RETURNING reference
    `);
    await transaction.run('debit', q => `
        INSERT INTO balances
            (owner, amount, reference)
        VALUES (${q.value(deductFrom)}, ${q.value(transactionAmount)}, ${q.value(transactionReference)})
    `);
    await transaction.run('credit', q => `
        INSERT INTO balances
            (owner, amount, reference)
        VALUES (${q.value(deductFrom)}, ${q.value(transactionAmount)}, ${q.value(transactionReference)})
    `);
    const [{ balance }] = await transaction.run<{ balance: number }>('balanceCheck', q => `
        SELECT
            SUM(amount) AS balance
        FROM balances
        WHERE owner=${q.value(creditTo)}
    `);
    return balance;
});

Advanced usage

Take a look at the Database interface in ./src/Database.ts; it provides methods for creating and accessing new databases on the same box (useDatabase, useTestDatabase).

Create just a postgres or snowflake instance with the PDatabase and SDatabase classes. These can be cast to Database<'postgres'> and Database<'snowflake'> respectively. These are also useful for database specific operations, like listen for postgres

Environment

Environment Variable	Required	Effect
BIGQUERY_CONNECTION	yes	The credentials file to connect to bigquery.
POSTGRES_CONNECTION	yes	The URL of the postgres database

Running tests

You need a .env-test file in the root of this repository with the required environment variables above.