Database Setup

Documentation Index

Fetch the complete documentation index at: https://mintlify.com/ashcroft08/provesa-web/llms.txt

Use this file to discover all available pages before exploring further.

​

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

​

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

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

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

​

Drizzle Configuration

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

​

Database Schema

• 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

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 });

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

​

Running Migrations

​

1. Generate Migration

npm run db:generate

​

2. Apply Migrations

npm run db:migrate

​

3. Push Schema (Development)

npm run db:push

​

Seeding the Database

​

Admin User Seed

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

​

Theme Seed

​

Run Seeds

# Run all seeds
npm run db:seed

​

Drizzle Studio

npm run db:studio

• 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

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

​

SSL Connection

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

​

Managed PostgreSQL

• 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

# Check if PostgreSQL is running
sudo systemctl status postgresql

# Start if not running
sudo systemctl start postgresql

​

Authentication Failed

• 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

• 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