How to Handle Trigger-Based Duplicates in Snaplet?

[yilverdeja]

I'm new to using Supabase and Snaplet, and I'm facing a challenge while setting up a local development environment in collaboration with an external company. It seems there might be a configuration issue related to database seeding.

Issue

I'm working with a project that has two user tables: auth.users and public.users. Our setup is designed such that an insert into auth.users automatically triggers an insert into public.users using the same user_id. However, when I try to create a record in public.post, which references user_id, it generates a duplicate key error in public.users.

Configuration

// seed.config.ts
import { SeedPg } from '@snaplet/seed/adapter-pg';
import { defineConfig } from '@snaplet/seed/config';
import { Client } from 'pg';

export default defineConfig({
  adapter: async () => {
    const client = new Client({
      connectionString:
        'postgresql://postgres:[email protected]:54322/postgres',
    });
    await client.connect();
    return new SeedPg(client);
  },
  select: [
    '!*',  // Ignore extension tables
    'public*',  // Include all public schema tables
    'auth.users', 'auth.identities', 'auth.sessions',  // Include specific auth schema tables
  ],
});

Sample Seed Script

// seed.ts
const seed = await CreateSeedClient({dryRun:true, connect: true})
await seed.auth_users((x) => x(3))
await seed.posts((x) => x(3))

/** stdout
INSERT INTO auth.users (...) VALUES (...), (...), (...)
INSERT INTO public.users (...) VALUES (...), (...), (...)
INSERT INTO public.posts (...) VALUES (...), (...), (...)
**/

Running the generated sql causes a duplicate key error since the insert into public.users is happening twice:

1. From the automatic insert after auth.users is created
2. From the manual insert when the INSERT into public.users is executed.

Quick Solution

As I want to be able to set some of the values of public.users, I'm adapting the INSERT INTO public.users line into an upsert:

INSERT INTO public.users (...) VALUES (...), (...), (...) ON CONFLICT(id) DO UPDATE SET ...

Questions

1. How can I modify the insert command to an upsert to handle duplicates in public.users?
2. Is there a way to temporarily disable the after-insert trigger on auth.users during the seed script execution?

[yilverdeja]

Rather than manually updating the command into an upsert, this is how I updated it in my seed.ts file to automatically update the stdout output:

import { createSeedClient, MinMaxOption } from '@snaplet/seed';
import { copycat } from '@snaplet/copycat';
import capcon from 'capture-console';

// Updates the INSERT to public users as an UPSERT to avoid duplicated keys issue
function processOutput(output: string) {
  return output
    .split('\r\n')
    .map((o) => {
      const trimmed = o.trim();
      if (trimmed.startsWith('INSERT INTO public.users')) {
        return (
          trimmed.substring(0, trimmed.length - 1) +
          'ON CONFLICT(id) DO UPDATE SET name = EXCLUDED.name
        );
      }
      return trimmed;
    })
    .join('\r\n');
}

const main = async () => {
  // start intercepting and capturing stdout
  let output = '';
  capcon.startCapture(process.stdout, { quiet: true }, (stdout) => {
    output += stdout.toString();
  });

  // With DryRun set to True, the Seed Client will not execute any database operations, it will log the generated SQL queries to stdout instead
  const seed = await createSeedClient({ dryRun: true, connect: true });
  
  // Sample seeding that inserts auth_users, public_users and posts - for example purposes haven't tested it here
  await seed.public_users((x) => x(3, ({index}) =>({
    name: `Name ${index}`,
    posts: (y) => y(5),
  })))

  // stops capturing from stdout
  capcon.stopCapture(process.stdout);

  // Process and log the new captured output to stdout
  const processedOutput = processOutput(output);
  console.log(processedOutput);

  process.exit();
};
main();