minato - npm Package Compare versions

Comparing version 3.3.0 to 3.4.0

lib/index.d.ts

@@ -5,3 +5,3 @@ import { Extract, Dict, Awaitable, MaybeArray, Intersect } from 'cosmokit';
 export namespace Query {
-    interface FieldExpr<T = any> {
+    export interface FieldExpr<T = any> {
         $or?: Field<T>[];
@@ -21,4 +21,7 @@ $and?: Field<T>[];
         $size?: Extract<T, any[], number>;
-        $regex?: Extract<T, string, string | RegExp>;
-        $regexFor?: Extract<T, string>;
+        $regex?: Extract<T, string, string | RegExpLike>;
+        $regexFor?: Extract<T, string, string | {
+            input: string;
+            flags?: string;
+        }>;
         $bitsAllClear?: Extract<T, number>;
@@ -28,4 +31,7 @@ $bitsAllSet?: Extract<T, number>;
         $bitsAnySet?: Extract<T, number>;
+        $some?: T extends (infer U)[] ? Query<U> : never;
+        $none?: T extends (infer U)[] ? Query<U> : never;
+        $every?: T extends (infer U)[] ? Query<U> : never;
     }
-    interface LogicalExpr<T = any> {
+    export interface LogicalExpr<T = any> {
         $or?: Expr<T>[];
@@ -35,8 +41,9 @@ $and?: Expr<T>[];
         /** @deprecated use query callback instead */
-        $expr?: Eval.Expr<boolean>;
+        $expr?: Eval.Term<boolean>;
     }
-    type Shorthand<T = any> = Extract<T, Comparable> | Extract<T, Indexable, T[]> | Extract<T, string, RegExp>;
-    type Field<T = any> = FieldExpr<T> | Shorthand<T>;
-    type Expr<T = any> = LogicalExpr<T> & {
-        [K in keyof T]?: null | Field<T[K]>;
+    export type Shorthand<T = any> = Extract<T, Comparable> | Extract<T, Indexable, T[]> | Extract<T, string, RegExp>;
+    export type Field<T = any> = FieldExpr<T> | Shorthand<T>;
+    type NonNullExpr<T> = T extends Values<AtomicTypes> | any[] ? Field<T> : T extends object ? Expr<Flatten<T>> | Selection.Callback<T, boolean> : Field<T>;
+    export type Expr<T = any> = LogicalExpr<T> & {
+        [K in keyof T]?: (undefined extends T[K] ? null : never) | NonNullExpr<Exclude<T[K], undefined>>;
     };
@@ -115,5 +122,9 @@ }
     project<U extends Dict<FieldLike<S>>>(fields: U): Selection<FieldMap<S, U>>;
+    join<K extends string, U>(name: K, selection: Selection<U>, callback?: (self: Row<S>, other: Row<U>) => Eval.Expr<boolean>, optional?: boolean): Selection<S & {
+        [P in K]: U;
+    }>;
     _action(type: Executable.Action, ...args: any[]): Executable<any, any>;
     evaluate<T>(callback: Selection.Callback<S, T, true>): Eval.Expr<T, true>;
-    evaluate<K extends Keys<S>>(field: K): Eval.Expr<S[K][], boolean>;
+    evaluate<K extends Keys<S>>(field: K): Eval.Expr<S[K][], false>;
+    evaluate<K extends Keys<S>>(field: K[]): Eval.Expr<any[][], false>;
     evaluate(): Eval.Expr<S[], boolean>;
@@ -188,6 +199,45 @@ execute(): Promise<S[]>;
 }
-export const Primary: unique symbol;
+declare const Primary: unique symbol;
 export type Primary = (string | number) & {
     [Primary]: true;
 };
+export namespace Relation {
+    const Marker: unique symbol;
+    export type Marker = {
+        [Marker]: true;
+    };
+    export const Type: readonly ["oneToOne", "oneToMany", "manyToOne", "manyToMany"];
+    export type Type = typeof Type[number];
+    export interface Config<S extends any = any, T extends Keys<S> = Keys<S>, K extends string = string> {
+        type: Type;
+        table: T;
+        references: Keys<S[T]>[];
+        fields: K[];
+        required: boolean;
+    }
+    export interface Definition<K extends string = string> {
+        type: 'oneToOne' | 'manyToOne' | 'manyToMany';
+        table?: string;
+        target?: string;
+        references?: MaybeArray<string>;
+        fields?: MaybeArray<K>;
+    }
+    export type Include<T, S> = boolean | {
+        [P in keyof T]?: T[P] extends MaybeArray<infer U extends S> | undefined ? Include<U, S> : never;
+    };
+    export type SetExpr<S extends object = any> = Row.Computed<S, Update<S>> | {
+        where: Query.Expr<Flatten<S>> | Selection.Callback<S, boolean>;
+        update: Row.Computed<S, Update<S>>;
+    };
+    export interface Modifier<S extends object = any> {
+        $create?: MaybeArray<DeepPartial<S>>;
+        $set?: MaybeArray<SetExpr<S>>;
+        $remove?: Query.Expr<Flatten<S>> | Selection.Callback<S, boolean>;
+        $connect?: Query.Expr<Flatten<S>> | Selection.Callback<S, boolean>;
+        $disconnect?: Query.Expr<Flatten<S>> | Selection.Callback<S, boolean>;
+    }
+    export function buildAssociationTable(...tables: [string, string]): string;
+    export function buildAssociationKey(key: string, table: string): string;
+    export function parse(def: Definition, key: string, model: Model, relmodel: Model): [Config, Config];
+}
 export interface Field<T = any> {
@@ -204,2 +254,3 @@ type: Type<T>;
     deprecated?: boolean;
+    relation?: Relation.Config;
     transformers?: Driver.Transformer[];
@@ -213,3 +264,3 @@ }
     export const object: Type[];
-    export type Type<T = any> = T extends Primary ? 'primary' : T extends number ? 'integer' | 'unsigned' | 'float' | 'double' | 'decimal' : T extends string ? 'char' | 'string' | 'text' : T extends boolean ? 'boolean' : T extends Date ? 'timestamp' | 'date' | 'time' : T extends ArrayBuffer ? 'binary' : T extends bigint ? 'bigint' : T extends unknown[] ? 'list' | 'json' : T extends object ? 'json' : 'expr';
+    export type Type<T = any> = T extends Primary ? 'primary' : T extends number ? 'integer' | 'unsigned' | 'float' | 'double' | 'decimal' : T extends string ? 'char' | 'string' | 'text' : T extends boolean ? 'boolean' : T extends Date ? 'timestamp' | 'date' | 'time' : T extends ArrayBuffer ? 'binary' : T extends bigint ? 'bigint' : T extends unknown[] ? 'list' | 'json' | 'oneToMany' | 'manyToMany' : T extends object ? 'json' | 'oneToOne' | 'manyToOne' : 'expr';
     type Shorthand<S extends string> = S | `${S}(${any})`;
@@ -238,3 +289,3 @@ export type Object<T = any, N = any> = {
     type MapField<O = any, N = any> = {
-        [K in keyof O]?: Literal<O[K], N> | Definition<O[K], N> | Transform<O[K], any, N>;
+        [K in keyof O]?: Literal<O[K], N> | Definition<O[K], N> | Transform<O[K], any, N> | (O[K] extends object ? Relation.Definition<FlatKeys<O>> : never);
     };
@@ -251,2 +302,3 @@ export type Extension<O = any, N = any> = MapField<Flatten<O>, N>;
     export function getInitial(type: Field.Type, initial?: any): any;
+    export function available(field?: Field): boolean;
 }
@@ -281,2 +333,3 @@ export namespace Model {
     create(data?: {}): any;
+    avaiableFields(): import("cosmokit").Dict<Field<S[K]> | undefined, string>;
     getType(): Type<S>;
@@ -292,2 +345,3 @@ getType(key: string): Type | undefined;
     array?: boolean;
+    ignoreNull?: boolean;
 }
@@ -306,3 +360,3 @@ export namespace Type {
     export function fromField<T, N>(field: any): Type<T, N>;
-    export function fromTerm<T>(value: Eval.Term<T>): Type<T>;
+    export function fromTerm<T>(value: Eval.Term<T>, initial?: Type): Type<T>;
     export function fromTerms(values: Eval.Term<any>[], initial?: Type): Type;
@@ -312,7 +366,12 @@ export function isType(value: any): value is Type;
     export function getInner(type?: Type, key?: string): Type | undefined;
+    export function transform(value: any, type: Type, callback: (value: any, type?: Type) => any): any;
 }
 export function isEvalExpr(value: any): value is Eval.Expr;
+export function isAggrExpr(expr: Eval.Expr): boolean;
 export function hasSubquery(value: any): boolean;
-export type Uneval<U, A extends boolean> = U extends number ? Eval.Term<number, A> : U extends string ? Eval.Term<string, A> : U extends boolean ? Eval.Term<boolean, A> : U extends Date ? Eval.Term<Date, A> : U extends RegExp ? Eval.Term<RegExp, A> : any;
-export type Eval<U> = U extends Comparable ? U : U extends Eval.Expr<infer T> ? T : never;
+type UnevalObject<S> = {
+    [K in keyof S]?: (undefined extends S[K] ? null : never) | Uneval<Exclude<S[K], undefined>, boolean>;
+};
+export type Uneval<U, A extends boolean> = U extends Values<AtomicTypes> ? Eval.Term<U, A> : U extends (infer T extends object)[] ? Relation.Modifier<T> | Eval.Array<T, A> : U extends object ? Eval.Expr<U, A> | UnevalObject<Flatten<U>> : any;
+export type Eval<U> = U extends Values<AtomicTypes> ? U : U extends Eval.Expr<infer T> ? T : never;
 declare const kExpr: unique symbol;
@@ -344,2 +403,5 @@ declare const kType: unique symbol;
         <A extends boolean>(key: string, value: any, type: Type): Eval.Expr<any, A>;
+        ignoreNull<T, A extends boolean>(value: Eval.Expr<T, A>): Eval.Expr<T, A>;
+        select(...args: Any[]): Expr<any[], false>;
+        query<T extends object>(row: Row<T>, query: Query.Expr<T>): Expr<boolean, false>;
         if<T extends Comparable, A extends boolean>(cond: Any<A>, vThen: Term<T, A>, vElse: Term<T, A>): Expr<T, A>;
@@ -375,5 +437,8 @@ ifNull<T extends Comparable, A extends boolean>(...args: Term<T, A>[]): Expr<T, A>;
         in<T extends Comparable, A extends boolean>(x: Term<T, A>, array: Array<T, A>): Expr<boolean, A>;
+        in<T extends Comparable, A extends boolean>(x: Term<T, A>[], array: Array<T[], A>): Expr<boolean, A>;
         nin<T extends Comparable, A extends boolean>(x: Term<T, A>, array: Array<T, A>): Expr<boolean, A>;
+        nin<T extends Comparable, A extends boolean>(x: Term<T, A>[], array: Array<T[], A>): Expr<boolean, A>;
         concat: Multi<string, string>;
-        regex<A extends boolean>(x: Term<string, A>, y: Term<string, A> | Term<RegExp, A>): Expr<boolean, A>;
+        regex<A extends boolean>(x: Term<string, A>, y: RegExp): Expr<boolean, A>;
+        regex<A extends boolean>(x: Term<string, A>, y: Term<string, A>, flags?: string): Expr<boolean, A>;
         and: Multi<boolean, boolean> & Multi<number, number> & Multi<bigint, bigint>;
@@ -391,3 +456,2 @@ or: Multi<boolean, boolean> & Multi<number, number> & Multi<bigint, bigint>;
         length(value: Any<false>): Expr<number, true>;
-        size<A extends boolean>(value: (Any | Expr<Any, A>)[] | Expr<Any[], A>): Expr<number, A>;
         length<A extends boolean>(value: any[] | Expr<any[], A>): Expr<number, A>;
@@ -401,6 +465,3 @@ object<T extends any>(row: Row.Cell<T>): Expr<T, false>;
 export { Eval as $ };
-type MapUneval<S> = {
-    [K in keyof S]?: Uneval<S[K], false>;
-};
-export type Update<T = any> = MapUneval<Flatten<T>>;
+export type Update<T = any> = UnevalObject<Flatten<T>>;
 export function executeEval(data: any, expr: any): any;
@@ -416,2 +477,5 @@ export function executeUpdate(data: any, update: any, ref: string): any;
 };
+export type DeepPartial<T> = T extends Values<AtomicTypes> ? T : T extends (infer U)[] ? DeepPartial<U>[] : T extends object ? {
+    [K in keyof T]?: DeepPartial<T[K]>;
+} : T;
 export interface AtomicTypes {
@@ -432,3 +496,3 @@ Number: number;
 type FlatWrap<S, A extends 0[], P extends string> = {
-    [K in P]?: S;
+    [K in P]: S;
 } | (S extends Values<AtomicTypes> ? never : S extends any[] ? never : string extends keyof S ? never : A extends [0, ...infer R extends 0[]] ? FlatMap<S, R, `${P}.`> : never);
@@ -448,5 +512,13 @@ type FlatMap<S, T extends 0[], P extends string = ''> = Values<{
 export function isComparable(value: any): value is Comparable;
+export function isFlat(value: any): value is Values<AtomicTypes>;
 export function randomId(): string;
-export function makeRegExp(source: string | RegExp): RegExp;
+export interface RegExpLike {
+    source: string;
+    flags?: string;
+}
+export function makeRegExp(source: string | RegExpLike, flags?: string): RegExp;
 export function unravel(source: object, init?: (value: any) => any): {};
+export function flatten(source: object, prefix?: string, ignore?: (value: any) => boolean): {};
+export function getCell(row: any, key: any): any;
+export function isEmpty(value: any): boolean;
 type TableLike<S> = Keys<S> | Selection;
@@ -496,3 +568,3 @@ type TableType<S, T extends TableLike<S>> = T extends Keys<S> ? S[T] : T extends Selection<infer U> ? U : never;
     select<T>(table: Selection<T>, query?: Query<T>): Selection<T>;
-    select<K extends Keys<S>>(table: K, query?: Query<S[K]>): Selection<S[K]>;
+    select<K extends Keys<S>>(table: K, query?: Query<S[K]>, cursor?: Relation.Include<S[K], Values<S>> | null): Selection<S[K]>;
     join<const X extends Join1.Input<S>>(tables: X, callback?: Join1.Predicate<S, X>, optional?: boolean[]): Selection<Join1.Output<S, X>>;
@@ -505,7 +577,7 @@ join<X extends Join2.Input<S>>(tables: X, callback?: Join2.Predicate<S, X>, optional?: Dict<boolean, Keys<X>>): Selection<Join2.Output<S, X>>;
     remove<K extends Keys<S>>(table: K, query: Query<S[K]>): Promise<Driver.WriteResult>;
-    create<K extends Keys<S>>(table: K, data: Partial<S[K]>): Promise<S[K]>;
+    create<K extends Keys<S>>(table: K, data: DeepPartial<S[K]>): Promise<S[K]>;
     upsert<K extends Keys<S>>(table: K, upsert: Row.Computed<S[K], Update<S[K]>[]>, keys?: MaybeArray<FlatKeys<S[K], Indexable>>): Promise<Driver.WriteResult>;
     makeProxy(marker: any, getDriver?: (driver: Driver<any, C>, database: this) => Driver<any, C>): this;
-    withTransaction(callback: (database: this) => Promise<void>): Promise<void>;
-    transact(callback: (database: this) => Promise<void>): Promise<void>;
+    withTransaction(callback: (database: this) => Promise<void>): Promise<any>;
+    transact<T>(callback: (database: this) => Promise<T>): Promise<any>;
     stopAll(): Promise<void>;
@@ -515,2 +587,5 @@ drop<K extends Keys<S>>(table: K): Promise<void>;
     stats(): Promise<Driver.Stats>;
+    private ensureTransaction;
+    private transformRelationQuery;
+    private processRelationUpdate;
 }
@@ -517,0 +592,0 @@ export namespace RuntimeError {

package.json

 {
   "name": "minato",
-  "version": "3.3.0",
+  "version": "3.4.0",
   "description": "Type Driven Database Framework",
@@ -65,3 +65,3 @@ "type": "module",
   "peerDependencies": {
-    "cordis": "^3.15.0"
+    "cordis": "^3.16.1"
   },
@@ -68,0 +68,0 @@ "dependencies": {

src/database.ts

@@ -1,6 +0,6 @@
-import { defineProperty, Dict, makeArray, mapValues, MaybeArray, omit } from 'cosmokit'
+import { defineProperty, Dict, filterKeys, makeArray, mapValues, MaybeArray, noop, omit } from 'cosmokit'
 import { Context, Service, Spread } from 'cordis'
-import { FlatKeys, FlatPick, Indexable, Keys, randomId, Row, unravel } from './utils.ts'
+import { DeepPartial, FlatKeys, FlatPick, getCell, Indexable, Keys, randomId, Row, unravel, Values } from './utils.ts'
 import { Selection } from './selection.ts'
-import { Field, Model } from './model.ts'
+import { Field, Model, Relation } from './model.ts'
 import { Driver } from './driver.ts'
@@ -114,2 +114,43 @@ import { Eval, Update } from './eval.ts'
     }
+    Object.entries(fields).forEach(([key, def]: [string, Relation.Definition]) => {
+      if (!Relation.Type.includes(def.type)) return
+      const [relation, inverse] = Relation.parse(def, key, model, this.tables[def.table ?? key])
+      if (!this.tables[relation.table]) throw new Error(`relation table ${relation.table} does not exist`)
+      ;(model.fields[key] = Field.parse('expr')).relation = relation
+      if (def.target) {
+        (this.tables[relation.table].fields[def.target] ??= Field.parse('expr')).relation = inverse
+      }
+
+      if (relation.type === 'oneToOne' || relation.type === 'manyToOne') {
+        relation.fields.forEach((x, i) => {
+          model.fields[x] ??= { ...this.tables[relation.table].fields[relation.references[i]] } as any
+          if (!relation.required) {
+            model.fields[x]!.nullable = true
+            model.fields[x]!.initial = null
+          }
+        })
+      } else if (relation.type === 'manyToMany') {
+        const assocTable = Relation.buildAssociationTable(relation.table, name)
+        if (this.tables[assocTable]) return
+        const fields = relation.fields.map(x => [Relation.buildAssociationKey(x, name), model.fields[x]?.deftype] as const)
+        const references = relation.references.map((x, i) => [Relation.buildAssociationKey(x, relation.table), fields[i][1]] as const)
+        this.extend(assocTable as any, {
+          ...Object.fromEntries([...fields, ...references]),
+          [name]: {
+            type: 'manyToOne',
+            table: name,
+            fields: fields.map(x => x[0]),
+            references: relation.references,
+          },
+          [relation.table]: {
+            type: 'manyToOne',
+            table: relation.table,
+            fields: references.map(x => x[0]),
+            references: relation.fields,
+          },
+        } as any, {
+          primary: [...fields.map(x => x[0]), ...references.map(x => x[0])],
+        })
+      }
+    })
     this.prepareTasks[name] = this.prepare(name)
@@ -226,5 +267,92 @@ ;(this.ctx as Context).emit('model', name)
   select<T>(table: Selection<T>, query?: Query<T>): Selection<T>
-  select<K extends Keys<S>>(table: K, query?: Query<S[K]>): Selection<S[K]>
-  select(table: any, query?: any) {
-    return new Selection(this.getDriver(table), table, query)
+  select<K extends Keys<S>>(
+    table: K,
+    query?: Query<S[K]>,
+    cursor?: Relation.Include<S[K], Values<S>> | null,
+  ): Selection<S[K]>
+
+  select(table: any, query?: any, cursor?: any) {
+    let sel = new Selection(this.getDriver(table), table, query)
+    if (typeof table !== 'string') return sel
+    const whereOnly = cursor === null
+    const rawquery = typeof query === 'function' ? query : () => query
+    const modelFields = this.tables[table].fields
+    if (cursor) cursor = filterKeys(cursor, (key) => !!modelFields[key]?.relation)
+    for (const key in { ...sel.query, ...sel.query.$not }) {
+      if (modelFields[key]?.relation) {
+        if (sel.query[key] === null && !modelFields[key].relation.required) {
+          sel.query[key] = Object.fromEntries(modelFields[key]!.relation!.references.map(k => [k, null]))
+        }
+        if (sel.query[key] && typeof sel.query[key] !== 'function' && typeof sel.query[key] === 'object'
+          && Object.keys(sel.query[key]).every(x => modelFields[key]!.relation!.fields.includes(`${key}.${x}`))) {
+          Object.entries(sel.query[key]).forEach(([k, v]) => sel.query[`${key}.${k}`] = v)
+          delete sel.query[key]
+        }
+        if (sel.query.$not?.[key] === null && !modelFields[key].relation.required) {
+          sel.query.$not[key] = Object.fromEntries(modelFields[key]!.relation!.references.map(k => [k, null]))
+        }
+        if (sel.query.$not?.[key] && typeof sel.query.$not[key] !== 'function' && typeof sel.query.$not[key] === 'object'
+          && Object.keys(sel.query.$not[key]).every(x => modelFields[key]!.relation!.fields.includes(`${key}.${x}`))) {
+          Object.entries(sel.query.$not[key]).forEach(([k, v]) => sel.query.$not![`${key}.${k}`] = v)
+          delete sel.query.$not[key]
+        }
+        if (!cursor || !Object.getOwnPropertyNames(cursor).includes(key)) {
+          (cursor ??= {})[key] = true
+        }
+      }
+    }
+
+    sel.query = omit(sel.query, Object.keys(cursor ?? {}))
+    if (Object.keys(sel.query.$not ?? {}).length) {
+      sel.query.$not = omit(sel.query.$not!, Object.keys(cursor ?? {}))
+      if (Object.keys(sel.query.$not).length === 0) Reflect.deleteProperty(sel.query, '$not')
+    }
+
+    if (cursor && typeof cursor === 'object') {
+      if (typeof table !== 'string') throw new Error('cannot include relations on derived selection')
+      const extraFields: string[] = []
+      const applyQuery = (sel: Selection, key: string) => {
+        const query2 = rawquery(sel.row)
+        const relquery = query2[key] !== undefined ? query2[key]
+          : query2.$not?.[key] !== undefined ? { $not: query2.$not?.[key] }
+            : undefined
+        return relquery === undefined ? sel : sel.where(this.transformRelationQuery(table, sel.row, key, relquery))
+      }
+      for (const key in cursor) {
+        if (!cursor[key] || !modelFields[key]?.relation) continue
+        const relation: Relation.Config<S> = modelFields[key]!.relation as any
+        if (relation.type === 'oneToOne' || relation.type === 'manyToOne') {
+          sel = whereOnly ? sel : sel.join(key, this.select(relation.table, {}, cursor[key]), (self, other) => Eval.and(
+            ...relation.fields.map((k, i) => Eval.eq(self[k], other[relation.references[i]])),
+          ), true)
+          sel = applyQuery(sel, key)
+        } else if (relation.type === 'oneToMany') {
+          sel = whereOnly ? sel : sel.join(key, this.select(relation.table, {}, cursor[key]), (self, other) => Eval.and(
+            ...relation.fields.map((k, i) => Eval.eq(self[k], other[relation.references[i]])),
+          ), true)
+          sel = applyQuery(sel, key)
+          sel = whereOnly ? sel : sel.groupBy([
+            ...Object.entries(modelFields).filter(([, field]) => Field.available(field)).map(([k]) => k),
+            ...extraFields,
+          ], {
+            [key]: row => Eval.ignoreNull(Eval.array(row[key])),
+          })
+        } else if (relation.type === 'manyToMany') {
+          const assocTable: any = Relation.buildAssociationTable(relation.table, table)
+          const references = relation.fields.map(x => Relation.buildAssociationKey(x, table))
+          sel = whereOnly ? sel : sel.join(key, this.select(assocTable, {}, { [relation.table]: cursor[key] } as any), (self, other) => Eval.and(
+            ...relation.fields.map((k, i) => Eval.eq(self[k], other[references[i]])),
+          ), true)
+          sel = applyQuery(sel, key)
+          sel = whereOnly ? sel : sel.groupBy([
+            ...Object.entries(modelFields).filter(([, field]) => Field.available(field)).map(([k]) => k),
+            ...extraFields,
+          ], {
+            [key]: row => Eval.ignoreNull(Eval.array(row[key][relation.table as any])),
+          })
+        }
+        extraFields.push(key)
+      }
+    }
+    return sel
   }
@@ -253,3 +381,3 @@
     if (Object.keys(sels).length === 0) throw new Error('no tables to join')
-    const drivers = new Set(Object.values(sels).map(sel => sel.driver))
+    const drivers = new Set(Object.values(sels).map(sel => sel.driver[Database.transact] ?? sel.driver))
     if (drivers.size !== 1) throw new Error('cannot join tables from different drivers')
@@ -280,3 +408,4 @@ if (Object.keys(sels).length === 2 && (optional?.[0] || optional?.[Object.keys(sels)[0]])) {
   async get<K extends Keys<S>>(table: K, query: Query<S[K]>, cursor?: any) {
-    return this.select(table, query).execute(cursor) as any
+    const fields = Array.isArray(cursor) ? cursor : cursor?.fields
+    return this.select(table, query, fields && Object.fromEntries(fields.map(x => [x, true])) as any).execute(cursor) as any
   }
@@ -293,3 +422,4 @@
   ): Promise<Driver.WriteResult> {
-    const sel = this.select(table, query)
+    const rawupdate = typeof update === 'function' ? update : () => update
+    const sel = this.select(table, query, null)
     if (typeof update === 'function') update = update(sel.row)
@@ -300,21 +430,102 @@ const primary = makeArray(sel.model.primary)
     }
+
+    const relations: [string, Relation.Config<S>][] = Object.entries(sel.model.fields)
+      .filter(([key, field]) => key in update && field!.relation)
+      .map(([key, field]) => [key, field!.relation!] as const) as any
+    if (relations.length) {
+      return await this.ensureTransaction(async (database) => {
+        const rows = await database.get(table, query)
+        let baseUpdate = omit(update, relations.map(([key]) => key) as any)
+        baseUpdate = sel.model.format(baseUpdate)
+        for (const [key, relation] of relations) {
+          if (relation.type === 'oneToOne') {
+            if (update[key] === null) {
+              await Promise.all(rows.map(row => database.remove(relation.table,
+                Object.fromEntries(relation.references.map((k, i) => [k, getCell(row, relation.fields[i])])) as any,
+              )))
+            } else {
+              await database.upsert(relation.table, rows.map(row => ({
+                ...Object.fromEntries(relation.references.map((k, i) => [k, getCell(row, relation.fields[i])])),
+                ...rawupdate(row as any)[key],
+              })), relation.references as any)
+            }
+          } else if (relation.type === 'manyToOne') {
+            await database.upsert(relation.table, rows.map(row => ({
+              ...Object.fromEntries(relation.references.map((k, i) => [k, getCell(row, relation.fields[i])])),
+              ...rawupdate(row as any)[key],
+            })), relation.references as any)
+          } else if (relation.type === 'oneToMany' || relation.type === 'manyToMany') {
+            await Promise.all(rows.map(row => this.processRelationUpdate(table, row, key, rawupdate(row as any)[key])))
+          }
+        }
+        return Object.keys(baseUpdate).length === 0 ? {} : await sel._action('set', baseUpdate).execute()
+      })
+    }
+
     update = sel.model.format(update)
     if (Object.keys(update).length === 0) return {}
-    return await sel._action('set', update).execute()
+    return sel._action('set', update).execute()
   }
 
   async remove<K extends Keys<S>>(table: K, query: Query<S[K]>): Promise<Driver.WriteResult> {
-    const sel = this.select(table, query)
-    return await sel._action('remove').execute()
+    const sel = this.select(table, query, null)
+    return sel._action('remove').execute()
   }
 
-  async create<K extends Keys<S>>(table: K, data: Partial<S[K]>): Promise<S[K]> {
+  async create<K extends Keys<S>>(table: K, data: DeepPartial<S[K]>): Promise<S[K]>
+  async create<K extends Keys<S>>(table: K, data: any): Promise<S[K]> {
     const sel = this.select(table)
-    const { primary, autoInc } = sel.model
+    const { primary, autoInc, fields } = sel.model
     if (!autoInc) {
       const keys = makeArray(primary)
-      if (keys.some(key => !(key in data))) {
+      if (keys.some(key => getCell(data, key) === undefined)) {
         throw new Error('missing primary key')
       }
     }
+
+    const tasks: any[] = []
+    for (const key in data) {
+      if (data[key] && this.tables[table].fields[key]?.relation) {
+        const relation = this.tables[table].fields[key].relation
+        if (relation.type === 'oneToOne' && relation.required) {
+          const mergedData = { ...data[key] }
+          for (const k in relation.fields) {
+            mergedData[relation.references[k]] = getCell(data, relation.fields[k])
+          }
+          tasks.push([relation.table, [mergedData], relation.references])
+        } else if (relation.type === 'oneToMany' && Array.isArray(data[key])) {
+          const mergedData = data[key].map(row => {
+            const mergedData = { ...row }
+            for (const k in relation.fields) {
+              mergedData[relation.references[k]] = getCell(data, relation.fields[k])
+            }
+            return mergedData
+          })
+
+          tasks.push([relation.table, mergedData])
+        } else {
+          // handle shadowed fields
+          data = {
+            ...omit(data, [key]),
+            ...Object.fromEntries(Object.entries(data[key]).map(([k, v]) => {
+              if (!fields[`${key}.${k}`]) {
+                throw new Error(`field ${key}.${k} does not exist`)
+              }
+              return [`${key}.${k}`, v]
+            })),
+          }
+          continue
+        }
+        data = omit(data, [key]) as any
+      }
+    }
+
+    if (tasks.length) {
+      return this.ensureTransaction(async (database) => {
+        for (const [table, data, keys] of tasks) {
+          await database.upsert(table, data, keys)
+        }
+        return database.create(table, data)
+      })
+    }
     return sel._action('create', sel.model.create(data)).execute()
@@ -330,5 +541,63 @@ }
     if (typeof upsert === 'function') upsert = upsert(sel.row)
+    else {
+      const buildKey = (relation: Relation.Config) => [relation.table, ...relation.references].join('__')
+      const tasks: Dict<{
+        table: string
+        upsert: any[]
+        keys?: string[]
+      }> = {}
+
+      const upsert2 = (upsert as any[]).map(data => {
+        for (const key in data) {
+          if (data[key] && this.tables[table].fields[key]?.relation) {
+            const relation = this.tables[table].fields[key].relation
+            if (relation.type === 'oneToOne' && relation.required) {
+              const mergedData = { ...data[key] }
+              for (const k in relation.fields) {
+                mergedData[relation.references[k]] = data[relation.fields[k]]
+              }
+              ;(tasks[buildKey(relation)] ??= {
+                table: relation.table,
+                upsert: [],
+                keys: relation.references,
+              }).upsert.push(mergedData)
+            } else if (relation.type === 'oneToMany' && Array.isArray(data[key])) {
+              const mergedData = data[key].map(row => {
+                const mergedData = { ...row }
+                for (const k in relation.fields) {
+                  mergedData[relation.references[k]] = data[relation.fields[k]]
+                }
+                return mergedData
+              })
+
+            ;(tasks[relation.table] ??= { table: relation.table, upsert: [] }).upsert.push(...mergedData)
+            } else {
+              // handle shadowed fields
+              data = {
+                ...omit(data, [key]),
+                ...Object.fromEntries(Object.entries(data[key]).map(([k, v]) => {
+                  if (!sel.model.fields[`${key}.${k}`]) throw new Error(`field ${key}.${k} does not exist`)
+                  return [`${key}.${k}`, v]
+                })),
+              }
+              continue
+            }
+            data = omit(data, [key]) as any
+          }
+        }
+        return data
+      })
+
+      if (Object.keys(tasks).length) {
+        return this.ensureTransaction(async (database) => {
+          for (const { table, upsert, keys } of Object.values(tasks)) {
+            await database.upsert(table as any, upsert, keys as any)
+          }
+          return database.upsert(table, upsert2)
+        })
+      }
+    }
     upsert = upsert.map(item => sel.model.format(item))
     keys = makeArray(keys || sel.model.primary) as any
-    return await sel._action('upsert', upsert, keys).execute()
+    return sel._action('upsert', upsert, keys).execute()
   }
@@ -365,7 +634,7 @@
 
-  async transact(callback: (database: this) => Promise<void>) {
+  async transact<T>(callback: (database: this) => Promise<T>) {
     if (this[Database.transact]) throw new Error('nested transactions are not supported')
     const finalTasks: Promise<void>[] = []
     const database = this.makeProxy(Database.transact, (driver) => {
-      let session: any
+      let initialized = false, session: any
       let _resolve: (value: any) => void
@@ -375,2 +644,3 @@ const sessionTask = new Promise((resolve) => _resolve = resolve)
         get: (target, p, receiver) => {
+          if (p === Database.transact) return target
           if (p === 'database') return database
@@ -383,12 +653,12 @@ if (p === 'session') return session
       finalTasks.push(driver.withTransaction((_session) => {
+        if (initialized) initialTask = initialTaskFactory()
+        initialized = true
         _resolve(session = _session)
-        return initialTask
+        return initialTask as any
       }))
       return driver
     })
-    const initialTask = (async () => {
-      await Promise.resolve()
-      await callback(database)
-    })()
-    await initialTask.finally(() => Promise.all(finalTasks))
+    const initialTaskFactory = () => Promise.resolve().then(() => callback(database))
+    let initialTask = initialTaskFactory()
+    return initialTask.catch(noop).finally(() => Promise.all(finalTasks))
   }
@@ -420,2 +690,158 @@
   }
+
+  private ensureTransaction<T>(callback: (database: this) => Promise<T>) {
+    if (this[Database.transact]) {
+      return callback(this)
+    } else {
+      return this.transact(callback)
+    }
+  }
+
+  private transformRelationQuery(table: any, row: any, key: any, query: Query.FieldExpr) {
+    const relation: Relation.Config<S> = this.tables[table].fields[key]!.relation! as any
+    const results: Eval.Expr<boolean>[] = []
+    if (relation.type === 'oneToOne' || relation.type === 'manyToOne') {
+      if (query === null) {
+        results.push(Eval.nin(
+          relation.fields.map(x => row[x]),
+          this.select(relation.table).evaluate(relation.references),
+        ))
+      } else {
+        results.push(Eval.in(
+          relation.fields.map(x => row[x]),
+          this.select(relation.table, query as any).evaluate(relation.references),
+        ))
+      }
+    } else if (relation.type === 'oneToMany') {
+      if (query.$some) {
+        results.push(Eval.in(
+          relation.fields.map(x => row[x]),
+          this.select(relation.table, query.$some).evaluate(relation.references),
+        ))
+      }
+      if (query.$none) {
+        results.push(Eval.nin(
+          relation.fields.map(x => row[x]),
+          this.select(relation.table, query.$none).evaluate(relation.references),
+        ))
+      }
+      if (query.$every) {
+        results.push(Eval.nin(
+          relation.fields.map(x => row[x]),
+          this.select(relation.table, Eval.not(query.$every as any) as any).evaluate(relation.references),
+        ))
+      }
+    } else if (relation.type === 'manyToMany') {
+      const assocTable: any = Relation.buildAssociationTable(table, relation.table)
+      const fields: any[] = relation.fields.map(x => Relation.buildAssociationKey(x, table))
+      const references = relation.references.map(x => Relation.buildAssociationKey(x, relation.table))
+      if (query.$some) {
+        const innerTable = this.select(relation.table, query.$some).evaluate(relation.references)
+        const relTable = this.select(assocTable, r => Eval.in(references.map(x => r[x]), innerTable)).evaluate(fields)
+        results.push(Eval.in(relation.fields.map(x => row[x]), relTable))
+      }
+      if (query.$none) {
+        const innerTable = this.select(relation.table, query.$none).evaluate(relation.references)
+        const relTable = this.select(assocTable, r => Eval.in(references.map(x => r[x]), innerTable)).evaluate(fields)
+        results.push(Eval.nin(relation.fields.map(x => row[x]), relTable))
+      }
+      if (query.$every) {
+        const innerTable = this.select(relation.table, Eval.not(query.$every as any) as any).evaluate(relation.references)
+        const relTable = this.select(assocTable, r => Eval.in(references.map(x => r[x]), innerTable)).evaluate(fields)
+        results.push(Eval.nin(relation.fields.map(x => row[x]), relTable))
+      }
+    }
+    return { $expr: Eval.and(...results) } as any
+  }
+
+  private async processRelationUpdate(table: any, row: any, key: any, modifier: Relation.Modifier) {
+    const relation: Relation.Config<S> = this.tables[table].fields[key]!.relation! as any
+    if (Array.isArray(modifier)) {
+      if (relation.type === 'oneToMany') {
+        modifier = { $remove: {}, $create: modifier }
+      } else if (relation.type === 'manyToMany') {
+        throw new Error('override for manyToMany relation is not supported')
+      }
+    }
+    if (modifier.$remove) {
+      if (relation.type === 'oneToMany') {
+        await this.remove(relation.table, (r: any) => Eval.query(r, {
+          ...Object.fromEntries(relation.references.map((k, i) => [k, row[relation.fields[i]]])),
+          ...(typeof modifier.$remove === 'function' ? { $expr: modifier.$remove(r) } : modifier.$remove),
+        }) as any)
+      } else if (relation.type === 'manyToMany') {
+        throw new Error('remove for manyToMany relation is not supported')
+      }
+    }
+    if (modifier.$set) {
+      if (relation.type === 'oneToMany') {
+        for (const setexpr of makeArray(modifier.$set) as any[]) {
+          const [query, update] = setexpr.update ? [setexpr.where, setexpr.update] : [{}, setexpr]
+          await this.set(relation.table,
+            (r: any) => Eval.query(r, {
+              ...Object.fromEntries(relation.references.map((k, i) => [k, row[relation.fields[i]]])),
+              ...(typeof query === 'function' ? { $expr: query } : query),
+            }) as any,
+            update,
+          )
+        }
+      } else if (relation.type === 'manyToMany') {
+        throw new Error('set for manyToMany relation is not supported')
+      }
+    }
+    if (modifier.$create) {
+      if (relation.type === 'oneToMany') {
+        const upsert = makeArray(modifier.$create).map((r: any) => {
+          const data = { ...r }
+          for (const k in relation.fields) {
+            data[relation.references[k]] = row[relation.fields[k]]
+          }
+          return data
+        })
+        await this.upsert(relation.table, upsert)
+      } else if (relation.type === 'manyToMany') {
+        throw new Error('create for manyToMany relation is not supported')
+      }
+    }
+    if (modifier.$disconnect) {
+      if (relation.type === 'oneToMany') {
+        await this.set(relation.table,
+          (r: any) => Eval.query(r, {
+            ...Object.fromEntries(relation.references.map((k, i) => [k, row[relation.fields[i]]])),
+            ...(typeof modifier.$disconnect === 'function' ? { $expr: modifier.$disconnect } : modifier.$disconnect),
+          } as any),
+          Object.fromEntries(relation.references.map((k, i) => [k, null])) as any,
+        )
+      } else if (relation.type === 'manyToMany') {
+        const assocTable = Relation.buildAssociationTable(table, relation.table) as Keys<S>
+        const fields = relation.fields.map(x => Relation.buildAssociationKey(x, table))
+        const references = relation.references.map(x => Relation.buildAssociationKey(x, relation.table))
+        const rows = await this.select(assocTable, {
+          ...Object.fromEntries(fields.map((k, i) => [k, row[relation.fields[i]]])) as any,
+          [relation.table]: modifier.$disconnect,
+        }, null).execute()
+        await this.remove(assocTable, r => Eval.in(
+          [...fields.map(x => r[x]), ...references.map(x => r[x])],
+          rows.map(r => [...fields.map(x => r[x]), ...references.map(x => r[x])]),
+        ))
+      }
+    }
+    if (modifier.$connect) {
+      if (relation.type === 'oneToMany') {
+        await this.set(relation.table,
+          modifier.$connect,
+          Object.fromEntries(relation.references.map((k, i) => [k, row[relation.fields[i]]])) as any,
+        )
+      } else if (relation.type === 'manyToMany') {
+        const assocTable: any = Relation.buildAssociationTable(table, relation.table)
+        const fields = relation.fields.map(x => Relation.buildAssociationKey(x, table))
+        const references = relation.references.map(x => Relation.buildAssociationKey(x, relation.table))
+        const rows = await this.get(relation.table, modifier.$connect)
+        await this.upsert(assocTable, rows.map(r => ({
+          ...Object.fromEntries(fields.map((k, i) => [k, row[relation.fields[i]]])),
+          ...Object.fromEntries(references.map((k, i) => [k, r[relation.references[i] as any]])),
+        })) as any)
+      }
+    }
+  }
 }

src/driver.ts

@@ -116,3 +116,3 @@ import { Awaitable, Dict, mapValues, remove } from 'cosmokit'
       for (const field in submodel.fields) {
-        if (submodel.fields[field]!.deprecated) continue
+        if (!Field.available(submodel.fields[field])) continue
         model.fields[`${key}.${field}`] = {
@@ -119,0 +119,0 @@ expr: Eval('', [table[key].ref, field], Type.fromField(submodel.fields[field]!)),

src/eval.ts

 import { defineProperty, isNullable, mapValues } from 'cosmokit'
-import { Comparable, Flatten, isComparable, makeRegExp, Row } from './utils.ts'
+import { AtomicTypes, Comparable, Flatten, isComparable, isEmpty, makeRegExp, Row, Values } from './utils.ts'
 import { Type } from './type.ts'
-import { Field } from './model.ts'
+import { Field, Relation } from './model.ts'
+import { Query } from './query.ts'
 
@@ -10,2 +11,6 @@ export function isEvalExpr(value: any): value is Eval.Expr {
 
+export function isAggrExpr(expr: Eval.Expr): boolean {
+  return expr['$'] || expr['$select']
+}
+
 export function hasSubquery(value: any): boolean {
@@ -23,12 +28,14 @@ if (!isEvalExpr(value)) return false
 
+type UnevalObject<S> = {
+  [K in keyof S]?: (undefined extends S[K] ? null : never) | Uneval<Exclude<S[K], undefined>, boolean>
+}
+
 export type Uneval<U, A extends boolean> =
-  | U extends number ? Eval.Term<number, A>
-  : U extends string ? Eval.Term<string, A>
-  : U extends boolean ? Eval.Term<boolean, A>
-  : U extends Date ? Eval.Term<Date, A>
-  : U extends RegExp ? Eval.Term<RegExp, A>
+  | U extends Values<AtomicTypes> ? Eval.Term<U, A>
+  : U extends (infer T extends object)[] ? Relation.Modifier<T> | Eval.Array<T, A>
+  : U extends object ? Eval.Expr<U, A> | UnevalObject<Flatten<U>>
   : any
 
 export type Eval<U> =
-  | U extends Comparable ? U
+  | U extends Values<AtomicTypes> ? U
   : U extends Eval.Expr<infer T> ? T
@@ -71,2 +78,6 @@ : never
 
+    ignoreNull<T, A extends boolean>(value: Eval.Expr<T, A>): Eval.Expr<T, A>
+    select(...args: Any[]): Expr<any[], false>
+    query<T extends object>(row: Row<T>, query: Query.Expr<T>): Expr<boolean, false>
+
     // univeral
@@ -111,7 +122,10 @@ if<T extends Comparable, A extends boolean>(cond: Any<A>, vThen: Term<T, A>, vElse: Term<T, A>): Expr<T, A>
     in<T extends Comparable, A extends boolean>(x: Term<T, A>, array: Array<T, A>): Expr<boolean, A>
+    in<T extends Comparable, A extends boolean>(x: Term<T, A>[], array: Array<T[], A>): Expr<boolean, A>
     nin<T extends Comparable, A extends boolean>(x: Term<T, A>, array: Array<T, A>): Expr<boolean, A>
+    nin<T extends Comparable, A extends boolean>(x: Term<T, A>[], array: Array<T[], A>): Expr<boolean, A>
 
     // string
     concat: Multi<string, string>
-    regex<A extends boolean>(x: Term<string, A>, y: Term<string, A> | Term<RegExp, A>): Expr<boolean, A>
+    regex<A extends boolean>(x: Term<string, A>, y: RegExp): Expr<boolean, A>
+    regex<A extends boolean>(x: Term<string, A>, y: Term<string, A>, flags?: string): Expr<boolean, A>
 
@@ -135,3 +149,2 @@ // logical / bitwise
     length(value: Any<false>): Expr<number, true>
-    size<A extends boolean>(value: (Any | Expr<Any, A>)[] | Expr<Any[], A>): Expr<number, A>
     length<A extends boolean>(value: any[] | Expr<any[], A>): Expr<number, A>
@@ -185,2 +198,6 @@
 
+Eval.ignoreNull = (expr) => (expr[Type.kType]!.ignoreNull = true, expr)
+Eval.select = multary('select', (args, table) => args.map(arg => executeEval(table, arg)), Type.Array())
+Eval.query = (row, query) => ({ $expr: true, ...query }) as any
+
 // univeral
@@ -218,8 +235,18 @@ Eval.if = multary('if', ([cond, vThen, vElse], data) => executeEval(data, cond) ? executeEval(data, vThen)
 // element
-Eval.in = multary('in', ([value, array], data) => executeEval(data, array).includes(executeEval(data, value)), Type.Boolean)
-Eval.nin = multary('nin', ([value, array], data) => !executeEval(data, array).includes(executeEval(data, value)), Type.Boolean)
+Eval.in = (value, array) => Eval('in', [Array.isArray(value) ? Eval.select(...value) : value, array], Type.Boolean)
+operators.$in = ([value, array], data) => {
+  const val = executeEval(data, value), arr = executeEval(data, array)
+  if (typeof val === 'object') return arr.includes(val) || arr.map(JSON.stringify).includes(JSON.stringify(val))
+  return arr.includes(val)
+}
+Eval.nin = (value, array) => Eval('nin', [Array.isArray(value) ? Eval.select(...value) : value, array], Type.Boolean)
+operators.$nin = ([value, array], data) => {
+  const val = executeEval(data, value), arr = executeEval(data, array)
+  if (typeof val === 'object') return !arr.includes(val) && !arr.map(JSON.stringify).includes(JSON.stringify(val))
+  return !arr.includes(val)
+}
 
 // string
 Eval.concat = multary('concat', (args, data) => args.map(arg => executeEval(data, arg)).join(''), Type.String)
-Eval.regex = multary('regex', ([value, regex], data) => makeRegExp(executeEval(data, regex)).test(executeEval(data, value)), Type.Boolean)
+Eval.regex = multary('regex', ([value, regex, flags], data) => makeRegExp(executeEval(data, regex), flags).test(executeEval(data, value)), Type.Boolean)
 
@@ -296,3 +323,3 @@ // logical / bitwise
     fields = Object.fromEntries(modelFields
-      .filter(([, field]) => !field.deprecated)
+      .filter(([, field]) => Field.available(field))
       .filter(([path]) => path.startsWith(prefix))
@@ -306,4 +333,4 @@ .map(([k]) => [k.slice(prefix.length), fields[k.slice(prefix.length)]]))
 Eval.array = unary('array', (expr, table) => Array.isArray(table)
-  ? table.map(data => executeAggr(expr, data))
-  : Array.from(executeEval(table, expr)), (expr) => Type.Array(Type.fromTerm(expr)))
+  ? table.map(data => executeAggr(expr, data)).filter(x => !expr[Type.kType]?.ignoreNull || !isEmpty(x))
+  : Array.from(executeEval(table, expr)).filter(x => !expr[Type.kType]?.ignoreNull || !isEmpty(x)), (expr) => Type.Array(Type.fromTerm(expr)))
 
@@ -314,8 +341,4 @@ Eval.exec = unary('exec', (expr, data) => (expr.driver as any).executeSelection(expr, data), (expr) => Type.fromTerm(expr.args[0]))
 
-type MapUneval<S> = {
-  [K in keyof S]?: Uneval<S[K], false>
-}
+export type Update<T = any> = UnevalObject<Flatten<T>>
 
-export type Update<T = any> = MapUneval<Flatten<T>>
-
 function getRecursive(args: string | string[], data: any): any {
@@ -322,0 +345,0 @@ if (typeof args === 'string') {

src/model.ts

@@ -1,11 +0,87 @@
-import { Binary, clone, isNullable, makeArray, mapValues, MaybeArray } from 'cosmokit'
+import { clone, filterKeys, isNullable, makeArray, mapValues, MaybeArray } from 'cosmokit'
 import { Context } from 'cordis'
-import { Eval, isEvalExpr } from './eval.ts'
-import { Flatten, Keys, unravel } from './utils.ts'
+import { Eval, Update } from './eval.ts'
+import { DeepPartial, FlatKeys, Flatten, isFlat, Keys, Row, unravel } from './utils.ts'
 import { Type } from './type.ts'
 import { Driver } from './driver.ts'
+import { Query } from './query.ts'
+import { Selection } from './selection.ts'
 
-export const Primary = Symbol('Primary')
+const Primary = Symbol('minato.primary')
 export type Primary = (string | number) & { [Primary]: true }
 
+export namespace Relation {
+  const Marker = Symbol('minato.relation')
+  export type Marker = { [Marker]: true }
+
+  export const Type = ['oneToOne', 'oneToMany', 'manyToOne', 'manyToMany'] as const
+  export type Type = typeof Type[number]
+
+  export interface Config<S extends any = any, T extends Keys<S> = Keys<S>, K extends string = string> {
+    type: Type
+    table: T
+    references: Keys<S[T]>[]
+    fields: K[]
+    required: boolean
+  }
+
+  export interface Definition<K extends string = string> {
+    type: 'oneToOne' | 'manyToOne' | 'manyToMany'
+    table?: string
+    target?: string
+    references?: MaybeArray<string>
+    fields?: MaybeArray<K>
+  }
+
+  export type Include<T, S> = boolean | {
+    [P in keyof T]?: T[P] extends MaybeArray<infer U extends S> | undefined ? Include<U, S> : never
+  }
+
+  export type SetExpr<S extends object = any> = Row.Computed<S, Update<S>> | {
+    where: Query.Expr<Flatten<S>> | Selection.Callback<S, boolean>
+    update: Row.Computed<S, Update<S>>
+  }
+
+  export interface Modifier<S extends object = any> {
+    $create?: MaybeArray<DeepPartial<S>>
+    $set?: MaybeArray<SetExpr<S>>
+    $remove?: Query.Expr<Flatten<S>> | Selection.Callback<S, boolean>
+    $connect?: Query.Expr<Flatten<S>> | Selection.Callback<S, boolean>
+    $disconnect?: Query.Expr<Flatten<S>> | Selection.Callback<S, boolean>
+  }
+
+  export function buildAssociationTable(...tables: [string, string]) {
+    return '_' + tables.sort().join('To')
+  }
+
+  export function buildAssociationKey(key: string, table: string) {
+    return `${table}_${key}`
+  }
+
+  export function parse(def: Definition, key: string, model: Model, relmodel: Model): [Config, Config] {
+    const fields = def.fields ?? ((model.name === relmodel.name || def.type === 'manyToOne'
+      || (def.type === 'oneToOne' && !makeArray(relmodel.primary).every(key => !relmodel.fields[key]?.nullable)))
+      ? makeArray(relmodel.primary).map(x => `${key}.${x}`) : model.primary)
+    const relation: Config = {
+      type: def.type,
+      table: def.table ?? relmodel.name,
+      fields: makeArray(fields),
+      references: makeArray(def.references ?? relmodel.primary),
+      required: def.type !== 'manyToOne' && model.name !== relmodel.name
+        && makeArray(fields).every(key => !model.fields[key]?.nullable || makeArray(model.primary).includes(key)),
+    }
+    const inverse: Config = {
+      type: relation.type === 'oneToMany' ? 'manyToOne'
+        : relation.type === 'manyToOne' ? 'oneToMany'
+          : relation.type,
+      table: model.name,
+      fields: relation.references,
+      references: relation.fields,
+      required: relation.type !== 'oneToMany' && !relation.required
+        && relation.references.every(key => !relmodel.fields[key]?.nullable || makeArray(relmodel.primary).includes(key)),
+    }
+    return [relation, inverse]
+  }
+}
+
 export interface Field<T = any> {
@@ -22,2 +98,3 @@ type: Type<T>
   deprecated?: boolean
+  relation?: Relation.Config
   transformers?: Driver.Transformer[]
@@ -41,4 +118,4 @@ }
     : T extends bigint ? 'bigint'
-    : T extends unknown[] ? 'list' | 'json'
-    : T extends object ? 'json'
+    : T extends unknown[] ? 'list' | 'json' | 'oneToMany' | 'manyToMany'
+    : T extends object ? 'json' | 'oneToOne' | 'manyToOne'
     : 'expr'
@@ -82,3 +159,7 @@
   type MapField<O = any, N = any> = {
-    [K in keyof O]?: Literal<O[K], N> | Definition<O[K], N> | Transform<O[K], any, N>
+    [K in keyof O]?:
+      | Literal<O[K], N>
+      | Definition<O[K], N>
+      | Transform<O[K], any, N>
+      | (O[K] extends object ? Relation.Definition<FlatKeys<O>> : never)
   }
@@ -88,3 +169,3 @@
 
-  const NewType = Symbol('newtype')
+  const NewType = Symbol('minato.newtype')
   export type NewType<T> = string & { [NewType]: T }
@@ -139,2 +220,6 @@
   }
+
+  export function available(field?: Field) {
+    return !!field && !field.deprecated && !field.relation
+  }
 }
@@ -247,3 +332,3 @@
   format(source: object, strict = true, prefix = '', result = {} as S) {
-    const fields = Object.keys(this.fields)
+    const fields = Object.keys(this.fields).filter(key => !this.fields[key].relation)
     Object.entries(source).map(([key, value]) => {
@@ -259,4 +344,4 @@ key = prefix + key
         result[key] = value
-      } else if (!value || typeof value !== 'object' || isEvalExpr(value) || Object.keys(value).length === 0) {
-        if (strict) {
+      } else if (isFlat(value)) {
+        if (strict && (typeof value !== 'object' || Object.keys(value).length)) {
           throw new TypeError(`unknown field "${key}" in model ${this.name}`)
@@ -268,7 +353,7 @@ }
     })
-    return prefix === '' ? this.resolveModel(result) : result
+    return (strict && prefix === '') ? this.resolveModel(result) : result
   }
 
   parse(source: object, strict = true, prefix = '', result = {} as S) {
-    const fields = Object.keys(this.fields)
+    const fields = Object.keys(this.fields).filter(key => !this.fields[key].relation)
     if (strict && prefix === '') {
@@ -293,3 +378,3 @@ // initialize object layout
           node[segments[0]] = value
-        } else if (!value || typeof value !== 'object' || isEvalExpr(value) || Array.isArray(value) || Binary.is(value) || Object.keys(value).length === 0) {
+        } else if (isFlat(value)) {
           if (strict) {
@@ -305,3 +390,3 @@ throw new TypeError(`unknown field "${fullKey}" in model ${this.name}`)
     }
-    return prefix === '' ? this.resolveModel(result) : result
+    return (strict && prefix === '') ? this.resolveModel(result) : result
   }
@@ -313,4 +398,4 @@
     for (const key in this.fields) {
-      const { initial, deprecated } = this.fields[key]!
-      if (deprecated) continue
+      if (!Field.available(this.fields[key])) continue
+      const { initial } = this.fields[key]!
       if (!keys.includes(key) && !isNullable(initial)) {
@@ -323,2 +408,6 @@ result[key] = clone(initial)
 
+  avaiableFields() {
+    return filterKeys(this.fields, (_, field) => Field.available(field))
+  }
+
   getType(): Type<S>
@@ -325,0 +414,0 @@ getType(key: string): Type | undefined

src/query.ts

 import { Extract, isNullable } from 'cosmokit'
 import { Eval, executeEval } from './eval.ts'
-import { Comparable, Flatten, Indexable, isComparable, makeRegExp } from './utils.ts'
+import { AtomicTypes, Comparable, Flatten, flatten, getCell, Indexable, isComparable, isFlat, makeRegExp, RegExpLike, Values } from './utils.ts'
 import { Selection } from './selection.ts'
@@ -35,4 +35,4 @@
     // regexp
-    $regex?: Extract<T, string, string | RegExp>
-    $regexFor?: Extract<T, string>
+    $regex?: Extract<T, string, string | RegExpLike>
+    $regexFor?: Extract<T, string, string | { input: string; flags?: string }>
 
@@ -44,2 +44,7 @@ // bitwise
     $bitsAnySet?: Extract<T, number>
+
+    // relation
+    $some?: T extends (infer U)[] ? Query<U> : never
+    $none?: T extends (infer U)[] ? Query<U> : never
+    $every?: T extends (infer U)[] ? Query<U> : never
   }
@@ -52,3 +57,3 @@
     /** @deprecated use query callback instead */
-    $expr?: Eval.Expr<boolean>
+    $expr?: Eval.Term<boolean>
   }
@@ -63,4 +68,8 @@
 
+  type NonNullExpr<T> = T extends Values<AtomicTypes> | any[] ? Field<T> : T extends object
+    ? Expr<Flatten<T>> | Selection.Callback<T, boolean>
+    : Field<T>
+
   export type Expr<T = any> = LogicalExpr<T> & {
-    [K in keyof T]?: null | Field<T[K]>
+    [K in keyof T]?: (undefined extends T[K] ? null : never) | NonNullExpr<Exclude<T[K], undefined>>
   }
@@ -96,3 +105,3 @@ }
   $regex: (query, data) => makeRegExp(query).test(data),
-  $regexFor: (query, data) => new RegExp(data, 'i').test(query),
+  $regexFor: (query, data) => typeof query === 'string' ? makeRegExp(data).test(query) : makeRegExp(data, query.flags).test(query.input),
 
@@ -147,3 +156,4 @@ // bitwise
     try {
-      return executeFieldQuery(value, data[key])
+      const flattenQuery = isFlat(query[key]) ? { [key]: query[key] } : flatten(query[key], `${key}.`)
+      return Object.entries(flattenQuery).every(([key, value]) => executeFieldQuery(value, getCell(data, key)))
     } catch {
@@ -150,0 +160,0 @@ return false

src/selection.ts

 import { defineProperty, Dict, filterKeys } from 'cosmokit'
 import { Driver } from './driver.ts'
-import { Eval, executeEval } from './eval.ts'
-import { Model } from './model.ts'
+import { Eval, executeEval, isAggrExpr, isEvalExpr } from './eval.ts'
+import { Field, Model } from './model.ts'
 import { Query } from './query.ts'
@@ -60,3 +60,3 @@ import { FlatKeys, FlatPick, Flatten, Keys, randomId, Row } from './utils.ts'
       // unknown field inside json
-      type = Type.fromField('expr')
+      type = model?.getType(`${prefix}${key}`) ?? Type.fromField('expr')
     }
@@ -89,4 +89,7 @@
   protected resolveQuery(query: Query<S> = {}): any {
-    if (typeof query === 'function') return { $expr: query(this.row) }
-    if (Array.isArray(query) || query instanceof RegExp || ['string', 'number'].includes(typeof query)) {
+    if (typeof query === 'function') {
+      const expr = query(this.row)
+      return expr['$expr'] ? expr : isEvalExpr(expr) ? { $expr: expr } : expr
+    }
+    if (Array.isArray(query) || query instanceof RegExp || ['string', 'number', 'bigint'].includes(typeof query)) {
       const { primary } = this.model
@@ -126,3 +129,3 @@ if (Array.isArray(primary)) {
         const expr = this.resolveField(field)
-        if (expr['$object']) {
+        if (expr['$object'] && !Type.fromTerm(expr).ignoreNull) {
           return Object.entries(expr['$object']).map(([key2, expr2]) => [`${key}.${key2}`, expr2])
@@ -248,2 +251,22 @@ }
 
+  join<K extends string, U>(
+    name: K,
+    selection: Selection<U>,
+    callback: (self: Row<S>, other: Row<U>) => Eval.Expr<boolean> = () => Eval.and(),
+    optional: boolean = false,
+  ): Selection<S & { [P in K]: U}> {
+    const fields = Object.fromEntries(Object.entries(this.model.fields)
+      .filter(([key, field]) => Field.available(field) && !key.startsWith(name + '.'))
+      .map(([key]) => [key, (row) => key.split('.').reduce((r, k) => r[k], row[this.ref])]))
+    if (optional) {
+      return this.driver.database
+        .join({ [this.ref]: this as Selection, [name]: selection }, (t: any) => callback(t[this.ref], t[name]), { [this.ref]: false, [name]: true })
+        .project({ ...fields, [name]: (row) => Eval.ignoreNull(row[name]) }) as any
+    } else {
+      return this.driver.database
+        .join({ [this.ref]: this as Selection, [name]: selection }, (t: any) => callback(t[this.ref], t[name]))
+        .project({ ...fields, [name]: (row) => Eval.ignoreNull(row[name]) }) as any
+    }
+  }
+
   _action(type: Executable.Action, ...args: any[]) {
@@ -254,3 +277,4 @@ return new Executable(this.driver, { ...this, type, args })
   evaluate<T>(callback: Selection.Callback<S, T, true>): Eval.Expr<T, true>
-  evaluate<K extends Keys<S>>(field: K): Eval.Expr<S[K][], boolean>
+  evaluate<K extends Keys<S>>(field: K): Eval.Expr<S[K][], false>
+  evaluate<K extends Keys<S>>(field: K[]): Eval.Expr<any[][], false>
   evaluate(): Eval.Expr<S[], boolean>
@@ -260,4 +284,4 @@ evaluate(callback?: any): any {
     if (!callback) callback = (row: any) => Eval.array(Eval.object(row))
-    const expr = this.resolveField(callback)
-    if (expr['$']) defineProperty(expr, Type.kType, Type.Array(Type.fromTerm(expr)))
+    const expr = Array.isArray(callback) ? Eval.select(...callback.map(x => this.resolveField(x))) : this.resolveField(callback)
+    if (isAggrExpr(expr)) defineProperty(expr, Type.kType, Type.Array(Type.fromTerm(expr)))
     return Eval.exec(selection._action('eval', expr))
@@ -264,0 +288,0 @@ }

src/type.ts

 import { Binary, defineProperty, isNullable, mapValues } from 'cosmokit'
 import { Field } from './model.ts'
 import { Eval, isEvalExpr } from './eval.ts'
+import { isEmpty } from './utils.ts'
 // import { Keys } from './utils.ts'
@@ -12,2 +13,4 @@
   array?: boolean
+  // For left joined unmatched result only
+  ignoreNull?: boolean
 }
@@ -64,4 +67,4 @@
 
-  export function fromTerm<T>(value: Eval.Term<T>): Type<T> {
-    if (isEvalExpr(value)) return value[kType] ?? fromField('expr' as any)
+  export function fromTerm<T>(value: Eval.Term<T>, initial?: Type): Type<T> {
+    if (isEvalExpr(value)) return value[kType] ?? initial ?? fromField('expr' as any)
     else return fromPrimitive(value as T)
@@ -71,3 +74,3 @@ }
   export function fromTerms(values: Eval.Term<any>[], initial?: Type): Type {
-    return values.map(fromTerm).find((type) => type.type !== 'expr') ?? initial ?? fromField('expr')
+    return values.map((x) => fromTerm(x)).find((type) => type.type !== 'expr') ?? initial ?? fromField('expr')
   }
@@ -94,2 +97,14 @@
   }
+
+  export function transform(value: any, type: Type, callback: (value: any, type?: Type) => any) {
+    if (!isNullable(value) && type?.inner) {
+      if (Type.isArray(type)) {
+        return (value as any[]).map(x => callback(x, Type.getInner(type))).filter(x => !type.ignoreNull || !isEmpty(x))
+      } else {
+        if (type.ignoreNull && isEmpty(value)) return null
+        return mapValues(value, (x, k) => callback(x, Type.getInner(type, k)))
+      }
+    }
+    return value
+  }
 }

src/utils.ts

@@ -1,3 +0,3 @@
-import { Intersect } from 'cosmokit'
-import { Eval } from './eval.ts'
+import { Binary, Intersect, isNullable } from 'cosmokit'
+import { Eval, isEvalExpr } from './eval.ts'
 
@@ -19,2 +19,8 @@ export type Values<S> = S[keyof S]
 
+export type DeepPartial<T> =
+  | T extends Values<AtomicTypes> ? T
+  : T extends (infer U)[] ? DeepPartial<U>[]
+  : T extends object ? { [K in keyof T]?: DeepPartial<T[K]> }
+  : T
+
 export interface AtomicTypes {
@@ -36,3 +42,3 @@ Number: number
 
-type FlatWrap<S, A extends 0[], P extends string> = { [K in P]?: S }
+type FlatWrap<S, A extends 0[], P extends string> = { [K in P]: S }
   // rule out atomic types
@@ -69,5 +75,17 @@ | (S extends Values<AtomicTypes> ? never
     || typeof value === 'boolean'
+    || typeof value === 'bigint'
     || value instanceof Date
 }
 
+export function isFlat(value: any): value is Values<AtomicTypes> {
+  return !value
+    || typeof value !== 'object'
+    || isEvalExpr(value)
+    || Object.keys(value).length === 0
+    || Array.isArray(value)
+    || value instanceof Date
+    || value instanceof RegExp
+    || Binary.isSource(value)
+}
+
 const letters = 'abcdefghijklmnopqrstuvwxyz'
@@ -79,6 +97,11 @@
 
-export function makeRegExp(source: string | RegExp) {
-  return source instanceof RegExp ? source : new RegExp(source)
+export interface RegExpLike {
+  source: string
+  flags?: string
 }
 
+export function makeRegExp(source: string | RegExpLike, flags?: string) {
+  return (source instanceof RegExp && !flags) ? source : new RegExp((source as any).source ?? source, flags ?? (source as any).flags)
+}
+
 export function unravel(source: object, init?: (value) => any) {
@@ -98,1 +121,28 @@ const result = {}
 }
+
+export function flatten(source: object, prefix = '', ignore: (value: any) => boolean = isFlat) {
+  const result = {}
+  for (const key in source) {
+    const value = source[key]
+    if (ignore(value)) {
+      result[`${prefix}${key}`] = value
+    } else {
+      Object.assign(result, flatten(value, `${prefix}${key}.`, ignore))
+    }
+  }
+  return result
+}
+
+export function getCell(row: any, key: any): any {
+  if (key in row) return row[key]
+  return key.split('.').reduce((r, k) => r === undefined ? undefined : r[k], row)
+}
+
+export function isEmpty(value: any) {
+  if (isNullable(value)) return true
+  if (typeof value !== 'object') return false
+  for (const key in value) {
+    if (!isEmpty(value[key])) return false
+  }
+  return true
+}

No alert changes

Improved metrics

Total package byte prevSize

408368

increased by45.63%

Lines of code

6633

increased by36.01%

No dependency changes