expo sqlite ORM (work in progress)

Builder.js

import Grammar from './Grammar.js';
import Connection from './Connection.js';

const operators = [
	'=', '<', '>', '<=', '>=', '<>', '!=', '<=>',
	'like', 'like binary', 'not like', 'ilike',
	'&', '|', '^', '<<', '>>', '&~', 'is', 'is not',
	'rlike', 'not rlike', 'regexp', 'not regexp',
	'~', '~*', '!~', '!~*', 'similar to',
	'not similar to', 'not ilike', '~~*', '!~~*',
];

export default class Builder {
	mapper = null;
	
	clauses = {
		distinct: false,
		from: null,
		columns: ['*'],
		wheres: [],
		orders: [],
		limit: null,
		offset: null,
	};
	
	bindings = {
		select: [],
		from: [],
		where: [],
		groupBy: [],
		having: [],
		order: [],
		union: [],
		unionOrder: [],
	};
	
	constructor() {
		this.grammar = new Grammar();
	}
	
	withMapping(mapper) {
		this.mapper = mapper;
		
		return this;
	}
	
	select(columns) {
		this.clauses.columns = columns;
		this.bindings.select = [];
		
		return this;
	}
	
	table(table, as = null) {
		return this.from(table, as);
	}
	
	from(table, as = null) {
		this.clauses.from = as
			? `${ table } as ${ as }`
			: table;
		
		return this;
	}
	
	where(column, operator = null, value = null, boolean = 'and') {
		[value, operator] = prepareValueAndOperator(value, operator, 2 === arguments.length);
		
		if (invalidOperator(operator)) {
			[value, operator] = [operator, '='];
		}
		
		if (null === value) {
			return this.whereNull(column, boolean, '=' !== operator);
		}
		
		this.clauses.wheres.push({
			type: 'Basic',
			column,
			operator,
			value,
			boolean,
		});
		
		this.bindings.where.push(value);
		
		return this;
	}
	
	whereNull(column, boolean = 'and', not = false) {
		this.clauses.wheres.push({
			type: not ? 'NotNull' : 'Null',
			column,
			boolean,
		});
		
		return this;
	}
	
	orderBy(column, direction = 'asc') {
		direction = direction.toLowerCase();
		
		if (false === ['asc', 'desc'].includes(direction)) {
			throw `Invalid direction: ${ direction }`;
		}
		
		this.clauses.orders.push({
			column,
			direction,
		});
		
		return this;
	}
	
	latest(column = 'created_at') {
		return this.orderBy(column, 'desc');
	}
	
	limit(value) {
		if (value >= 0) {
			this.clauses.limit = value === null
				? null
				: parseInt(value);
		}
		
		return this;
	}
	
	offset(value) {
		this.clauses.offset = Math.max(0, parseInt(value));
		
		return this;
	}
	
	get(columns = ['*']) {
		return Connection.singleton.select(this.grammar.compileSelect(this), this.getBindings())
			.then(results => {
				return this.mapper
					? results.map(this.mapper)
					: results;
			});
	}
	
	first(columns = ['*']) {
		return this.limit(1)
			.get(columns)
			.then(results => results.length ? results[0] : null);
	}
	
	async insert(values) {
		await this.performInsert(values);
		
		return true;
	}
	
	async insertGetId(values) {
		const result = await this.performInsert(values);
		
		return result.insertId;
	}
	
	async performInsert(values) {
		if (!Array.isArray(values)) {
			values = [values];
		}
		
		if (!values.length) {
			return true;
		}
		
		// Sort the keys in each object so that they're all the same
		values = values.map(record => {
			return Object.keys(record)
				.sort()
				.reduce((result, key) => {
					result[key] = record[key];
					return result;
				}, {});
		});
		
		return await Connection.singleton.execute(
			this.grammar.compileInsert(this, values),
			values.reduce((carry, record) => carry.concat(Object.values(record)), []),
		);
	}
	
	async update(values) {
		return await Connection.singleton.execute(
			this.grammar.compileUpdate(this, values),
			this.prepareBindingsForUpdate(this.bindings, values),
		);
	}
	
	prepareBindingsForUpdate(bindings, values) {
		const clean_bindings = Object.entries(bindings)
			.reduce((carry, [key, bindings]) => {
				return ['select', 'join'].includes(key)
					? bindings
					: carry.concat(bindings);
			}, []);
		
		return Object.values(values).concat(clean_bindings);
	}
	
	getBindings() {
		return Object.values(this.bindings)
			.reduce((carry, bindings) => {
				return carry.concat(bindings);
			}, []);
	}
}

function prepareValueAndOperator(value, operator, use_default = false) {
	if (use_default) {
		return [operator, '='];
	}
	
	if (invalidOperatorAndValue(operator, value)) {
		throw 'Illegal operator and value combination.';
	}
	
	return [value, operator];
}

function invalidOperator(operator) {
	return 'string' !== typeof operator || false === operators.includes(operator.toLowerCase());
}

function invalidOperatorAndValue(operator, value) {
	return null === value
		&& operators.includes(operator)
		&& false === ['=', '<>', '!='].includes(operator);
}

Connection.js

import { openDatabase } from 'expo-sqlite';
import { format } from 'date-fns';

let singleton = null;

export default class Connection {
	logging = false;
	
	/**
	 * @return Connection
	 */
	static get singleton() {
		if (null === singleton) {
			singleton = new Connection();
		}
		
		return singleton;
	}
	
	constructor(filename = 'app.db') {
		this.db = openDatabase(filename);
	}
	
	/**
	 * @param {string} sql
	 * @param {array} bindings
	 */
	select(sql, bindings = []) {
		return new Promise((resolve, reject) => {
			this.db.readTransaction((transaction) => {
				const prepared_bindings = this.prepareBindings(bindings);
				transaction.executeSql(
					sql, 
					prepared_bindings,
					function(transaction, results) {
						resolve(results.rows._array);
					},
					function(transaction, error) {
						reject(error);
					},
				);
				
				if (this.logging) {
					console.log({ sql, prepared_bindings });
				}
			});
		});
	}
	
	execute(sql, bindings = []) {
		return new Promise((resolve, reject) => {
			this.db.transaction((transaction) => {
				const prepared_bindings = this.prepareBindings(bindings);
				transaction.executeSql(
					sql,
					prepared_bindings,
					function(transaction, results) {
						resolve(results);
					},
					function(transaction, error) {
						reject(error);
					},
				);
				
				if (this.logging) {
					console.log({ sql, prepared_bindings });
				}
			});
		});
	}
	
	prepareBindings(bindings) {
		return bindings.map(binding => {
			if (binding instanceof Date) {
				return binding.getTime();
			}
			if (true === binding) {
				return 1;
			}
			if (false === binding) {
				return 0;
			}
			
			return binding;
		});
	}
}

Grammar.js

const selectComponents = [
	// 'aggregate',
	'columns',
	'from',
	// 'joins',
	'wheres',
	// 'groups',
	// 'havings',
	'orders',
	'limit',
	'offset',
	// 'lock',
];

export default class Grammar {
	/**
	 * @param {Builder} builder
	 */
	compileSelect(builder) {
		return this.concatenate(this.compileComponents(builder)).trim();
	}
	
	/**
	 * @param {Builder} builder
	 * @param {array} values
	 */
	compileInsert(builder, values) {
		const table = this.wrapTable(builder.clauses.from);
		
		if (!values.length) {
			return `insert into ${ table } default values`;
		}
		
		const columns = this.columnize(Object.keys(values[0]));
		const parameters = values
			.map(record => `(${ this.parameterize(Object.values(record)) })`)
			.join(', ');
		
		return `insert into ${ table } (${ columns }) values ${ parameters }`;
	}
	
	/**
	 * @param {Builder} builder
	 * @param values
	 */
	compileUpdate(builder, values) {
		const table = this.wrapTable(builder.clauses.from);
		const columns = this.compileUpdateColumns(builder, values);
		const where = this.compileWheres(builder);
		
		return `update ${ table } set ${ columns } ${ where }`;
	}
	
	/**
	 * @param {Builder} builder
	 * @param values
	 */
	compileUpdateColumns(builder, values) {
		return Object.entries(values)
			.map(([key, value]) => `${ this.wrap(key) } = ${ this.parameter(value) }`)
			.join(', ');
	}
	
	/**
	 * @param {Builder} builder
	 */
	compileComponents(builder) {
		const components = [];
		
		selectComponents.forEach(component => {
			if (component in builder.clauses && null !== builder.clauses[component]) {
				const compileMethod = getCompileMethodName(component);
				if (!(compileMethod in this)) {
					throw `No such compile method: "${ compileMethod }"`;
				}
				components.push(this[compileMethod](builder, builder.clauses[component]));
			}
		});
		
		return components;
	}
	
	concatenate(segments) {
		return segments.filter(value => `${ value }` !== '').join(' ');
	}
	
	/**
	 * @param {Builder} builder
	 * @param {array} columns
	 */
	compileColumns(builder, columns) {
		return builder.clauses.distinct
			? `select distinct ${ this.columnize(columns) }`
			: `select ${ this.columnize(columns) }`;
	}
	
	/**
	 * @param {Builder} builder
	 * @param {string} table
	 */
	compileFrom(builder, table) {
		return `from ${ this.wrapTable(table) }`;
	}
	
	/**
	 * @param {Builder} builder
	 */
	compileWheres(builder) {
		if (null == builder.clauses.wheres) {
			return '';
		}
		
		const clauses = this.compileWheresToArray(builder);
		
		if (!clauses.length) {
			return '';
		}
		
		return this.concatenateWhereClauses(builder, clauses);
	}
	
	/**
	 * @param {Builder} builder
	 * @param {array} orders
	 */
	compileOrders(builder, orders) {
		if (!orders.length) {
			return '';
		}
		
		return `order by ${ this.compileOrdersToArray(builder, orders).join(', ') }`;
	}
	
	/**
	 * @param {Builder} builder
	 * @param {array} orders
	 */
	compileOrdersToArray(builder, orders) {
		return orders.map(order => {
			return `${ this.wrap(order.column) } ${ order.direction }`;
		});
	}
	
	/**
	 * @param {Builder} builder
	 * @param {number} limit
	 */
	compileLimit(builder, limit) {
		return `limit ${ parseInt(limit) }`;
	}
	
	/**
	 * @param {Builder} builder
	 * @param {number} offset
	 */
	compileOffset(builder, offset) {
		return `offset ${ parseInt(offset) }`;
	}
	
	/**
	 * @param {Builder} builder
	 * @param {string[]} clauses
	 */
	concatenateWhereClauses(builder, clauses) {
		const sql = this.removeLeadingBoolean(clauses.join(' '));
		
		return `where ${ sql }`;
	}
	
	removeLeadingBoolean(value) {
		return value.replace(/and |or /i, '');
	}
	
	/**
	 * @param {Builder} builder
	 */
	compileWheresToArray(builder) {
		return builder.clauses.wheres.map(where => {
			switch (where.type) {
				case 'Basic':
					return `${ where.boolean } ${ this.whereBasic(builder, where) }`;
				default:
					throw `Unhandled "where" type: ${ where.type }`;
			}
		});
	}
	
	/**
	 * @param {Builder} builder
	 * @param where
	 */
	whereBasic(builder, where) {
		const value = this.parameter(where.value);
		const operator = where.operator.replaceAll('?', '??');
		return `${ this.wrap(where.column) } ${ operator } ${ value }`;
	}
	
	parameter(value) {
		return '?';
	}
	
	wrap(value, prefix_alias) {
		if (value.match(/ as /i)) {
			return this.wrapAliasedValue(value, prefix_alias);
		}
		
		return this.wrapSegments(value.split('.'));
	}
	
	wrapSegments(segments) {
		return segments
			.map((segment, key) => {
				return 0 === key && segments.length > 1
					? this.wrapTable(segment)
					: this.wrapValue(segment);
			})
			.join('.');
	}
	
	wrapAliasedValue(value, prefix_alias) {
		throw 'wrapAliasedValue is not implemented';
	}
	
	wrapTable(table) {
		return this.wrap(table, true);
	}
	
	wrapValue(value) {
		return '*' === value
			? value
			: `"${ value.replaceAll('"', '""') }"`;
	}
	
	columnize(columns) {
		return columns
			.map(column => this.wrap(column))
			.join(', ');
	}
	
	parameterize(values) {
		return values.map(value => this.parameter(value)).join(', ');
	}
}

function getCompileMethodName(component) {
	return [
		'compile',
		component.charAt(0).toUpperCase(),
		component.slice(1),
	].join('');
}

Migrator.js

import SchemaBuilder from './SchemaBuilder.js';
import Connection from './Connection.js';
import { useEffect } from 'react';

export function useMigrations(migrations)
{
	return useEffect(() => {
		new Migrator(migrations).migrate();
	}, [migrations]);
}

export default class Migrator
{
	constructor(migrations = {}) {
		this.migrations = migrations;
		this.builder = new SchemaBuilder();
		this.connection = Connection.singleton;
	}
	
	async migrate() {
		// await this.connection.execute(`drop table if exists "migrations"`);
		// await this.connection.execute(`drop table if exists "jobs"`);
		
		if (!await this.builder.hasTable('migrations')) {
			console.log(`Creating migrations table`);
			await this.connection.execute(`create table "migrations" (
				"id" integer not null,
				"migration" text not null unique,
				"batch" integer not null,
				primary key ("id")
			)`);
		}
		
		const run = await this.connection.select(`select * from "migrations"`);
		
		const [has_run, last_batch] = run.reduce((carry, row) => {
			const [has_run, max] = carry;
			return [[ ...has_run, row.migration ], Math.max(max, row.batch)];
		}, [[], 0]);
		
		for (const [name, callback] of Object.entries(this.migrations)) {
			if (has_run.includes(name)) {
				console.log(`Skipping: ${ name }`);
				continue;
			}
			
			console.log(`Migrating: ${ name }`);
			await callback(this.connection);
			
			await this.connection.execute(
				`insert into "migrations" ("migration", "batch") values (?, ?)`,
				[name, last_batch + 1],
			);
		}
	}
}

Model.ts

import Builder from './Builder.js';
import {EventRegister} from 'react-native-event-listeners';
import {cloneDeep, get, isEqual} from 'lodash';

export default abstract class Model {
	public exists = false;
	public wasRecentlyCreated = false;
	
	public attributes: object = {};
	public original: object = {};
	
	mutations = 0;
	
	static get casts() {
		return {
			created_at: 'date',
			updated_at: 'date',
		};
	};
	
	static get table() {
		throw 'Please implement get table()';
	}
	
	public static newFromBuilder(attributes: object) {
		const model = new this();
		
		model.attributes = attributes;
		model.exists = true;
		
		model.syncOriginal();
		
		return model;
	}
	
	static async find(id: Number) {
		return await this.query()
			.where('id', id)
			.limit(1)
			.first();
	}
	
	static async create(values: object): Promise<Model> {
		const model = new this(values);
		
		await model.save();
		
		return model;
	}
	
	static query(): Builder {
		return new Builder()
			.from(this.table)
			.withMapping(attributes => this.newFromBuilder(attributes));
	}
	
	constructor(attributes = {}) {
		this.attributes = attributes;
		this.syncOriginal();
		return this.asProxy();
	}
	
	asProxy() {
		return new Proxy(this, {
			get: function (target, prop) {
				if ('undefined' !== typeof target[prop]) {
					return target[prop];
				}
				
				return target.getAttribute(prop);
			},
		});
	}
	
	getAttributes() {
		return Object.keys(this.attributes)
			.reduce((attributes: object, key) => {
				attributes[key] = this.getAttribute(key);
				return attributes;
			}, {});
	}
	
	getAttribute(attribute: string, defaultValue: any = null): any {
		let value = get(this.attributes, attribute, defaultValue);
		return this.castFromDatabase(attribute, value);
	}
	
	is(model: Model): boolean {
		return model instanceof this.constructor
			&& model.constructor.table === this.constructor.table
			&& model.attributes.id === this.attributes.id;
	}
	
	async update(values: object): Promise<Model> {
		Object.keys(values)
			.forEach(key => {
				this.attributes[key] = values[key];
			});
		
		await this.save();
		
		return this;
	}
	
	public async save(): Promise<boolean> {
		const builder = this.constructor.query();
		
		const saved = this.exists
			? await this.performUpdate(builder)
			: await this.performInsert(builder);
		
		this.syncOriginal();
		
		if (saved) {
			EventRegister.emit('model:saved', {model: this});
		}
		
		return saved;
	}
	
	async performUpdate(builder) {
		const dirty = this.getDirty();
		
		if (Object.keys(dirty).length) {
			this.attributes.updated_at = new Date();
			dirty.updated_at = this.getAttribute('updated_at');
			
			await builder
				.where('id', this.attributes.id)
				.update(this.castValuesToDatabase(dirty));
			
			EventRegister.emit('model:updated', {model: this, changed: dirty});
		} else {
			console.log('Nothing to update.');
		}
		
		return true;
	}
	
	async performInsert(builder) {
		this.attributes.created_at = new Date();
		this.attributes.updated_at = this.attributes.created_at;
		
		this.attributes.id = await builder.insertGetId(this.attributesForSave());
		this.wasRecentlyCreated = true;
		this.exists = true;
		
		EventRegister.emit('model:inserted', {model: this});
		
		return true;
	}
	
	attributesForSave() {
		return this.castValuesToDatabase(this.attributes);
	}
	
	syncOriginal() {
		this.original = cloneDeep(this.attributes);
		this.mutations++;
	}
	
	getDirty() {
		return Object.keys(this.attributes)
			.reduce((dirty, key) => {
				const attribute = this.attributes[key];
				if (!isEqual(attribute, get(this.original, key))) {
					dirty[key] = attribute;
				}
				return dirty;
			}, {});
	}
	
	castFromDatabase(attribute, value) {
		switch (get(this.constructor.casts, attribute)) {
			case 'date':
			case 'datetime':
				if (value && !(value instanceof Date)) {
					return new Date(value);
				}
				return value;
			
			case 'bool':
			case 'boolean':
				return Boolean(value);
			
			case 'object':
				if ('string' === typeof value) {
					return JSON.parse(value);
				}
				return value;
			
			default:
				return value;
		}
	}
	
	castToDatabase(attribute, value) {
		switch (get(this.constructor.casts, attribute)) {
			case 'object':
				if ('string' !== typeof value) {
					return JSON.stringify(value);
				}
				break;
			
			default:
				return value;
		}
	}
	
	castValuesToDatabase(values) {
		return Object.entries(values)
			.reduce((result, [key, value]) => {
				result[key] = this.castToDatabase(key, value);
				return result;
			}, {});
	}
}

SchemaBuilder.js

import Connection from './Connection.js';

export default class SchemaBuilder
{
	async hasTable(table) {
		const results = await Connection.singleton.select(
			'select "name" from "sqlite_master" where "type" = ? and "name" = ?',
			['table', table]
		);
		
		return results.length > 0;
	}
}