Database Architecture

PostgreSQL database architecture and structure. The database is managed using Drizzle ORM with automatic migrations and type-safe queries.

Type-Safe Database

The database schema is defined using Drizzle ORM, providing type safety and automatic migrations. All queries are type-safe and checked at compile time.

What You’ll Learn

This guide will help you understand:

• ✅ Database structure and main tables
• ✅ How tables are related
• ✅ Indexing strategy
• ✅ Migration management
• ✅ Query optimization
• ✅ Best practices

Overview

The PostgreSQL database stores all persistent data. The schema is defined using Drizzle ORM and managed through migrations.

Structure

Main Tables

Table	Purpose	Key Fields
users	User accounts	id, email, name, status
products	Product catalog	id, name, price, status
orders	Customer orders	id, status, total, date
order_lines	Order items	id, order_id, product_id, quantity
storage	Stock management	id, product_id, quantity, location
breeding	Breeding records	id, location_id, population
organizations	Organizations	id, name, type
sessions	User sessions	id, user_id, token, expires_at

Relations

Tables are linked by foreign keys, ensuring referential integrity:

• orders → users (customer)
• order_lines → orders and products
• storage → products
• breeding → locations and products

Learn more: Database Schema Documentation

Indexing

Indexes optimize query performance:

Index Type	Use Case	Example
Primary Key	Unique identifiers	id columns
Foreign Key	Join operations	user_id, product_id
Composite	Multi-column queries	(status, created_at)
Unique	Uniqueness constraints	email, token
Full-text	Search operations	name, description

Migrations

Drizzle migrations manage schema evolution:

# Generate migrations from schema changes
bunx drizzle-kit generate

# Apply migrations
bunx drizzle-kit migrate

# Check migration status
bunx drizzle-kit check

Best Practices:

• Always review generated migrations
• Test migrations in development first
• Use transactions for data migrations
• Keep migrations small and focused

Query Optimization

Connection Pooling

Connection pooling improves performance:

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

const connectionString = Bun.env.DATABASE_URL;
const client = postgres(connectionString, {
  max: 20, // Maximum connections
  idle_timeout: 20,
  connect_timeout: 10,
});

export const db = drizzle(client);

Query Patterns

Use efficient query patterns:

// ✅ Good: Use indexes
const products = await db.select().from(products).where(eq(products.status, 'ACTIVE')).limit(20);

// ❌ Bad: Full table scan
const allProducts = await db.select().from(products);

Quick Reference

Task	Documentation
Database schema	Database Schema
Migrations	Drizzle Kit
Query helpers	Drizzle Query Library

Next Steps

Continue learning:

1. 📊 Data Flow - Data flow patterns
2. 📈 Scalability - Scalability strategies
3. 📋 Database Schema - Complete schema documentation
4. 🛠️ Drizzle Query Library - Query helpers

Related Topics

• Data Flow - Data flow patterns
• Scalability - Scalability strategies
• Database Schema - Complete schema documentation
• Architecture Overview - System architecture