firebird-query
A node-firebird wrapper for easy and safe query building.
Support with a start ⭐️
Installation
npm install firebird-querySetting 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 wantUsage
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
ANDclauses
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 insertedupdateOne
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
-
commit
-
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);
}
})