Database Setup

This guide covers database configuration, migrations, and management for Jubiloop.

Overview

Jubiloop uses PostgreSQL as its primary database with Lucid ORM (built into AdonisJS) for database interactions.

Initial Setup

1. Database Creation

The database is automatically created when you start the local infrastructure:

cd infra/local_dev
docker compose up -d

This creates a PostgreSQL instance with:

• Database name: jubiloop_dev_db
• Username: postgres
• Password: postgres
• Port: 5433

2. Environment Configuration

Configure your database connection in apps/server/.env:

DB_CONNECTION=pg
DB_HOST=localhost
DB_PORT=5433
DB_USER=postgres
DB_PASSWORD=postgres
DB_DATABASE=jubiloop_dev_db

3. Run Migrations

# From project root
npm run migration:run

# Or directly from server
cd apps/server
node ace migration:run

4. Seed Database (Optional)

# From project root
npm run db:seed

# Or directly from server
cd apps/server
node ace db:seed

Database Schema

Core Tables

Users

• Stores user accounts and authentication data
• Includes profile information
• Handles authentication tokens

Events

• Main event data
• Event details and settings
• Links to organizers and participants

Organizations

• Company/organization accounts
• Can have multiple users
• Event ownership

Relationships

Users ←→ Organizations (many-to-many)
Organizations → Events (one-to-many)
Users ←→ Events (many-to-many through participants)

Migrations

Creating Migrations

cd apps/server

# Create a new migration
node ace make:migration create_table_name

# Create with specific action
node ace make:migration add_column_to_users --table=users

Migration Structure

import { BaseSchema } from '@adonisjs/lucid/schema'

export default class extends BaseSchema {
  protected tableName = 'users'

  public async up() {
    this.schema.createTable(this.tableName, (table) => {
      table.uuid('id').primary()
      table.string('email', 255).notNullable().unique()
      table.string('password', 180).notNullable()
      table.timestamp('created_at').notNullable()
      table.timestamp('updated_at').nullable()
    })
  }

  public async down() {
    this.schema.dropTable(this.tableName)
  }
}

Running Migrations

# Run all pending migrations
node ace migration:run

# Rollback last batch
node ace migration:rollback

# Rollback all
node ace migration:rollback --batch=0

# Check migration status
node ace migration:status

# Dry run (see SQL without executing)
node ace migration:run --dry-run

Models

Creating Models

cd apps/server

# Create a model
node ace make:model User

# Create model with migration
node ace make:model Event -m

# Create model with controller
node ace make:model Organization -c

Model Example

import { DateTime } from 'luxon'
import { BaseModel, column, hasMany } from '@adonisjs/lucid/orm'
import Event from './event.js'

export default class User extends BaseModel {
  @column({ isPrimary: true })
  declare id: number

  @column()
  declare email: string

  @column({ serializeAs: null })
  declare password: string

  @column.dateTime({ autoCreate: true })
  declare createdAt: DateTime

  @column.dateTime({ autoCreate: true, autoUpdate: true })
  declare updatedAt: DateTime

  @hasMany(() => Event)
  declare events: HasMany<typeof Event>
}

Database Operations

Query Examples

// Find by ID
const user = await User.find(1)

// Find by attributes
const user = await User.findBy('email', 'user@example.com')

// Create
const user = await User.create({
  email: 'new@example.com',
  password: 'hashed_password',
})

// Update
user.name = 'Updated Name'
await user.save()

// Delete
await user.delete()

// Complex queries
const activeEvents = await Event.query()
  .where('status', 'active')
  .where('start_date', '>', DateTime.now().toSQL())
  .preload('organizer')
  .orderBy('start_date', 'asc')
  .limit(10)

Relationships

// Eager loading
const user = await User.query().preload('events').where('id', 1).first()

// Lazy loading
const user = await User.find(1)
await user.load('events')

// Creating with relationships
const event = await user.related('events').create({
  title: 'New Event',
  date: DateTime.now(),
})

Database Management

Accessing Database

Via Docker:

docker exec -it jubiloop_local_dev_postgres psql -U postgres -d jubiloop_dev_db

Via GUI Tools:

• Host: localhost
• Port: 5433
• Username: postgres
• Password: postgres
• Database: jubiloop_dev_db

Common SQL Commands

-- List all tables
\dt

-- Describe table structure
\d users

-- View table data
SELECT * FROM users;

-- Check migration status
SELECT * FROM adonis_schema;

-- Database size
SELECT pg_database_size('jubiloop_dev_db');

Backup and Restore

Backup:

docker exec jubiloop_local_dev_postgres pg_dump -U postgres jubiloop_dev_db > backup.sql

Restore:

docker exec -i jubiloop_local_dev_postgres psql -U postgres jubiloop_dev_db < backup.sql

Environment-Specific Configuration

Development

• Uses Docker PostgreSQL
• Port 5433 to avoid conflicts
• Migrations run on startup
• Sample data via seeders

Production

• Managed PostgreSQL on DigitalOcean
• Automated backups
• Connection pooling
• SSL required

Testing

• Separate test database
• Transactions for test isolation
• Automatic cleanup

Best Practices

Schema Design

1. Use UUID v7 for primary keys - Better database performance with timestamp-based ordering
2. Add indexes for frequently queried columns
3. Use foreign key constraints
4. Add NOT NULL constraints where appropriate
5. Use appropriate column types

UUID v7 Implementation

Jubiloop uses UUID v7 instead of UUID v4 for improved database performance:

• Location: apps/server/app/models/base_model_with_uuid.ts
• Package: Official uuid npm package (v7 function)
• Benefits:
  • Timestamp-based ordering improves index performance
  • Better locality of reference in B-tree indexes
  • Maintains uniqueness while providing chronological sorting
  • Reduces index fragmentation compared to random UUID v4

All models that need UUID primary keys extend BaseModelWithUuid which automatically assigns UUID v7 values before creation:

import BaseModelWithUuid from '#models/base_model_with_uuid'

export default class Event extends BaseModelWithUuid {
  // UUID v7 will be automatically assigned on creation
  @column({ isPrimary: true })
  declare id: string
}

Performance

1. Use eager loading to avoid N+1 queries
2. Add database indexes for search columns
3. Use query scopes for complex queries
4. Monitor slow queries
5. Use connection pooling in production

Security

1. Never store plain text passwords
2. Use parameterized queries (ORM handles this)
3. Validate input before database operations
4. Use database roles with minimal permissions
5. Enable SSL for production connections

Troubleshooting

Connection Refused

1. Check if PostgreSQL is running:

   docker compose ps

2. Verify connection settings in .env

3. Check PostgreSQL logs:

   docker compose logs postgres

Migration Errors

1. Check migration syntax
2. Verify database connection
3. Look for dependency issues
4. Use --dry-run to preview SQL

Performance Issues

1. Check for missing indexes
2. Analyze query execution plans
3. Monitor connection pool usage
4. Review eager loading strategy