Outs PostgreSQL Database

PostgreSQL 18 database setup with Drizzle ORM, matching all Mongoose schemas from the original MongoDB implementation.

Features

• 🐘 PostgreSQL 18
• 🔥 Drizzle ORM
• 🐳 Docker & Docker Compose (dev + prod)
• 🌱 Seed scripts with demo users
• 🔐 Bcrypt password hashing
• 📊 Pre-built query functions with CLI commands
• 🎯 Type-safe schemas matching Mongoose models

Quick Start

1. Install Dependencies

bun install

2. Set Up Environment

cp .env.example .env

Edit .env if needed (default is fine for local development).

3. Start PostgreSQL with Docker

bun run docker:up

This starts PostgreSQL 18 on localhost:5432.

4. Generate and Push Database Schema

# Generate migration files
bun run db:generate

# Push schema to database
bun run db:push

5. Seed the Database

bun run db:seed

This creates demo users with bcrypted passwords:

• [email protected] / password123
• [email protected] / securepass456
• [email protected] / admin2024!
• [email protected] / mypassword789
• [email protected] / alicepass2024

6. Test Connection

bun run db:test

Schema Overview

All schemas match the Mongoose models exactly:

Account Module

• Users (account_users) - User accounts with bcrypt passwords, push devices, settings
• Subscriptions (account_subscriptions) - User subscriptions to seasons
• Leagues (account_leagues) - Leagues with previous versions support

Blueprint Module

• Blueprint Seasons (blueprint_seasons) - Season blueprints
• Blueprint Domains (blueprint_domains) - Domain configurations
• Blueprint Challenges (blueprint_challenges) - Challenge templates
• Blueprint Tournaments (blueprint_tournaments) - Tournament configurations

Calendar Module

• Seasons (calendar_seasons) - Active seasons
• Stages (calendar_stages) - Season stages
• Rounds (calendar_rounds) - Competition rounds with relevant users

Entry Module

• Challenges (entry_challenges) - User challenges with fixture slots
• Bets (entry_bets) - User bets with previous versions support

Group Module

• Standings (group_standings) - Group standings with history
• Fixtures (group_fixtures) - Group fixtures
• Cursors (group_cursors) - Cursor tracking for groups

Real Module

• Real Fixtures (real_fixtures) - Real-world fixture data
• Real Teams (real_teams) - Team information
• Real Players (real_players) - Player data
• Real Events (real_events) - Match events
• Real Channels (real_channels) - Data channels

Delivery Module

• Notifications (delivery_notifications) - Push notifications
• Delivery Sessions (delivery_sessions) - Delivery tracking
• Live Activities (delivery_liveactivities) - iOS Live Activities

Timeline Module

• Snapshots (timeline_snapshots) - Timeline snapshots

Key Differences from MongoDB

Relationships

• MongoDB uses string references (_user, _season, etc.)
• PostgreSQL maintains the same string reference pattern for compatibility
• Use joins in queries when needed (Drizzle makes this easy)

Embedded Documents

• MongoDB embedded documents → PostgreSQL JSONB columns
• Examples: pushDevices, settings, fixtureSlots, rows

Arrays

• MongoDB arrays → PostgreSQL JSONB arrays for complex types
• Simple string arrays remain as TEXT[]

Previous Versions

• MongoDB prevs array → PostgreSQL JSONB with refPrevKey
• Tables: challenges, bets, leagues

CLI Query Commands

Run queries directly from the command line:

# List all users
bun run query:users

# Get user by ID or email
bun run src/query.ts user [email protected]
bun run src/query.ts user user-john-doe

# Get user with their subscriptions
bun run src/query.ts user-subscriptions user-john-doe

# List all subscriptions
bun run query:subscriptions

# List all seasons
bun run query:seasons

# Search users
bun run src/query.ts search john

# Show statistics
bun run src/query.ts stats

Query Functions

Import and use query functions in your code:

import {
  getAllUsers,
  getUserBy,
  getUserWithSubscriptions,
  getAllSubscriptions,
  getAllSeasons,
  getUserBetsForRound,
  getChallengesForRound,
  getRealFixturesByDateRange,
  getStandingsForSeason,
  searchUsers,
  getUserStats,
} from './src/query'

// Get all active users
const users = await getAllUsers({ status: 'active', limit: 10 })

// Get user by email
const user = await getUserBy('email', '[email protected]')

// Search users
const results = await searchUsers('john')

Drizzle Studio

Visual database browser:

bun run db:studio

Opens at https://local.drizzle.studio

Docker Commands

# Start dev database
bun run docker:up

# Stop dev database
bun run docker:down

# View logs
bun run docker:logs

# Start production database
bun run docker:prod:up

# Stop production database
bun run docker:prod:down

Database Management

# Generate migration files
bun run db:generate

# Run migrations
bun run db:migrate

# Push schema directly (no migrations)
bun run db:push

# Clear all data
bun run src/migrations/clear.ts

# Re-seed database
bun run db:seed

Production Deployment

1. Copy docker-compose.prod.yml to your server
2. Create .env file with secure credentials:

POSTGRES_USER=your_user
POSTGRES_PASSWORD=your_secure_password
POSTGRES_DB=outs_prod
POSTGRES_PORT=5432

3. Start production container:

docker-compose -f docker-compose.prod.yml up -d

4. Run migrations:

DATABASE_URL=postgresql://user:pass@host:5432/db bun run db:push

Publishing to npm

This package is designed to be published to npm as a shared schema library.

bun run publish:npm

This will:

1. Clean the dist directory
2. Build TypeScript declarations and JS files
3. Publish to npm with public access

After publishing, update consuming packages:

{
  "dependencies": {
    "@outs/postgres": "^1.0.0"
  }
}

Using in Your Application

Import all schemas (not recommended for production):

import { users, subscriptions, leagues } from '@outs/postgres'

Import specific schemas (recommended for tree-shaking):

// Only import account schemas (much smaller bundle)
import { users, type User, type PushDevice } from '@outs/postgres/schemas/account'

// Only import calendar schemas
import { seasons, stages, rounds } from '@outs/postgres/schemas/calendar'

// Only import real data schemas
import { fixtures, teams, players } from '@outs/postgres/schemas/real'

Available schema modules for tree-shaking:

• @outs/postgres/schemas/account - users, subscriptions, leagues
• @outs/postgres/schemas/blueprint - seasons, domains, challenges, tournaments
• @outs/postgres/schemas/calendar - seasons, stages, rounds
• @outs/postgres/schemas/entry - challenges, bets
• @outs/postgres/schemas/group - standings, fixtures, cursors
• @outs/postgres/schemas/real - fixtures, teams, players, events, channels
• @outs/postgres/schemas/delivery - notifications, sessions, live activities
• @outs/postgres/schemas/timeline - snapshots

Example: Using in Cloudflare Workers

import { drizzle } from 'drizzle-orm/postgres-js'
import postgres from 'postgres'
import { users } from '@outs/postgres/schemas/account'
import { eq } from 'drizzle-orm'

export default {
  async fetch(request: Request, env: Env) {
    const client = postgres(env.DATABASE_URL)
    const db = drizzle(client)

    const user = await db.select().from(users).where(eq(users.email, '[email protected]')).limit(1)

    await client.end()
    return Response.json({ user })
  },
}

Example: Local database queries

import { db } from '@outs/postgres'
import { users, eq } from '@outs/postgres'

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

// Query with conditions
const user = await db.select().from(users).where(eq(users.email, '[email protected]'))

// Insert
await db.insert(users).values({
  id: 'user-new',
  url: '/api/users/user-new',
  email: '[email protected]',
  password: hashedPassword,
})

// Update
await db.update(users).set({ name: 'New Name' }).where(eq(users.id, 'user-123'))

// Delete
await db.delete(users).where(eq(users.id, 'user-123'))

Type Safety

All tables have type-safe insert and select types:

import type { User, NewUser } from '@outs/postgres'

// Select type (includes all fields including defaults)
const user: User = await db.select().from(users).where(eq(users.id, 'user-1'))

// Insert type (only required fields)
const newUser: NewUser = {
  id: 'user-new',
  url: '/api/users/user-new',
  email: '[email protected]',
}

Resolving type issues when importing from @outs-tand-ing/postgres for the PG/Drizzle schemas (best practices going forward)

1. Import drizzle utilities directly: Always use import { eq, and, or } from 'drizzle-orm' in your ops code
2. Import schemas from postgres package: import { type Round, type Season } from '@outs-tand-ing/postgres'
3. Keep versions in sync: Both packages use exact version 0.45.1 - maintain this consistency
4. After updating postgres package: Run bun install in ops to pick up changes

License

MIT