Skip to main content

​
Overview

Macuin uses PostgreSQL 16 as its primary database, running in a Docker container. The database schema is managed through Laravel migrations, providing version control for your database structure.

​
Database Configuration

​
Docker Setup

The PostgreSQL database runs in a Docker container defined in docker-compose.yml:
docker-compose.yml
services:
  postgres:
    image: postgres:16-alpine
    container_name: laravel_postgres
    environment:
      POSTGRES_DB: laravel
      POSTGRES_USER: laravel
      POSTGRES_PASSWORD: secret
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
    networks:
      - laravel_network
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U laravel -d laravel"]
      interval: 5s
      timeout: 3s
      retries: 20
The database container includes a health check that ensures PostgreSQL is ready before the application container starts.

​
Laravel Configuration

Database connection settings are configured in config/database.php. The default connection is set via environment variables:
config/database.php
'default' => env('DB_CONNECTION', 'sqlite'),

'connections' => [
    'pgsql' => [
        'driver' => 'pgsql',
        'url' => env('DB_URL'),
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '5432'),
        'database' => env('DB_DATABASE', 'laravel'),
        'username' => env('DB_USERNAME', 'root'),
        'password' => env('DB_PASSWORD', ''),
        'charset' => env('DB_CHARSET', 'utf8'),
        'prefix' => '',
        'prefix_indexes' => true,
        'search_path' => 'public',
        'sslmode' => env('DB_SSLMODE', 'prefer'),
    ],
],

​
Environment Variables

The application container is configured with these database environment variables:
docker-compose.yml
app:
  environment:
    DB_CONNECTION: pgsql
    DB_HOST: postgres
    DB_PORT: 5432
    DB_DATABASE: laravel
    DB_USERNAME: laravel
    DB_PASSWORD: secret

Database

Name: laravel
Port: 5432

Credentials

User: laravel
Password: secret

​
Database Schema

​
Migrations

Macuin includes three core migrations that create the foundational database structure:

​
1. Users and Authentication Tables

File: 0001_01_01_000000_create_users_table.php This migration creates tables for user management and authentication: 
Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('email')->unique();
    $table->timestamp('email_verified_at')->nullable();
    $table->string('password');
    $table->rememberToken();
    $table->timestamps();
});
users Table
ColumnTypeDescription
idbigintPrimary key
namevarcharUser’s full name
emailvarcharEmail address (unique)
email_verified_attimestampEmail verification time
passwordvarcharHashed password
remember_tokenvarchar”Remember me” token
created_attimestampRecord creation time
updated_attimestampLast update time
password_reset_tokens Table
ColumnTypeDescription
emailvarcharUser email (primary key)
tokenvarcharPassword reset token
created_attimestampToken creation time
sessions Table
ColumnTypeDescription
idvarcharSession ID (primary key)
user_idbigintForeign key to users
ip_addressvarcharClient IP address
user_agenttextBrowser user agent
payloadlongtextSerialized session data
last_activityintegerUnix timestamp of last activity

​
2. Cache Tables

File: 0001_01_01_000001_create_cache_table.php Tables for Laravel’s database cache driver: 
Schema::create('cache', function (Blueprint $table) {
    $table->string('key')->primary();
    $table->mediumText('value');
    $table->integer('expiration')->index();
});
cache Table
ColumnTypeDescription
keyvarcharCache key (primary key)
valuemediumtextCached value
expirationintegerExpiration timestamp
cache_locks Table
ColumnTypeDescription
keyvarcharLock key (primary key)
ownervarcharLock owner identifier
expirationintegerLock expiration timestamp

​
3. Queue and Job Tables

File: 0001_01_01_000002_create_jobs_table.php Tables for Laravel’s queue system: 
Schema::create('jobs', function (Blueprint $table) {
    $table->id();
    $table->string('queue')->index();
    $table->longText('payload');
    $table->unsignedTinyInteger('attempts');
    $table->unsignedInteger('reserved_at')->nullable();
    $table->unsignedInteger('available_at');
    $table->unsignedInteger('created_at');
});
jobs Table
ColumnTypeDescription
idbigintPrimary key
queuevarcharQueue name (indexed)
payloadlongtextSerialized job data
attemptstinyintNumber of attempts
reserved_atintegerWhen job was reserved
available_atintegerWhen job becomes available
created_atintegerJob creation timestamp
failed_jobs Table
ColumnTypeDescription
idbigintPrimary key
uuidvarcharUnique job identifier
connectiontextQueue connection
queuetextQueue name
payloadlongtextJob payload
exceptionlongtextException details
failed_attimestampFailure timestamp

​
Models

​
User Model

The User model is located at app/Models/User.php:
app/Models/User.php
class User extends Authenticatable
{
    use HasFactory, Notifiable;

    protected $fillable = [
        'name',
        'email',
        'password',
    ];

    protected $hidden = [
        'password',
        'remember_token',
    ];

    protected function casts(): array
    {
        return [
            'email_verified_at' => 'datetime',
            'password' => 'hashed',
        ];
    }
}
The password attribute is automatically hashed when set, thanks to the 'password' => 'hashed' cast.

​
Running Migrations

To run migrations in the Docker environment: 
# Run all pending migrations
docker compose exec app php artisan migrate

# Rollback the last migration
docker compose exec app php artisan migrate:rollback

# Reset and re-run all migrations
docker compose exec app php artisan migrate:fresh

# Check migration status
docker compose exec app php artisan migrate:status
Migrations are tracked in the migrations table, which is automatically created by Laravel.

​
Database Tools

​
Connecting to PostgreSQL

Connect to the database directly: 
# Using docker compose
docker compose exec postgres psql -U laravel -d laravel

# From host machine (if psql is installed)
psql -h localhost -p 5432 -U laravel -d laravel

​
Useful PostgreSQL Commands

-- List all tables
\dt

-- Describe a table
\d users

-- Show all databases
\l

-- Quit
\q

​
Best Practices

Always Use Migrations

Never modify the database schema directly. Always create migrations for schema changes.

Test Migrations

Test rollback functionality to ensure migrations can be reversed safely.

Seed Test Data

Use seeders to populate test data for development environments.

Backup Production

Always backup production data before running migrations.

Build docs developers (and LLMs) love

Get started for freeTalk to us