Database Setup

Overview

Ryva uses SQLC to generate type-safe Go code from SQL queries. This approach gives you the safety of an ORM with the flexibility and performance of raw SQL.

Database Stack

PostgreSQL

Primary database with JSONB support

pgx/v5

High-performance PostgreSQL driver

SQLC

SQL-to-Go code generator

SQLC Workflow

Write SQL schema

Define tables in db/schema/*.sql

Write SQL queries

Create queries in db/queries/**/*.sql

Generate Go code

Run sqlc generate to create type-safe code

Use in repositories

Import generated code in your modules

SQLC Configuration

The sqlc.yml file configures code generation:

version: '2'
sql:
  # Waitlist domain
  - &common-settings
    schema:
      - 'db/schema/waitlist.sql'
    queries: 'db/queries/waitlist'
    engine: 'postgresql'
    gen:
      go:
        package: 'waitlist'
        out: 'internal/db/waitlist'
        sql_package: 'pgx/v5'
        emit_json_tags: true
        emit_prepared_queries: false
        emit_interface: true
        emit_exact_table_names: false
        emit_empty_slices: true
        emit_enum_valid_method: true
        emit_all_enum_values: true

  # Auth domain
  - <<: *common-settings
    schema:
      - 'db/schema/auth.sql'
      - 'db/schema/organizations.sql'
    queries: 'db/queries/auth'
    gen:
      go:
        package: 'auth'
        out: 'internal/db/auth'
        # ... same settings

Key Settings

sql_package: 'pgx/v5' - Use pgx driver types
emit_interface: true - Generate query interface for testing
emit_json_tags: true - Add JSON tags to structs
emit_empty_slices: true - Return [] instead of nil for empty results

Schema Organization

Schemas are organized by domain in db/schema/:

Auth Schema (`db/schema/auth.sql`)

-- Auth domain schema
-- Users table and related types

CREATE TABLE public.users (
    id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
    email TEXT NOT NULL UNIQUE,
    full_name TEXT,
    avatar_url TEXT,
    onboarding_completed BOOLEAN NOT NULL DEFAULT false,
    preferences JSONB NOT NULL DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Organizations Schema (`db/schema/organizations.sql`)

-- Organizations domain schema

CREATE TYPE public.organization_plan AS ENUM ('free', 'personal', 'team', 'enterprise');
CREATE TYPE public.organization_role AS ENUM ('owner', 'admin', 'member');
CREATE TYPE public.invitation_status AS ENUM ('pending', 'accepted', 'expired', 'revoked');

CREATE TABLE public.organizations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    slug TEXT UNIQUE,
    plan public.organization_plan NOT NULL DEFAULT 'free',
    settings JSONB NOT NULL DEFAULT '{}',
    created_by UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ,
    CONSTRAINT slug_format CHECK (
        slug IS NULL OR (
            slug ~ '^[a-z0-9][a-z0-9-]{1,61}[a-z0-9]$' AND
            LENGTH(slug) >= 3 AND
            LENGTH(slug) <= 63
        )
    )
);

Billing Schema (`db/schema/billing.sql`)

-- Billing domain schema

CREATE TYPE public.subscription_status AS ENUM (
    'active', 'trialing', 'past_due', 'canceled',
    'unpaid', 'incomplete', 'incomplete_expired'
);

CREATE TABLE public.subscriptions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES public.organizations(id) ON DELETE CASCADE,
    stripe_subscription_id TEXT NOT NULL UNIQUE,
    stripe_customer_id TEXT NOT NULL,
    plan public.organization_plan NOT NULL,
    status public.subscription_status NOT NULL,
    current_period_start TIMESTAMPTZ NOT NULL,
    current_period_end TIMESTAMPTZ NOT NULL,
    cancel_at_period_end BOOLEAN NOT NULL DEFAULT false,
    canceled_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Schema Best Practices

Use meaningful constraints - CHECK, UNIQUE, FOREIGN KEY
Default values - Reduce NULL handling in code
Timestamps - Always include created_at and updated_at
Soft deletes - Use deleted_at for important entities
JSONB for flexibility - Settings, preferences, metadata
Enums for status - Type-safe status values

Writing Queries

Queries are written with SQLC annotations in db/queries/:

Query Annotations

SQLC uses special comments to generate methods:

-- name: <MethodName> :<return-type>

Return types:

:one - Single row (returns struct or error)
:many - Multiple rows (returns slice)
:exec - No return value (INSERT/UPDATE/DELETE)
:execrows - Returns number of affected rows

Example Queries (`db/queries/auth/users.sql`)

Simple Query

-- name: GetUserByID :one
SELECT * FROM public.users
WHERE id = $1 LIMIT 1;

Generates:

func (q *Queries) GetUserByID(ctx context.Context, id pgtype.UUID) (User, error)

Query with Nullable Parameters

-- name: UpdateUserProfile :one
UPDATE public.users
SET
    full_name = COALESCE(sqlc.narg('full_name'), full_name),
    avatar_url = COALESCE(sqlc.narg('avatar_url'), avatar_url),
    updated_at = NOW()
WHERE id = $1
RETURNING *;

Generates:

type UpdateUserProfileParams struct {
    ID       pgtype.UUID
    FullName pgtype.Text
    AvatarUrl pgtype.Text
}

func (q *Queries) UpdateUserProfile(
    ctx context.Context,
    arg UpdateUserProfileParams,
) (User, error)

sqlc.narg() marks a parameter as nullable, generating pgtype.Text instead of string

Complex JOIN Query

-- name: GetUserWithOrganizations :one
SELECT
    u.*,
    COALESCE(
        json_agg(
            json_build_object(
                'organization_id', o.id,
                'organization_name', o.name,
                'organization_slug', o.slug,
                'organization_plan', o.plan,
                'user_role', om.role,
                'joined_at', om.joined_at
            )
            ORDER BY om.joined_at DESC
        ) FILTER (WHERE o.id IS NOT NULL),
        '[]'
    ) as organizations
FROM public.users u
LEFT JOIN public.organization_members om ON om.user_id = u.id
LEFT JOIN public.organizations o ON o.id = om.organization_id AND o.deleted_at IS NULL
WHERE u.id = $1
GROUP BY u.id;

Returns user with embedded JSON array of organizations.

Existence Check

-- name: UserExists :one
SELECT EXISTS(
    SELECT 1 FROM public.users WHERE id = $1
) AS exists;

Generates:

func (q *Queries) UserExists(ctx context.Context, id pgtype.UUID) (bool, error)

List with Pagination

-- name: ListUsers :many
SELECT * FROM public.users
ORDER BY created_at DESC
LIMIT $1 OFFSET $2;

Generates:

type ListUsersParams struct {
    Limit  int32
    Offset int32
}

func (q *Queries) ListUsers(
    ctx context.Context,
    arg ListUsersParams,
) ([]User, error)

Generated Code Structure

For each domain, SQLC generates:

internal/db/auth/
├── db.go              # New() constructor
├── models.go          # Struct types for tables and enums
├── querier.go         # Interface with all query methods
└── users.sql.go       # Generated query implementations

Using Generated Code

import (
    "github.com/egeuysall/ryva/internal/db/auth"
    "github.com/jackc/pgx/v5/pgxpool"
)

// Create queries instance
pool := pgxpool.New(ctx, databaseURL)
queries := auth.New(pool)

// Execute query
user, err := queries.GetUserByID(ctx, userID)
if err != nil {
    if errors.Is(err, pgx.ErrNoRows) {
        // Handle not found
    }
    return err
}

Database Connection

The central database manager in internal/db/db.go:

type DB struct {
    pool     *pgxpool.Pool
    Waitlist *waitlist.Queries
    Auth     *auth.Queries
    // Add other domains as needed
}

func New(ctx context.Context, databaseURL string) (*DB, error) {
    pool, err := pgxpool.New(ctx, databaseURL)
    if err != nil {
        return nil, fmt.Errorf("failed to create connection pool: %w", err)
    }

    if err := pool.Ping(ctx); err != nil {
        pool.Close()
        return nil, fmt.Errorf("failed to ping database: %w", err)
    }

    return &DB{
        pool:     pool,
        Waitlist: waitlist.New(pool),
        Auth:     auth.New(pool),
    }, nil
}

Transactions

Handle multi-step operations with transactions:

// WithTx executes a function within a database transaction
func (db *DB) WithTx(ctx context.Context, fn func(*TxQueries) error) error {
    tx, err := db.pool.Begin(ctx)
    if err != nil {
        return fmt.Errorf("failed to begin transaction: %w", err)
    }

    defer func() {
        _ = tx.Rollback(ctx)  // Safe if already committed
    }()

    txQueries := &TxQueries{
        Waitlist: waitlist.New(tx),
        Auth:     auth.New(tx),
    }

    if err := fn(txQueries); err != nil {
        return err
    }

    if err := tx.Commit(ctx); err != nil {
        return fmt.Errorf("failed to commit transaction: %w", err)
    }

    return nil
}

Usage in repositories:

func (r *Repository) CreateUserAndOrg(ctx context.Context, email string) error {
    return r.db.WithTx(ctx, func(tx *db.TxQueries) error {
        // Create user
        user, err := tx.Auth.CreateUser(ctx, params)
        if err != nil {
            return err
        }

        // Create organization
        _, err = tx.Organizations.CreateOrganization(ctx, orgParams)
        return err
    })
}

Type Conversions

Handle PostgreSQL-specific types:

UUID Conversion

import (
    "github.com/google/uuid"
    "github.com/jackc/pgx/v5/pgtype"
)

// UUID to pgx UUID
func UUIDToPgUUID(id uuid.UUID) pgtype.UUID {
    return pgtype.UUID{Bytes: id, Valid: true}
}

// pgx UUID to UUID
func PgUUIDToUUID(id pgtype.UUID) uuid.UUID {
    return uuid.UUID(id.Bytes)
}

Nullable Text

// String pointer to pgx Text
func StringToPgText(s *string) pgtype.Text {
    if s == nil {
        return pgtype.Text{Valid: false}
    }
    return pgtype.Text{String: *s, Valid: true}
}

// pgx Text to string pointer
func PgTextToString(t pgtype.Text) *string {
    if !t.Valid {
        return nil
    }
    return &t.String
}

JSONB Handling

import "encoding/json"

// Serialize to JSONB
func SerializePreferences(prefs map[string]any) ([]byte, error) {
    return json.Marshal(prefs)
}

// Parse from JSONB
func ParsePreferences(data []byte) map[string]any {
    var prefs map[string]any
    if err := json.Unmarshal(data, &prefs); err != nil {
        return map[string]any{}
    }
    return prefs
}

Error Handling

Convert database errors to application errors:

import (
    "errors"
    "github.com/jackc/pgx/v5"
    "github.com/egeuysall/ryva/internal/shared/apperrors"
)

func (r *Repository) GetUserByID(ctx context.Context, id uuid.UUID) (*User, error) {
    dbUser, err := r.queries.GetUserByID(ctx, UUIDToPgUUID(id))
    if err != nil {
        if errors.Is(err, pgx.ErrNoRows) {
            return nil, apperrors.NotFound("User not found")
        }
        return nil, apperrors.DatabaseError("failed to get user").WithInternal(err)
    }
    return FromDB(&dbUser)
}

Adding a New Domain

Create schema file

Create db/schema/mydomain.sql with table definitions:

CREATE TABLE public.my_table (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Create queries directory

Create db/queries/mydomain/ with query files:

-- name: GetByID :one
SELECT * FROM public.my_table WHERE id = $1;

-- name: Create :one
INSERT INTO public.my_table (name) VALUES ($1) RETURNING *;

Add to sqlc.yml

- <<: *common-settings
  schema:
    - 'db/schema/mydomain.sql'
  queries: 'db/queries/mydomain'
  gen:
    go:
      package: 'mydomain'
      out: 'internal/db/mydomain'

Generate code

sqlc generate

Update DB manager

Add to internal/db/db.go:

type DB struct {
    pool     *pgxpool.Pool
    MyDomain *mydomain.Queries
}

func New(ctx context.Context, databaseURL string) (*DB, error) {
    // ...
    return &DB{
        pool:     pool,
        MyDomain: mydomain.New(pool),
    }, nil
}

Development Workflow

# 1. Edit schema or queries
vim db/schema/auth.sql
vim db/queries/auth/users.sql

# 2. Generate Go code
sqlc generate

# 3. Verify generated code
ls internal/db/auth/

# 4. Use in repository
vim internal/modules/auth/repository.go

Common Patterns

Soft Deletes

CREATE TABLE public.organizations (
    id UUID PRIMARY KEY,
    name TEXT NOT NULL,
    deleted_at TIMESTAMPTZ
);

-- Always filter out deleted records
-- name: ListOrganizations :many
SELECT * FROM public.organizations
WHERE deleted_at IS NULL
ORDER BY created_at DESC;

-- Soft delete
-- name: DeleteOrganization :exec
UPDATE public.organizations
SET deleted_at = NOW()
WHERE id = $1;

Audit Trails

CREATE TABLE public.audit_log (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    action TEXT NOT NULL,
    resource_type TEXT NOT NULL,
    resource_id UUID NOT NULL,
    changes JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Enum Types

CREATE TYPE public.user_role AS ENUM ('admin', 'member', 'viewer');

CREATE TABLE public.users (
    id UUID PRIMARY KEY,
    role public.user_role NOT NULL DEFAULT 'viewer'
);

SQLC generates:

type UserRole string

const (
    UserRoleAdmin  UserRole = "admin"
    UserRoleMember UserRole = "member"
    UserRoleViewer UserRole = "viewer"
)

Best Practices

Query Design

Keep queries simple and focused
Use indexes for frequently queried columns
Avoid N+1 queries with JOINs or json_agg
Use EXPLAIN ANALYZE to optimize slow queries

Type Safety

Let SQLC generate types - don’t manually define structs
Use nullable types (pgtype.Text) for optional fields
Always convert between pgx and domain types in repositories

Error Handling

Check for pgx.ErrNoRows and return apperrors.NotFound
Wrap other errors with context using apperrors.DatabaseError
Log internal errors but don’t expose to API

Next Steps

Module Architecture

Use generated queries in repositories

Backend Structure

Understand overall project organization

Documentation Index

​Overview

​Database Stack

PostgreSQL

pgx/v5

SQLC

​SQLC Workflow

​SQLC Configuration

​Schema Organization

​Auth Schema (db/schema/auth.sql)

​Organizations Schema (db/schema/organizations.sql)

​Billing Schema (db/schema/billing.sql)

​Writing Queries

​Query Annotations

​Example Queries (db/queries/auth/users.sql)

​Generated Code Structure

​Using Generated Code

​Database Connection

​Transactions

​Type Conversions

​UUID Conversion

​Nullable Text

​JSONB Handling

​Error Handling

​Adding a New Domain

​Development Workflow

​Common Patterns

​Best Practices

​Next Steps

Module Architecture

Backend Structure

Overview

Database Stack

SQLC Workflow

SQLC Configuration

Schema Organization

Auth Schema (`db/schema/auth.sql`)

Organizations Schema (`db/schema/organizations.sql`)

Billing Schema (`db/schema/billing.sql`)

Writing Queries

Query Annotations

Example Queries (`db/queries/auth/users.sql`)

Generated Code Structure

Using Generated Code

Database Connection

Transactions

Type Conversions

UUID Conversion

Nullable Text

JSONB Handling

Error Handling

Adding a New Domain

Development Workflow

Common Patterns

Best Practices

Next Steps