Apatite NPM | npm.io

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

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