Flyweight

An ORM for SQLite

ORMSQL
thebinarysearchtree/flyweightcreate-flyweight

README

Flyweight


An ORM for SQLite and NodeJS. Flyweight combines a very simple API for performing basic operations, with the ability to create SQL queries that are parsed by the ORM.

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

  1. ```sql
  2. select
  3.     u.id,
  4.     u.name,
  5.     groupArray(r.name) as roles
  6. from
  7.     users u join
  8.     userRoles ur on ur.userId = u.id join
  9.     roles r on ur.roleId = r.id
  10. where
  11.     u.name = $name
  12. group by
  13.     u.id
  14. ```

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

Shorthand JSON functions


  1. ```sql
  2. object(
  3.     u.id,
  4.     u.name,
  5.     u.social) as user
  6. ```

is just shorthand for

  1. ```sql
  2. json_object(
  3.     'id', u.id,
  4.     'name', u.name,
  5.     'social', u.social) as user
  6. ```

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

Alias stars


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

  1. ```sql
  2. select
  3.     e.*,
  4.     l.name as locationName
  5. from
  6.     events e join
  7.     locations l on e.locationId = l.id
  8. ```

Creating tables


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 json stored as text. These additional types are automatically parsed by the ORM.

  1. ```sql
  2. create table events (
  3.     id integer primary key,
  4.     name text not null,
  5.     startTime date not null,
  6.     locationId integer references locations
  7. );
  8. ```

Each table has a singular and plural form. If you want to get one row with the basic API, you can use:

  1. ```js
  2. const event = await db.event.get({ id: 100 });
  3. ```

If you want to get many rows, you can use:

  1. ```js
  2. const names = await db.events.get({ id: eventIds }, 'name');
  3. ```

If you want to insert a row, you can do:

  1. ```js
  2. const id = await db.coach.insert({
  3.   name: 'Eugene Bareman',
  4.   city: 'Auckland'
  5. });
  6. ```

Getting started


  1. ```
  2. mkdir test
  3. cd test
  4. npm init
  5. npx create-flyweight database
  6. ```

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:

  1. ```js
  2. import { db } from './database/db.js';

  4. await db.user.insert({ name: 'Andrew' });
  5. const users = await db.users.get();
  6. console.log(users);
  7. ```

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.

Migrations


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

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.

Regular expressions


Flyweight supports regular expressions in some of its methods. These regular expressions are converted to like statements, which limits what kind of regular expressions you can make.

  1. ```js
  2. const coach = await db.coach.get({ name: /^Eugene.+/ });
  3. ```

Default values


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

  1. ```sql
  2. create table users (
  3.   id integer primary key,
  4.   isDisabled boolean not null default false,
  5.   createdAt date not null default now()
  6. );
  7. ```

  1. ```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.

  3. ## The API

  5. Every table has ```get```, ```update```, ```insert```, 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``` method available to them.

  7. ### Insert

  9. ```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. The plural version of ```insert``` is for batch inserts and takes an array of ```params```. It doesn't return anything.