firebird-query v0.4.4

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  maxConn = 10; /* optional */
const  options = {
	host:  '000.000.000.000',
	port:  3050,
	database:  '/path/Database/FILE.FDB',
	user:  'SYSDBA',
	password:  'my_secure_password'
};

export const db = new FirebirdQuery(options, maxConn);

Or

Configure a .env

DB_HOST="000.000.000.000"
DB_PORT=3050
DB_DATABASE="/path/Database/FILE.FDB"
DB_USER="SYSDBA"
DB_PASSWORD="my_secure_password"

Then

export const db = new FirebirdQuery(); // use it wherever you want

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 = 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

If a where clause resolved to 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'

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);
        }
    })