Basie

A TypeScript Node package that provides a fluent semi-typesafe SQL query builder akin to the one shipped with Laravel. Supports sqlite (using sqlite3), MySQL (using mysql2) and PostgreSQL (using pg). Find the documentation here.
Example
import Basie, { BaseModel, R, A } from "basie";
class _User extends BaseModel {
public name: string;
public age: number;
public phones: A<Phone> = this.hasMany(Phone);
}
const User = Basie.wrap<_User>()(_User);
type User = _User;
class _Phone extends BaseModel {
public number: string;
public user_id: number;
public user: R<User> = this.belongsTo(User);
}
const Phone = Basie.wrap<_Phone>()(_Phone);
type Phone = _Phone;
const allUsersAbove20 = await User.where("age", ">", 20).get();
const complex = await User.where("age", ">", 20).orWhere(nested => {
nested.where("name", "LIKE", "%a%");
nested.orWhere("name", "LIKE", "%A%");
}).limit(10).orderByDesc("age").get();
const user = new User();
user.name = "Thijs";
user.age = 17;
await user.save();
user.age = 18;
await user.save();
await user.delete();
const phones = await user.phones();
console.assert(phones === await user.phones(), "Not lazy");
const longPhones = await user.phones.select("LEN(number) as number_length").where("number_length", ">=", 10).get();
Features
- Fluent Querying Interface: QueryBuilder is a fluent querying builder interface that attempts to be as type-safe as possible. This means that
where(nonexistentKey, 10)
will fail, and that where("is_admin", "true")
won't compile if is_admin
is supposed to be a boolean.
- Easily Managable Objects: Simply call
save()
and delete()
on objects to either insert, update or delete the specific model.
Limitations
- Only string, boolean and number fields are supported. Basie does not actually query your database for the scheme, so it doesn't know the types of your database columns. Only strings, numbers and booleans will be converted properly. For all other values, you can use custom getters and setters to wrap a value.
- Does not create tables. Basie only queries tables and simply assumes that your model matches the database layout.
- Tables are required to have an id column. Basie tracks documents by their id, and it does not support changing the name or type of the ID column.
- Relations are read-only. You have to manually fiddle with id columns to change relationships.
Usage
For a complete overhead view of all methods available, it is recommended you check out the documentation. The next few sections describe some common usecases and gotchas.
Configuring the Database
Basie supports SQLite, MySQL and PostgreSQL. Simply import the static Basie instance and call the appropriate method to configure Basie to use that database:
import Basie from "basie";
import { Database } from "sqlite3";
const db = new Database("path_to_database.db", error => {
if (error) throw new Error();
Basie.sqlite(db);
});
import * as mysql from "mysql2";
const connection = await mysql.createConnection(<any>{
user: databaseUsername,
password: databasePassword,
database: databaseName,
host: databaseHost,
port: databasePort,
decimalNumbers: true
});
Basie.mysql(connection);
import * as pg from "pg";
const pool = new pg.Pool({
user: databaseUsername,
password: databasePassword,
database: databaseName,
host: databaseHost,
port: databasePort,
max: 10,
idleTimeoutMillis: 30000
});
pg.types.setTypeParser(20, parseInt);
pg.types.setTypeParser(1700, parseFloat);
Basie.postgres(pool);
Models, Methods and Computed Properties
Your models are simple TypeScript classes, which means that they support methods and computed properties. Basie will ensure that whenever a model instance gets loaded from the database, it will have the appropriate prototype.
class _User extends BaseModel {
public name: string;
public lastChangeTimestamp: number;
get lastChange(): Date {
return new Date(this.lastChangeTimestamp);
}
set lastChange(newDate: Date) {
this.lastChangeTimestamp = newDate.getTime();
}
greet() {
console.log("Hello, I'm " + this.name);
}
}
const User = Basie.wrap<_User>()(_User);
type User = _User;
Relations
For a full overview of all relations, please check the documentation. Relations work similar to how Laravel's Eloquent handles relations. Do note that foreign keys will need to be present on objects.
import Basie, { BaseModel, A, R } from "basie";
class _User extends BaseModel {
public name: string;
public age: number;
public readonly phones: A<Phone> = this.hasMany(Phone);
}
const User = Basie.wrap<_User>()(_User);
type User = _User;
class _Phone extends BaseModel {
public number: string;
public user_id: number;
public readonly user: R<User> = this.belongsTo(User);
}
const Phone = Basie.wrap<_Phone>()(_Phone);
type Phone = _Phone;
Relations are lazy and return a promise. Calling a relation without ()
will instead return a query builder that can be refined further:
const phones = await user.phones();
const phoneQueryBuilder = user.phones;
Development
Begin by running npm install
or yarn install
. Use npm run watch
(or yarn watch
) to start the TypeScript compilation service. Tests can be ran using npm test
or yarn test
. Pull requests are welcome! ;)
License
MIT