Apatite
Object persistence framework for Node.js. ORM framework supports: MySQL, Oracle, Postgres, SQL Server, Sqlite.
Features
- Based on object oriented principles.
- Zero dependencies.
- Transaction based.
- Generate (or execute) SQL scripts to create tables/columns for models/attributes.
- Apart from standard mappings, supports inheritence mapping too.
- Optional objects session cache management.
Prerequisites
- Node version >=16.19.0.
- oracledb if you plan to use Oracle: $ npm install oracledb@5.5.0
- pg if you plan to use Postgres: $ npm install pg@8.10.0
- mysql if you plan to use Mysql: $ npm install mysql@2.18.1
- tedious if you plan to use Microsoft SQL Server: $ npm install tedious@15.1.3, optionally for connection pool: tedious-connection-pool2 : $ npm install tedious-connection-pool2@2.1.0
- sqlite3 if you plan to use Sqlite: $ npm install sqlite3@5.1.4
Installation
C:\my-project> npm install apatite
Quick Start
-
Install the prerequisites.
-
Create your class and define a static method getModelDescriptor which takes apatite as an argument.
class Department {
constructor() {
this.oid = 0;
this.name = '';
}
printName() {
console.log(this.name);
}
static getModelDescriptor(apatite) {
var table = apatite.newTable('DEPT');
var modelDescriptor = apatite.newModelDescriptor(this, table);
var column = table.addNewColumn('OID', apatite.dialect.newSerialType());
column.bePrimaryKey();
modelDescriptor.newSimpleMapping('oid', column);
column = table.addNewColumn('NAME', apatite.dialect.newVarCharType(100));
modelDescriptor.newSimpleMapping('name', column);
return modelDescriptor;
}
}
You could also create descriptor from a simple object:
static getModelDescriptor(apatite) {
var object = {
table: 'DEPT',
model: this,
mappings: [
{attr: 'oid', col: 'OID', pk: true, type: 'serial'},
{attr: 'name', col: 'NAME', type: 'varchar', length: 100}
]
}
return apatite.newDescriptorFromObject(object);
}
- Register your models.
// Oracle
var connOptions = { userName: 'apatite', password: 'apatite', connectionInfo: 'localhost/apatite' };
var apatite = require('apatite').forOracle(connOptions);
// Postgres
var connOptions = { userName: 'apatite', password: 'apatite', connectionInfo: 'localhost/apatite' };
var apatite = require('apatite').forPostgres(connOptions);
// Mysql
var connOptions = { userName: 'apatite', password: 'apatite', connectionInfo: 'localhost/apatite' };
var apatite = require('apatite').forMysql(connOptions);
// Mssql
var connOptions = { userName: 'apatite', password: 'apatite', connectionInfo: 'localhost/apatite' };
var apatite = require('apatite').forMssql(connOptions);
// Sqlite
var connOptions = { connectionInfo: ':memory:' };
var apatite = require('apatite').forSqlite(connOptions);
apatite.registerModel(Department);
- Create session and start querying your objects.
// Creates a new session and database connection
apatite.newSession(function (err, session) {
if (err) {
console.error(err.message);
return;
}
var query = session.newQuery(Department);
query.execute(function(err, departments) {
if (err) {
console.error(err.message);
return;
}
console.log(JSON.stringify(departments));
if (departments.length)
departments[0].printName();
endSession(session);
});
});
//closes the database connection
function endSession(session) {
session.end(function(err) {
if (err)
console.error(err.message);
})
}
// Using promise to execute queries
// Creates a new session and database connection
apatite.newSession(function (err, session) {
if (err) {
console.error(err.message);
return;
}
var query = session.newQuery(Department);
var promise = query.execute();
promise.then(function(departments) {
console.log(JSON.stringify(departments));
if (departments.length)
departments[0].printName();
endSession(session);
}, function(err) {
console.error(err.message);
endSession(session);
});
});
//closes the database connection
function endSession(session) {
session.end(function(err) {
if (err)
console.error(err.message);
})
}
//query results from cursor stream
apatite.newSession(function (err, session) {
var query = session.newQuery(Department)
query.returnCursorStream = true
query.execute(function (err, cursorStream) {
if (err) {
console.log(err)
return endSession(session)
}
cursorStream.on('error', function(cursorStreamErr) {
console.log(cursorStreamErr)
endSession(session)
})
cursorStream.on('result', function(department) {
console.log(JSON.stringify(department))
})
cursorStream.on('end', function() {
endSession(session)
})
})
})
function endSession(session) {
session.end(function (endConnErr) {
if (endConnErr)
return console.log(endConnErr)
console.log('Connection ended.')
})
}
- Do changes to your objects and save.
...
// Create new department
var department = new Department();
department.name = 'Sales';
// Register it to session
var changesToDo = function (changesDone) {
session.registerNew(department);
changesDone(); // must be called when you are done with all changes
}
session.doChangesAndSave(changesToDo, function (saveErr) {
if (saveErr)
console.error(saveErr.message);
});
...
...
// Change an existing department
var query = session.newQuery(Department);
query.attr('name').eq('Sales');
// Or you could create query from an array
// const query = session.newQueryFromArray(Department, [['name', '=', 'Sales']])
query.execute(function(executeErr, departments) {
if (executeErr) {
return console.error(executeErr);
}
var changesToDo = function (changesDone) {
departments[0].name = 'Pre-Sales';
changesDone(); // must be called when you are done with all changes
}
session.doChangesAndSave(changesToDo, function (saveErr) {
if (saveErr)
console.error(saveErr.message);
});
});
...
...
// Delete an existing department
var changesToDo = function (changesDone) {
var query = session.newQuery(Department);
query.attr('name').eq('Pre-Sales');
// Or you could create query from an array
// const query = session.newQueryFromArray(Department, [['name', '=', 'Pre-Sales']])
query.execute(function(executeErr, departments) {
if (executeErr) {
changesDone(executeErr);
return;
}
session.registerDelete(departments[0]);
changesDone(); // must be called when you are done with all changes
});
}
session.doChangesAndSave(changesToDo, function (saveErr) {
if (saveErr)
console.error(saveErr.message);
});
...
- Below is a complete example which can be run multiple times. All you need to do is create a postgres DB with user name/db name/password as apatite
const connOptions = { userName: 'apatite', password: 'apatite', connectionInfo: 'localhost/apatite' }
const apatite = require('apatite').forPostgres(connOptions)
class Department {
constructor() {
this.oid = 0
this.name = ''
}
printName() {
console.log(this.name)
}
static getModelDescriptor(apatite) {
const table = apatite.newTable('DEPT')
const modelDescriptor = apatite.newModelDescriptor(this, table)
let column = table.addNewColumn('OID', apatite.dialect.newSerialType())
column.bePrimaryKey()
modelDescriptor.newSimpleMapping('oid', column)
column = table.addNewColumn('NAME', apatite.dialect.newVarCharType(100))
modelDescriptor.newSimpleMapping('name', column)
return modelDescriptor
}
}
apatite.registerModel(Department)
apatite.newSession((sessionErr, session) => {
if (sessionErr) {
return console.error(sessionErr)
}
session.existsDBTable('dept', (existsErr, tableInfo) => {
if (existsErr) {
endSession(session)
return console.error(existsErr)
}
if (tableInfo.rows.length === 0) {
session.createDBTablesForAllModels((creationErr) => {
if (creationErr) {
return console.error(creationErr)
}
createDepartments(session)
})
} else {
queryDepartments(session)
}
})
})
function createDepartments(session) {
let changesToDo = (changesDone) => {
for(let i = 0; i < 50; i++) {
let department = new Department()
department.name = `Department ${`000${i + 1}`.slice(-3)}`
session.registerNew(department)
}
changesDone()
}
session.doChangesAndSave(changesToDo, (saveErr) => {
if (saveErr) {
endSession(session)
return console.error(saveErr)
}
queryDepartments(session)
})
}
function queryDepartments(session) {
const query = session.newQueryFromArray(Department, [['oid', '>', 0]])
query.execute((executeErr, departments) => {
if (executeErr) {
console.error(executeErr)
}
console.log(JSON.stringify(departments))
endSession(session)
})
}
function endSession(session) {
session.end((endSessionErr) => {
if (endSessionErr) {
console.error(endSessionErr)
}
})
}
Contributions
Welcome.
Links
Tests
Install all supported databases and then install dependencies:
C:\my-project> npm install
Run the tests:
C:\my-project> npm test