Local Development with Vercel Postgres

When developing applications that use Vercel Postgres, you have two main options for local development:

1. Using a separate Vercel Postgres database for development
2. Using a local Postgres instance (e.g., with Docker)

Both approaches have their merits, and the choice depends on your specific development needs and preferences.

With this approach, you create separate Vercel Postgres databases for development and production environments. This method allows you to work with a cloud-hosted database that closely mirrors your production environment.

• Consistent environment between local development and production
• No need to set up and maintain a local database
• Easy collaboration with team members

1. Create a new Vercel Postgres database for development in your Vercel dashboard
2. Get its connection URL from the Vercel Dashboard and set it as an environment variable locally. In Next.js this is achieved by adding a POSTGRES_URL variable in a .env.development.local file.

This approach involves setting up a local Postgres instance using Docker, coupled with a WebSocket proxy to mimic the serverless nature of Vercel Postgres.

• Complete control over your local database
• No need for an internet connection during development
• Faster query execution for rapid development
• No cost associated during development

1. Install dependencies:

   pnpm

   yarn

   npm

   pnpm i @vercel/postgres @neondatabase/serverless

2. Create a docker-compose.yml file in your project root:

   services:
     postgres:
       image: postgres
       environment:
         POSTGRES_PASSWORD: password
       # Expose the Postgres port to the host machine,
       # so you can inspect and administrate it
       ports:
         - '54320:5432'
     pg_proxy:
       image: ghcr.io/neondatabase/wsproxy:latest
       environment:
         APPEND_PORT: 'postgres:5432'
         ALLOW_ADDR_REGEX: '.*'
         LOG_TRAFFIC: 'true'
       ports:
         # Expose the WebSocket proxy port to the host machine,
         # this is where @vercel/postgres will connect
         - '54330:80'
       depends_on:
         - postgres

3. Create a src/db.ts file to configure the database connection:

   import { neonConfig } from '@neondatabase/serverless';
    
   if (process.env.VERCEL_ENV === 'development') {
     neonConfig.wsProxy = (host) => `${host}:54330/v1`;
     neonConfig.useSecureWebSocket = false;
     neonConfig.pipelineTLS = false;
     neonConfig.pipelineConnect = false;
   }
    
   export * from '@vercel/postgres';

4. Set up your .env.development file:

   VERCEL_ENV=development
   POSTGRES_URL=postgresql://postgres:password@localhost:54320/postgres

5. Use the sql method in your application:

   import { sql } from '@/db';
    
   export const dynamic = 'force-dynamic';
    
   export async function GET() {
     await sql.query(`INSERT INTO users (name) VALUES ('John Doe');`);
     return Response.json({ success: true });
   }

6. Start your local Postgres instance:

   docker-compose up -d

Now you can develop your application locally, connecting to a Postgres instance that mimics the behavior of Vercel Postgres in production.

• Use separate Vercel Postgres databases if you want a setup that's closest to your production environment and easy collaboration.
• Use a local Postgres instance if you need more control, faster development cycles, or the ability to work offline.

Remember to never commit sensitive information like database credentials to your version control system. Always use environment variables or secrets management for such data.