database

The database package provides PostgreSQL connection management with built-in migration support using sqlx.

Core Components:

• Database: Database connection with migration orchestration capabilities.
• Migration: Represents a single database migration with Up and Down SQL statements.
• New(connection string) (*Database, error): Creates a new PostgreSQL database connection.
• ParseMigrations(fsys fs.FS) ([]Migration, error): Parses SQL migration files from a filesystem.

Full package docs at pkg.go.dev

Step-by-step guide

1. Create a database connection

   db, err := database.New("postgres://user:password@localhost:5432/mydb?sslmode=disable")
   if err != nil {
       log.ErrorContext(ctx, "failed to connect", "error", err)
       os.Exit(1)
   }

   This establishes a PostgreSQL connection using the provided connection string.

2. Define your repository with migrations

   type UserRepository struct {
       db *sqlx.DB
   }
   
   //go:embed *.sql
   var migrations embed.FS
   
   func (r *UserRepository) Migrations() fs.FS {
       return migrations
   }

   The Migrations() method returns a filesystem containing SQL migration files.

3. Create SQL migration files

   -- 001_create_users_table.sql
   -- +migrate Up
   CREATE TABLE IF NOT EXISTS users (
       id SERIAL PRIMARY KEY,
       name TEXT NOT NULL,
       email TEXT UNIQUE NOT NULL
   );
   
   -- +migrate Down
   DROP TABLE users;

   Migration files use -- +migrate Up and -- +migrate Down markers to define schema changes.

4. Register your repository

   userRepo := NewUserRepository(db.Connection())
   db.RegisterRepository("users", userRepo)

   Repositories that implement the Migrations() method returning fs.FS will automatically be registered for migration.

5. Run migrations

   err = db.Migrate(ctx)
   if err != nil {
       log.ErrorContext(ctx, "failed to run migrations", "error", err)
       os.Exit(1)
   }

   This applies all pending migrations and tracks them in the platforma_migrations table.

6. Use the database connection

   var users []User
   err := db.Connection().SelectContext(ctx, &users, "SELECT id, name, email FROM users")

   Access the underlying sqlx.DB connection through the Connection() method.

Using with Application

The Database type does not implement the Runner interface. Instead, it’s registered with the application using the dedicated database registration methods:

app := application.New()

// Register database connection
app.RegisterDatabase("main", db)

// Register repositories with the database
app.RegisterRepository("main", "users", userRepo)

// Register services
app.RegisterService("api", httpServer)

// Run the application (starts services)
app.Run(ctx)

Migrations are NOT run automatically when starting services. You must run migrations separately using the CLI:

# Run migrations first
myapp migrate

# Then start services
myapp run

If migrations fail, the application will exit with an error.

Migration file format

Migration files use special markers to separate up and down SQL:

-- +migrate Up
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- +migrate Down
DROP TABLE users;

Marker	Required	Description
-- +migrate Up	Yes	Marks the start of the up migration SQL
-- +migrate Down	No	Marks the start of the down migration SQL

The migration ID is derived from the filename without the .sql extension. For example, 001_create_users.sql becomes ID 001_create_users.

Migration tracking

Migrations are tracked in the platforma_migrations table with three columns:

Column	Description
repository	Name used in RegisterRepository
id	Migration ID derived from the SQL filename
timestamp	When the migration was applied

If a migration fails, previously applied migrations in the same batch are reverted using their Down SQL.

Complete example

001_create_users_table.sql

-- +migrate Up
CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL
);

-- +migrate Down
DROP TABLE users;

main.go

package main

import (
  "context"
  "embed"
  "fmt"
  "io/fs"
  "os"

  "github.com/jmoiron/sqlx"
  "github.com/platforma-dev/platforma/database"
  "github.com/platforma-dev/platforma/log"
)

// User represents a user in our system
type User struct {
  ID    int    `db:"id"`
  Name  string `db:"name"`
  Email string `db:"email"`
}

// UserRepository handles database operations for users
type UserRepository struct {
  db *sqlx.DB
}

// NewUserRepository creates a new UserRepository with the given connection
func NewUserRepository(db *sqlx.DB) *UserRepository {
  return &UserRepository{db: db}
}

//go:embed *.sql
var migrations embed.FS

func (r *UserRepository) Migrations() fs.FS {
  return migrations
}

// Create inserts a new user into the database
func (r *UserRepository) Create(ctx context.Context, name, email string) (User, error) {
  var user User
  err := r.db.QueryRowxContext(ctx,
    "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id, name, email",
    name, email,
  ).StructScan(&user)
  return user, err
}

// GetAll retrieves all users from the database
func (r *UserRepository) GetAll(ctx context.Context) ([]User, error) {
  var users []User
  err := r.db.SelectContext(ctx, &users, "SELECT id, name, email FROM users")
  return users, err
}

func main() {
  ctx := context.Background()

  // Get database connection string from environment variable
  // Example: "postgres://user:password@localhost:5432/mydb?sslmode=disable"
  connStr := os.Getenv("DATABASE_URL")
  if connStr == "" {
    log.ErrorContext(ctx, "DATABASE_URL environment variable is not set")
    os.Exit(1)
  }

  // Create new database connection
  db, err := database.New(connStr)
  if err != nil {
    log.ErrorContext(ctx, "failed to connect to database", "error", err)
    os.Exit(1)
  }

  log.InfoContext(ctx, "connected to database")

  // Create repository and register it with the database
  userRepo := NewUserRepository(db.Connection())
  db.RegisterRepository("users", userRepo)

  // Run migrations
  err = db.Migrate(ctx)
  if err != nil {
    log.ErrorContext(ctx, "failed to run migrations", "error", err)
    os.Exit(1)
  }

  log.InfoContext(ctx, "migrations completed successfully")

  // Create a new user
  user, err := userRepo.Create(ctx, "John Doe", "john@example.com")
  if err != nil {
    log.ErrorContext(ctx, "failed to create user", "error", err)
    os.Exit(1)
  }

  log.InfoContext(ctx, "user created", "id", user.ID, "name", user.Name, "email", user.Email)

  // Get all users
  users, err := userRepo.GetAll(ctx)
  if err != nil {
    log.ErrorContext(ctx, "failed to get users", "error", err)
    os.Exit(1)
  }

  fmt.Printf("Found %d user(s):\n", len(users))
  for _, u := range users {
    fmt.Printf("  - ID: %d, Name: %s, Email: %s\n", u.ID, u.Name, u.Email)
  }
}