Scribe

Scribe is a TypeScript library for storing and retrieving data in PostgreSQL database.

* Type Safe: Scribe keeps track of your persisted object types, so you always have fully-typed query results, even when using JOIN and GROUP BY.
* Immutable Queries: Operate on SQL queries as easily as you would arrays, using operators that always return a new query rather than mutate the initial one.
* Realtime by Default: Easily subscribe to data changes downstream, as Scribe returns observables for all query results.
* Automatic Schema Updates: Scribe can automatically update your database schema on file-save, so you don't have to switch between app-logic and SQL while developing

Quick Start

1. Install the package
``
npm install @triframe/scribe @triframe/transpiler
`
2. Specify a Database
Next, use an environment variable to specify a connection string for the database. This is usually easiest to do using a .env file in your project root:
`bash

~/.env

DATABASE_URL=postgresql://USERNAME:PASSWORD@localhost:5432/DB_NAME
`
Note: While Scribe can be used to read or write data in a database using more granular privileges, to get the benefits of automatic schema updates in development, the username and password provided must be for a DB Superuser, or the owner of the db specified by DB_NAME.

3. Add a Project Entry Point
Add a src folder to your project, containing an entry file (usually index.ts).
`ts
// ~/src/index.ts

import { persist } from '@triframe/scribe';

type User = {
username: string;
}

const Users = persist();

`

4. Build Your Project
You only need to do this once:
`
npx triframe node.build src/index.ts
`

5. Run Your Project
Do this every time you want to run your project:
`
npx triframe.node.dev src/index.ts
`

Amongst other startup output, you should see this line:
`sql
CREATE TABLE "User" ("username" VARCHAR NOT NULL);
`

This is Scribe automatically creating a table to store User objects in, based on the type argument you passed to persist.

persist is the primary entry point for all usage of Scribe- from here, you can go on to learn about how to create table schemas based on the typen argument you _pass_ to persist (User), or you can learn about how to interact with the data in the schema using the value returned by persist (Users).

Schemas

Schemas are created from TypeScript types.

By default, PostgreSQL types are inferred from TypeScript types:

| TS Type | PostgreSQL Type |
| -------- | ------- |
| string | varchar |
| number | int |
| boolean | Bool |
| Date | TIMESTAMP WITH TIME ZONE |
| { ... } | JSON |

However, Scribe also exports types to use for more explicit type definitions:

`ts
import { persist, Varchar, Integer, Float } from '@triframe/scribe';

type User = {
username: Varchar<255>;
age: Integer;
height: Float<4>;
}

const Users = persist();
`
`sql
-- Result:
CREATE TABLE "User" (
"username" VARCHAR (255) NOT NULL,
"age" INT NOT NULL,
"height" FLOAT4 NOT NULL
);

`
In your TypeScript code, these explicit type definitions are compatible with TypeScript types, for example Varchar is assignable to string, and Integer and Float are both assignable to number.

Note that while inferring PostgrSQL types from TypeScript types is effective in a majority of situations, there are many PostgreSQL types which can only be defined explicitly, such as Float.

$3


By default, the columns of a table created using persist are non-nullable. Use | null to mark a column as nullable:
`ts
import { persist, Float, Serial } from '@triframe/scribe';

type User = {
id: Serial;
username: string;
age: number;
height: Float<4> | null;
}

const Users = persist();
`
`sql
-- Result:
CREATE TABLE "User" (
"id" SERIAL NOT NULL,
"username" VARCHAR NOT NULL,
"age" INT NOT NULL,
"height" INT
);
`

$3


Scribe exports a Serial type for defining auto-incrementing columns, such as an id:
`ts
import { persist, Float, Serial } from '@triframe/scribe';

type User = {
id: Serial;
username: string;
age: number;
height: Float<4>;
}

const Users = persist();
`
`sql
-- Result:
CREATE TABLE "User" (
"id" SERIAL NOT NULL,
"username" VARCHAR NOT NULL,
"age" INT NOT NULL,
"height" FLOAT4 NOT NULL
);

`

$3


There are a few methods you can call on a CollectionQuery that will further modify the schema.

#### Adding a Primary Key to a Collection
SCHEMA MODIFIER | CollectionQuery.primaryKey(...fields: (keyof T)[]): CollectionQuery

Add a primary key to a table using .primaryKey:
`ts
import { persist, Float, Serial } from '@triframe/scribe';

type User = {
id: Serial;
username: string;
age: number;
height: Float<4> | null;
}

const Users = persist()
.primaryKey('id');
`
`sql
-- Result:
CREATE TABLE "User" (
"id" SERIAL NOT NULL,
"username" VARCHAR NOT NULL,
"age" INT NOT NULL,
"height" INT,
PRIMARY KEY ("id")
);
`

#### Adding a Foreign Key to a Collection
SCHEMA MODIFIER | CollectionQuery.foreignKey(fields: (keyof T) | (keyof T)[], to: To): CollectionQuery

Add a foreign key to a table using .foreignKey and to:
`ts
import { persist, Float, Serial, to } from '@triframe/scribe';

type User = {
id: Serial;
username: string;
age: number;
height: Float<4>;
}

const Users = persist()
.primaryKey('id');

type Pet = {
name: string;
ownerId: number;
}

const Pets = persist()
.foreignKey('ownerId', to('id'));

`
`sql
-- Result:

CREATE TABLE "User" (
"id" SERIAL NOT NULL,
"username" VARCHAR NOT NULL,
"age" INT NOT NULL,
"height" INT,
PRIMARY KEY ("id")
);
CREATE TABLE "Pet" (
"name" VARCHAR NOT NULL,
"ownerId" INT NOT NULL,
FOREIGN KEY ("ownerId") REFERENCES "User" ("id")
);
`

#### Adding an Index to a Collection
SCHEMA MODIFIER | CollectionQuery.indexBy(...fields: (keyof T)[]): CollectionQuery

Add a index to a table using .indexBy:
`ts
import { persist, Float, Serial } from '@triframe/scribe';

type User = {
id: Serial;
username: string;
age: number;
height: Float<4> | null;
}

const Users = persist()
.indexBy('age');
`
`sql
-- Result:

CREATE TABLE "User" (
"id" SERIAL NOT NULL,
"username" VARCHAR NOT NULL,
"age" INT NOT NULL,
"height" INT
);
CREATE INDEX "User_age" ON "User" ("age");
`

#### Adding a Unique Index to a Collection
SCHEMA MODIFIER | CollectionQuery.uniqueIndexBy(...fields: (keyof T)[]): CollectionQuery

Add a unique index to a table using .uniqueIndexBy:
`ts
import { persist, Float, Serial } from '@triframe/scribe';

type User = {
id: Serial;
username: string;
age: number;
height: Float<4> | null;
}

const Users = persist()
.uniqueIndexBy('username');
`
`sql
-- Result:

CREATE TABLE "User" (
"id" SERIAL NOT NULL,
"username" VARCHAR NOT NULL,
"age" INT NOT NULL,
"height" INT,
UNIQUE ("username")
);
`

#### Defining Default Values for a Collection
SCHEMA MODIFIER | CollectionQuery.defaults(defaultValues: Partial): CollectionQuery

Add a default values to a table using .defaults:
`ts
import { persist, Float, Serial } from '@triframe/scribe';

type User = {
id: Serial;
username: string;
age: number;
height: Float<4> | null;
}

const Users = persist()
.defaults({ age: 1 });
`
`sql
-- Result:

CREATE TABLE "User" (
"id" SERIAL NOT NULL,
"username" VARCHAR NOT NULL,
"age" INT DEFAULT '1' NOT NULL,
"height" INT
);
`

Queries


persist returns a CollectionQuery object, representing all rows in a table. Using a query OPERTATORs, you can filter a table down to a single row, producing a RowQuery. Both CollectionQuery and RowQuery objects are plain JavaScript objects, which can be assigned to variables, passed as arguments, etc.
They are also immutable; if you use an OPERTATOR to change a query, it will return a _new query_ with the change- this provides a very powerful mechanisim for query re-use across your codebase. Both CollectionQuery and RowQuery objects also have ACCESSORs, as opposed to OPERATORs, these methods will execute a query, retreiving or mutating the data targeted by the query.

$3

#### Adding a record to a Collection
ACCESSOR | CollectionQuery.append(record: T): T

You can insert a new record into a collection using .append:
`ts
await Users.append({
username: 'stormagedon',
age: 1
})
`
`sql
-- Result:

INSERT INTO "User" ("username", "age")
VALUES ($1, 1)
-- params: [ 'stormagedon' ]
`

#### Listing the records in a Collection
ACCESSOR | CollectionQuery.list(): Observable

You can retreive an array of records using .list:
`ts
const users = await Users.list();
`
`sql
-- Result:
SELECT "id",
"username",
"age",
"height"
FROM "User"
LIMIT 20 OFFSET 0;
`
`jsonc
// users:
[ { "id": 1, "username": "stormagedon", "age": 1, "height": null } ]
`
By default, the query is limited to returning 20 results. Scribe intentionally adopts a "limit-by-default" mentality, making it the responsability of the application developer to build pagination into whatever the calling contexts where .list is being called. If you absolutely want to run a query without a limit, you can disable the default limit using .limit: await Users.limit(null).list().

.list returns an Observable. This can be treated as a Promise (as shown in the example above), or watched using .observe, or consumed using any pipeline that supports TriFrame observables.


#### Filtering a Collection
OPERATOR | CollectionQuery.filter((record: T) => boolean): CollectionQuery

You can filter a collection using a callback:
`ts
const users = await Users.filter(user => user.age > 18).list();
`
`sql
-- Result:
SELECT "id",
"username",
"age",
"height"
FROM "User"
WHERE "User"."age" > 18
LIMIT 20 OFFSET 0;
`
Note that .filter did not execute the query, it produced a new CollectionQuery with the filter, and then we used .list to execute a SQL query that selected all of the matching rows.
This allows us to re-use the filter if desired as folows:
`ts
const Adults = Users.filter(user => user.age > 18);
const users = await Adults.list();
`
`sql
-- Result:
SELECT "id",
"username",
"age",
"height"
FROM "User"
WHERE "User"."age" > 18
LIMIT 20 OFFSET 0;
`
It is also worth noting that, when the query is executed, the provided callback is not executed within the JavaScript runtime; instead it is serialized during type checking and then executed as part of the SQL query itself.

#### Finding a Row in a Collection
OPERATOR | CollectionQuery.find((record: T) => boolean): RowQuery;

If you want to target the first row that matches a callback, you can use .find:
`ts
const CurrentUser = Users.find(user => user.id === 1);
`
Like .filter, this produces a Query object, it does not execute the query. _Unlike_ .filter, it produces a RowQuery instead of a CollectionQuery.
As a result, we cannot call ACCESSORs that operate against a collection on it, like .list.


#### Getting a single record from a Collection
ACCESSOR | RowQuery.get(): Observable

To get a single record from a table, you first need to filter a CollectionQuery down to a RowQuery using a query OPERATOR, like .find:
`ts
const currentUser = await Users.find(user => user.id === 1).get();
`
`sql
-- Result:
SELECT "id",
"username",
"age",
"height"
FROM "User"
WHERE "User"."id" = 1
LIMIT 1 OFFSET 0
`
`jsonc
// currentUser:
{ "id": 1, "username": "stormagedon", "age": 1, "height": null }
`

#### Updating records in a Collection
ACCESSOR | (CollectionQuery | RowQuery).set(callback: (row: T) => Partial): void

You can update data in a collection using .set:
`ts
await Users.find(user => user.id === 1).set(user => ({ age: 2 }));
`
`sql
-- Result:
UPDATE "User"
SET "age" = 2
WHERE "User"."id" = 1;
`
Like other Scribe methods that accept a callback, the callback is not executed within the JavaScript runtime; instead it is serialized during type checking and then executed as part of the SQL query itself. This allows us to write performant queries that modify rows relative to their current values:
`ts
await Users.find(user => user.id === 1).set(user => ({ age: user.age + 1 }));
`
`sql
-- Result:
UPDATE "User"
SET "age" = "User"."age" + 1
WHERE "User"."id" = 1
`
Also note that, while this example finds a specific row to update, you can also call .set on a CollectionQuery to update multiple rows in a table if desired.

#### Deleting records in a Collection
ACCESSOR | Query.remove(): void

You can delete data from a table using .remove:
`ts
await Users.find(user => user.id === 1).remove();
`
`sql
-- Result:
DELETE FROM "User"
WHERE "User"."id" = 1;
`

#### Upserting records into a Collection
ACCESSOR | Query.upsert(record: T): void

You can upsert data into a table using .upsert:
`ts
await Users.upsert({ id: 1, username: 'jmiles', age: 28 })
`
`sql
-- Result:

INSERT INTO "User" ("id", "username", "age")
VALUES (1, $1, 28) ON CONFLICT ("id") DO
UPDATE
SET "id" = "excluded"."id",
"username" = "excluded"."username",
"age" = "excluded"."age"

-- params: [ 'jmiles' ]
`

IMPORTANT: .upsert will only work on CollectionQuerys with unique indexes.

This will insert the record if no record with the specified unique index exists, or updates the existing record if there is one.

#### Limit and Offset
OPERATOR | CollectionQuery.limit(limit: number, offset?: number): CollectionQuery

You paginate a collection using .limit:
`ts
const users = await Users.limit(10, 20).list();
`
`sql
-- Result:
SELECT "id",
"username",
"age",
"height"
FROM "User"
LIMIT 10 OFFSET 20;
`

#### Order By
OPERATOR | CollectionQuery.orderBy({ field: keyof T, direction: 'ASC' | 'DESC' }[]): CollectionQuery

You order a collection using .orderBy:
`ts
const users = await Users
.orderBy([
{
field: 'age',
direction: 'DESC'
}
])
.list();
`
`sql
-- Result:
SELECT "id",
"username",
"age",
"height"
FROM "User"
ORDER BY "age" DESC
LIMIT 20 OFFSET 0
`

$3


There are more advanced OPERATORs which change the shape of a collection.

#### Adding Computed Fields to Queries
OPERATOR | Query.extend(callback: (record: T) => R): Query

You can add fields to a query using .extend, which can be thought of as similar to Array.prototype.map:
`ts
type User = {
id: Serial;
firstName: string;
lastName: string;
age: number;
}

const Users = persist()

const users = await Users
.extend(user => ({
...user,
fullName: ${user.firstName} ${user.lastName}
}))
.list()
`
`sql
-- Result:

SELECT "id",
"firstName",
"lastName",
"age",
"User"."firstName" || ' ' || "User"."lastName" as "fullName"
FROM "User"
LIMIT 20 OFFSET 0
`
`jsonc
// users:
[ { "id": 1, "firstName": "Josh", "lastName": "Miles", "age": 28, "fullName": "Josh Miles" } ]
`
Note how the JavaScript template literal is being transpiled into a SQL string concatenation.
Many other basic language constructs, such as mathematical and binary operations, are supported:
`ts
const users = await Users
.extend(user => ({
...user,
fullName: ${user.firstName} ${user.lastName},
isAdult: user.age > 18
}))
.list()
`
`sql
--- Result:

SELECT "id",
"firstName",
"lastName",
"age",
"User"."firstName" || ' ' || "User"."lastName" as "fullName",
"User"."age" > 18 as "isAdult"
FROM "User"
LIMIT 20 OFFSET 0
`
`jsonc
// users:
[ { "id": 1, "firstName": "Josh", "lastName": "Miles", "age": 28, "fullName": "Josh Miles", "isAdult": true } ]
`

.extend in particular is more powerful as a toole for code re-use, for example:
`ts
const UserProfiles = Users.extend(user => ({
...user,
fullName: ${user.firstName} ${user.lastName},
isAdult: user.age > 18
}))

const users = await UserProfiles.list();
`

Fields added using .extend can be referenced in .filter and .find callbacks, in addition to query output:
`ts
const UserProfiles = Users.extend(user => ({
...user,
fullName: ${user.firstName} ${user.lastName},
isAdult: user.age > 18
}))

const adults = await UserProfiles.filter(user => user.isAdult).list();
`
`sql
-- Result:

SELECT "id",
"firstName",
"lastName",
"age",
"User"."firstName" || ' ' || "User"."lastName" as "fullName",
"User"."age" > 18 as "isAdult"
FROM "User"
WHERE "User"."age" > 18
LIMIT 20 OFFSET 0
`

#### Hiding Fields from Query Output
OPERATOR | Query.private(...fields: (keyof T)): Query>
Often, it is valuable to remove a field from the output of a query. This can be accomplished using .private:
`ts
type User = {
id: Serial;
firstName: string;
lastName: string;
age: number;
}

const Users = persist()

const users = await Users.private('age').list();
`
`sql
--- Result:

SELECT "id",
"firstName",
"lastName"
FROM "User"
LIMIT 20 OFFSET 0
`
`jsonc
// users:
[ { "id": 1, "firstName": "Josh", "lastName": "Miles" } ]
`

Notably, .private fields can still be referenced in .filter, .find, and .extend callbacks:

`ts
type User = {
id: Serial;
firstName: string;
lastName: string;
age: number;
}

const Users = persist()

const users = await Users.private('age').filter(user => user.age > 18).list();
`
`sql
--- Result:

SELECT "id",
"firstName",
"lastName"
FROM "User"
WHERE "User"."age" > 18
LIMIT 20 OFFSET 0
`

#### Adding Fields Hidden from Query Output
Occasionally, it is valuable to add a computed field that should never be included in the output of a query. This can be accomplished using .extendPrivate:
`ts
type User = {
id: Serial;
firstName: string;
lastName: string;
age: number;
}

const Users = persist();

const UserProfiles = Users.extendPrivate(user => ({
...user,
isAdult: user.age > 18
}))

const adults = await UserProfiles.list();
`
`sql
--- Result:

SELECT "id",
"firstName",
"lastName",
"age"
FROM "User"
WHERE "User"."age" > 18
LIMIT 20 OFFSET 0
`
`jsonc
// users:
[ { "id": 1, "firstName": "Josh", "lastName": "Miles", "age": 28 } ]
`

This is a convenience method for calling .extend, and then .private, which is functionally identical.

#### Changing the behavior of .remove
CONFIG | Query.onRemove(callback: (query: Query) => any): Query

Often, it is valuable to change the default behavior of .remove from it's default, which is .hardDelete. For example, to facilitate soft-deletes:
`ts
type User = {
id: Serial;
firstName: string;
lastName: string;
isDeleted: boolean;
}

const UsersAndDeletedUsers = persist();

const Users = UsersAndDeletedUsers
.filter( user => user.isDeleted === false)
.onRemove( Users => Users.set(() => ({ isDeleted: true })))

await Users.find( user => user.id === 1).remove();
`
`sql
-- Result:

UPDATE "User"
SET "isDeleted" = true
WHERE "User"."isDeleted" = false
AND "User"."id" = 1
`

#### Removing Fields from Queries
OPERATOR | Query.omit(...fields: (keyof T)): Query>

Occasionally, it is valuable to remove a field from a query alltogether so that it cannot be accessed anywhere else in your codebase and accidentally leaked to an end user.
This can be accomplished using .omit:
`ts
type User = {
id: Serial;
username: string;
passwordDigest: string;
}

const UsersWithPasswordDigests = persist()

const Users = UsersWithPasswordDigests.omit('passwordDigest')

const users = await Users.list();
`
`sql
--- Result:

SELECT "id",
"username",
FROM "User"
LIMIT 20 OFFSET 0
`
`jsonc
// users:
[ { "id": 1, "username": "jmiles" } ]
`
Note how this is different than .private, because even attempting to reference the omitted field in a .find or filter will throw an error.

$3


In addition to the default OPERATORs, CollectionQuery objects will have OPERATORs specific to it's indexes.

#### Finding a Row by unique index or primary key
OPERATOR | CollectionQuery.findBy{IndexName}(values: mixed): CollectionQuery

A CollectionQuery can be filtered to a single RowQuery by any unique index or primary key on the CollectionQuery.
For example:
`ts
type User = {
id: Serial;
firstName: string;
lastName: string;
age: number;
}

const Users = persist()
.primaryKey('id');

const currentUser = await Users.findById(1).get();
`
`sql
-- Result:

SELECT "id",
"firstName",
"lastName",
"age"
FROM "User"
WHERE "User"."id" = 1
ORDER BY "id" ASC
LIMIT 1 OFFSET 0
`
This is a convenience method, that is functionally equivalent to using .find:
`ts
await Users.find(user => user.id === 1).get()
`

#### Filtering a Collection by an index
OPERATOR | CollectionQuery.filterBy{IndexName}(values: mixed): CollectionQuery

A CollectionQuery can be filtered by any index or foreign key on the CollectionQuery.
For example:
`ts
type User = {
id: Serial;
firstName: string;
lastName: string;
age: number;
}

const Users = persist()
.indexBy('age');

const twentyEightYearOlds = await Users.filterByAge(28).list();
`
`sql
-- Result:

SELECT "id",
"firstName",
"lastName",
"age"
FROM "User"
WHERE "User"."age" = 28
ORDER BY "id" ASC
LIMIT 20 OFFSET 0
`
This is a convenience method, that is functionally equivalent to using .filter:
`ts
await Users.filter(user => user.age === 28).list()
`

#### Finding / Filtering on an index and Hiding the Index Fields from Query Output
OPERATOR | CollectionQuery.with{IndexName}(values: mixed): CollectionQuery

It is often useful to both filter a CollectionQuery _and_ hide the field filtered on from the resulting output.
This may seem odd at first, but if you think of _filtering_ a colleciton as producing a _new collection_, then it becomes apparent that filtering a collection to where a field matches a specific value renders that attribute duplicitious - the given field has the same value for all members of the new collection, _by definition_, making it meaningless, and sometimes confusing, data to include in query results.

In these cases, whether you are filtering on a unique or non-unique index, you can use the with{IndexName} method. For example:
`ts
type User = {
id: Serial;
firstName: string;
lastName: string;
isDeleted: boolean;
}

const UsersAndDeletedUsers = persist()
.primaryKey('id')
.indexBy('isDeleted');

const ActiveUsers = UsersAndDeletedUsers.withIsDeleted(false);

const users = await ActiveUsers.list();

`
`sql
-- Result:

SELECT "id",
"firstName",
"lastName"
FROM "User"
WHERE "User"."isDeleted" = false
ORDER BY "id" ASC
LIMIT 20 OFFSET 0
`
Note how isDeleted has been redacted from the list of selected fields automatically; it does not make sense for a result from the ActiveUsers query to have an isDeleted property, as by definition, all ActiveUsers are not deleted.

This is a convenience method, that is functionally equivalent to using .filter or .find and then .private:
`ts
await Users.filter(user => user.isDeleted === false).private('isDeleted').list()
`

$3


While many of the examples of OPERATOR usage in this documentation deals with retrieving data, it is important to know that, when possible, Scribe will apply the transformations specified by OPERATORs for mutations too.

#### Inferred fields for filtered collections
When inserting into a collection filtered by a strict equality check against a literal value, Scribe will "infer" that value as the default.
`ts
type User = {
id: Serial;
firstName: string;
lastName: string | null;
}

const Users = persist();

const MilesFamily = Users.filter(user => user.lastName === 'Miles');

await MilesFamily.append({ firstName: 'Wesley' })
`
`sql
-- Result:
INSERT INTO "User" ( "firstName", "lastName")
VALUES ($1, $2);
-- params: ['Wesley', 'Miles']
`
We had to make "lastName" nullable in the last example, otherwise we would get a type error on our call to .append- which is strictly typed to look for all non-serial fields present in the collection.

However, there are better ways to do this- we could use .private, to redact the field from the required type:
`ts
type User = {
id: Serial;
firstName: string;
lastName: string;
}

const Users = persist();

const MilesFamily = Users.filter(user => user.lastName === 'Miles').private('lastName');

await MilesFamily.append({ firstName: 'Wesley' })
`

Or, best yet, use an index and the with{IndexName} operator:
`ts
type User = {
id: Serial;
firstName: string;
lastName: string;
}

const Users = persist().indexBy('lastName');

const MilesFamily = Users.withLastName('Miles');

await MilesFamily.append({ firstName: 'Wesley' })
`

#### Re-labeling of aliased fields
Let's say that we've renamed a field using .extend and .private:
`ts
type Book = {
id: Serial;
name: string;
}

const Books = persist()
.primaryKey('id')

const BooksWithTitle = Books
.extend(book => ({
...book,
title: book.name
}))
.omit('name');
`

Adding or updating data in the BooksWithTitle will correctly re-label the title to name in the resulting SQL:
`ts
await BooksWithTitle.append({ title: 'Lord of the Rings' })
`
`sql
-- Result:
INSERT INTO "Book" ("name")
VALUES ($1);
-- params: [ 'Lord of the Rings' ]
`

$3


Another common set of OPERATORs deal with joining multiple Query objects together.

For example, if we had a collection of Users and a collection of Pets:
`ts
type User = {
id: Serial;
firstName: string;
lastName: string;
}

type Pet = {
id: Serial;
ownerId: number | null;
name: string;
}

const Users = persist()
.primaryKey('id')

const Pets = persist()
.foreignKey('ownerId', to('id'))
`

We could use the below join OPERATORs to operate on them where they intersect.

In each of these examples, we will look at using .list to retrieve the joined data, but it's important to note that the result of join is still a Query object- you can continue to operate on it with filter, find, extend, and other joins.

You can use any accessor against it, though it much easier to think about mutating joined data using Associations, as described below.

#### Left Joins
OPERATOR | Query.leftJoin(callback: (record: T1) => Query): Query

Left joins return all rows from the base query (T1), and any matching rows from the joined query (T2).
`ts
const UsersAndPets = Users.leftJoin(user => Pets.filterByOwnerId(user.id))

const usersAndPets = await UsersAndPets.list();
`
`sql
-- Result:

SELECT "User"."id" as "id",
"Pet"."ownerId" as "ownerId",
"Pet"."name" as "name",
"User"."firstName" as "firstName",
"User"."lastName" as "lastName"
FROM "User"
LEFT JOIN "Pet" ON "Pet"."ownerId" = "User"."id"
ORDER BY "id" ASC
LIMIT 20 OFFSET 0
`
`jsonc
// usersAndPets:
[
{ "id": 1, "ownerId": 1, "name": "Tilly", "firstName": "Theresa", "lastName": "Miles" },
{ "id": 1, "ownerId": 1, "name": "Tess", "firstName": "Theresa", "lastName": "Miles" },
{ "id": 2, "ownerId": null, "name": null, "firstName": "Josh", "lastName": "Miles" }
]
`

#### Right Joins
OPERATOR | Query.rightJoin(callback: (record: T1) => Query): Query

Right joins return all rows from the joined query (T2), and any matching rows from the base query (T1).
`ts
const UsersAndPets = Users.rightJoin(user => Pets.filterByOwnerId(user.id))

const usersAndPets = await UsersAndPets.list();
`
`sql
-- Result:

SELECT "User"."id" as "id",
"Pet"."ownerId" as "ownerId",
"Pet"."name" as "name",
"User"."firstName" as "firstName",
"User"."lastName" as "lastName"
FROM "User"
RIGHT JOIN "Pet" ON "Pet"."ownerId" = "User"."id"
ORDER BY "id" ASC
LIMIT 20 OFFSET 0
`
`jsonc
// usersAndPets:
[
{ "id": 1, "ownerId": 1, "name": "Tilly", "firstName": "Theresa", "lastName": "Miles" },
{ "id": 1, "ownerId": 1, "name": "Tess", "firstName": "Theresa", "lastName": "Miles" },
{ "id": null, "ownerId": null, "name": "Stray Cat", "firstName": null, "lastName": null}

]
`

#### Inner Joins
OPERATOR | Query.innerJoin(callback: (record: T1) => Query): Query

Inner joins return only rows where both the base query (T1) and joined query (T2) have values.
`ts
const UsersAndPets = Users.innerJoin(user => Pets.filterByOwnerId(user.id))

const usersAndPets = await UsersAndPets.list();
`
`sql
-- Result:

SELECT "User"."id" as "id",
"Pet"."ownerId" as "ownerId",
"Pet"."name" as "name",
"User"."firstName" as "firstName",
"User"."lastName" as "lastName"
FROM "User"
INNER JOIN "Pet" ON "Pet"."ownerId" = "User"."id"
ORDER BY "id" ASC
LIMIT 20 OFFSET 0
`
`jsonc
// usersAndPets:
[
{ "id": 1, "ownerId": 1, "name": "Tilly", "firstName": "Theresa", "lastName": "Miles" },
{ "id": 1, "ownerId": 1, "name": "Tess", "firstName": "Theresa", "lastName": "Miles" },
]
`

#### Outer Joins
OPERATOR | Query.outerJoin(callback: (record: T1) => Query): Query

Outer joins return all rows in both the base query (T1) and joined query (T2).
`ts
const UsersAndPets = Users.outerJoin(user => Pets.filterByOwnerId(user.id))

const usersAndPets = await UsersAndPets.list();
`
`sql
-- Result:

SELECT "User"."id" as "id",
"Pet"."ownerId" as "ownerId",
"Pet"."name" as "name",
"User"."firstName" as "firstName",
"User"."lastName" as "lastName"
FROM "User"
OUTER JOIN "Pet" ON "Pet"."ownerId" = "User"."id"
ORDER BY "id" ASC
LIMIT 20 OFFSET 0
`
`jsonc
// usersAndPets:
[
{ "id": 1, "ownerId": 1, "name": "Tilly", "firstName": "Theresa", "lastName": "Miles" },
{ "id": 1, "ownerId": 1, "name": "Tess", "firstName": "Theresa", "lastName": "Miles" },
{ "id": 2, "ownerId": null, "name": null, "firstName": "Josh", "lastName": "Miles" },
{ "id": null, "ownerId": null, "name": "Stray Cat", "firstName": null, "lastName": null}
]
`

#### Left Join Lateral
OPERATOR | Query.leftJoinLateral(callback: (record: T1) => Query): Query

Left joins return all rows from the base query (T1), and any matching rows from the joined query (T2).

A full discussion of lateral joins is beyond the scope of this documentation; they are worth mentioning as they are a primitive ontop of which Scribe's associations functionality is built.
`ts
const UsersAndPets = Users.leftJoinLateral(user => Pets.filterByOwnerId(user.id))

const usersAndPets = await UsersAndPets.list();
`
`sql
-- Result:

SELECT "User"."id" as "id",
"Pet"."ownerId" as "ownerId",
"Pet"."name" as "name",
"User"."firstName" as "firstName",
"User"."lastName" as "lastName"
FROM "User"
LEFT JOIN LATERAL (
SELECT "id",
"ownerId",
"name"
FROM "Pet"
WHERE "Pet"."ownerId" = "User"."id"
LIMIT 20 OFFSET 0
) AS "Pet" ON 1 = 1
ORDER BY "id" ASC
LIMIT 20 OFFSET 0
`

$3


Another common set of OPERATORs deal with grouping CollectionQuery rows.

#### Grouping All Rows from a Collection
OPERATOR | CollectionQuery.groupAll(): RowQuery>

Grouping all records in a collection can be useful for aggregating, for example:
`ts
import { persist, Serial, count, max, min } from '@triframe/scribe';

type User = {
id: Serial;
firstName: string;
lastName: string;
age: number;
}

const Users = persist()
.primaryKey('id')

const UserStatistics = Users.groupAll().extend(users => ({
...users
total: count(users.id),
maxAge: max(users.age),
minAge: min(users.age)
}))

const userStatistics = await UserStatistics.get();
`
`sql
-- Result:

SELECT COUNT("User"."id") as "total",
MAX("User"."age") as "maxAge",
MIN("User"."age") as "minAge"
FROM "User"
LIMIT 1 OFFSET 0
`
`jsonc
// userStatistics:
{ "total": 3, "maxAge": 28, "minAge": 1 }
`
#### Grouping Rows by a field
OPERATOR | CollectionQuery.groupBy(...fields: (keyof T)[]): CollectionQuery>

Grouping records in a collection by a specific field value can also be useful for aggregating, for example:
`ts
import { persist, Serial, count, max, min } from '@triframe/scribe';

type User = {
id: Serial;
firstName: string;
lastName: string;
age: number;
}

const Users = persist()
.primaryKey('id')

const UserStatistics = Users.groupBy('lastName').extend(users => ({
...users
total: count(users.id),
maxAge: max(users.age),
minAge: min(users.age)
}))

const userStatistics = await UserStatistics.get();
`
`sql
-- Result:

SELECT "lastName",
COUNT("User"."id") as "total",
MAX("User"."age") as "maxAge",
MIN("User"."age") as "minAge"
FROM "User"
GROUP BY "User"."lastName"
ORDER BY "lastName" ASC
LIMIT 20 OFFSET 0
`
`jsonc
// userStatistics:
[
{ "lastName": "Miles", "total": 3, "maxAge": 28, "minAge": 1 },
{ "lastName": "Parkin", "total": 1, "maxAge": 30, "minAge": 30 }
]
`




$3

Associations are a convenience syntax on top of Scribes robust support for joins.


Consider again our example of a User and their Pets:
`ts
type User = {
id: Serial;
firstName: string;
lastName: string;
}

type Pet = {
id: Serial;
ownerId: number | null;
name: string;
}

const Users = persist()
.primaryKey('id')

const Pets = persist()
.foreignKey('ownerId', to('id'))
`

We can define an association using .extend:
`ts
const UsersWithPets = Users.extend(user => ({
...user,
pets: Pets.withOwnerId(user.id)
}))
`

The resulting query is _like_ the result of a join operation, in that it now references both tables.

However, joining tables this way creates a nested structure more common to app and API development, as opposed to the flattened structure returned by joins.

Using .list or .get to retrieve data from queries containing associations will result in a LATERAL JOIN:
`ts
const usersWithPets = await UsersWithPets.list();
`
`sql
SELECT "User"."id" as "id",
"User"."firstName" as "firstName",
"User"."lastName" as "lastName",
"Pet"."pets" as "pets"
FROM "User"
LEFT JOIN LATERAL (
SELECT COALESCE(
JSON_AGG(
JSONB_BUILD_OBJECT('id', "Pet"."id", 'name', "Pet"."name")
),
'[]'
) as "pets"
FROM "Pet"
WHERE "Pet"."ownerId" = "User"."id"
LIMIT 1 OFFSET 0
) AS "Pet" ON 1 = 1
ORDER BY "id" ASC
LIMIT 20 OFFSET 0
`
`jsonc
// results:
[
{
"id": 1,
"firstName": "Theresa",
"lastName": "Miles",
"pets": [
{
"id": 1,
"name": "Tilly"
},
{
"id": 2,
"name": "Tess"
}
]
},
{
"id": 2,
"firstName": "Josh",
"lastName": "Miles",
"pets": []
}
]
`

The defining characteristic of an association is using another query as a field when calling .extend; while we use the index operator withOwnerId in the example, it is not required that an index or key constraint exist, we could just as easily write a filter by hand:
`ts
const UsersWithPets = Users.extend(user => ({
...user,
pets: Pets.filter(pet => pet.ownerId === user.id)
}))
`

With the only difference being that ownerId would be included with the resulting pets, though we could easily redact it:
`ts
const UsersWithPets = Users.extend(user => ({
...user,
pets: Pets.filter(pet => pet.ownerId === user.id).private('ownerId')
}))
`

Or, while not particularly useful or performant, we could drop any kind of condition:
`ts
const UsersWithPets = Users.extend(user => ({
...user,
pets: Pets
}))
const results = await UsersWithPets.list();
`
`sql
SELECT "User"."id" as "id",
"User"."firstName" as "firstName",
"User"."lastName" as "lastName",
"Pet"."pets" as "pets"
FROM "User"
LEFT JOIN LATERAL (
SELECT COALESCE(
JSON_AGG(
JSONB_BUILD_OBJECT(
'id',
"Pet"."id",
'ownerId',
"Pet"."ownerId",
'name',
"Pet"."name"
)
),
'[]'
) as "pets"
FROM "Pet"
LIMIT 1 OFFSET 0
) AS "Pet" ON 1 = 1
ORDER BY "id" ASC
LIMIT 20 OFFSET 0
`
`jsonc
// results:
[
{
"id": 1,
"firstName": "Theresa",
"lastName": "Miles",
"pets": [
{
"id": 1,
"ownerId": 1,
"name": "Tilly"
},
{
"id": 2,
"ownerId": 1,
"name": "Tess"
},
{
"id": 3,
"ownerId": null,
"name": "Stray Cat"
}
]
},
{
"id": 2,
"firstName": "Josh",
"lastName": "Miles",
"pets": [
{
"id": 1,
"ownerId": 1,
"name": "Tilly"
},
{
"id": 2,
"ownerId": 1,
"name": "Tess"
},
{
"id": 3,
"ownerId": null,
"name": "Stray Cat"
}
]
}
]
`

If we filter an association down to a single row, it will be represented as an object, instead of an array, in the resulting nested structure:
`ts
const PetsWithOwner = Pets.extend(pet => ({
...pet,
owner: Users.findById(pet.ownerId)
}))
const results = await PetsWithOwner.list();
`
`sql
SELECT "Pet"."id" as "id",
"Pet"."ownerId" as "ownerId",
"Pet"."name" as "name",
"User"."owner" as "owner"
FROM "Pet"
LEFT JOIN LATERAL (
SELECT JSONB_BUILD_OBJECT(
'id',
"User"."id",
'firstName',
"User"."firstName",
'lastName',
"User"."lastName"
) as "owner"
FROM "User"
WHERE "User"."id" = "Pet"."ownerId"
LIMIT 1 OFFSET 0
) AS "User" ON 1 = 1
LIMIT 20 OFFSET 0
`
`jsonc
// Result
[
{
"id": 1,
"ownerId": 1,
"name": "Tilly",
"owner": { "id": 1, "lastName": "Miles", "firstName": "Theresa" }
},
{
"id": 2,
"ownerId": 1,
"name": "Tess",
"owner": { "id": 1, "lastName": "Miles", "firstName": "Theresa" }
},
{
"id": 3,
"ownerId": null,
"name": "Stray Cat",
"owner": null
}
]
`

Associations can also be used when updating data. There are three important principles to remember when when updating associated data using a single query:

1. The primary action (.append, .set, .upsert, etc.) will be completed first
2. If the association is a CollectionQuery, any records not specified in the corresponding array will be removed
3. Any specified associated data will be upserted, with inferred fields applied

For example:
`ts
const UsersWithPets = Users.extend(user => ({
...user,
pets: Pets.withOwnerId(user.id)
}))

await UsersWithPets.withId(1).set(() => ({
firstName: 'Theresa',
lastName: 'Miles',
pets: [
{ name: 'Sandy' },
{ name: 'Annie' }
]
}));

`
`sql
BEGIN

UPDATE "User"
SET "firstName" = $1,
"lastName" = $2
WHERE "User"."id" = 1;
-- params: [ 'Theresa', 'Miles' ]

DELETE FROM "Pet" USING "User"
WHERE "Pet"."ownerId" = "User"."id"
AND ("Pet"."ownerId" = 1)
AND "User"."id" = 1;

INSERT INTO "Pet" ("ownerId", "name")
VALUES
(1, $1),
(1, $2);
-- params: [ 'Sandy', 'Annie' ]

COMMIT;
`

Because associated data is upserted, you can create _or_ update associated rows:
`ts
const UsersWithPets = Users.extend(user => ({
...user,
pets: Pets.withOwnerId(user.id)
}))

await UsersWithPets.withId(2).set(() => ({
firstName: 'Josh',
lastName: 'Miles',
pets: [
{ id: 1, name: 'Tilly' },
{ id: 2, name: 'Tess' }
]
}));

`
`sql
BEGIN

UPDATE "User"
SET "firstName" = $1,
"lastName" = $2
WHERE "User"."id" = 2;
-- params: [ 'Josh', 'Miles' ]

DELETE FROM "Pet" USING "User"
WHERE "Pet"."ownerId" = "User"."id"
AND (1 != "Pet"."id")
AND (2 != "Pet"."id")
AND ("Pet"."ownerId" = 2)
AND "User"."id" = 2;

INSERT INTO "Pet" ("ownerId", "id", "name")
VALUES
(2, 1, $1),
(2, 2, $2)
ON CONFLICT ("id") DO UPDATE SET
"ownerId" = "excluded"."ownerId",
"id" = "excluded"."id",
"name" = "excluded"."name"
-- params: [ 'Tilly', 'Tess' ]
COMMIT;
`
Note how this not only updates Josh's pets, but re-associates pet 1 and 2, which were associated with Theresa, with Josh instead.

In many situations, hard-deleting rows that are being disassociated is probably not the desired behavior. This is an instance where using .onRemove to override the default removal behavior can be very helpful:
`ts
const UsersWithPets = Users.extend(user => ({
...user,
pets: Pets
.withOwnerId(user.id)
.onRemove(pets => pets.set(() => ({ ownerId: null })))
}))

await UsersWithPets.withId(1).set(() => ({
firstName: 'Theresa',
lastName: 'Miles',
pets: [
{ name: 'Sandy' },
{ name: 'Annie' }
]
}));
`
`sql
BEGIN

UPDATE "User"
SET "firstName" = $1,
"lastName" = $2
WHERE "User"."id" = 1;
-- params: [ 'Theresa', 'Miles' ]

UPDATE "Pet"
SET "ownerId" = NULL
WHERE "Pet"."ownerId" = 1;

INSERT INTO "Pet" ("ownerId", "name")
VALUES
(1, $1),
(1, $2);
-- params: [ 'Sandy', 'Annie' ]
``