@theredhead/data
Purpose
This package provides a minimalistic yet fully featured programming interface to work with databases. It supports sqlite and mysql and is supported by a full suite of tests with a high level of coverage. Initially intended for mysqlf to have something nicer to work with than the different native APIs offered by sqlite and mysql, but built in a way that makes it possible to add support for more databases in the future.
Features
- Easy to use
- Full CRUD and straight SQL executing.
- Fully built with typescript
- Optional peer dependencies on mysql2 and/or sqlite3. (only grab the engine you use)
- Source code freely available
Some quick examples using FetchRequest fluently through FetchRequestBuilder
const db = new SqliteDatabase('starwars.sqlite3');
const carrie = await db.from('actor')
.where( 'surname = ? AND name = ?', 'Carrie','Fischer')
.orderBy('surname', 'DESC')
.fetch();
const mark = await db.from('actor')
.whereAnd(
['surname = ?', 'Hamill'],
['name = ?', 'Mark']
)
.fetch();
const otiginalTrilogyMainCast = await db.from('actor')
.whereOr(
['name = ?', 'Carrie'],
['name = ?', 'Mark']
['name = ?', 'Harrison'],
['name = ?', 'Peter'],
['name = ?', 'Kenny'],
['name = ?', 'Anthony'],
)
.fetch();
The interfaces making this possible:
interface IDbConnection {
// straight sql execution
executeScalar<T>(text: string, params: DbParams): Promise<T>;
executeSingle<T extends PartialRecord>(text: string, params: DbParams): Promise<T>;
executeArray<T extends Record>(text: string, params: DbParams): Promise<T[]>;
executeNonQuery(text: string, params: DbParams): Promise<number>;
// schema inspection
tableExists(table: string): Promise<boolean>;
columnExists(table: string, column: string): Promise<boolean>;
// CRUD
insert<T extends PartialRecord>(table: string, obj: T): Promise<T>;
update<T extends Record>(table: string, obj: T): Promise<T>;
delete<T extends Record>(table: string, id: number): Promise<T>;
// FetchRequest support
fetch<T extends Record>(request: FetchRequest): Promise<T[]>;
from(table: string): FetchRequestBuilder;
}
interface FetchRequestBuilder {
where(text: string, ...params: any[]): FetchRequestBuilder;
whereAnd(...predicates: PredicateShorthand[]): FetchRequestBuilder;
whereOr(...predicates: PredicateShorthand[]): FetchRequestBuilder;
orderBy(column: string, direction: "ASC" | "DESC"): FetchRequestBuilder;
page(pageIndex: number, pageSize: number): FetchRequestBuilder;
fetch(): Promise<Record[]>;
}
type PredicateShorthand = [text: string, ...params: any[]];
interface FetchRequest {
table: string;
predicates: FetchPredicte;
sort: Sort;
pageIndex?: number;
pageSize?: number;
}
interface FetchSimplePredicteClause {
text: string;
params: any[];
}
interface FetchCompoundPredicteClause {
type: "AND" | "OR";
predicates: FetchPredicates;
}
type FetchPredicteClause =
| FetchSimplePredicteClause
| FetchCompoundPredicteClause;
type FetchPredicates = FetchPredicteClause[];
type Sort = SortClause[];
interface SortClause {
column: string;
direction: "ASC" | "DESC";
}
Connecting to mysql
const connection = new MySqlConnection({
host: "127.0.0.1",
user: "user",
password: "password",
database: "database",
})
Connecting to sqlite
const connection = new SqliteConnection('/path/to/file');
Conventions that avoid problems
insert
, update
and delete
methods
For the We expect every table to have a unique, numeric identity column. This means that for mysql tables, you must have a rowid column probably declared as rowid BIGINT NOT NULL UNIQUE AUTO_INCREMENT
. Note that this is for the database, not for the data, so it does not need to be a primary key, but it must be unique. If you don't want it to be named rowid
, you can set the rowIdColumn
property on your MySqlConnection
to something else.
sqlite does not need special consideration. but you will get the rowid column along with your records from FetchRequest
s and the insert
/update
methods.