minato - npm Package Compare versions

Comparing version 3.4.0 to 3.4.1

lib/index.d.ts

@@ -140,8 +140,9 @@ import { Extract, Dict, Awaitable, MaybeArray, Intersect } from 'cosmokit';
     }
-    type Cursor<K extends string = never> = K[] | CursorOptions<K>;
-    interface CursorOptions<K extends string> {
+    type Cursor<K extends string = string, S = any, T extends Keys<S> = any> = K[] | CursorOptions<K, S, T>;
+    interface CursorOptions<K extends string = string, S = any, T extends Keys<S> = any> {
         limit?: number;
         offset?: number;
         fields?: K[];
-        sort?: Dict<Direction, K>;
+        sort?: Partial<Dict<Direction, FlatKeys<S[T]>>>;
+        include?: Relation.Include<S[T], Values<S>>;
     }
@@ -211,2 +212,3 @@ interface WriteResult {
         fields: K[];
+        shared: Record<K, Keys<S[T]>>;
         required: boolean;
@@ -220,20 +222,23 @@ }
         fields?: MaybeArray<K>;
+        shared?: MaybeArray<K> | Partial<Record<K, string>>;
     }
     export type Include<T, S> = boolean | {
-        [P in keyof T]?: T[P] extends MaybeArray<infer U extends S> | undefined ? Include<U, S> : never;
+        [P in keyof T]?: T[P] extends MaybeArray<infer U> | undefined ? U extends S ? Include<U, S> : never : never;
     };
-    export type SetExpr<S extends object = any> = Row.Computed<S, Update<S>> | {
+    export type SetExpr<S extends object = any> = ((row: Row<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 interface Modifier<T extends object = any, S extends any = any> {
+        $create?: MaybeArray<Create<T, S>>;
+        $upsert?: MaybeArray<DeepPartial<T>>;
+        $set?: MaybeArray<SetExpr<T>>;
+        $remove?: Query.Expr<Flatten<T>> | Selection.Callback<T, boolean>;
+        $connect?: Query.Expr<Flatten<T>> | Selection.Callback<T, boolean>;
+        $disconnect?: Query.Expr<Flatten<T>> | Selection.Callback<T, 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 function buildSharedKey(field: string, reference: string): string;
+    export function parse(def: Definition, key: string, model: Model, relmodel: Model, subprimary?: boolean): [Config, Config];
 }
@@ -284,3 +289,3 @@ export interface Field<T = any> {
     type MapField<O = any, N = any> = {
-        [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);
+        [K in keyof O]?: Literal<O[K], N> | Definition<O[K], N> | Transform<O[K], any, N> | (O[K] extends object | undefined ? Relation.Definition<FlatKeys<O>> : never);
     };
@@ -360,2 +365,3 @@ export type Extension<O = any, N = any> = MapField<Flatten<O>, N>;
 export function isEvalExpr(value: any): value is Eval.Expr;
+export const isUpdateExpr: (value: any) => boolean;
 export function isAggrExpr(expr: Eval.Expr): boolean;
@@ -366,3 +372,3 @@ export function hasSubquery(value: any): 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 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>> | Relation.Modifier<U> : any;
 export type Eval<U> = U extends Values<AtomicTypes> ? U : U extends Eval.Expr<infer T> ? T : never;
@@ -397,3 +403,3 @@ declare const kExpr: unique symbol;
         select(...args: Any[]): Expr<any[], false>;
-        query<T extends object>(row: Row<T>, query: Query.Expr<T>): Expr<boolean, false>;
+        query<T extends object>(row: Row<T>, query: Query.Expr<T>, expr?: Term<boolean>): Expr<boolean, false>;
         if<T extends Comparable, A extends boolean>(cond: Any<A>, vThen: Term<T, A>, vElse: Term<T, A>): Expr<T, A>;
@@ -508,3 +514,3 @@ ifNull<T extends Comparable, A extends boolean>(...args: Term<T, A>[]): Expr<T, A>;
 export function flatten(source: object, prefix?: string, ignore?: (value: any) => boolean): {};
-export function getCell(row: any, key: any): any;
+export function getCell(row: any, path: any): any;
 export function isEmpty(value: any): boolean;
@@ -531,2 +537,16 @@ type TableLike<S> = Keys<S> | Selection;
 }
+type CreateMap<T, S> = {
+    [K in keyof T]?: Create<T[K], S>;
+};
+export type Create<T, S> = T extends Values<AtomicTypes> ? T : T extends (infer I extends Values<S>)[] ? CreateMap<I, S>[] | {
+    $literal?: DeepPartial<I>;
+    $create?: MaybeArray<CreateMap<I, S>>;
+    $upsert?: MaybeArray<CreateMap<I, S>>;
+    $connect?: Query.Expr<Flatten<I>>;
+} : T extends Values<S> ? CreateMap<T, S> | {
+    $literal?: DeepPartial<T>;
+    $create?: CreateMap<T, S>;
+    $upsert?: CreateMap<T, S>;
+    $connect?: Query.Expr<Flatten<T>>;
+} : T extends (infer U)[] ? DeepPartial<U>[] : T extends object ? CreateMap<T, S> : T;
 export class Database<S = {}, N = {}, C extends Context = Context> extends Service<undefined, C> {
@@ -549,3 +569,3 @@ static [Service.provide]: string;
     private prepare;
-    extend<K extends Keys<S>, T extends Field.Extension<S[K], N>>(name: K, fields: T, config?: Partial<Model.Config<Keys<T>>>): void;
+    extend<K extends Keys<S>>(name: K, fields: Field.Extension<S[K], N>, config?: Partial<Model.Config<FlatKeys<S[K]>>>): void;
     private _parseField;
@@ -557,11 +577,11 @@ private parseField;
     select<T>(table: Selection<T>, query?: Query<T>): Selection<T>;
-    select<K extends Keys<S>>(table: K, query?: Query<S[K]>, cursor?: Relation.Include<S[K], Values<S>> | null): Selection<S[K]>;
+    select<K extends Keys<S>>(table: K, query?: Query<S[K]>, include?: 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>>;
     join<X extends Join2.Input<S>>(tables: X, callback?: Join2.Predicate<S, X>, optional?: Dict<boolean, Keys<X>>): Selection<Join2.Output<S, X>>;
     get<K extends Keys<S>>(table: K, query: Query<S[K]>): Promise<S[K][]>;
-    get<K extends Keys<S>, P extends FlatKeys<S[K]> = any>(table: K, query: Query<S[K]>, cursor?: Driver.Cursor<P>): Promise<FlatPick<S[K], P>[]>;
+    get<K extends Keys<S>, P extends FlatKeys<S[K]> = any>(table: K, query: Query<S[K]>, cursor?: Driver.Cursor<P, S, K>): Promise<FlatPick<S[K], P>[]>;
     eval<K extends Keys<S>, T>(table: K, expr: Selection.Callback<S[K], T, true>, query?: Query<S[K]>): Promise<T>;
     set<K extends Keys<S>>(table: K, query: Query<S[K]>, update: Row.Computed<S[K], Update<S[K]>>): Promise<Driver.WriteResult>;
     remove<K extends Keys<S>>(table: K, query: Query<S[K]>): Promise<Driver.WriteResult>;
-    create<K extends Keys<S>>(table: K, data: DeepPartial<S[K]>): Promise<S[K]>;
+    create<K extends Keys<S>>(table: K, data: Create<S[K], S>): 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>;
@@ -577,3 +597,5 @@ makeProxy(marker: any, getDriver?: (driver: Driver<any, C>, database: this) => Driver<any, C>): this;
     private transformRelationQuery;
+    private createOrUpdate;
     private processRelationUpdate;
+    private hasRelation;
 }
@@ -580,0 +602,0 @@ export namespace RuntimeError {

package.json

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

src/database.ts

@@ -1,8 +0,8 @@
-import { defineProperty, Dict, filterKeys, makeArray, mapValues, MaybeArray, noop, omit } from 'cosmokit'
+import { deduplicate, defineProperty, Dict, filterKeys, isNullable, makeArray, mapValues, MaybeArray, noop, omit, pick, remove } from 'cosmokit'
 import { Context, Service, Spread } from 'cordis'
-import { DeepPartial, FlatKeys, FlatPick, getCell, Indexable, Keys, randomId, Row, unravel, Values } from './utils.ts'
+import { AtomicTypes, DeepPartial, FlatKeys, FlatPick, Flatten, getCell, Indexable, Keys, randomId, Row, unravel, Values } from './utils.ts'
 import { Selection } from './selection.ts'
 import { Field, Model, Relation } from './model.ts'
 import { Driver } from './driver.ts'
-import { Eval, Update } from './eval.ts'
+import { Eval, isUpdateExpr, Update } from './eval.ts'
 import { Query } from './query.ts'
@@ -47,5 +47,39 @@ import { Type } from './type.ts'
 
+type CreateMap<T, S> = { [K in keyof T]?: Create<T[K], S> }
+
+export type Create<T, S> =
+  | T extends Values<AtomicTypes> ? T
+  : T extends (infer I extends Values<S>)[] ? CreateMap<I, S>[] |
+    {
+      $literal?: DeepPartial<I>
+      $create?: MaybeArray<CreateMap<I, S>>
+      $upsert?: MaybeArray<CreateMap<I, S>>
+      $connect?: Query.Expr<Flatten<I>>
+    }
+  : T extends Values<S> ? CreateMap<T, S> |
+    {
+      $literal?: DeepPartial<T>
+      $create?: CreateMap<T, S>
+      $upsert?: CreateMap<T, S>
+      $connect?: Query.Expr<Flatten<T>>
+    }
+  : T extends (infer U)[] ? DeepPartial<U>[]
+  : T extends object ? CreateMap<T, S>
+  : T
+
+function mergeQuery<T>(base: Query.FieldExpr<T>, query: Query.Expr<Flatten<T>> | ((row: Row<T>) => Query.Expr<Flatten<T>>)): Selection.Callback<T, boolean> {
+  if (typeof query === 'function') {
+    return (row: any) => {
+      const q = query(row)
+      return { $expr: true, ...base, ...(q.$expr ? q : { $expr: q }) } as any
+    }
+  } else {
+    return (_: any) => ({ $expr: true, ...base, ...query }) as any
+  }
+}
+
 export class Database<S = {}, N = {}, C extends Context = Context> extends Service<undefined, C> {
   static [Service.provide] = 'model'
   static [Service.immediate] = true
+
   static readonly transact = Symbol('minato.transact')
@@ -101,3 +135,3 @@ static readonly migrate = Symbol('minato.migrate')
 
-  extend<K extends Keys<S>, T extends Field.Extension<S[K], N>>(name: K, fields: T, config: Partial<Model.Config<Keys<T>>> = {}) {
+  extend<K extends Keys<S>>(name: K, fields: Field.Extension<S[K], N>, config: Partial<Model.Config<FlatKeys<S[K]>>> = {}) {
     let model = this.tables[name]
@@ -114,7 +148,8 @@ if (!model) {
     if (makeArray(model.primary).every(key => key in fields)) {
-      defineProperty(model, 'ctx', this[Context.origin])
+      defineProperty(model, 'ctx', this.ctx)
     }
     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])
+      const subprimary = !def.fields && makeArray(model.primary).includes(key)
+      const [relation, inverse] = Relation.parse(def, key, model, this.tables[def.table ?? key], subprimary)
       if (!this.tables[relation.table]) throw new Error(`relation table ${relation.table} does not exist`)
@@ -137,11 +172,12 @@ ;(model.fields[key] = Field.parse('expr')).relation = relation
         if (this.tables[assocTable]) return
-        const fields = relation.fields.map(x => [Relation.buildAssociationKey(x, name), model.fields[x]?.deftype] as const)
+        const shared = Object.entries(relation.shared).map(([x, y]) => [Relation.buildSharedKey(x, y), model.fields[x]!.deftype] as const)
+        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]),
+          ...Object.fromEntries([...shared, ...fields, ...references]),
           [name]: {
             type: 'manyToOne',
             table: name,
-            fields: fields.map(x => x[0]),
-            references: relation.references,
+            fields: [...shared, ...fields].map(x => x[0]),
+            references: [...Object.keys(relation.shared), ...relation.fields],
           },
@@ -151,10 +187,14 @@ [relation.table]: {
             table: relation.table,
-            fields: references.map(x => x[0]),
-            references: relation.fields,
+            fields: [...shared, ...references].map(x => x[0]),
+            references: [...Object.values(relation.shared), ...relation.references],
           },
         } as any, {
-          primary: [...fields.map(x => x[0]), ...references.map(x => x[0])],
+          primary: [...shared, ...fields, ...references].map(x => x[0]) as any,
         })
       }
     })
+    // use relation field as primary
+    if (Array.isArray(model.primary) && model.primary.every(key => model.fields[key]?.relation)) {
+      model.primary = deduplicate(model.primary.map(key => model.fields[key]!.relation!.fields).flat())
+    }
     this.prepareTasks[name] = this.prepare(name)
@@ -254,3 +294,3 @@ ;(this.ctx as Context).emit('model', name)
 
-    this[Context.current].effect(() => {
+    this.ctx.effect(() => {
       this.types[name] = { ...field }
@@ -275,12 +315,12 @@ this.types[name].deftype ??= this.types[field.type]?.deftype ?? type.type as any
     query?: Query<S[K]>,
-    cursor?: Relation.Include<S[K], Values<S>> | null,
+    include?: Relation.Include<S[K], Values<S>> | null,
   ): Selection<S[K]>
 
-  select(table: any, query?: any, cursor?: any) {
+  select(table: any, query?: any, include?: any) {
     let sel = new Selection(this.getDriver(table), table, query)
     if (typeof table !== 'string') return sel
-    const whereOnly = cursor === null
+    const whereOnly = include === null
     const rawquery = typeof query === 'function' ? query : () => query
     const modelFields = this.tables[table].fields
-    if (cursor) cursor = filterKeys(cursor, (key) => !!modelFields[key]?.relation)
+    if (include) include = filterKeys(include, (key) => !!modelFields[key]?.relation)
     for (const key in { ...sel.query, ...sel.query.$not }) {
@@ -304,4 +344,4 @@ if (modelFields[key]?.relation) {
         }
-        if (!cursor || !Object.getOwnPropertyNames(cursor).includes(key)) {
-          (cursor ??= {})[key] = true
+        if (!include || !Object.getOwnPropertyNames(include).includes(key)) {
+          (include ??= {})[key] = true
         }
@@ -311,9 +351,9 @@ }
 
-    sel.query = omit(sel.query, Object.keys(cursor ?? {}))
+    sel.query = omit(sel.query, Object.keys(include ?? {}))
     if (Object.keys(sel.query.$not ?? {}).length) {
-      sel.query.$not = omit(sel.query.$not!, Object.keys(cursor ?? {}))
+      sel.query.$not = omit(sel.query.$not!, Object.keys(include ?? {}))
       if (Object.keys(sel.query.$not).length === 0) Reflect.deleteProperty(sel.query, '$not')
     }
 
-    if (cursor && typeof cursor === 'object') {
+    if (include && typeof include === 'object') {
       if (typeof table !== 'string') throw new Error('cannot include relations on derived selection')
@@ -328,7 +368,7 @@ const extraFields: string[] = []
       }
-      for (const key in cursor) {
-        if (!cursor[key] || !modelFields[key]?.relation) continue
+      for (const key in include) {
+        if (!include[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(
+          sel = whereOnly ? sel : sel.join(key, this.select(relation.table, {}, include[key]), (self, other) => Eval.and(
             ...relation.fields.map((k, i) => Eval.eq(self[k], other[relation.references[i]])),
@@ -338,3 +378,3 @@ ), true)
         } else if (relation.type === 'oneToMany') {
-          sel = whereOnly ? sel : sel.join(key, this.select(relation.table, {}, cursor[key]), (self, other) => Eval.and(
+          sel = whereOnly ? sel : sel.join(key, this.select(relation.table, {}, include[key]), (self, other) => Eval.and(
             ...relation.fields.map((k, i) => Eval.eq(self[k], other[relation.references[i]])),
@@ -344,3 +384,3 @@ ), true)
           sel = whereOnly ? sel : sel.groupBy([
-            ...Object.entries(modelFields).filter(([, field]) => Field.available(field)).map(([k]) => k),
+            ...Object.entries(modelFields).filter(([k, field]) => !extraFields.some(x => k.startsWith(`${x}.`)) && Field.available(field)).map(([k]) => k),
             ...extraFields,
@@ -353,3 +393,8 @@ ], {
           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(
+          const shared = Object.entries(relation.shared).map(([x, y]) => [Relation.buildSharedKey(x, y), {
+            field: x,
+            reference: y,
+          }] as const)
+          sel = whereOnly ? sel : sel.join(key, this.select(assocTable, {}, { [relation.table]: include[key] } as any), (self, other) => Eval.and(
+            ...shared.map(([k, v]) => Eval.eq(self[v.field], other[k])),
             ...relation.fields.map((k, i) => Eval.eq(self[k], other[references[i]])),
@@ -359,3 +404,3 @@ ), true)
           sel = whereOnly ? sel : sel.groupBy([
-            ...Object.entries(modelFields).filter(([, field]) => Field.available(field)).map(([k]) => k),
+            ...Object.entries(modelFields).filter(([k, field]) => !extraFields.some(x => k.startsWith(`${x}.`)) && Field.available(field)).map(([k]) => k),
             ...extraFields,
@@ -415,8 +460,9 @@ ], {
     query: Query<S[K]>,
-    cursor?: Driver.Cursor<P>,
+    cursor?: Driver.Cursor<P, S, K>,
   ): Promise<FlatPick<S[K], P>[]>
 
   async get<K extends Keys<S>>(table: K, query: Query<S[K]>, cursor?: 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
+    let fields = Array.isArray(cursor) ? cursor : cursor?.fields
+    fields = fields ? Object.fromEntries(fields.map(x => [x, true])) : cursor?.include
+    return this.select(table, query, fields).execute(cursor) as any
   }
@@ -434,3 +480,3 @@
     const rawupdate = typeof update === 'function' ? update : () => update
-    const sel = this.select(table, query, null)
+    let sel = this.select(table, query, null)
     if (typeof update === 'function') update = update(sel.row)
@@ -448,24 +494,7 @@ const primary = makeArray(sel.model.primary)
         const rows = await database.get(table, query)
-        let baseUpdate = omit(update, relations.map(([key]) => key) as any)
+        sel = database.select(table, query, null)
+        let baseUpdate = omit(rawupdate(sel.row), 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])))
-          }
+        for (const [key] of relations) {
+          await Promise.all(rows.map(row => database.processRelationUpdate(table, row, key, rawupdate(row as any)[key])))
         }
@@ -486,59 +515,18 @@ return Object.keys(baseUpdate).length === 0 ? {} : await sel._action('set', baseUpdate).execute()
 
-  async create<K extends Keys<S>>(table: K, data: DeepPartial<S[K]>): Promise<S[K]>
+  async create<K extends Keys<S>>(table: K, data: Create<S[K], S>): Promise<S[K]>
   async create<K extends Keys<S>>(table: K, data: any): Promise<S[K]> {
     const sel = this.select(table)
-    const { primary, autoInc, fields } = sel.model
-    if (!autoInc) {
-      const keys = makeArray(primary)
-      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
+    if (!this.hasRelation(table, data)) {
+      const { primary, autoInc } = sel.model
+      if (!autoInc) {
+        const keys = makeArray(primary)
+        if (keys.some(key => getCell(data, key) === undefined)) {
+          throw new Error('missing primary key')
         }
-        data = omit(data, [key]) as any
       }
+      return sel._action('create', sel.model.create(data)).execute()
+    } else {
+      return this.ensureTransaction(database => database.createOrUpdate(table, data, false))
     }
-
-    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()
   }
@@ -553,60 +541,2 @@
     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))
@@ -765,86 +695,380 @@ keys = makeArray(keys || sel.model.primary) 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')
+  private async createOrUpdate<K extends Keys<S>>(table: K, data: any, upsert: boolean = true): Promise<S[K]> {
+    const sel = this.select(table)
+    data = { ...data }
+    const tasks = ['']
+    for (const key in data) {
+      if (data[key] !== undefined && this.tables[table].fields[key]?.relation) {
+        const relation = this.tables[table].fields[key].relation
+        if (relation.type === 'oneToOne' && relation.required) tasks.push(key)
+        else if (relation.type === 'oneToOne') tasks.unshift(key)
+        else if (relation.type === 'oneToMany') tasks.push(key)
+        else if (relation.type === 'manyToOne') tasks.unshift(key)
+        else if (relation.type === 'manyToMany') tasks.push(key)
       }
     }
-    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)
+
+    for (const key of [...tasks]) {
+      if (!key) {
+        // create the plain data, with or without upsert
+        const { primary, autoInc } = sel.model
+        const keys = makeArray(primary)
+        if (keys.some(key => isNullable(getCell(data, key)))) {
+          if (!autoInc) {
+            throw new Error('missing primary key')
+          } else {
+            // nullable relation may pass null here, remove it to enable autoInc
+            delete data[primary as string]
+            upsert = false
+          }
+        }
+        if (upsert) {
+          await sel._action('upsert', [sel.model.format(omit(data, tasks))], keys).execute()
+        } else {
+          Object.assign(data, await sel._action('create', sel.model.create(omit(data, tasks))).execute())
+        }
+        continue
+      }
+      const value = data[key]
+      const relation: Relation.Config<S> = this.tables[table].fields[key]!.relation! as any
+      if (relation.type === 'oneToOne') {
+        if (value.$literal) {
+          data[key] = value.$literal
+          remove(tasks, key)
+        } else if (value.$create || !isUpdateExpr(value)) {
+          const result = await this.createOrUpdate(relation.table, {
+            ...Object.fromEntries(relation.references.map((k, i) => [k, getCell(data, relation.fields[i])])),
+            ...value.$create ?? value,
+          } as any)
+          if (!relation.required) {
+            relation.references.forEach((k, i) => data[relation.fields[i]] = getCell(result, k))
+          }
+        } else if (value.$upsert) {
+          await this.upsert(relation.table, [{
+            ...Object.fromEntries(relation.references.map((k, i) => [k, getCell(data, relation.fields[i])])),
+            ...value.$upsert,
+          }])
+          if (!relation.required) {
+            relation.references.forEach((k, i) => data[relation.fields[i]] = getCell(value.$upsert, k))
+          }
+        } else if (value.$connect) {
+          if (relation.required) {
+            await this.set(relation.table,
+              value.$connect,
+              Object.fromEntries(relation.references.map((k, i) => [k, getCell(data, relation.fields[i])])) as any,
+            )
+          } else {
+            const result = relation.references.every(k => value.$connect![k as any] !== undefined) ? [value.$connect]
+              : await this.get(relation.table, value.$connect as any)
+            if (result.length !== 1) throw new Error('related row not found or not unique')
+            relation.references.forEach((k, i) => data[relation.fields[i]] = getCell(result[0], k))
+          }
+        }
+      } else if (relation.type === 'manyToOne') {
+        if (value.$literal) {
+          data[key] = value.$literal
+          remove(tasks, key)
+        } else if (value.$create || !isUpdateExpr(value)) {
+          const result = await this.createOrUpdate(relation.table, value.$create ?? value)
+          relation.references.forEach((k, i) => data[relation.fields[i]] = getCell(result, k))
+        } else if (value.$upsert) {
+          await this.upsert(relation.table, [value.$upsert])
+          relation.references.forEach((k, i) => data[relation.fields[i]] = getCell(value.$upsert, k))
+        } else if (value.$connect) {
+          const result = relation.references.every(k => value.$connect![k as any] !== undefined) ? [value.$connect]
+            : await this.get(relation.table, value.$connect as any)
+          if (result.length !== 1) throw new Error('related row not found or not unique')
+          relation.references.forEach((k, i) => data[relation.fields[i]] = getCell(result[0], k))
+        }
+      } else if (relation.type === 'oneToMany') {
+        if (value.$create || Array.isArray(value)) {
+          for (const item of makeArray(value.$create ?? value)) {
+            await this.createOrUpdate(relation.table, {
+              ...Object.fromEntries(relation.references.map((k, i) => [k, getCell(data, relation.fields[i])])),
+              ...item,
+            })
+          }
+        }
+        if (value.$upsert) {
+          await this.upsert(relation.table, makeArray(value.$upsert).map(r => ({
+            ...Object.fromEntries(relation.references.map((k, i) => [k, getCell(data, relation.fields[i])])),
+            ...r,
+          })))
+        }
+        if (value.$connect) {
+          await this.set(relation.table,
+            value.$connect,
+            Object.fromEntries(relation.references.map((k, i) => [k, getCell(data, relation.fields[i])])) as any,
+          )
+        }
       } else if (relation.type === 'manyToMany') {
-        throw new Error('remove for manyToMany relation is not supported')
+        const assocTable = Relation.buildAssociationTable(relation.table, table)
+        const fields = relation.fields.map(x => Relation.buildAssociationKey(x, table))
+        const references = relation.references.map(x => Relation.buildAssociationKey(x, relation.table))
+        const shared = Object.entries(relation.shared).map(([x, y]) => [Relation.buildSharedKey(x, y), {
+          field: x,
+          reference: y,
+        }] as const)
+        const result: any[] = []
+        if (value.$create || Array.isArray(value)) {
+          for (const item of makeArray(value.$create ?? value)) {
+            result.push(await this.createOrUpdate(relation.table, {
+              ...Object.fromEntries(shared.map(([, v]) => [v.reference, getCell(item, v.reference) ?? getCell(data, v.field)])),
+              ...item,
+            }))
+          }
+        }
+        if (value.$upsert) {
+          const upsert = makeArray(value.$upsert).map(r => ({
+            ...Object.fromEntries(shared.map(([, v]) => [v.reference, getCell(r, v.reference) ?? getCell(data, v.field)])),
+            ...r,
+          }))
+          await this.upsert(relation.table, upsert)
+          result.push(...upsert)
+        }
+        if (value.$connect) {
+          for (const item of makeArray(value.$connect)) {
+            if (references.every(k => item[k] !== undefined)) result.push(item)
+            else result.push(...await this.get(relation.table, item))
+          }
+        }
+        await this.upsert(assocTable as any, result.map(r => ({
+          ...Object.fromEntries(shared.map(([k, v]) => [k, getCell(r, v.reference) ?? getCell(data, v.field)])),
+          ...Object.fromEntries(fields.map((k, i) => [k, getCell(data, relation.fields[i])])),
+          ...Object.fromEntries(references.map((k, i) => [k, getCell(r, relation.references[i])])),
+        } as any)))
       }
     }
-    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]
+    return data
+  }
+
+  private async processRelationUpdate(table: any, row: any, key: any, value: Relation.Modifier) {
+    const model = this.tables[table], update = Object.create(null)
+    const relation: Relation.Config<S> = this.tables[table].fields[key]!.relation! as any
+    if (relation.type === 'oneToOne') {
+      if (value === null) {
+        value = relation.required ? { $remove: {} } : { $disconnect: {} }
+      }
+      if (typeof value === 'object' && !isUpdateExpr(value)) {
+        value = { $create: value }
+      }
+      if (value.$remove) {
+        await this.remove(relation.table, Object.fromEntries(relation.references.map((k, i) => [k, getCell(row, relation.fields[i])])) as any)
+      }
+      if (value.$disconnect) {
+        if (relation.required) {
           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,
+            mergeQuery(Object.fromEntries(relation.references.map((k, i) => [k, getCell(row, relation.fields[i])])), value.$disconnect),
+            Object.fromEntries(relation.references.map((k, i) => [k, null])) as any,
           )
+        } else {
+          Object.assign(update, Object.fromEntries(relation.fields.map((k, i) => [k, null])))
         }
-      } 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
+      if (value.$set || typeof value === 'function') {
+        await this.set(
+          relation.table,
+          Object.fromEntries(relation.references.map((k, i) => [k, getCell(row, relation.fields[i])])) as any,
+          value.$set ?? value as any,
+        )
+      }
+      if (value.$create) {
+        const result = await this.createOrUpdate(relation.table, {
+          ...Object.fromEntries(relation.references.map((k, i) => [k, getCell(row, relation.fields[i])])),
+          ...value.$create,
         })
-        await this.upsert(relation.table, upsert)
-      } else if (relation.type === 'manyToMany') {
-        throw new Error('create for manyToMany relation is not supported')
+        if (!relation.required) {
+          Object.assign(update, Object.fromEntries(relation.fields.map((k, i) => [k, getCell(result, relation.references[i])])))
+        }
       }
-    }
-    if (modifier.$disconnect) {
-      if (relation.type === 'oneToMany') {
+      if (value.$upsert) {
+        await this.upsert(relation.table, makeArray(value.$upsert).map(r => ({
+          ...Object.fromEntries(relation.references.map((k, i) => [k, getCell(row, relation.fields[i])])),
+          ...r,
+        })))
+        if (!relation.required) {
+          Object.assign(update, Object.fromEntries(relation.fields.map((k, i) => [k, getCell(value.$upsert, relation.references[i])])))
+        }
+      }
+      if (value.$connect) {
+        if (relation.required) {
+          await this.set(relation.table,
+            value.$connect,
+            Object.fromEntries(relation.references.map((k, i) => [k, getCell(row, relation.fields[i])])) as any,
+          )
+        } else {
+          const result = await this.get(relation.table, value.$connect as any)
+          if (result.length !== 1) throw new Error('related row not found or not unique')
+          Object.assign(update, Object.fromEntries(relation.fields.map((k, i) => [k, getCell(result[0], relation.references[i])])))
+        }
+      }
+    } else if (relation.type === 'manyToOne') {
+      if (value === null) {
+        value = { $disconnect: {} }
+      }
+      if (typeof value === 'object' && !isUpdateExpr(value)) {
+        value = { $create: value }
+      }
+      if (value.$remove) {
+        await this.remove(relation.table, Object.fromEntries(relation.references.map((k, i) => [k, getCell(row, relation.fields[i])])) as any)
+      }
+      if (value.$disconnect) {
+        Object.assign(update, Object.fromEntries(relation.fields.map((k, i) => [k, null])))
+      }
+      if (value.$set || typeof value === 'function') {
+        await this.set(
+          relation.table,
+          Object.fromEntries(relation.references.map((k, i) => [k, getCell(row, relation.fields[i])])) as any,
+          value.$set ?? value as any,
+        )
+      }
+      if (value.$create) {
+        const result = await this.createOrUpdate(relation.table, {
+          ...Object.fromEntries(relation.references.map((k, i) => [k, getCell(row, relation.fields[i])])),
+          ...value.$create,
+        })
+        Object.assign(update, Object.fromEntries(relation.fields.map((k, i) => [k, getCell(result, relation.references[i])])))
+      }
+      if (value.$upsert) {
+        await this.upsert(relation.table, makeArray(value.$upsert).map(r => ({
+          ...Object.fromEntries(relation.references.map((k, i) => [k, getCell(row, relation.fields[i])])),
+          ...r,
+        })))
+        Object.assign(update, Object.fromEntries(relation.fields.map((k, i) => [k, getCell(value.$upsert, relation.references[i])])))
+      }
+      if (value.$connect) {
+        const result = await this.get(relation.table, value.$connect)
+        if (result.length !== 1) throw new Error('related row not found or not unique')
+        Object.assign(update, Object.fromEntries(relation.fields.map((k, i) => [k, getCell(result[0], relation.references[i])])))
+      }
+    } else if (relation.type === 'oneToMany') {
+      if (Array.isArray(value)) {
+        const $create: any[] = [], $upsert: any[] = []
+        value.forEach(item => this.hasRelation(relation.table, item) ? $create.push(item) : $upsert.push(item))
+        value = { $remove: {}, $create, $upsert }
+      }
+      if (value.$remove) {
+        await this.remove(relation.table, mergeQuery(Object.fromEntries(relation.references.map((k, i) => [k, row[relation.fields[i]]])), value.$remove))
+      }
+      if (value.$disconnect) {
         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),
+          mergeQuery(Object.fromEntries(relation.references.map((k, i) => [k, getCell(row, relation.fields[i])])), value.$disconnect),
           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))
+      }
+      if (value.$set || typeof value === 'function') {
+        for (const setexpr of makeArray(value.$set ?? value) as any[]) {
+          const [query, update] = setexpr.update ? [setexpr.where, setexpr.update] : [{}, setexpr]
+          await this.set(relation.table,
+            mergeQuery(Object.fromEntries(relation.references.map((k, i) => [k, row[relation.fields[i]]])), query),
+            update,
+          )
+        }
+      }
+      if (value.$create) {
+        for (const item of makeArray(value.$create)) {
+          await this.createOrUpdate(relation.table, {
+            ...Object.fromEntries(relation.references.map((k, i) => [k, getCell(row, relation.fields[i])])),
+            ...item,
+          })
+        }
+      }
+      if (value.$upsert) {
+        await this.upsert(relation.table, makeArray(value.$upsert).map(r => ({
+          ...Object.fromEntries(relation.references.map((k, i) => [k, getCell(row, relation.fields[i])])),
+          ...r,
+        })))
+      }
+      if (value.$connect) {
+        await this.set(relation.table,
+          value.$connect,
+          Object.fromEntries(relation.references.map((k, i) => [k, row[relation.fields[i]]])) 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 shared = Object.entries(relation.shared).map(([x, y]) => [Relation.buildSharedKey(x, y), {
+        field: x,
+        reference: y,
+      }] as const)
+      if (Array.isArray(value)) {
+        const $create: any[] = [], $upsert: any[] = []
+        value.forEach(item => this.hasRelation(relation.table, item) ? $create.push(item) : $upsert.push(item))
+        value = { $disconnect: {}, $create, $upsert }
+      }
+      if (value.$remove) {
         const rows = await this.select(assocTable, {
-          ...Object.fromEntries(fields.map((k, i) => [k, row[relation.fields[i]]])) as any,
-          [relation.table]: modifier.$disconnect,
+          ...Object.fromEntries(shared.map(([k, v]) => [k, getCell(row, v.field)])),
+          ...Object.fromEntries(fields.map((k, i) => [k, getCell(row, relation.fields[i])])) as any,
+          [relation.table]: value.$remove,
         }, 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])]),
+          [...shared.map(([k, v]) => r[k]), ...fields.map(x => r[x]), ...references.map(x => r[x])],
+          rows.map(r => [...shared.map(([k, v]) => getCell(r, k)), ...fields.map(x => getCell(r, x)), ...references.map(x => getCell(r, x))]),
         ))
+        await this.remove(relation.table, (r) => Eval.in(
+          [...shared.map(([k, v]) => r[v.reference]), ...relation.references.map(x => r[x])],
+          rows.map(r => [...shared.map(([k, v]) => getCell(r, k)), ...references.map(x => getCell(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)
+      if (value.$disconnect) {
+        const rows = await this.select(assocTable, {
+          ...Object.fromEntries(shared.map(([k, v]) => [k, getCell(row, v.field)])),
+          ...Object.fromEntries(fields.map((k, i) => [k, getCell(row, relation.fields[i])])) as any,
+          [relation.table]: value.$disconnect,
+        }, null).execute()
+        await this.remove(assocTable, r => Eval.in(
+          [...shared.map(([k, v]) => r[k]), ...fields.map(x => r[x]), ...references.map(x => r[x])],
+          rows.map(r => [...shared.map(([k, v]) => getCell(r, k)), ...fields.map(x => getCell(r, x)), ...references.map(x => getCell(r, x))]),
+        ))
+      }
+      if (value.$set) {
+        for (const setexpr of makeArray(value.$set) as any[]) {
+          const [query, update] = setexpr.update ? [setexpr.where, setexpr.update] : [{}, setexpr]
+          const rows = await this.select(assocTable, (r: any) => ({
+            ...Object.fromEntries(shared.map(([k, v]) => [k, getCell(row, v.field)])),
+            ...Object.fromEntries(fields.map((k, i) => [k, getCell(row, relation.fields[i])])) as any,
+            [relation.table]: query,
+          }), null).execute()
+          await this.set(relation.table,
+            (r) => Eval.in(
+              [...shared.map(([k, v]) => r[v.reference]), ...relation.references.map(x => r[x])],
+              rows.map(r => [...shared.map(([k, v]) => getCell(r, k)), ...references.map(x => getCell(r, x))]),
+            ),
+            update,
+          )
+        }
+      }
+      if (value.$create) {
+        const result: any[] = []
+        for (const item of makeArray(value.$create)) {
+          result.push(await this.createOrUpdate(relation.table, {
+            ...Object.fromEntries(relation.references.map((k, i) => [k, getCell(row, relation.fields[i])])),
+            ...item,
+          }))
+        }
+        await this.upsert(assocTable, result.map(r => ({
+          ...Object.fromEntries(shared.map(([k, v]) => [k, getCell(row, v.field)])),
+          ...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)
+      }
+      if (value.$upsert) {
+        await this.upsert(relation.table, makeArray(value.$upsert).map(r => ({
+          ...Object.fromEntries(relation.references.map((k, i) => [k, getCell(row, relation.fields[i])])),
+          ...r,
+        })))
+        await this.upsert(assocTable, makeArray(value.$upsert).map(r => ({
+          ...Object.fromEntries(shared.map(([k, v]) => [k, getCell(row, v.field)])),
+          ...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)
+      }
+      if (value.$connect) {
+        const rows = await this.get(relation.table,
+          mergeQuery(Object.fromEntries(shared.map(([k, v]) => [v.reference, getCell(row, v.field)])), value.$connect))
         await this.upsert(assocTable, rows.map(r => ({
+          ...Object.fromEntries(shared.map(([k, v]) => [k, getCell(row, v.field)])),
           ...Object.fromEntries(fields.map((k, i) => [k, row[relation.fields[i]]])),
@@ -855,3 +1079,14 @@ ...Object.fromEntries(references.map((k, i) => [k, r[relation.references[i] as any]])),
     }
+    if (Object.keys(update).length) {
+      await this.set(table, pick(model.format(row), makeArray(model.primary)), update)
+    }
   }
+
+  private hasRelation<K extends Keys<S>>(table: K, data: Create<S[K], S>): boolean
+  private hasRelation(table: any, data: any) {
+    for (const key in data) {
+      if (data[key] !== undefined && this.tables[table].fields[key]?.relation) return true
+    }
+    return false
+  }
 }

src/driver.ts

 import { Awaitable, Dict, mapValues, remove } from 'cosmokit'
-import { Context, Logger } from 'cordis'
+import { Context, Logger, Service } from 'cordis'
 import { Eval, Update } from './eval.ts'
 import { Direction, Modifier, Selection } from './selection.ts'
-import { Field, Model } from './model.ts'
+import { Field, Model, Relation } from './model.ts'
 import { Database } from './database.ts'
 import { Type } from './type.ts'
+import { FlatKeys, Keys, Values } from './utils.ts'
 
@@ -20,9 +21,10 @@ export namespace Driver {
 
-  export type Cursor<K extends string = never> = K[] | CursorOptions<K>
+  export type Cursor<K extends string = string, S = any, T extends Keys<S> = any> = K[] | CursorOptions<K, S, T>
 
-  export interface CursorOptions<K extends string> {
+  export interface CursorOptions<K extends string = string, S = any, T extends Keys<S> = any> {
     limit?: number
     offset?: number
     fields?: K[]
-    sort?: Dict<Direction, K>
+    sort?: Partial<Dict<Direction, FlatKeys<S[T]>>>
+    include?: Relation.Include<S[T], Values<S>>
   }
@@ -80,2 +82,6 @@
       database._driver = this
+      database[Service.tracker] = {
+        associate: 'database',
+        property: 'ctx',
+      }
       ctx.set('database', Context.associate(database, 'database'))
@@ -82,0 +88,0 @@ })

src/eval.ts

@@ -11,2 +11,4 @@ import { defineProperty, isNullable, mapValues } from 'cosmokit'
 
+export const isUpdateExpr: (value: any) => boolean = isEvalExpr
+
 export function isAggrExpr(expr: Eval.Expr): boolean {
@@ -35,3 +37,3 @@ return expr['$'] || expr['$select']
   : U extends (infer T extends object)[] ? Relation.Modifier<T> | Eval.Array<T, A>
-  : U extends object ? Eval.Expr<U, A> | UnevalObject<Flatten<U>>
+  : U extends object ? Eval.Expr<U, A> | UnevalObject<Flatten<U>> | Relation.Modifier<U>
   : any
@@ -80,3 +82,3 @@
     select(...args: Any[]): Expr<any[], false>
-    query<T extends object>(row: Row<T>, query: Query.Expr<T>): Expr<boolean, false>
+    query<T extends object>(row: Row<T>, query: Query.Expr<T>, expr?: Term<boolean>): Expr<boolean, false>
 
@@ -198,3 +200,3 @@ // univeral
 Eval.select = multary('select', (args, table) => args.map(arg => executeEval(table, arg)), Type.Array())
-Eval.query = (row, query) => ({ $expr: true, ...query }) as any
+Eval.query = (row, query, expr = true) => ({ $expr: expr, ...query }) as any
 
@@ -201,0 +203,0 @@ // univeral

src/model.ts

@@ -9,2 +9,3 @@ import { clone, filterKeys, isNullable, makeArray, mapValues, MaybeArray } from 'cosmokit'
 import { Selection } from './selection.ts'
+import { Create } from './database.ts'
 
@@ -26,2 +27,3 @@ const Primary = Symbol('minato.primary')
     fields: K[]
+    shared: Record<K, Keys<S[T]>>
     required: boolean
@@ -36,9 +38,10 @@ }
     fields?: MaybeArray<K>
+    shared?: MaybeArray<K> | Partial<Record<K, string>>
   }
 
   export type Include<T, S> = boolean | {
-    [P in keyof T]?: T[P] extends MaybeArray<infer U extends S> | undefined ? Include<U, S> : never
+    [P in keyof T]?: T[P] extends MaybeArray<infer U> | undefined ? U extends S ? Include<U, S> : never : never
   }
 
-  export type SetExpr<S extends object = any> = Row.Computed<S, Update<S>> | {
+  export type SetExpr<S extends object = any> = ((row: Row<S>) => Update<S>) | {
     where: Query.Expr<Flatten<S>> | Selection.Callback<S, boolean>
@@ -48,20 +51,29 @@ 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 interface Modifier<T extends object = any, S extends any = any> {
+    $create?: MaybeArray<Create<T, S>>
+    $upsert?: MaybeArray<DeepPartial<T>>
+    $set?: MaybeArray<SetExpr<T>>
+    $remove?: Query.Expr<Flatten<T>> | Selection.Callback<T, boolean>
+    $connect?: Query.Expr<Flatten<T>> | Selection.Callback<T, boolean>
+    $disconnect?: Query.Expr<Flatten<T>> | Selection.Callback<T, boolean>
   }
 
   export function buildAssociationTable(...tables: [string, string]) {
-    return '_' + tables.sort().join('To')
+    return '_' + tables.sort().join('_')
   }
 
   export function buildAssociationKey(key: string, table: string) {
-    return `${table}_${key}`
+    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'
+  export function buildSharedKey(field: string, reference: string) {
+    return [field, reference].sort().join('_')
+  }
+
+  export function parse(def: Definition, key: string, model: Model, relmodel: Model, subprimary?: boolean): [Config, Config] {
+    const shared = !def.shared ? {}
+      : typeof def.shared === 'string' ? { [def.shared]: def.shared }
+        : Array.isArray(def.shared) ? Object.fromEntries(def.shared.map(x => [x, x]))
+          : def.shared
+    const fields = def.fields ?? ((subprimary || model.name === relmodel.name || def.type === 'manyToOne'
       || (def.type === 'oneToOne' && !makeArray(relmodel.primary).every(key => !relmodel.fields[key]?.nullable)))
@@ -73,2 +85,3 @@ ? makeArray(relmodel.primary).map(x => `${key}.${x}`) : model.primary)
       fields: makeArray(fields),
+      shared: shared as any,
       references: makeArray(def.references ?? relmodel.primary),
@@ -78,2 +91,7 @@ required: def.type !== 'manyToOne' && model.name !== relmodel.name
     }
+    // remove shared keys from fields and references
+    Object.entries(shared).forEach(([k, v]) => {
+      relation.fields = relation.fields.filter(x => x !== k)
+      relation.references = relation.references.filter(x => x !== v)
+    })
     const inverse: Config = {
@@ -86,5 +104,7 @@ type: relation.type === 'oneToMany' ? 'manyToOne'
       references: relation.fields,
-      required: relation.type !== 'oneToMany' && !relation.required
+      shared: Object.fromEntries(Object.entries(shared).map(([k, v]) => [v, k])),
+      required: relation.type !== 'oneToMany'
         && relation.references.every(key => !relmodel.fields[key]?.nullable || makeArray(relmodel.primary).includes(key)),
     }
+    if (inverse.required) relation.required = false
     return [relation, inverse]
@@ -168,3 +188,3 @@ }
       | Transform<O[K], any, N>
-      | (O[K] extends object ? Relation.Definition<FlatKeys<O>> : never)
+      | (O[K] extends object | undefined ? Relation.Definition<FlatKeys<O>> : never)
   }
@@ -226,3 +246,3 @@
   export function available(field?: Field) {
-    return !!field && !field.deprecated && !field.relation
+    return !!field && !field.deprecated && !field.relation && field.deftype !== 'expr'
   }
@@ -248,3 +268,3 @@ }
 export class Model<S = any> {
-  ctx?: Context
+  declare ctx?: Context
   fields: Field.Config<S> = {}
@@ -251,0 +271,0 @@ migrations = new Map<Model.Migration, string[]>()

src/selection.ts

@@ -1,2 +0,2 @@
-import { defineProperty, Dict, filterKeys } from 'cosmokit'
+import { defineProperty, Dict, filterKeys, mapValues } from 'cosmokit'
 import { Driver } from './driver.ts'
@@ -6,3 +6,3 @@ import { Eval, executeEval, isAggrExpr, isEvalExpr } from './eval.ts'
 import { Query } from './query.ts'
-import { FlatKeys, FlatPick, Flatten, Keys, randomId, Row } from './utils.ts'
+import { FlatKeys, FlatPick, Flatten, getCell, Keys, randomId, Row } from './utils.ts'
 import { Type } from './type.ts'
@@ -65,3 +65,3 @@
     const row = createRow(ref, Eval('', [ref, `${prefix}${key}`], type), `${prefix}${key}.`, model)
-    if (Object.keys(model?.fields!).some(k => k.startsWith(`${prefix}${key}.`))) {
+    if (!field && Object.keys(model?.fields!).some(k => k.startsWith(`${prefix}${key}.`))) {
       return createRow(ref, Eval.object(row), `${prefix}${key}.`, model)
@@ -258,11 +258,16 @@ } else {
       .filter(([key, field]) => Field.available(field) && !key.startsWith(name + '.'))
-      .map(([key]) => [key, (row) => key.split('.').reduce((r, k) => r[k], row[this.ref])]))
+      .map(([key]) => [key, (row) => getCell(row[this.ref], key)]))
+    const joinFields = Object.fromEntries(Object.entries(selection.model.fields)
+      .filter(([key, field]) => Field.available(field) || Field.available(this.model.fields[`${name}.${key}`]))
+      .map(([key]) => [key,
+        (row) => Field.available(this.model.fields[`${name}.${key}`]) ? getCell(row[this.ref], `${name}.${key}`) : getCell(row[name], key),
+      ]))
     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
+        .project({ ...fields, [name]: (row) => Eval.ignoreNull(Eval.object(mapValues(joinFields, x => x(row)))) }) 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
+        .project({ ...fields, [name]: (row) => Eval.ignoreNull(Eval.object(mapValues(joinFields, x => x(row)))) }) as any
     }
@@ -269,0 +274,0 @@ }

src/utils.ts

@@ -131,5 +131,10 @@ import { Binary, Intersect, isNullable } from 'cosmokit'
 
-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 getCell(row: any, path: any): any {
+  if (path in row) return row[path]
+  if (path.includes('.')) {
+    const index = path.indexOf('.')
+    return getCell(row[path.slice(0, index)] ?? {}, path.slice(index + 1))
+  } else {
+    return row[path]
+  }
 }
@@ -136,0 +141,0 @@

New alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Fixed alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

474709

increased by16.25%

Lines of code

7441

increased by12.18%

No dependency changes