Database Setup

SkyTeam ROBLOX uses PostgreSQL with Drizzle ORM for type-safe database operations. This guide covers database setup, schema management, and migrations.

Database Technology Stack

PostgreSQL

Relational database

Drizzle ORM

Type-safe ORM (v0.40.0)

Drizzle Kit

Schema management (v0.30.5)

Prerequisites

Ensure you have:

PostgreSQL server installed and running
DATABASE_URL configured in .env file
Dependencies installed via pnpm install

Initial Database Setup

Create Database

Create a PostgreSQL database for SkyTeam:

# Connect to PostgreSQL
psql -U postgres

# Create database and user
CREATE DATABASE skyteam;
CREATE USER skyteam_user WITH PASSWORD 'your_password';
GRANT ALL PRIVILEGES ON DATABASE skyteam TO skyteam_user;

# Exit psql
\q

Configure Connection String

Update your .env file with the database URL:

.env

DATABASE_URL="postgresql://skyteam_user:your_password@localhost:5432/skyteam"

Push Schema to Database

Initialize the database schema using Drizzle Kit:

pnpm --filter @skyteam/database db:push

This command:

Reads the schema from packages/database/src/schema.ts
Creates all tables, columns, and relationships
Applies constraints and indexes

You should see output confirming table creation for users, airlines, brands, flights, and more.

Verify Database

Open Drizzle Studio to verify the schema:

pnpm db:studio

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

Browse tables and relationships
View and edit data
Test queries

Database Schema Overview

SkyTeam’s database schema is defined in packages/database/src/schema.ts. Here’s an overview of the main tables:

Users Table

Stores user information and mileage points.

users {
  userId: text (PK)           // User identifier
  username: text (unique)     // Unique username
  displayName: text           // Display name
  miles: integer (default 0)  // SkyTeam miles balance
  avatarUrl: text?            // Profile avatar URL
  createdAt: timestamp        // Account creation
  updatedAt: timestamp        // Last update
}

Airlines Table

Represents airline organizations in SkyTeam.

airlines {
  airlineId: text (PK)        // Airline identifier
  name: text                  // Airline name
  token: text (unique)        // API authentication token
  inviteLink: text            // Discord invite link
  serverId: text              // Discord server ID
  createdAt: timestamp        // Creation time
  updatedAt: timestamp        // Last update
}

Brands Table

Airline brands with IATA/ICAO codes and styling.

brands {
  brandId: text (PK)          // Brand identifier
  airlineId: text (FK)        // Parent airline
  name: text                  // Brand name
  iata: text (unique)         // IATA code (e.g., "AA")
  icao: text (unique)         // ICAO code (e.g., "AAL")
  callsign: text (unique)     // Radio callsign
  isPrimary: boolean          // Primary brand flag
  logoUrl: text               // Brand logo URL
  accentColor: text           // Primary color (#hex)
  secondaryColor: text        // Secondary color
  elementColor: text          // UI element color
}

Flights Table

Tracks active and completed flights.

flights {
  id: uuid (PK)               // Flight identifier
  code: text                  // Flight number (e.g., "AA100")
  gameId: text                // ROBLOX game ID
  aircraft: text              // Aircraft type
  airlineId: text (FK)        // Operating airline
  brandId: text (FK)          // Operating brand
  startTime: timestamp        // Scheduled start
  endTime: timestamp?         // Scheduled end
  codeshareAirlineId: text?   // Codeshare partner
  departure: text             // Departure airport code
  arrival: text               // Arrival airport code
  startedAt: timestamp?       // Actual start time
  discordEventLink: text      // Discord event URL
}

Flight Passengers Table

Many-to-many relationship between flights and users.

flightPassengers {
  flightId: uuid (FK)         // Flight reference
  userId: text (FK)           // User reference
  miles: integer              // Miles earned
  joinedAt: timestamp         // Join time
}

Miles Transactions Table

Audit log for all mileage changes.

milesTransactions {
  id: uuid (PK)               // Transaction ID
  userId: text (FK)           // User reference
  amount: integer             // Miles (+ earn, - spend)
  type: text                  // "earn" or "spend"
  source: text                // Source (e.g., "flight")
  flightId: uuid? (FK)        // Related flight
  productId: text?            // Related product
  note: text?                 // Additional notes
  createdAt: timestamp        // Transaction time
}

Miles Products Table

Rewards available for purchase with miles.

milesProducts {
  productId: text (PK)        // Product identifier
  airlineId: text (FK)        // Offering airline
  name: text                  // Product name
  description: text?          // Product description
  priceMiles: integer         // Cost in miles
  active: boolean             // Available for purchase
  createdAt: timestamp        // Creation time
}

Database Relationships

Database Package Structure

The database package (@skyteam/database) is organized as follows:

packages/database/
├── src/
│   ├── db.ts              # Database client initialization
│   ├── schema.ts          # Schema definitions
│   ├── users.ts           # User operations
│   ├── airlines.ts        # Airline operations
│   ├── brands.ts          # Brand operations
│   ├── flights.ts         # Flight operations
│   ├── flightPassengers.ts
│   ├── miles.ts           # Miles operations
│   ├── utils.ts           # Helper functions
│   └── index.ts           # Package exports
├── drizzle.config.ts      # Drizzle Kit configuration
└── package.json

Drizzle Configuration

The Drizzle configuration is defined in packages/database/drizzle.config.ts:

drizzle.config.ts

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",
  schema: "./src/schema.ts",
  out: "./drizzle",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
  verbose: true,
  strict: true,
});

Database Operations

Available Commands

# Push schema changes to database (no migration files)
pnpm --filter @skyteam/database db:push

# Or from root
pnpm db:push

Database Client Usage

The database client is initialized in packages/database/src/db.ts:

db.ts

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";

const connectionString = process.env.DATABASE_URL;
if (!connectionString) {
  throw new Error("DATABASE_URL environment variable is not set");
}

const client = postgres(connectionString);
export const db = drizzle(client);

Usage in applications:

import { db, users } from "@skyteam/database";

// Query users
const allUsers = await db.select().from(users);

// Insert user
const newUser = await db.insert(users).values({
  userId: "user_123",
  username: "pilot123",
  displayName: "Captain Pilot",
  miles: 0,
});

// Update user miles
await db.update(users)
  .set({ miles: 1000 })
  .where(eq(users.userId, "user_123"));

Schema Management

Making Schema Changes

Edit Schema

Modify packages/database/src/schema.ts to add/change tables:

export const newTable = pgTable("new_table", {
  id: uuid("id").defaultRandom().primaryKey(),
  name: text("name").notNull(),
});

Generate Migration (Optional)

Create migration files for versioned changes:

pnpm --filter @skyteam/database db:generate

This creates SQL migration files in packages/database/drizzle/.

Push Changes

Apply changes to database:

pnpm --filter @skyteam/database db:push

db:push is great for development but can be destructive. Use migrations for production.

Development vs Production

Development
Production

Use db:push for rapid prototyping:

pnpm db:push

Pros:

Fast iteration
No migration files

Cons:

Can lose data
No version history

Use db:generate + db:migrate for versioned migrations:

# Generate migration
pnpm --filter @skyteam/database db:generate

# Apply migration
pnpm --filter @skyteam/database db:migrate

Pros:

Safe data migrations
Version control
Rollback capability

Cons:

Requires more setup

Connection Pooling

The postgres client provides automatic connection pooling. Configure pool settings:

const client = postgres(connectionString, {
  max: 10,           // Maximum connections
  idle_timeout: 20,  // Close idle connections after 20s
  connect_timeout: 10, // Connection timeout
});

Database Backup & Restore

Backup

# Backup entire database
pg_dump -U skyteam_user -d skyteam -f backup.sql

# Backup with data
pg_dump -U skyteam_user -d skyteam -F c -f backup.dump

# Backup schema only
pg_dump -U skyteam_user -d skyteam -s -f schema.sql

Restore

# Restore from SQL file
psql -U skyteam_user -d skyteam -f backup.sql

# Restore from dump file
pg_restore -U skyteam_user -d skyteam backup.dump

Troubleshooting

Connection refused errors

Check PostgreSQL is running:

# Linux
sudo systemctl status postgresql
sudo systemctl start postgresql

# macOS
brew services list
brew services start postgresql@14

# Windows
# Check Services app for PostgreSQL service

Authentication failed

Verify credentials in DATABASE_URL:

# Test connection manually
psql "postgresql://user:password@localhost:5432/skyteam"

Reset password if needed:

ALTER USER skyteam_user WITH PASSWORD 'new_password';

Schema push fails

Common issues:Foreign key violations:

# Drop and recreate database
dropdb skyteam
createdb skyteam
pnpm db:push

Permission errors:

GRANT ALL PRIVILEGES ON DATABASE skyteam TO skyteam_user;
GRANT ALL ON SCHEMA public TO skyteam_user;

Drizzle Studio won't start

Ensure DATABASE_URL is set:

# Load .env and start studio
dotenv -- pnpm db:studio

Get Started

Setup & Deployment

Core Features

Applications

Database Technology Stack

PostgreSQL

Drizzle ORM

Drizzle Kit

Prerequisites

Initial Database Setup

Database Schema Overview

Users Table

Airlines Table

Brands Table

Flights Table

Flight Passengers Table

Miles Transactions Table

Miles Products Table

Database Relationships

Database Package Structure

Drizzle Configuration

Database Operations

Available Commands

Database Client Usage

Schema Management

Making Schema Changes

Development vs Production

Connection Pooling

Database Backup & Restore

Backup

Restore

Troubleshooting

Next Steps

Docker Setup

API Development

Build docs developers (and LLMs) love

Get Started

Setup & Deployment

Core Features

Applications

​Database Technology Stack

PostgreSQL

Drizzle ORM

Drizzle Kit

​Prerequisites

​Initial Database Setup

​Database Schema Overview

​Users Table

​Airlines Table

​Brands Table

​Flights Table

​Flight Passengers Table

​Miles Transactions Table

​Miles Products Table

​Database Relationships

​Database Package Structure

​Drizzle Configuration

​Database Operations

​Available Commands

​Database Client Usage

​Schema Management

​Making Schema Changes

​Development vs Production

​Connection Pooling

​Database Backup & Restore

​Backup

​Restore

​Troubleshooting

​Next Steps

Docker Setup

API Development

Build docs developers (and LLMs) love

Database Technology Stack

Prerequisites

Initial Database Setup

Database Schema Overview

Users Table

Airlines Table

Brands Table

Flights Table

Flight Passengers Table

Miles Transactions Table

Miles Products Table

Database Relationships

Database Package Structure

Drizzle Configuration

Database Operations

Available Commands

Database Client Usage

Schema Management

Making Schema Changes

Development vs Production

Connection Pooling

Database Backup & Restore

Backup

Restore

Troubleshooting

Next Steps