The time after the 11th hour. Midnight is a NodeJS ORM for SQLite and Turso with full TypeScript support without needing to generate any code. Even complex SQL queries can be written inside of JavaScript.
class Forests extends Table {
id = this.IntPrimary;
name = this.Text;
address = this.Text;
displayName = this.Concat(this.name, ' - ', this.address);
}
class Trees extends Table {
id = this.IntPrimary;
name = this.Text;
planted = this.Index(this.Date);
forestId = this.Cascade(Forests);
alive = true;
}
There are two levels of API. The first is a table-level syntax for basic queries.
const tree = await db.trees.get({
id: 1,
alive: true
});
Includes are specified at the time of the query, rather than in the schema.
const forests = await db.forests.query({
include: {
trees: (t, c) => t.trees.many({
forestId: c.id
})
},
where: {
name: n => n.like('National%')
}
});
The second type of syntax is much like SQL and builds on many of the new features that JavaScript has added to its language in recent times.
const trees = await db.query(c => {
const {
forests: f,
trees: t
} = c;
return {
select: {
...t,
forest: f.name
},
join: [t.forestId, f.id],
where: {
[t.id]: [1, 2, 3]
}
}
});
This syntax allows you to perform queries that usually aren't possible in ORMs.
npm install @andrewitsover/midnight
import { SQLiteDatabase, Table } from '@andrewitsover/midnight';
const database = new SQLiteDatabase('forest.db');
class Clouds extends Table {
id = this.IntPrimary;
name = this.Text;
};
const db = database.getClient({ Clouds });
await db.clouds.insert({ name: 'Nimbus' });
const clouds = await db.clouds.many();
console.log(clouds);
Every table has get
, many
, first
, query
, update
, upsert
, insert
, insertMany
, and remove
methods available to it, along with any of the custom methods that are created when you add a new SQL file to the corresponding table's folder. Views only have the get
, many
, first
, and query
methods available to them.
insert
simply takes one argument - params
, with the keys and values corresponding to the column names and values you want to insert. It returns the primary key, or part of the primary key if the table has a composite primary key. For batch inserts you can use insertMany
and it takes an array of params
. It doesn't return anything.
const id = await db.moons.insert({
name: 'Europa',
orbit: 'Retrograde'
});
update
takes an object with an optional where
property, and a set
property. It returns a number representing the number of rows that were affected by the query. For example:
await db.moons.update({
where: { id: 100 },
set: { orbit: 'Prograde' }
});
If you want to update columns based on their existing value, you can pass a function into the set
properties like this:
await db.moons.update({
set: {
orbit: (c, f) => f.concat(c.orbit, ' - Circular')
},
where: {
id: 3
}
});
All of the built-in SQLite functions are available, in addition to the mathematical operators plus
, minus
, divide
, and multiply
.
upsert
will update the row if the target's uniqueness contraint is violated by the insert. If target
or set
are not provided, the upsert will do nothing when there is a conflict. upsert
returns the primary key of the inserted or updated row.
const id = await db.forests.upsert({
values: {
id: 1,
name: 'Daisy Hill Forest',
address: 'Brisbane'
},
target: 'id',
set: {
address: 'Brisbane'
}
});
get
and many
take two optional arguments. The first is params
- an object representing the where clause. For example:
const trees = await db.trees.many({
forestId: 9,
alive: true
});
If an array is passed in, an in
clause is used, such as:
const trees = await db.trees.many({
forestId: [1, 2, 3]
});
If null is passed in as the value, the SQL will use is null
.
The second argument to get
or many
selects which columns to return. It can be one of the following:
- a string representing a column to select. In this case, the result returned is a single value or array of single values, depending on whether
get
ormany
is used.
const planted = await db.trees.get({ id: 3 }, 'planted');
- an array of strings, representing the columns to select.
const tree = await db.trees.get({ id: 3 }, ['id', 'born']);
You can use the query
or first
syntax for more complex queries. query
returns an array in the same way as many
, and first
returns an object or undefined
if nothing is found. The additional keywords are:
select
: a string or array of strings representing the columns to select.
omit
: a string or array of strings representing the columns to omit. All of the other columns will be selected.
include
: include other tables in the result.
orderBy
: a string or an array representing the column or columns to order the result by. This can also be a function that utilises the built-in SQLite functions.
const trees = await db.trees.query({
where: {
category: 'Evergreen'
},
orderBy: (c, f) => f.lower(c.name)
});
desc
: set to true when using orderBy
if you want the results in descending order.
limit
and offset
: corresponding to the SQL keywords with the same name.
distinct
: adds the distinct
keywords to the start of the select clause.
debug
: when set to true, the result will include debug information such as the raw SQL used in the query.
For example:
const trees = await db.trees.query({
where: {
alive: true
},
select: ['name', 'category'],
orderBy: 'id',
limit: 10
});
You can also include additional relations:
const animals = await db.animals.query({
include: {
sightings: (t, c) => t.sightings.query({
where: {
animalId: c.id
}
})
}
});
While the default interpretation of the query parameters is =
, you can pass in a function to use not
, gt
, gte
, lt
, lte
, like
, match
and glob
.
For example:
const excluded = [1, 2, 3];
const moons = await db.moons.many({ id: c => c.not(excluded) });
const count = await db.moons.count({
where: {
id: c => c.gt(10)
}
});
If you need to perform complex logic in the where
clause, you can use the and
or or
properties. For example:
const wolves = await db.animals.query({
where: {
or: [
{ name: c => c.like('Gray%') },
{ id: c => c.lt(10) },
{
and: [
{ tagged: c => c.gt(time) },
{ name: c => c.like('Red%') }
]
}
]
}
});
You should only include one condition per object.
There are multiple functions that aggregate the results into a single value. These include count
, avg
, min
, max
, and sum
. Despite its name, sum
uses the SQLite function total
to determine the results.
All of these functions take three arguments:
where
: the where clause
column
: the column to aggregate. This is optional for count
.
distinct
: the same as column
but it aggregates by distinct values.
const count = await db.trees.count({
where: {
native: true
}
});
There is also an exists
function that takes one argument representing the where clause.
const exists = await db.moons.exists({
name: 'Cumulus'
});
You can write group by
statements like this:
const trees = await db.fighters
.groupBy('forestId')
.avg({
column: {
height: 'heightCm'
},
where: {
avg: c => c.gt(170)
},
limit: 3
});
An aggregate function should come after the groupBy
method. distinct
can be used instead of column
to aggregate by distinct values. distinct
or column
needs to be an object with a single property representing the alias for the aggregrate function, and the column to aggregate by.
In addition to aggregate functions such as avg
or count
, there is also an array
function that simply groups the rows into an array. The select
option takes an object with a single property representing the name of the resulting array, and the column or columns to select.
const trees = await db.trees
.groupBy('forestId')
.array({
select: {
planted: 'planted'
},
limit: 3
});
remove
takes one argument representing the where clause and returns the number of rows affected by the query.
const changes = await db.moons.remove({ id: 100 });
Transactions involve locking writes to the database with getTransaction
. If multiple transactions try to run at the same time, they will wait until the current transaction is complete.
const tx = await db.getTransaction();
try {
await tx.begin();
const animalId = await tx.animals.insert({
name: 'Gray Wolf',
speed: 73
});
const personId = await tx.people.get({ name: c => c.like('Andrew%') }, 'id');
await tx.sightings.insert({
personId,
animalId
});
await tx.commit();
}
catch (e) {
await tx.rollback();
}
You can also run multiple statements inside a single transaction without any logic using batch
.
const forestId = 1;
const [forest, trees, sightings] = await db.batch((bx) => [
bx.forests.get({ id: forestId }),
bx.trees.many({ forestId }),
bx.sightings.many({ forestId })
]);
const result = { ...forest, trees, sightings };
The client returned from getClient
has three methods that can be used to create a migration system. This includes:
getSchema
: return the tables loaded into the getClient
method in a format suitable for saving as JSON.
diff
: takes a saved schema and diffs it with the currently loaded schema to create a migration.
migrate
: takes a SQL string representing the migration. This method defers the foreign keys and wraps the SQL in a transaction.
In addition to the built-in SQLite types of Integer
, Real
, Text
, and Blob
, Midnight adds a few extra types. Boolean
is stored in the database as a 1 or a 0, Date
is stored as an ISO8601 string, and Json
is a JSONB blob.
To create a table, you simply extend the Table
class.
class Moons extends Table {
id = this.IntPrimary;
name = this.Unique(this.Text);
planetId = this.Cascade(Planets);
discovered = this.Now;
}
To specify the primary key, you use one of the modified types that has Primary
at the end.
Column types can be wrapped in many different methods:
Null
: assert that the column can contain nulls.
Index
: add an index to the column.
Unique
: add a unique index to the column.
Constraints can be represented as either an array of valid values, or a comparison function.
class Trees extends Table {
id = this.IntPrimary;
height = this.Int;
leaves = this.Check(this.Int, this.Gte(0));
alive = true;
}
Constraints can also be defined in the Attributes
function and span across multiple columns.
class Rangers extends Table {
id = this.IntPrimary;
admin = false;
staffLimit = 3;
createdAt = this.Now;
Attributes = () => {
this.Check({
or: [
{ [admin]: true },
{ [staffLimit]: this.Gt(0) }
]
});
}
}
Foreign keys do not need to specify a column type, as the type will be determined by the table that is referenced.
By default, an index is created for the foreign key, and the column is set to not null. Also, the related column in the references table is assumed to be the primary key of that table.
class Sightings extends Table {
id = this.IntPrimary;
personId = this.Cascade(People);
animalId = this.Cascade(Animals);
date = this.Now;
}
class Animals extends Table {
id = this.IntPrimary;
name = this.Text;
ownerId = this.References(Sightings, {
column: 'personId',
null: true,
index: false,
onDelete: 'set null',
onUpdate: 'cascade'
});
}
Cascade
is simply a shorthand version of References
that has the onDelete
property set to cascade
.
For indexes that span multiple columns or are based on expressions, you can define an Attributes
function on the class.
class Trees extends Table {
id = this.IntPrimary;
name = this.Text;
category = this.Text;
planted = this.Now;
Attributes = () => {
const computed = this.Cast(this.StrfTime('%Y', this.planted), 'integer');
this.Index(computed);
this.Unique(this.name, this.category);
}
}
Partial indexes can be defined on a class field.
class Animals extends Table {
id = this.IntPrimary;
name = this.Index(this.Text, name => {
return {
[name]: this.Like('%Wolf')
}
});
}
The can also be defined inside the Attributes
function if they span across multiple columns.
class Trees extends Table {
id = this.IntPrimary;
name = this.Text;
forestId = this.References(Forests);
alive = true;
Attributes = () => {
this.Index(this.name, {
[this.alive]: true
});
}
}
The above example applies a partial index on name
where alive
is true
.
Computed fields use the built-in SQLite functions and therefore can be used in any part of a query.
class Trees extends Table {
id = this.IntPrimary;
name = this.Text;
category = this.Text;
displayName = this.Concat(this.name, ' (', this.category, ')');
}
Midnight alllows you to create complex SQL queries without leaving JavaScript.
The following queries uses a window function to rank trees by their height.
const trees = await db.query(c => {
const {
id,
name,
height
} = c.trees;
return {
select: {
id,
name,
rank: c.rowNumber({
orderBy: height,
desc: true
})
},
where: {
[height]: c.gt(1)
}
}
});
The built-in SQLite functions are just JavaScript functions. This query gets the tree planted the furthest time away from the supplied date.
const tree = await db.first(c => {
const { id, name, planted } = c.trees;
const now = new Date();
const max = c.max(c.timeDiff(planted, now));
return {
select: {
id,
name,
max
},
orderBy: max,
desc: true
}
});
The c
parameter of the query represents the context of the database, including both tables and functions.
The group
function represents json_group_array
or json_group_object
depending on the number of parameters supplied to the function.
If you want to create a subquery for use in many different queries, you can use the subquery
method.
The query below creates a list of people that have sighted a particular animalId
.
const sighted = db.subquery(c => {
const { personId, animalId } = c.sightings;
const p = c.people;
return {
select: {
animalId,
sightedBy: c.group(p)
},
join: [personId, p.id],
groupBy: animalId
}
});
You can now use this subquery in other queries.
const animals = await db.query(c => {
const { animalId, sightedBy } = c.use(sighted);
const a = c.animals;
return {
select: {
...a,
sightedBy
},
where: {
[c.length(a.name)]: c.gt(10)
},
join: [a.id, animalId, 'left']
}
});
The object returned from the query
and subquery
methods can include the following:
select
, optional
, distinct
, join
, where
, groupBy
, having
, orderBy
, desc
, limit
, and offset
.
optional
: the same as select
but provides hints to TypeScript that these columns may be null
. This is useful for columns that come from a left join.
distinct
: used instead of select
when you want the results to be distinct.
join
: a tuple or array of tuples representing the keys to join on. For a left or right join, you can use: