Implementing the unnest function in pg-mem

[lukasholzer]

Hi folks, first of all this project is super awesome!

I'm currently in the progress of switching my tests to use pg-mem and I'm currently hitting a roadblock with many to many relationships where I'm using the unnest operation to create the joining table.

I'm using pg-mem with kysely and in my code I have this function:

export const createUserWithOrganization = (
  database: ProductDatabase,
  user: Insertable<UsersTable>,
  organizations: string[],
) =>
  database
    .with('created_user', (db) =>
      db.insertInto('users').values(user).returningAll(),
    )
    .with('created_relationships', (db) =>
      db
        .insertInto('organizations_users')
        .columns(['user_id', 'organization_id'])
        .expression((expressionBuilder) =>
          expressionBuilder
            .selectFrom('created_user')
            .select([
              'created_user.id as user_id',
              sql`unnest(${organizations}::text[])`.as('organization_id'),
            ]),
        ),
    )
    .selectFrom('created_user')
    .selectAll()
    .execute();

I'm wondering if someone had already faced this issue on implementing the unnest function in pg-mem.

I've already implemented some easy functions like uuid generation but this one seems more tricky!
Would appreciate your help folks!