Upsert method not working as expected in Orchid ORM

[KMJ-007]

Description

The upsert method is not functioning correctly when trying to upsert a user with associated auth provider data. The method is not visible or not working as expected.

Steps to Reproduce

1. Set up a project with Orchid ORM
2. Create the following table definitions:

User Table

```typescript
import { Queryable, Selectable, Updatable } from "orchid-orm";
import { BaseTable } from "./baseTable";
import { UserWorkspaceTable } from "./userWorkspace.table";
import { PostsTable } from "./posts.table";
import { AuthProviderTable } from "./authProvider.table";

export class UserTable extends BaseTable {
  readonly table = "user";

  columns = this.setColumns((t) => ({
    id: t.uuid().primaryKey().default(t.sqlgen_random_uuid()),
    name: t.string().trim().nullable(),
    email: t.string().trim().unique(),
    isVerified: t.boolean().default(false),
    profilePicture: t.string().nullable(),
    lastLoginAt: t.timestamp().nullable(),
    createdAt: t.timestamps().createdAt.nullable(),
    updatedAt: t.timestamps().updatedAt.nullable(),
  }));

  relations = {
    workspaces: this.hasMany(() => UserWorkspaceTable, {
      columns: ['id'],
      references: ['userId'],
    }),
    posts: this.hasMany(() => PostsTable, {
      columns: ['id'],
      references: ['authorId'],
    }),
    authProviders: this.hasMany(() => AuthProviderTable, {
      columns: ['id'],
      references: ['userId'],
    }),
  };
}

export type User = Selectable;
export type UserUpdate = Updatable;
export type UserForQuery = Queryable;

Auth Provider Table

export class AuthProviderTable extends BaseTable {
  readonly table = "auth_provider";

  columns = this.setColumns((t) => ({
    id: t.uuid().primaryKey().default(t.sqlgen_random_uuid()),
    userId: t.uuid().foreignKey(() => UserTable, 'id'),
    provider: t.string(),
    providerUserId: t.string(),
    accessToken: t.string(),
    refreshToken: t.string().nullable(),
    expiresAt: t.timestamp(),
    tokenType: t.string(),
    scope: t.string().nullable(),
    createdAt: t.timestamps().createdAt.nullable(),
    updatedAt: t.timestamps().updatedAt.nullable(),
  }));

  relations = {
    user: this.belongsTo(() => UserTable, {
      columns: ['userId'],
      references: ['id'],
    }),
  };
}

export type AuthProvider = Selectable;
export type AuthProviderUpdate = Updatable;
export type AuthProviderForQuery = Queryable;

3. Attempt to use the upsert method in a service function:

export const upsertUserWithAuthProvider = async (
  userData: Omit<User, "id" | "createdAt" | "updatedAt" | "lastLoginAt"> & {
    providerData: Omit<AuthProvider, "id" | "userId" | "createdAt" | "updatedAt">
  }
): Promise<UserType & { authProviders: AuthProvider[] }> => {
  const user = await db.user
    .select(
      "id",
      "email",
      "isVerified",
      "name",
      "profilePicture",
      "lastLoginAt",
      "createdAt",
      "updatedAt",
      {
        authProviders: (q) => q.authProviders.select(''),
      }
    )
    .findBy({ email: userData.email })
    .upsert({
      create: {
        ...userData,
        authProviders: {
          create: [userData.providerData]
        }
      },
      update: {
        ...userData,
        authProviders: {
          upsert: {
            where: {
              provider: userData.providerData.provider,
              providerUserId: userData.providerData.providerUserId
            },
            create: userData.providerData,
            update: userData.providerData,
          }
        }
      }
    });

  return user;
};

Expected Behavior

The upsert method should be available and work correctly, allowing us to upsert a user with associated auth provider data.

Actual Behavior

The upsert method is not showing up or not working as expected.

Environment

• Orchid ORM version: ^1.31.7
• Node.js version: v18.19.1

[romeerez]

The reason is that upsert does only work for belongsTo and hasOne relations. (docs).

It should be possible to add support for hasMany as in your case.

[romeerez]

This is a tough one, many things to change, I hope it's no a big deal for you to workaround this by upserting an auth provider in a separate query.

I'll add support for upsert for hasMany and hasAndBelongsToMany, but I'd like to postpone it to be done after other bugfixes and changes.