Database seed fails due to using global sql instance in /seed/route.tsx

[bevane]

Lesson: App Router
Chapter: 6
Database provider: Supabase (It is likely the error will occur with other providers as well)

Seeding the database with /seed fails and returns the following response with 500 status.

Possible cause
From what I understand the seed functions do not make use of the scoped sql instance provided by sql.begin and instead end up using the sql instance declared at the top of /seed/route.tsx file.

 const result = await sql.begin((sql) => [
      seedUsers(),
      seedCustomers(),
      seedInvoices(),
      seedRevenue(),
    ]);

Fix
Update /seed/route.tsx to make use of the scoped sql instance supplied by sql.begin

import bcrypt from 'bcrypt';
import postgres from 'postgres';
import { invoices, customers, revenue, users } from '../lib/placeholder-data';

const sql = postgres(process.env.POSTGRES_URL!, { ssl: 'require' });

async function seedUsers(sql: postgres.TransactionSql) {
  await sql`CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`;
  await sql`
    CREATE TABLE IF NOT EXISTS users (
      id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
      name VARCHAR(255) NOT NULL,
      email TEXT NOT NULL UNIQUE,
      password TEXT NOT NULL
    );
  `;

  const insertedUsers = await Promise.all(
    users.map(async (user) => {
      const hashedPassword = await bcrypt.hash(user.password, 10);
      return sql`
        INSERT INTO users (id, name, email, password)
        VALUES (${user.id}, ${user.name}, ${user.email}, ${hashedPassword})
        ON CONFLICT (id) DO NOTHING;
      `;
    }),
  );

  return insertedUsers;
}

async function seedInvoices(sql: postgres.TransactionSql) {
  await sql`CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`;

  await sql`
    CREATE TABLE IF NOT EXISTS invoices (
      id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
      customer_id UUID NOT NULL,
      amount INT NOT NULL,
      status VARCHAR(255) NOT NULL,
      date DATE NOT NULL
    );
  `;

  const insertedInvoices = await Promise.all(
    invoices.map(
      (invoice) => sql`
        INSERT INTO invoices (customer_id, amount, status, date)
        VALUES (${invoice.customer_id}, ${invoice.amount}, ${invoice.status}, ${invoice.date})
        ON CONFLICT (id) DO NOTHING;
      `,
    ),
  );

  return insertedInvoices;
}

async function seedCustomers(sql: postgres.TransactionSql) {
  await sql`CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`;

  await sql`
    CREATE TABLE IF NOT EXISTS customers (
      id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
      name VARCHAR(255) NOT NULL,
      email VARCHAR(255) NOT NULL,
      image_url VARCHAR(255) NOT NULL
    );
  `;

  const insertedCustomers = await Promise.all(
    customers.map(
      (customer) => sql`
        INSERT INTO customers (id, name, email, image_url)
        VALUES (${customer.id}, ${customer.name}, ${customer.email}, ${customer.image_url})
        ON CONFLICT (id) DO NOTHING;
      `,
    ),
  );

  return insertedCustomers;
}

async function seedRevenue(sql: postgres.TransactionSql) {
  await sql`
    CREATE TABLE IF NOT EXISTS revenue (
      month VARCHAR(4) NOT NULL UNIQUE,
      revenue INT NOT NULL
    );
  `;

  const insertedRevenue = await Promise.all(
    revenue.map(
      (rev) => sql`
        INSERT INTO revenue (month, revenue)
        VALUES (${rev.month}, ${rev.revenue})
        ON CONFLICT (month) DO NOTHING;
      `,
    ),
  );

  return insertedRevenue;
}

export async function GET() {
  try {
    const result = await sql.begin((sql) => [
      seedUsers(sql),
      seedCustomers(sql),
      seedInvoices(sql),
      seedRevenue(sql),
    ]);

    return Response.json({ message: 'Database seeded successfully' });
  } catch (error) {
    return Response.json({ error }, { status: 500 });
  }
}

Replacing the /seed/route.tsx with above code fixes the issue and /seed script completes successfully

References:
https://www.npmjs.com/package/postgres#transactions

[denolia]

This PR (#1013) should fix it.

Meanwhile you can try to resend the seed request by updating the page and it might work because the uuid-ossp extension is already added.

[bevane]

@denolia I believe the PR is addressing a different issue due to unique constraint violation with error code 23505. In my case I had error code 26000 and had no issues with unique constraint violation. Also if you notice in my fix above, the sql`CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`; part is left untouched and still exists in every seed function and the fix works.

Since I do not see others complaining about my issue, I believe my issue is due to some weird interaction with supabase and using the global sql instance. Making use of the scoped sql instance makes the problem go away for me without touching any other parts of the code.

[yinyouq]

Your modification was extremely helpful—thank you very much for sharing it!

[lucnguyen2407]

thank you very much for sharing it!

[icyJoseph]

This is rather strange - I've done this tutorial a couple of times now, because of some other claims, like the env var names, and things work fine 🤔 I did it with both Neon and Supabase

Making use of the scoped sql instance makes the problem go away for me without touching any other parts of the code.

Is there any setting or something I may be accidentally skipping int he steps that makes it accidentally work?

[bevane]

@icyJoseph I also found it strange that it worked for others without having to use the scoped sql instance. I'll try and reproduce the issue with a fresh clone of the repo and report back.