database

MySQL Integration Guide

This document explains how to integrate MySQL databases using sqlc in this Go template project. The architecture follows a clean separation of concerns with read/write separation and proper dependency injection.

Architecture Overview

Directory Structure

pkg/infra/database/
 base_config.go              # ConfigProvider interface & MySqlConfig
 db_connections.go           # Database connections & FX providers
 mysql/
    <domain>/               # Domain-specific database (e.g., user, order, product)
        ro/                 # Read-Only operations
           config.go       # RO-specific configuration
           query.sql       # Read-only SQL queries
           sqlc.yaml       # sqlc config (reads RW schema)
        rw/                 # Read-Write operations
            config.go       # RW-specific configuration
            schema.sql      # Database schema definition
            query.sql       # Read-write SQL queries
            sqlc.yaml       # sqlc config
 readme.md                   # This file

pkg/database/<domain>/
 model.go                    # Domain models (wrapper around sqlc types)
 <domain>_data_store.go      # Service interface & implementation

Key Patterns

1. RW/RO Separation: Read-Write operations are separated from Read-Only for scalability
2. Shared Schema: RO reads schema from RW to ensure consistency
3. sqlc Code Generation: SQL queries are converted to type-safe Go code
4. Service Layer: Business logic uses domain-specific interfaces, not direct sqlc types
5. Dependency Injection: Uses Uber FX for clean dependency management

Step-by-Step Integration Guide

Step 1: Create Directory Structure

For domain orders:

mkdir -p pkg/infra/database/mysql/orders/{ro,rw}
mkdir -p pkg/database/orders

Step 2: Define Database Schema

Create pkg/infra/database/mysql/orders/rw/schema.sql:

-- Database: your_db_name
-- Table: orders

CREATE TABLE orders (
    order_id VARCHAR(36) PRIMARY KEY,
    order_qty INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Step 3: Define SQL Queries

RW Queries (pkg/infra/database/mysql/orders/rw/query.sql):

-- name: CreateOrder :exec
INSERT INTO orders (order_id, order_qty, amount)
VALUES (?, ?, ?);

-- name: GetOrderByID :one
SELECT order_id, order_qty, amount, created_at, updated_at
FROM orders
WHERE order_id = ?;

-- name: GetAllOrders :many
SELECT order_id, order_qty, amount, created_at, updated_at
FROM orders
ORDER BY created_at DESC;

RO Queries (pkg/infra/database/mysql/orders/ro/query.sql):

-- name: GetOrderByID :one
SELECT order_id, order_qty, amount, created_at, updated_at
FROM orders
WHERE order_id = ?;

-- name: GetAllOrders :many
SELECT order_id, order_qty, amount, created_at, updated_at
FROM orders
ORDER BY created_at DESC;

Step 4: Configure sqlc

RW Configuration (pkg/infra/database/mysql/orders/rw/sqlc.yaml):

version: "2"
sql:
  - engine: "mysql"
    queries: "query.sql"
    schema: "schema.sql"
    gen:
      go:
        emit_result_struct_pointers: true
        emit_sql_as_comment: true
        emit_prepared_queries: true
        emit_interface: true
        emit_exact_table_names: false
        emit_empty_slices: true
        emit_json_tags: true
        package: "ordersDataStore"
        out: "."

RO Configuration (pkg/infra/database/mysql/orders/ro/sqlc.yaml):

version: "2"
sql:
  - engine: "mysql"
    queries: "query.sql"
    schema: "../rw/schema.sql"  # IMPORTANT: Read schema from RW
    gen:
      go:
        emit_result_struct_pointers: true
        emit_sql_as_comment: true
        emit_prepared_queries: true
        emit_interface: true
        emit_exact_table_names: false
        emit_empty_slices: true
        emit_json_tags: true
        package: "orderRoDataStore"
        out: "."

Step 5: Create Configuration Files

Copy and modify pkg/infra/database/mysql/user/ro/config.go to:

• pkg/infra/database/mysql/orders/ro/config.go
• pkg/infra/database/mysql/orders/rw/config.go

Update package names accordingly.

Step 6: Add Database Connections

Update pkg/infra/database/db_connections.go:

1. Add DB connections to struct:

type DbConnections struct {
    OrdersSqlDbConnection  *sql.DB
    OrderRoSqlDbConnection *sql.DB
    // Add your new connections here
    YourDomainSqlDbConnection   *sql.DB
    YourDomainRoSqlDbConnection *sql.DB
}

1. Add providers in fx.Options:

// Build all SQL connections here
fx.Provide(func(
    ordersDataStoreCfg *ordersDataStore.MySqlConfig,
    orderRoDataStoreCfg *orderRoDataStore.MySqlConfig,
    // Add your configurations
    yourDomainCfg *yourDomainDataStore.MySqlConfig,
    yourDomainRoCfg *yourDomainRoDataStore.MySqlConfig,
) (*DbConnections, error) {
    // Build connections...
    yourDomainConn, err := buildDatabaseConnection(yourDomainCfg)
    if err != nil {
        return nil, err
    }
    yourDomainRoConn, err := buildDatabaseConnection(yourDomainRoCfg)
    if err != nil {
        return nil, err
    }
    
    return &DbConnections{
        // Existing connections...
        YourDomainSqlDbConnection:   yourDomainConn,
        YourDomainRoSqlDbConnection: yourDomainRoConn,
    }, nil
}),

// Build specific querier for RW
fx.Provide(func(cf gox.CrossFunction, dbConnections *DbConnections) (yourDomainDataStore.Querier, *yourDomainDataStore.Queries, error) {
    q, err := yourDomainDataStore.Prepare(context.Background(), dbConnections.YourDomainSqlDbConnection)
    return q, q, err
}),

// Build specific querier for RO
fx.Provide(func(cf gox.CrossFunction, dbConnections *DbConnections) (yourDomainRoDataStore.Querier, *yourDomainRoDataStore.Queries, error) {
    q, err := yourDomainRoDataStore.Prepare(context.Background(), dbConnections.YourDomainRoSqlDbConnection)
    return q, q, err
}),

Step 7: Create Domain Models

Create pkg/database/orders/model.go:

package orders

import (
    "context"
    ordersDataStore "github.com/devlibx/go-template-project/pkg/infra/database/mysql/orders/rw"
    "time"
)

type Order struct {
    OrderID   string    `json:"order_id"`
    OrderQty  int       `json:"order_qty"`
    Amount    string    `json:"amount"`
    CreatedAt time.Time `json:"created_at"`
    UpdatedAt time.Time `json:"updated_at"`
}

type CreateOrderRequest struct {
    OrderID  string `json:"order_id"`
    OrderQty int    `json:"order_qty"`
    Amount   string `json:"amount"`
}

func (o *Order) FromOrder(ctx context.Context, in *ordersDataStore.Order) *Order {
    return &Order{
        OrderID:   in.OrderID,
        OrderQty:  int(in.OrderQty),
        Amount:    in.Amount,
        CreatedAt: in.CreatedAt,
        UpdatedAt: in.UpdatedAt,
    }
}

Step 8: Create Service Interface

Create pkg/database/orders/order_data_store.go:

package orders

import (
    "context"
    ordersDataStore "github.com/devlibx/go-template-project/pkg/infra/database/mysql/orders/rw"
    "github.com/devlibx/gox-base/v2"
)

type OrderDataStore interface {
    CreateOrder(ctx context.Context, arg CreateOrderRequest) error
    GetAllOrders(ctx context.Context) ([]*Order, error)
    GetOrderByID(ctx context.Context, orderID string) (*Order, error)
}

type orderDataStoreImpl struct {
    gox.CrossFunction
    querier ordersDataStore.Querier
    queries *ordersDataStore.Queries
}

func (o orderDataStoreImpl) CreateOrder(ctx context.Context, arg CreateOrderRequest) error {
    return o.querier.CreateOrder(ctx, ordersDataStore.CreateOrderParams{
        OrderID:  arg.OrderID,
        OrderQty: int32(arg.OrderQty),
        Amount:   arg.Amount,
    })
}

func (o orderDataStoreImpl) GetAllOrders(ctx context.Context) ([]*Order, error) {
    if orders, err := o.querier.GetAllOrders(ctx); err != nil {
        return nil, err
    } else {
        ret := make([]*Order, len(orders))
        for i, order := range orders {
            ret[i] = &Order{}
            ret[i].FromOrder(ctx, order)
        }
        return ret, nil
    }
}

func (o orderDataStoreImpl) GetOrderByID(ctx context.Context, orderID string) (*Order, error) {
    if order, err := o.querier.GetOrderByID(ctx, orderID); err != nil {
        return nil, err
    } else {
        ret := &Order{}
        ret.FromOrder(ctx, order)
        return ret, nil
    }
}

// Constructor function
func NewOrderDataStore(cf gox.CrossFunction, querier ordersDataStore.Querier, queries *ordersDataStore.Queries) OrderDataStore {
    return &orderDataStoreImpl{
        CrossFunction: cf,
        querier:       querier,
        queries:       queries,
    }
}

Step 9: Generate Code

Run sqlc to generate Go code:

# From project root
cd pkg/infra/database/mysql/orders/rw && sqlc generate
cd ../ro && sqlc generate

Step 10: Configure Application

Add database configuration to your app config (YAML):

orders_data_store:
  database: "your_db_name"
  host: "localhost"
  port: 3306
  user: "root"
  password: "password"
  max_idle_connections: 10
  max_open_connections: 10
  connection_max_lifetime_sec: 60
  connection_max_idle_time_sec: 60

order_ro_data_store:
  database: "your_db_name"
  host: "localhost"
  port: 3306
  user: "root"
  password: "password"
  max_idle_connections: 10
  max_open_connections: 10
  connection_max_lifetime_sec: 60
  connection_max_idle_time_sec: 60

Usage in Services

type YourService struct {
    orderStore orders.OrderDataStore
}

func (s *YourService) CreateOrder(ctx context.Context, req orders.CreateOrderRequest) error {
    return s.orderStore.CreateOrder(ctx, req)
}

func (s *YourService) GetOrder(ctx context.Context, id string) (*orders.Order, error) {
    return s.orderStore.GetOrderByID(ctx, id)
}

Important Notes

1. Schema Sharing: RO always reads schema from RW (../rw/schema.sql)
2. Package Names: Use descriptive package names (ordersDataStore, orderRoDataStore)
3. Domain Models: Always wrap sqlc types with domain-specific models
4. Interface: Services should depend on interfaces, not concrete implementations
5. Error Handling: Implement proper error handling in your data store methods
6. Connection Pooling: Configured via ConfigProvider interface
7. sqlc Generation: Run after any schema or query changes

Configuration Interface

The ConfigProvider interface ensures consistent database configuration:

type ConfigProvider interface {
    SetupDefault()
    GetDatabase() string
    GetHost() string
    GetPort() int
    GetUser() string
    GetPassword() string
    GetMaxIdleConnection() int
    GetMaxOpenConnection() int
    GetConnMaxLifetimeInSec() int
    GetConnMaxIdleTimeInSec() int
}

Common Commands

See the Makefile for common operations:

• make sqlc-generate: Generate all sqlc code
• make db-migrate: Run database migrations
• make db-create: Create database and tables

This architecture provides a clean, testable, and maintainable way to integrate MySQL databases with type-safe queries and proper separation of concerns.