@pomgui/database
v1.1.0
Published
Library to handle database connections using Promises and allowing named parameters
Downloads
8
Readme
Database generic library
PiDatabase is a generic typescript library that provides an interface to execute the normal operations with a client database driver as connect, start transaction, commit, etc. for different database client drivers. (Currently it supports node-firebird and mysql drivers)
Advantages:
- All methods return promises.
- It uses query parameters like
:id
instead of?
. - The parameters understand object's hierarchy, so it understands parameters like
:entry.id
. - The returned rows also are preprocessed to return objects if needed (See Usage Example section).
- It maintains the same interface no matter the database, so it helps with the migration from different databases E.g. MySQL to Firebird or vice versa.
Installation
Use npm to install the library.
npm install @pomgui/database --save
Usage Example
Firebird usage example (using @pomgui/database-mysql)
const options = {
host: 'localhost',
port: 3050,
user: 'sysdba',
password: 'masterkey',
database: 'test.fdb'
};
async work(){
const pool = new PiFirebirdPool(options, 10);
const db = await pool.get();
await db.beginTransaction();
try{
const param = {entry: {id: 3}};
const data = await db.query(`
SELECT
e.entry_id "id", e.entry_date,
b.benef_id "benef.id", b.name "benef.name"
FROM ENTRIES e JOIN BENEFICIARIES db ON b.benef_id = e.benef_id
WHERE entry_id >= :entry.id
LIMIT 0,10`, param);
console.log(data);
await db.commit();
}catch(err){
console.error(err);
await db.rollback();
}finally{
await db.close();
}
}
This will print something like:
[{ id: 3,
entryDate: 2020-08-01T00:00:00.000Z,
benef: {
id: 1,
name: 'John Doe'
}
},{ id: 4,
date: 2020-08-02T00:00:00.000Z,
benef: {
id: 1,
name: 'Jane Doe'
}
}, ...
]
MySQL usage example
const options = {
host: 'localhost',
user: 'user',
password: 'secret',
database: 'test'
};
async work(){
const pool = new PiMySqlPool(options, 10);
const db = await pool.get();
await db.beginTransaction();
try{
const param = {entry: {id: 3}};
const data = await db.query(`
SELECT
e.entry_id "id", e.entry_date,
b.benef_id "benef.id", b.name "benef.name"
FROM ENTRIES e JOIN BENEFICIARIES db ON b.benef_id = e.benef_id
WHERE entry_id >= :entry.id
LIMIT 0,10`, param);
console.log(data);
await db.commit();
}catch(err){
console.error(err);
await db.rollback();
}finally{
await db.close();
}
}
This will print something like:
[{ id: 3,
entryDate: 2020-08-01T00:00:00.000Z,
benef: {
id: 1,
name: 'John Doe'
}
},{ id: 4,
date: 2020-08-02T00:00:00.000Z,
benef: {
id: 1,
name: 'Jane Doe'
}
}, ...
]