Skip to main content

​
Overview

BookMe uses PostgreSQL 14+ as its database. This guide covers database creation, configuration, and running migrations.

​
Prerequisites

PostgreSQL 14 or higher is required. Earlier versions may not support all features used by BookMe.
Ensure PostgreSQL is installed and running: 
# Check PostgreSQL version
psql --version

# Check if PostgreSQL is running
pg_isready

​
Database Creation

1

Create Database

Create the bookme database using createdb:
createdb bookme
Or using psql:
psql postgres

CREATE DATABASE bookme;
2

Create Database User (Optional)

For production, create a dedicated database user:
CREATE USER bookme WITH PASSWORD 'your-secure-password';
GRANT ALL PRIVILEGES ON DATABASE bookme TO bookme;
3

Verify Connection

Test the database connection:
psql -d bookme -c "SELECT version();"

​
Connection String

Configure your database connection in the .env file: 
# Using default postgres user
DB_URL=postgres://postgres:password@localhost:5432/bookme?sslmode=disable

​
Connection String Format

postgres://[username]:[password]@[host]:[port]/[database]?[parameters]
ComponentDescriptionExample
usernameDatabase userbookme
passwordUser passwordyour-password
hostDatabase hostlocalhost or db.example.com
portPostgreSQL port5432 (default)
databaseDatabase namebookme
parametersQuery parameterssslmode=disable
Use sslmode=disable for local development only. Production should use sslmode=require.

​
Database Schema

BookMe uses SQL migrations to manage the database schema. The migrations are located in sql/schema/.

​
Migration Files

sql/schema/
├── 001_users.sql           # Users table
├── 002_rooms.sql           # Meeting rooms table
├── 003_reservations.sql    # Reservations table
└── 004_populate_rooms.sql  # Initial room data

​
Running Migrations

1

Install Goose

Install the Goose migration tool:
go install github.com/pressly/goose/v3/cmd/goose@latest
2

Run Migrations

Apply all migrations:
goose -dir sql/schema postgres "postgres://username:password@localhost:5432/bookme?sslmode=disable" up
Replace the connection string with your actual database credentials.
3

Verify Migrations

Check migration status:
goose -dir sql/schema postgres "your-db-url" status
You should see all migrations marked as applied.

​
Manual Migration

If you prefer to apply migrations manually:
1

Connect to Database

psql -d bookme
2

Apply Migrations in Order

Run each SQL file in sequence:
\i sql/schema/001_users.sql
\i sql/schema/002_rooms.sql
\i sql/schema/003_reservations.sql
\i sql/schema/004_populate_rooms.sql
3

Verify Tables

List all tables:
\dt
You should see:
  • users
  • rooms
  • reservations

​
Database Schema Overview

​
Users Table

Stores user information from 42 Intra OAuth: 
CREATE TABLE users (
    id UUID PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(100) NOT NULL,
    role VARCHAR(20) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);
Roles:
  • student - Can create and cancel own reservations
  • staff - Can view all reservations and cancel any booking

​
Rooms Table

Stores available meeting rooms: 
CREATE TABLE rooms (
    id UUID PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    capacity INT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

​
Reservations Table

Stores room reservations: 
CREATE TABLE reservations (
    id UUID PRIMARY KEY,
    room_id UUID NOT NULL REFERENCES rooms(id),
    user_id UUID NOT NULL REFERENCES users(id),
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP NOT NULL,
    google_event_id VARCHAR(255),
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);
The google_event_id field stores the Google Calendar event ID when a staff member creates a reservation with Calendar sync enabled.

​
Goose Commands Reference

​
Check Migration Status

goose -dir sql/schema postgres "your-db-url" status

​
Apply All Pending Migrations

goose -dir sql/schema postgres "your-db-url" up

​
Rollback Last Migration

goose -dir sql/schema postgres "your-db-url" down

​
Reset Database

goose -dir sql/schema postgres "your-db-url" reset
goose reset will drop all tables. Use with caution, especially in production.

​
Verify Database Setup

After running migrations, verify your setup:
1

Check Tables

SELECT table_name FROM information_schema.tables 
WHERE table_schema = 'public';
Expected output:
  • users
  • rooms
  • reservations
  • goose_db_version (migration tracking)
2

Check Initial Data

Verify that rooms were populated:
SELECT id, name, capacity FROM rooms;
3

Test Application Connection

Start the BookMe server:
make run
Check the logs for successful database connection:
INFO database connection established

​
Database Configuration Tips

​
Connection Pooling

The application uses Go’s database/sql package with SQLC, which provides built-in connection pooling.

​
Performance Tuning

For production deployments, consider:
  • Indexes: The migrations include indexes on frequently queried columns
  • Connection Limits: PostgreSQL default is 100 connections
  • Backup Strategy: Set up regular automated backups

​
Backup and Restore

​
Backup

pg_dump -U bookme -d bookme > bookme_backup.sql

​
Restore

psql -U bookme -d bookme < bookme_backup.sql

​
Troubleshooting

​
Connection Refused

connection refused
Solution: Ensure PostgreSQL is running: 
pg_isready
sudo systemctl status postgresql

​
Authentication Failed

password authentication failed
Solution: Check your credentials in DB_URL. Verify user exists: 
\du

​
Database Does Not Exist

database "bookme" does not exist
Solution: Create the database: 
createdb bookme

​
Migration Failed

Solution: Check which migrations are applied: 
goose -dir sql/schema postgres "your-db-url" status
Rollback and retry: 
goose -dir sql/schema postgres "your-db-url" down
goose -dir sql/schema postgres "your-db-url" up

​
Next Steps

After setting up your database:
  1. Configure OAuth authentication
  2. Set up email notifications
  3. Start making API calls

Build docs developers (and LLMs) love

Get started for freeTalk to us