Neon PostgreSQL Setup Guide

This guide explains how to set up and use Neon PostgreSQL with this project.

Overview

We’ve migrated from MongoDB to Neon PostgreSQL for better serverless compatibility and performance. Neon is a serverless PostgreSQL service that offers:

• Autoscaling
• Branching for development environments
• Serverless driver for edge environments
• SQL standard compliance

Environment Variables

Make sure these environment variables are set:

NEON_CONNECTION_STRING="postgresql://user:password@endpoint/dbname?sslmode=require"
NEON_DATABASE_URL="postgresql://user:password@endpoint/dbname?sslmode=require"

These should be set in:

• .env.development for local development
• wrangler.jsonc for Cloudflare deployment

Database Schema Setup

To set up the database schema:

1. Ensure your environment variables are configured
2. Run the setup script:

node scripts/setup-neon-db.js

This will create all necessary tables and functions in your Neon database.

Database Structure

The main tables include:

• review_views: Tracks view counts for reviews (migrated from MongoDB)
• marketing_popup_impressions: Tracks popup impressions
• marketing_popup_conversions: Tracks popup conversions
• Additional analytics tables for detailed metrics

API Usage

The database can be accessed in two ways:

1. Via middleware: Access through locals.neonDb in any Astro endpoint:

export const GET: APIRoute = async ({ locals }) => {
  const result = await locals.neonDb.query('SELECT * FROM review_views');
  return new Response(JSON.stringify(result));
};

2. Direct import: Import the utility functions:

import { executeQuery } from '../utils/db/neon-serverless';

const result = await executeQuery('SELECT * FROM review_views');

Troubleshooting

If you encounter connection issues:

1. Verify your connection string is correct
2. Check that SSL is enabled (sslmode=require)
3. Ensure your IP is allowed in Neon’s connection settings
4. For Cloudflare deployment, verify the environment variables in wrangler.jsonc