firebird-query

firebird-query

A node-firebird wrapper for easy and safe query building.

Support with a start ⭐️

Installation

npm  install  firebird-query

Setting up

Quick setup

import { FirebirdQuery } from "firebird-query";

const dbOptions = {
  host: "000.000.000.000",
  port: 3050,
  database: "/path/Database/FILE.FDB",
  user: "SYSDBA",
  password: "my_secure_password",
  maxConnections: 10, // optional
};

export const db = new FirebirdQuery(dbOptions, {
  queryLogger: true, //logs every query execution
});

Usage

queryRaw

• Input: template string literal. Parameters are automatically escaped avoiding query injection.

• Execution return: array of objects.

• Supports pagination.

import { db } from "./db.service.js";

const result = await db.queryRaw`
	SELECT COD, NAME
	FROM USERS
	WHERE SIGN_UP_DATE < ${date}`.execute();

console.log(result);
// --> [ { COD: 1, NAME: 'JOHN' }, { COD: 2, NAME: 'JANE' } ]

const result = await db.queryRaw`
	SELECT COD, NAME
	FROM USERS
	WHERE SIGN_UP_DATE < ${date}`.paginated(1, 2);
// take: 1, page: 2 (default: 1)

console.log(result);
// --> [ { COD: 2, NAME: 'JANE' } ]

Where clauses

An object can be provided instead of a raw value.

• Object keys correspond to column names. Object values to column values.

• Multiple keys are combined as AND clauses

const result = await t.queryRaw`SELECT COD, NAME FROM USERS WHERE ${{
  COD: 1,
  NAME: "John",
}}`.getQuery();

console.log(result);
// SELECT COD, NAME FROM USERS WHERE COD = '1' AND NAME = 'John'

Conditional statements

When a where happens to resolve undefined, it will be replaced with a tautology, making it irrelevant to the query result .

Take advantage of this behavior to conditionally add statements.

const name = "Tom";
const result = await t.queryRaw`
	SELECT COD, NAME FROM USERS WHERE ${{
    COD: name.startsWith("J") ? 1 : undefined,
    NAME: name,
  }}`.getQuery();

console.log(result);
// SELECT COD, NAME FROM USERS WHERE 1=1 AND NAME = 'Tom'

Manually escaped statement

You can also provide a function that returns an unsafe string. It is your responsibility to escape the parameters. This method can be useful for adding conditional clauses.

// Define any parameters in your customClause function
const customClause = (withEscaping: boolean): ManuallyEscapedStatement => {
  // returning a function
  return (esc) => {
    const store = "McDonald's";
    if (withEscaping) {
      return `STORE = ${esc(store)}`;
    }
    return `STORE = ${store}`;
  };
};
const res = db.queryRaw`
        SELECT * FROM USERS WHERE ${customClause(true)};`.getQuery();
console.log(res);
// Expected: SELECT * FROM USERS WHERE STORE = 'McDonald''s';

Advance statements

Set anything as object key.

This example handles case insensitive queries.

const name = "Tom";
const result = await t.queryRaw`
	SELECT COD, NAME FROM USERS WHERE ${{
    ["LOWER(NAME)"]: name.toLowerCase(),
  }}`.getQuery();

console.log(result);
// SELECT COD, NAME FROM USERS WHERE LOWER(NAME) = 'tom'

Operators

• Number operators

• ne: not equal !=

• gt: greater than >

• gte: greater than or equal >=

• lt: lower than <

• lte: lower than or equal <=

• between: { from: number; to: number }

• IN: number array. [1,2,3...]

• notIN: NOT IN. Number array.

• Date operators

• ne: not equal !=

• gt: greater than >

• gte: greater than or equal >=

• lt: lower than <

• lte: lower than or equal <=

• between: { from: Date; to: Date }

• IN: array

• notIN. array.

• String operators

• ne: not equal

• IN

• notIN

• startsWith

• endsWith

• contains

const name = "Tom";
const result = await t.queryRaw`
	SELECT COD, NAME FROM USERS WHERE ${{
    COD: { gte: 1 },
    NAME: { startsWith: name },
  }}`.getQuery();

console.log(result);
// SELECT COD, NAME FROM USERS WHERE COD >= '1' AND NAME LIKE 'Tom%'

insertOne

• rowValues: the object keys correspond to database column names

• returning: optional array of string with column names to be returned

const result = await db
  .insertOne({
    tableName: "USERS",
    rowValues: {
      NAME: "JAKE",
    },
    returning: ["COD"],
  })
  .execute();

console.log(result); // --> { COD: 3 }

insertMany

Performs an efficient INSERT statement and inserts multiple rows in a single query.

Does not support returning clause.

const result = await db
  .insertMany({
    tableName: "USERS",
    columnNames: ["NAME", "PHONE"],
    rowValues: [
      { NAME: "John", PHONE: "555-555-5555" },
      { NAME: "Jane", PHONE: "555-555-0000" },
    ],
  })
  .execute();

console.log(result); // --> 2 rows inserted

updateOne

Update a single row. Optionally, supports returning.

const result = await db.updateOne({
  tableName: "USERS",
  rowValues: {
    NAME: "John",
    PHONE: "555-555-5555",
  },
  where: {
    COD: 1,
  },
  returning: ["COD"],
});

console.log(result); // --> { COD: 1 }

updateOrInsert

Update or insert a single row. Supports returning clause

WARNING: Ensure there’s only one potential row affected.

const result = await db.updateOrInsert({
  tableName: "USERS",
  rowValues: {
    COD: 1,
    NAME: "John",
  },
  returning: ["COD"],
});

console.log(result); // --> { COD: 1 }

Typescript usage

Each method counts on typescript inference as long as a return parameter is provided.

queryRaw

The ouput must be manually inferred.

The result is always an array of the type provided

const result = await db.queryRaw<{ COD: number }>`
	SELECT COD
	FROM USERS
	WHERE COD = ${1}`.execute();

console.log(result); // --> [ { COD: 1 } ]

initTransaction

A callback managed function that returns a ISOLATION_READ_COMMITTED transaction instance to work with. It has the same methods to query and mutate the database in addition to

1. commit

2. rollback

// recommended approach
db.initTransaction(async (t) => {
  // t(ransaction) is scoped into this async function.
  try {
    const data = await t.queryRaw`
            SELECT 1 AS TEST FROM RDB$DATABASE;
            `.execute();
    console.log(data); // --> [{ TEST: 1 }]
  } catch (error) {
    console.log(error);
  }
});