Database Setup

Configure PostgreSQL database for PROVESA Web using Drizzle ORM

PROVESA Web uses PostgreSQL as its database with Drizzle ORM for schema management and migrations.

Prerequisites

• PostgreSQL 14 or higher installed
• Node.js 18+ for running Drizzle commands
• Access to create databases and users

PostgreSQL Installation

Ubuntu/Debian

# Install PostgreSQL
sudo apt update
sudo apt install postgresql postgresql-contrib

# Start PostgreSQL service
sudo systemctl start postgresql
sudo systemctl enable postgresql

macOS

# Using Homebrew
brew install postgresql@14
brew services start postgresql@14

Windows

Download and install from postgresql.org

Database Creation

1. Access PostgreSQL

# Switch to postgres user (Linux)
sudo -u postgres psql

# Or connect directly (macOS/Windows)
psql postgres

2. Create Database and User

-- Create a new user for PROVESA
CREATE USER provesa_user WITH PASSWORD 'your_secure_password';

-- Create the database
CREATE DATABASE provesa_db;

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE provesa_db TO provesa_user;

-- Exit psql
\q

3. Configure Connection String

Add the connection string to your .env file:

DATABASE_URL="postgres://provesa_user:your_secure_password@localhost:5432/provesa_db"

Connection String Format:

postgres://[user]:[password]@[host]:[port]/[database]

Drizzle Configuration

PROVESA Web uses Drizzle Kit for database migrations. The configuration is defined in drizzle.config.js:

import { defineConfig } from 'drizzle-kit';

if (!process.env.DATABASE_URL) throw new Error('DATABASE_URL is not set');

export default defineConfig({
  schema: './src/lib/server/db/schema.js',
  dialect: 'postgresql',
  dbCredentials: { url: process.env.DATABASE_URL },
  verbose: true,
  strict: true
});

Configuration Options

schema · string

Path to the database schema file. All schema definitions are exported from ./src/lib/server/db/schema.js

dialect · string

Database dialect. Set to postgresql for PostgreSQL databases.

dbCredentials.url · string

Database connection URL from the DATABASE_URL environment variable.

verbose · boolean

Enable verbose logging for database operations. Set to true for detailed output.

strict · boolean

Enable strict mode for schema validation. Set to true to catch potential issues.

Database Schema

The database schema is organized in multiple files under src/lib/server/db/schemas/:

• auth.schema.js - Better Auth tables (users, sessions, accounts)
• theme_config.schema.js - Theme customization settings
• footer.schema.js - Footer content and links
• legal_pages.schema.js - Legal pages (privacy, terms)
• hero_slides.schema.js - Homepage carousel slides
• products.schema.js - Product catalog
• nosotros_config.schema.js - About page configuration
• nosotros_page.schema.js - About page content
• sugerencias.schema.js - Customer suggestions
• sugerencias_config.schema.js - Suggestions configuration
• postulaciones.schema.js - Job applications
• empleo_sucursales.schema.js - Branch offices for jobs
• site_config.schema.js - Global site settings
• concursos.schema.js - Contests and promotions

Database Connection

The database connection is established in src/lib/server/db/index.js:

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema';
import { env } from '$env/dynamic/private';

if (!env.DATABASE_URL) throw new Error('DATABASE_URL is not set');

const client = postgres(env.DATABASE_URL);

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

This creates a Drizzle ORM instance that:

• Uses the postgres-js driver
• Loads all schemas from ./schema.js
• Validates that DATABASE_URL is set

Running Migrations

1. Generate Migration

After modifying schema files, generate a migration:

npm run db:generate

This creates migration SQL files in the drizzle/ directory.

2. Apply Migrations

Apply pending migrations to the database:

npm run db:migrate

3. Push Schema (Development)

For rapid development, push schema changes directly without migrations:

npm run db:push

Warning: This can result in data loss. Only use in development.

Seeding the Database

PROVESA Web includes seed files to populate initial data:

Admin User Seed

The admin seed (src/lib/server/db/seeds/seed-admin.js) creates the initial administrator account using these environment variables:

• ADMIN_NAME - Administrator display name
• ADMIN_EMAIL - Administrator email (login)
• ADMIN_PASSWORD - Administrator password

Theme Seed

The theme seed (src/lib/server/db/seeds/seed-theme.js) sets up default theme configuration.

Run Seeds

# Run all seeds
npm run db:seed

Drizzle Studio

Drizzle Studio provides a visual database browser:

npm run db:studio

This opens a web interface at https://local.drizzle.studio where you can:

• Browse tables and data
• Run queries
• Edit records
• View relationships

Common Operations

Reset Database

# Drop and recreate database
dropdb provesa_db
createdb provesa_db

# Run migrations and seeds
npm run db:migrate
npm run db:seed

Backup Database

pg_dump provesa_db > backup.sql

Restore Database

psql provesa_db < backup.sql

Production Considerations

Connection Pooling

For production, configure connection pooling in DATABASE_URL:

DATABASE_URL="postgres://user:pass@host:5432/db?max=20&idle_timeout=30"

SSL Connection

For remote databases, enable SSL:

DATABASE_URL="postgres://user:pass@host:5432/db?sslmode=require"

Managed PostgreSQL

Consider using managed PostgreSQL services:

• Supabase - Free tier available, includes auth and storage
• Neon - Serverless PostgreSQL with generous free tier
• Railway - Simple deployment with PostgreSQL included
• AWS RDS - Enterprise-grade managed database

Troubleshooting

Connection Refused

Error: ECONNREFUSED 127.0.0.1:5432

Solution:

# Check if PostgreSQL is running
sudo systemctl status postgresql

# Start if not running
sudo systemctl start postgresql

Authentication Failed

Error: password authentication failed for user

Solution:

• Verify username and password in DATABASE_URL
• Check PostgreSQL password with: psql -U provesa_user -d provesa_db
• Reset password if needed:
  ALTER USER provesa_user WITH PASSWORD 'new_password';
  

DATABASE_URL Not Set

Error: DATABASE_URL is not set

Solution:

• Ensure .env file exists in project root
• Verify DATABASE_URL is defined in .env
• Restart your development server

Next Steps

• Environment Variables - Complete environment variable reference
• Email Setup - Configure email for password recovery