Should I use WS or HTTP for seeding database?

When deploying our preview environments, I want to seed them and have a script file I call using:

pnpm tsx ./src/server/db/seedDev.ts

pnpm tsx ./src/server/db/seedDev.ts

The script uses Drizzle. I'm curious do I have to use the http (non-pooled) connection for this? I tried using it but got:

Error: All attempts to open a WebSocket to connect to the database failed. Please refer to https://github.com/neondatabase/serverless/blob/main/CONFIG.md#websocketconstructor-typeof-websocket--undefined. Details: fetch failed

Error: All attempts to open a WebSocket to connect to the database failed. Please refer to https://github.com/neondatabase/serverless/blob/main/CONFIG.md#websocketconstructor-typeof-websocket--undefined. Details: fetch failed

7 Replies

national-gold•2y ago

hmm, do you mind sharing what your script looks like? Are you able to import the connection string from an env variable?

unwilling-turquoiseOP•2y ago

Okay so I followed what the github page mentioned actually, I added the ws config to the drizzle instance like below:

import { env } from "@/env";
import * as schema from "./schema";

import { Pool } from "@neondatabase/serverless";
import { drizzle } from "drizzle-orm/neon-serverless";
import { neonConfig } from '@neondatabase/serverless';
import ws from 'ws';
neonConfig.webSocketConstructor = ws; 

const pool = new Pool({ connectionString: env.DATABASE_URL });

export const db = drizzle(pool, { schema });

export const conn = pool;

import { env } from "@/env";
import * as schema from "./schema";

import { Pool } from "@neondatabase/serverless";
import { drizzle } from "drizzle-orm/neon-serverless";
import { neonConfig } from '@neondatabase/serverless';
import ws from 'ws';
neonConfig.webSocketConstructor = ws; 

const pool = new Pool({ connectionString: env.DATABASE_URL });

export const db = drizzle(pool, { schema });

export const conn = pool;

My script looks like this:

import "dotenv/config";
import { conn, db } from "@/server/db";
import {
  task,
  tasksForUserRole,
  terms,
  user,
  userRole,
  userToUserRoleAssignment,
} from "@/server/db/schema";
import { sql } from "drizzle-orm";
import { UI_TASKS, roles } from "@/shared/types/Tasks";
import * as dotenv from "dotenv";
dotenv.config();
import { createClerkClient } from "@clerk/fastify";
import { supabaseServerClient } from "../api/fileUpload/fileUploadRouter";
import { storageBuckets } from "@/shared/enums/storageBuckets";

const seed = async () => {
  // SEED SECURITY
  await seedTasks();
  await seedUserRoles();
  await seedUsersInClerk();
  await seedTasksToUserRoles();
  await seedRolesForUser();

  // SEED ACCOUNTING
  await seedCreditTerms();
  await seedFinancialAccounts();
  await seedTransactionAutomationData();

  // SEED BUSINESS PARTNERS
  await seedVendor();

  // SEED PRODUCT
  await seedTestProduct();

  // SEED storage buckets
  for (const bucket of Object.values(storageBuckets)) {
    await supabaseServerClient.storage.createBucket(bucket);
  }

  await db.execute(sql`CREATE EXTENSION IF NOT EXISTS pg_trgm;`);
  await conn.end();
};

import "dotenv/config";
import { conn, db } from "@/server/db";
import {
  task,
  tasksForUserRole,
  terms,
  user,
  userRole,
  userToUserRoleAssignment,
} from "@/server/db/schema";
import { sql } from "drizzle-orm";
import { UI_TASKS, roles } from "@/shared/types/Tasks";
import * as dotenv from "dotenv";
dotenv.config();
import { createClerkClient } from "@clerk/fastify";
import { supabaseServerClient } from "../api/fileUpload/fileUploadRouter";
import { storageBuckets } from "@/shared/enums/storageBuckets";

const seed = async () => {
  // SEED SECURITY
  await seedTasks();
  await seedUserRoles();
  await seedUsersInClerk();
  await seedTasksToUserRoles();
  await seedRolesForUser();

  // SEED ACCOUNTING
  await seedCreditTerms();
  await seedFinancialAccounts();
  await seedTransactionAutomationData();

  // SEED BUSINESS PARTNERS
  await seedVendor();

  // SEED PRODUCT
  await seedTestProduct();

  // SEED storage buckets
  for (const bucket of Object.values(storageBuckets)) {
    await supabaseServerClient.storage.createBucket(bucket);
  }

  await db.execute(sql`CREATE EXTENSION IF NOT EXISTS pg_trgm;`);
  await conn.end();
};

Each one of those calls uses the db instance to create rows...

other-emerald•2y ago

What platform do you deploy your preview environments to?

unwilling-turquoiseOP•2y ago

Vercel, I am using their automatic preview builds and the Neon integration. I then modified the default build script inside vercel to first run my migrations > next build > run the seed script

extended-yellow•2y ago

Did you get this working? I believe it should be fine. The only time I have seen this issue before was when the websocket pool was used in Next.js middleware

unwilling-turquoiseOP•2y ago

Yeah it's all good now

extended-yellow•2y ago

Awesome!

Gaming

Programming

Should I use WS or HTTP for seeding database?

Did you find this page helpful?