Support for database connection in development environments

[mzavattaro]

I've been trying to add support for Neon Postgresql database read/writes when in development rather than relying on PGlite due to losing the temporary database when when you exit the dev build.

I've got the DATABASE_URL in an .env.development.local file and the connection works based on my updated code below. But I still can't get it to read/write to the Neon development database.

Is there anywhere else I need to change?

import { PGlite } from '@electric-sql/pglite';
import { drizzle as drizzlePg } from 'drizzle-orm/node-postgres';
import { migrate as migratePg } from 'drizzle-orm/node-postgres/migrator';
import type { PgDatabase } from 'drizzle-orm/pg-core';
import { drizzle as drizzlePglite } from 'drizzle-orm/pglite';
import { migrate as migratePglite } from 'drizzle-orm/pglite/migrator';
import { Client } from 'pg';

import * as schema from '@/models/Schema';

import { Env } from './Env';

let client;
let drizzle: PgDatabase<any, any, any>;

if (process.env.NODE_ENV === 'development' && Env.DATABASE_URL) {
  // Use PostgreSQL client for development
  client = new Client({
    connectionString: Env.DATABASE_URL,
  });
  await client.connect();

  drizzle = drizzlePg(client, { schema });
  await migratePg(drizzle, { migrationsFolder: './migrations' });
} else if (process.env.NODE_ENV === 'production' && Env.DATABASE_URL) {
  // Use PostgreSQL client for production
  client = new Client({
    connectionString: Env.DATABASE_URL,
  });
  await client.connect();

  drizzle = drizzlePg(client, { schema });
  await migratePg(drizzle, { migrationsFolder: './migrations' });
} else {
  // Fallback to PGlite for other cases
  const global = globalThis as unknown as { client: PGlite };

  if (!global.client) {
    global.client = new PGlite();
    await global.client.waitReady;
  }

  drizzle = drizzlePglite(global.client, { schema });
  await migratePglite(drizzle, { migrationsFolder: './migrations' });
}

export const db = drizzle;

[ixartz]

I'm surprised, the changes in DB.ts and the environment files should be enough.

[mzavattaro]

I think maybe the issue is that on npm run dev it's not taking into consideration the .env.development.local file, whereas the npm run build does take into consideration the .env.producation.local file:

Edit: no, that's not it either

[ixartz]

Just merged the PR: https://github.com/ixartz/Next-js-Boilerplate/pull/290