🌒 Midnight

⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⣀⡀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀
⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠸⠁⠸⢳⡄⠀⠀⠀⠀⠀⠀⠀⠀
⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⢠⠃⠀⠀⢸⠸⠀⡠⣄⠀⠀⠀⠀⠀
⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⡠⠃⠀⠀⢠⣞⣀⡿⠀⠀⣧⠀⠀⠀⠀
⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⣀⣠⡖⠁⠀⠀⠀⢸⠈⢈⡇⠀⢀⡏⠀⠀⠀⠀
⠀⠀⠀⠀⠀⠀⠀⠀⠀⡴⠩⢠⡴⠀⠀⠀⠀⠀⠈⡶⠉⠀⠀⡸⠀⠀⠀⠀⠀
⠀⠀⠀⠀⠀⠀⠀⢀⠎⢠⣇⠏⠀⠀⠀⠀⠀⠀⠀⠁⠀⢀⠄⡇⠀⠀⠀⠀⠀
⠀⠀⠀⠀⠀⠀⢠⠏⠀⢸⣿⣴⠀⠀⠀⠀⠀⠀⣆⣀⢾⢟⠴⡇⠀⠀⠀⠀⠀
⠀⠀⠀⠀⠀⢀⣿⠀⠠⣄⠸⢹⣦⠀⠀⡄⠀⠀⢋⡟⠀⠀⠁⣇⠀⠀⠀⠀⠀
⠀⠀⠀⠀⢀⡾⠁⢠⠀⣿⠃⠘⢹⣦⢠⣼⠀⠀⠉⠀⠀⠀⠀⢸⡀⠀⠀⠀⠀
⠀⠀⢀⣴⠫⠤⣶⣿⢀⡏⠀⠀⠘⢸⡟⠋⠀⠀⠀⠀⠀⠀⠀⠀⢳⠀⠀⠀⠀
⠐⠿⢿⣿⣤⣴⣿⣣⢾⡄⠀⠀⠀⠀⠳⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⢣⠀⠀⠀
⠀⠀⠀⣨⣟⡍⠉⠚⠹⣇⡄⠀⠀⠀⠀⠀⠀⠀⠀⠈⢦⠀⠀⢀⡀⣾⡇⠀⠀
⠀⠀⢠⠟⣹⣧⠃⠀⠀⢿⢻⡀⢄⠀⠀⠀⠀⠐⣦⡀⣸⣆⠀⣾⣧⣯⢻⠀⠀
⠀⠀⠘⣰⣿⣿⡄⡆⠀⠀⠀⠳⣼⢦⡘⣄⠀⠀⡟⡷⠃⠘⢶⣿⡎⠻⣆⠀⠀
⠀⠀⠀⡟⡿⢿⡿⠀⠀⠀⠀⠀⠙⠀⠻⢯⢷⣼⠁⠁⠀⠀⠀⠙⢿⡄⡈⢆⠀
⠀⠀⠀⠀⡇⣿⡅⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠙⠦⠀⠀⠀⠀⠀⠀⡇⢹⢿⡀
⠀⠀⠀⠀⠁⠛⠓⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠼⠇⠁

The time after the 11th hour. Midnight is a NodeJS ORM for SQLite with full TypeScript support without needing to generate any code. Even complex SQL queries can be written inside of JavaScript.

Tables are written in JavaScript like this:

class Forests extends Table {
  name = this.Text;
  address = this.Text;

  displayName = this.Concat(this.name, ' - ', this.address);
}

class Trees extends Table {
  name;
  planted = this.Index(this.Date);
  forestId = this.Cascade(Forests);
  alive = this.True;
}

There are two levels of API. The first is a table-level syntax for basic queries.

const tree = db.trees.get({ 
  id: 1,
  alive: true
});

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 = 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.

Getting started

Prerequists

Make sure you have installed Node.js version 22.13.1 or higher.

Creating a project

Create a directory for your project and initialise it with npm:

mkdir forests
cd forests
npm init -y
npm install @andrewitsover/midnight
touch main.js

In the package.json, change the following lines:

{
  "main": "main.js",
  "type": "module"
}

Paste the code below into the main.js file.

This example will create a clouds table in a database named forest.db and then insert and read some rows.

import { SQLiteDatabase, Table } from '@andrewitsover/midnight';

const database = new SQLiteDatabase('forest.db');

class Clouds extends Table {
  name;
};

const db = database.getClient({ Clouds });
const sql = db.diff();
db.migrate(sql);

db.clouds.insert({ name: 'Nimbus' });
const clouds = db.clouds.many();
console.log(clouds);

To run it, you can use node main.js from the project root directory.

See the sample project for a more complete setup.

It is a good idea to set any SQLite database that you have created to pragma journal_mode=WAL. You can do this yourself after the database has been created. See here for more details.

The API

Every table has get, many, first, query, update, upsert, insert, insertMany, and delete methods available to it.

Insert

insert inserts a row into the database. For batch inserts you can use insertMany, which takes an array of objects.

const id = db.moons.insert({
  name: 'Europa',
  orbit: 'Retrograde'
});

Update

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:

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:

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

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 = db.forests.upsert({
  values: {
    id: 1,
    name: 'Daisy Hill Forest',
    address: 'Brisbane'
  },
  target: 'id',
  set: {
    address: 'Brisbane'
  }
});

Get and Many

get and many take two optional arguments. The first argument represents the where clause. For example:

const trees = db.trees.many({ 
  forestId: 9,
  alive: true
});

If an array is passed in, an in clause is used, such as:

const trees = 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:

1. 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 or many is used.

const planted = db.trees.get({ id: 3 }, 'planted');

2. an array of strings, representing the columns to select.

const tree = db.trees.get({ id: 3 }, ['id', 'born']);

Query and First

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: an array of strings representing the columns to select.

return: a string representing the column to select.

omit: a string or array of strings representing the columns to omit. All of the other columns will be selected.

const rangers = db.rangers.query({
  omit: 'password',
  where: {
    id: [1, 2, 3]
  }
});

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 = 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.

For example:

const trees = db.trees.query({
  where: { 
    alive: true 
  }, 
  select: ['name', 'category'],
  orderBy: 'id',
  limit: 10
});

While the default interpretation of the query parameters is =, you can pass in a function to use not, gt, gte, lt, lte, like, match or glob.

For example:

const excluded = [1, 2, 3];
const moons = db.moons.many({ id: c => c.not(excluded) });
const count = db.moons.count({
  where: {
    id: c => c.gt(10)
  }
});

Complex filtering

If you need to perform complex logic in the where clause, you can use the and or or properties. For example:

const wolves = 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.

Aggregate functions

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 = db.trees.count({
  where: {
    native: true
  }
});

There is also an exists function that takes one argument representing the where clause.

const exists = db.moons.exists({ 
  name: 'Cumulus'
});

GroupBy

You can write group by statements like this:

const trees = 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 = db.trees
  .groupBy('forestId')
  .array({
    select: {
      planted: 'planted'
    },
    limit: 3
  });

Delete

delete takes one argument representing the where clause and returns the number of rows affected by the query.

const changes = db.moons.delete({ id: 100 });

Transactions

Transactions allow all operations to succeed or fail together so that the database is not left in an incorrect state. Make sure you do not await on any functions while performing a transaction as this will allow other operations outside of the transaction to run and therefore be included in the transaction unintentionally. In other words, there should be no await statement between the begin and