Flyweight

Flyweight is a NodeJS ORM for SQLite and Turso.

Features include a comprehensive API, the ability to automatically type and query inside JSON, and advanced typing of raw SQL queries so that you are not without TypeScript support in any situation.

mkdir test
cd test
npm init

For a standard SQLite database, then run

npx create-flyweight database

For Turso, run

npx create-flyweight turso database

You can run the npx command at the root of either an existing or a new project. Once that is done, you can import the database this way:

import { db } from './database/db.js';

await db.users.insert({ name: 'Andrew' });
const users = await db.users.many();
console.log(users);

A users table has already been created for you to play around with.

You can update types whenever you change the SQL by either calling npm run watch to automatically update the types, or npm run types to do it manually.

Configuration options can be found in the config.js file. Go to the migrations section to learn how to start adding columns and tables.

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.coaches.insert({
  name: 'Eugene Bareman',
  city: 'Auckland'
});

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.coaches.update({
  where: { id: 100 }, 
  set: { city: 'Brisbane' }
});

which corresponds to

update coaches set city = 'Brisbane' where id = 100;

If you want to update columns based on their existing value, you can pass a function into the set properties like this:

await db.coaches.update({
  set: {
    city: (c, f) => f.concat(c.city, ', Australia')
  },
  where: {
    id: coachId
  }
});

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.coaches.upsert({
  values: {
    id: 1,
    name: 'Test User',
    city: 'Test City'
  },
  target: 'id',
  set: {
    city: 'Updated City'
  }
});

get and many take two optional arguments. The first is params - an object representing the where clause. For example:

const fights = await db.fights.many({ cardId: 9, titleFight: true });

translates to

select * from fights where cardId = 9 and titleFight = 1;

The keys to params must be the column names of the table. The values can either be of the same type as the column, an array of values that are the same type as the column or null. If an array is passed in, an in clause is used, such as:

const fights = await db.fights.many({ cardId: [1, 2, 3] });

which translates to

select * from fights where cardId in (1, 2, 3);

If null is passed in as the value, the SQL will use is null.

All of the arguments are passed in as parameters for security reasons.

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 born = await db.fighters.get({ id: 3 }, 'born');

2. a lambda function that traverses a JSON object.

const instagram = await db.fighters.get({ id: 3 }, c => c.social.instagram);

In this case, social is a JSON object with an instagram property.

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

const fighter = await db.fighters.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 orderBy = await db.fighters.query({
  where: {
    hometown: 'Brisbane, Australia'
  },
  orderBy: (c, f) => f.lower(c.instagram)
});

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 fighters = await db.fighters.query({
  where: { 
    isActive: true 
  }, 
  select: ['name', 'hometown'],
  orderBy: 'reachCm',
  limit: 10
});

You can also include additional relations:

const locations = await db.locations.query({
  include: {
    events: (t, c) => t.events.query({ 
      where: {
        locationId: 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 users = await db.users.many({ id: c => c.not(excluded) });
const count = await db.users.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 events = await db.events.query({
  where: {
    or: [
      { name: c => c.like('UFC 1_: The%') },
      { id: c => c.lt(10) },
      {
        and: [
          { startTime: c => c.gt(time) },
          { name: c => c.like('%Japan%') }
        ]
      }
    ]
  }
});

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.fighters.count({
  where: {
    hometown: 'Brisbane, Australia'
  }
});

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

const exists = await db.fighters.exists({ name: 'Israel Adesanya' });

You can write group by statements like this:

const towns = await db.fighters
  .groupBy('hometown')
  .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 groupValues = await db.events
  .groupBy('locationId')
  .array({
    select: {
      startTimes: 'startTime'
    },
    limit: 3
  });

All of the functions built into SQLite, such as concat, round, and substring can be used to create fields that are computed inside the database and can therefore be used in all of the clauses, such as orderBy or where.

db.fighters.compute({
  displayName: (c, f) => f.concat(c.name, ' (', c.nickname, ')'),
  instagram: c => c.social.instagram
});

The instagram example nagivates a JSON type to extract a specific field. You can then use these fields in the rest of the API in exactly the same way as you do with standard columns.

const orderBy = await db.fighters.query({
  select: 'instagram',
  where: {
    and: [
      { id: c => c.gt(100) },
      { id: c => c.lt(120) },
      { instagram: c => c.not(null) }
    ]
  },
  orderBy: (c, f) => f.lower(c.instagram)
});

Computed fields are not automatically included in results and have to be specifically selected. They should be defined in the db.js file.

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

const changes = await db.fighters.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.

import { db } from './db.js';

try {
  const tx = await db.getTransaction();
  await tx.begin();

  const coachId = await tx.coaches.insert({
    name: 'Eugene Bareman',
    city: 'Auckland'
  });
  const fighterId = await tx.fighters.get({ name: c => c.like('Israel%') }, 'id');
  await tx.fighterCoaches.insert({
    fighterId,
    coachId
  });
  
  await tx.commit();
}
catch (e) {
  console.log(e);
  await tx.rollback();
}

You can also run multiple statements inside a single transaction without any logic using batch.

const projectId = 1;
const [project, tags, issues] = await db.batch((bx) => [
  bx.projects.get({ id: projectId }),
  bx.tags.many({ projectId }),
  bx.issues.many({ projectId })
]);

const result = { ...project, tags, issues };

Tables are defined in ./database/sql/tables.sql. You can add or change tables from here and then run the migration command npm run migrate <migration-name>.

If you want to reset the migration system to a new database that already has tables created on it, edit the tables.sql file and then run npm run reset.

If you want to add a new column to a table without needing to drop the table, make sure you put the column at the end of the list of columns.

Tables are created the same way as they are in SQL. The native types available in strict mode are integer, real, text, blob, and any. In addition to these types, four additional types are included by default: boolean, date, and json. boolean is a column in which the values are restricted to 1 or 0, date is a JavaScript Date stored as an ISO8601 string, and json is jsonb stored as a blob. These additional types are automatically parsed by the ORM.

create table events (
    id integer primary key,
    name text not null,
    startTime date not null,
    locationId integer references locations
);

Default values can be set for boolean and date columns using the following syntax:

create table users (
  id integer primary key,
  isDisabled boolean not null default false,
  createdAt date not null default now()
);

current_timestamp will not work properly when wanting to set the default date to the current time. This is because current_timestamp does not include timezone information and therefore when parsing the date string from the database, JavaScript will assume it is in local time when it is in fact in UTC time.

When the API doesn't do what you need it to do, you can create SQL queries. You can do this by creating a folder with the same name as the table, such as ./database/sql/users. You can then put SQL files in this folder that will be available in the API.

For example, if you create a query in ./database/sql/users/roles.sql that looks like this:

select
    u.id,
    u.name,
    groupArray(r.name) as roles
from
    users u join
    userRoles ur on ur.userId = u.id join
    roles r on ur.roleId = r.id
where 
    u.name = $name
group by 
    u.id

A function db.users.roles will be available in the API with the correct types.

When creating SQL queries, make sure you give an alias to any columns in the select statement that don't have a name. For example, do not do:

select max(startTime) from events;

as there is no name given to max(startTime).

Parameters in SQL files should use the $name notation. If you want to include dynamic content that cannot be parameterized, you should use the ${column} format and then pass in a second argument when calling the SQL statement in JavaScript. For example:

select * from users where location = $location order by ${column};

const options = {
  unsafe: {
    column: 'lastName'
  }
};
const users = await db.users.from({ location: 'Brisbane' }, options);

If the unsafe parameter is undefined in the options argument, it will be removed from the SQL statement.

Single quotes in strings should be escaped with \.

object(
    u.id, 
    u.name, 
    u.social) as user

is just shorthand for

json_object(
    'id', u.id, 
    'name', u.name, 
    'social', u.social) as user

Other commands available are groupArray which is shorthand for json_group_array, and array, which is shorthand for json_array.

Normally, SQLite doesn't support aliased stars, but this syntax is now available when writing SQL statements with Flyweight.

select
    e.*,
    l.name as locationName
from 
    events e join
    locations l on e.locationId = l.id

Views are treated like read-only tables. If you want to create a view called activeUsers, you can add a file in the views folder called ./database/views/activeUsers.sql that might have SQL like this:

create view activeUsers as
select * from users where isActive = true;

You can now use it in the API like this:

import { db } from './database/db.js';

const user = await db.activeUsers.get({ id: 100 }, ['name', 'email']);
console.log(user.email);

You can create subqueries programmatically that can be used like views in that they act as read-only tables. In the db.js file you can add a query like this:

await db.subquery((tables, is, compute) => {
  const {
    locations: l,
    events: e
  } = tables;
  const nameLength = compute.length(e.name);
  return {
    select: {
      ...e,
      location: l.name,
      nameLength
    },
    join: {
      [e.locationId]: l.id
    },
    where: {
      [nameLength]: is.gt(20)
    },
    as: 'detailedEvents'
  }
});

The above code will create what appears to be a new table in the API called detailedEvents that can be used like other read-only tables. Parameters in the where clause and elsewhere can only be of type boolean, Date, ````number, or null``` to prevent SQL injection attacks, even though no user-supplied input should be used in these queries either way.

The object returned from the subquery method can include the following:

select, where, groupBy, having, orderBy, desc, limit, and offset. The name of the subquery is provided by the as property.

join: an object representing the keys to join on. leftJoin can be used instead if the tables should be left joined. If no join option is provided, the ORM will assume that there is a single table and use the first table that was taken from the tables parameter.

Flyweight can sample columns that are declared with the json type to create richer type information.

To sample your local database, run npm run sample.