SQL Module

Overview

Yokai provides a fxsql module, allowing your application to interact with databases.

It wraps the sql module, based on database/sql.

It comes with:

• primary and auxiliary databases connections management
• automatic SQL operations logging and tracing
• possibility to define and execute database migrations (based on Goose)
• possibility to register and execute database seeds
• possibility to register database hooks around the SQL operations

Since this module enables you to work with sql.DB, you keep full control on your database interactions with SQL, and you can enhance your developer experience with tools like SQLC.

Installation

First install the module:

go get github.com/ankorstore/yokai/fxsql

Then activate it in your application bootstrapper:

internal/bootstrap.go

package internal

import (
    "github.com/ankorstore/yokai/fxcore"
    "github.com/ankorstore/yokai/fxsql"
)

var Bootstrapper = fxcore.NewBootstrapper().WithOptions(
    // load fxsql module
    fxsql.FxSQLModule,
    // ...
)

Configuration

Configuration overview:

configs/config.yaml

modules:
  sql:
    driver: mysql                                               # primary database driver
    dsn: "user:password@tcp(localhost:3306)/db?parseTime=true"  # primary database DSN
    migrations:
      path: db/migrations  # migrations path (empty by default)
      stdout: true         # to print in stdout the migration execution logs (disabled by default)
    log:
      enabled: true        # to enable SQL queries logging (disabled by default)
      level: debug         # to configure SQL queries logs level (debug by default)
      arguments: true      # to add SQL queries arguments to logs (disabled by default)
      exclude:             # to exclude SQL operations from logging (empty by default)
        - "connection:ping"
        - "connection:reset-session"
    trace:
      enabled: true        # to enable SQL queries tracing (disabled by default)
      arguments: true      # to add SQL queries arguments to trace spans (disabled by default)
      exclude:             # to exclude SQL operations from tracing (empty by default)
        - "connection:ping"
        - "connection:reset-session"
    auxiliaries:         # auxiliary databases configurations (empty by default)
      postgres-db:
        driver: postgres
        dsn: "postgres://user:password@localhost:5432/db?sslmode=disable"
      sqlite-db:
        driver: sqlite
        dsn: ":memory:"

This module provides the possibility to configure several database connections:

• a primary database connection, that will be made injectable by default as sql.DB
• optionally some auxiliary databases connections, accessible via an injectable database connection pool

This module supports the following database connection driver types:

• mysql for MySQL databases (based on go-sql-driver/mysql)
• postgres for PostgreSQL databases (based on lib/pq)
• sqlite for SQLite databases (based on mattn/go-sqlite3)

You can find below the list of supported SQL operations:

ConnectionStatementTransaction

• connection:begin
• connection:begin-tx
• connection:exec
• connection:exec-context
• connection:query
• connection:query-context
• connection:prepare
• connection:prepare-context
• connection:ping
• connection:reset-session
• connection:close

• statement:exec
• statement:exec-context
• statement:query
• statement:query-context

• transaction:commit
• transaction:rollback

Usage

Primary database connection

This module will automatically make the primary database connection sql.DB instance available in Yokai dependency injection system.

To access it, inject it where needed, for example in a repository:

internal/repository/foo.go

package repository

import (
    "context"
    "database/sql"
)

type FooRepository struct {
    db *sql.DB
}

func NewFooRepository(db *sql.DB) *FooRepository {
    return &FooRepository{
        db: db,
    }
}

func (r *FooRepository) Insert(ctx context.Context, bar string) (sql.Result, error) {
    return r.db.ExecContext(ctx, "INSERT INTO foo (bar) VALUES ?", bar)
}

Like any other services, the FooRepository needs to be registered to have its dependencies autowired:

internal/register.go

package internal

import (
    "github.com/foo/bar/internal/repository"
    "go.uber.org/fx"
)

func Register() fx.Option {
    return fx.Options(
        // register the FooRepository
        fx.Provide(repository.NewFooRepository),
        // ...
    )
}

Database connections pool

This module provides a DatabasePool, allowing to retrieve:

• the primary database connection via Primary() (also made available as sql.DB by default)
• auxiliary databases connections via Auxiliary(name string)

To retrieve auxiliary databases connections, inject the DatabasePool where required, and use Auxiliary(name string) to retrieve the desired connection.

For example:

# ./configs/config.yaml
modules:
  sql:
    auxiliaries:
      postgres-db:
        driver: postgres
        dsn: "postgres://user:password@localhost:5432/db?sslmode=disable"

To access them, you just need to inject the pool where needed, for example in a repository:

internal/repository/foo.go

package repository

import (
    "context"
    "database/sql"

    "github.com/ankorstore/yokai/fxsql"
)

type FooRepository struct {
    pool *fxsql.DatabasePool
}

func NewFooRepository(pool *fxsql.DatabasePool) *FooRepository {
    return &FooRepository{
        pool: pool,
    }
}

func (r *FooRepository) Insert(ctx context.Context, bar string) (sql.Result, error) {
    // primary connection
    res, err := r.pool.Primary().DB().ExecContext(ctx, "INSERT INTO foo (bar) VALUES ?", bar)
    // auxiliary "postgres-db" connection
    res, err = r.pool.Auxiliary("postgres-db").DB().ExecContext(ctx, "INSERT INTO baz (qux) VALUES ?", bar)
    // ...
    return res, err
}

Migrations

This module provides the possibility to run your primary database migrations, using Goose under the hood.

Migrations creation

You can configure where to find your migration files:

configs/config.yaml

modules:
  sql:
  migrations:
    path: db/migrations  # migrations path
    stdout: true         # to print in stdout the migration execution logs

And create them following Goose SQL migrations conventions:

db/migrations/00001_create_foo_table.sql

-- +goose Up
CREATE TABLE IF NOT EXISTS foo (
    id  INTEGER NOT NULL PRIMARY KEY,
    bar VARCHAR(255)
);

-- +goose Down
DROP TABLE IF EXISTS foo;

Migrations execution

This is done via:

• RunFxSQLMigration(command, args) to execute a migration command
• RunFxSQLMigrationAndShutdown(command, args) to execute a migration command and shut down

Available migration commands:

• up: migrate the DB to the most recent version available
• up-by-one: migrate the DB up by 1
• up-to VERSION: migrate the DB to a specific VERSION
• down: roll back the version by 1
• down-to VERSION: roll back to a specific VERSION
• redo: re-run the latest migration
• reset: roll back all migrations
• status: dump the migration status for the current DB
• version: print the current version of the database
• create NAME [sql|go]: creates new migration file with the current timestamp
• fix: apply sequential ordering to migrations
• validate: check migration files without running them

At bootstrap

To run the migrations automatically at bootstrap, you just need to call RunFxSQLMigration():

internal/bootstrap.go

package internal

import (
    "github.com/ankorstore/yokai/fxcore"
    "github.com/ankorstore/yokai/fxsql"
)

// ...

func Run(ctx context.Context) {
    Bootstrapper.WithContext(ctx).RunApp(
        // run database migrations
        fxsql.RunFxSQLMigration("up"),
        // ...
    )
}

func RunTest(tb testing.TB, options ...fx.Option) {
    // ...

    Bootstrapper.RunTestApp(
        tb,
        // test options
        fx.Options(options...),
        // run database migrations for tests
        fxsql.RunFxSQLMigration("up"),
        // ...
    )
}

Dedicated command

A preferable way to run migrations is via a dedicated command.

You can create it in the cmd/ directory of your application:

cmd/migrate.go

package cmd

import (
    "github.com/ankorstore/yokai/fxcore"
    "github.com/ankorstore/yokai/fxsql"
    "github.com/spf13/cobra"
    "go.uber.org/fx"
)

func init() {
    rootCmd.AddCommand(migrateCmd)
}

var migrateCmd = &cobra.Command{
    Use:   "migrate",
    Short: "Run database migrations",
    Args:  cobra.MinimumNArgs(1),
    Run: func(cmd *cobra.Command, args []string) {
        fxcore.
            NewBootstrapper().
            WithContext(cmd.Context()).
            WithOptions(
                fx.NopLogger,
                // modules
                fxsql.FxSQLModule,
                // migrate and shutdown
                fxsql.RunFxSQLMigrationAndShutdown(args[0], args[1:]...),
            ).
            RunApp()
    },
}

You can then execute this command when needed by running for example app migrate up from a dedicated step in your deployment pipeline.

Seeds

This module provides the possibility to seed your primary database, useful for testing.

Seeds creation

This module provides the Seed interface for your seeds implementations.

For example:

db/seeds/example.go

package seeds

import (
    "context"
    "database/sql"

    "github.com/ankorstore/yokai/config"
)

type ExampleSeed struct {
    config *config.Config
}

func NewExampleSeed(config *config.Config) *ExampleSeed {
    return &ExampleSeed{
        config: config,
    }
}

func (s *ExampleSeed) Name() string {
    return "example-seed"
}

func (s *ExampleSeed) Run(ctx context.Context, db *sql.DB) error {
    _, err := db.ExecContext(
        ctx,
        "INSERT INTO foo (bar) VALUES (?)",
        s.config.GetString("config.seeds.example-seed.value"),
    )

    return err
}

Seeds registration

Once your seeds are created, you can register them via:

• AsSQLSeed() to register a seed
• AsSQLSeeds() to register several seeds at once

internal/register.go

package internal

import (
    "github.com/foo/bar/db/seeds"
    "github.com/ankorstore/yokai/fxsql"
    "go.uber.org/fx"
)

func Register() fx.Option {
    return fx.Options(
        // register the ExampleSeed
        fxsql.AsSQLSeed(seeds.NewExampleSeed),
        // ...
    )
}

The dependencies of your seeds constructors will be autowired.

Seeds execution

Once your seeds are registered, you can execute them via RunFxSQLSeeds():

internal/example_test.go

package internal_test

import (
    "testing"

    "github.com/ankorstore/yokai/fxsql"
    "github.com/foo/bar/internal"
    "go.uber.org/fx"
)

func TestExample(t *testing.T) {
    internal.RunTest(
        t,
        // apply seeds
        fxsql.RunFxSQLSeeds(),
    )

    // ...
}

You can also call for example RunFxSQLSeeds("example-seed", "other-seed") to run only specific seeds, in provided order.

Hooks

This module provides the possibility to extend the logic around the SQL operations via a hooking mechanism.

Hooks will be applied on all connections (primary and auxiliaries).

Hooks creation

This module provides the Hook interface for your hooks implementations.

For example:

db/hooks/example.go

package hooks

import (
    "context"

    "github.com/ankorstore/yokai/config"
    "github.com/ankorstore/yokai/sql"
)

type ExampleHook struct {
    config *config.Config
}

func NewExampleHook(config *config.Config) *ExampleHook {
    return &ExampleHook{
        config: config,
    }
}

func (h *ExampleHook) Before(ctx context.Context, event *sql.HookEvent) context.Context {
    // before SQL operation logic
    if h.config.GetBool("config.hooks.example-hook.enabled") {
        // ...
    }

    return ctx
}

func (h *ExampleHook) After(ctx context.Context, event *sql.HookEvent) {
    // after SQL operation logic
    if h.config.GetBool("config.hooks.example-hook.enabled") {
        // ...
    }
}

Hooks registration

Once your hooks are created, you can register them via:

• AsSQLHook() to register a hook
• AsSQLHooks() to register several hooks at once

internal/register.go

package internal

import (
    "github.com/foo/bar/db/hooks"
    "github.com/ankorstore/yokai/fxsql"
    "go.uber.org/fx"
)

func Register() fx.Option {
    return fx.Options(
        // register the ExampleHook
        fxsql.AsSQLHook(hooks.NewExampleHook),
        // ...
    )
}

The dependencies of your hooks constructors will be autowired.

Hooks execution

Yokai collects all registered hooks and executes them automatically on each SQL operations.

Health Check

This module provides a ready to use SQLProbe, to be used by the health check module.

It will perform a ping to the configured database connection to ensure it is healthy.

You just need to register it:

internal/register.go

package internal

import (
    "github.com/ankorstore/yokai/fxhealthcheck"
    "github.com/ankorstore/yokai/sql/healthcheck"
    "go.uber.org/fx"
)

func Register() fx.Option {
    return fx.Options(
        // register the SQLProbe probe for startup, liveness and readiness checks
        fxhealthcheck.AsCheckerProbe(healthcheck.NewSQLProbe),
        // ...
    )
}

Logging

You can enable the SQL queries automatic logging of your database connections with modules.sql.log.enabled=true:

configs/config.yaml

modules:
  sql:
    log:
      enabled: true    # to enable SQL queries logging (disabled by default)
      level: debug     # to configure SQL queries logs level (debug by default)
      arguments: true  # to add SQL queries arguments to logs (disabled by default)
      exclude:         # to exclude SQL operations from logging (empty by default)
        - "connection:ping"
        - "connection:reset-session"

As a result, in your application logs:

DBG system:"mysql" operation:"connection:exec-context" latency="54.32µs" query="INSERT INTO foo (bar) VALUES (?)" lastInsertId=0 rowsAffected=0

If needed, you can log the SQL queries arguments with modules.sql.log.arguments=true:

DBG system:"mysql" operation:"connection:exec-context" latency="54.32µs" query="INSERT INTO foo (bar) VALUES (?)" arguments=[map[Name: Ordinal:1 Value:baz]] lastInsertId=0 rowsAffected=0

Tracing

You can enable the SQL queries automatic tracing of your database connections with modules.sql.trace.enabled=true:

configs/config.yaml

modules:
  sql:
    trace:
      enabled: true    # to enable SQL queries tracing (disabled by default)
      arguments: true  # to add SQL queries arguments to trace spans (disabled by default)
      exclude:         # to exclude SQL operations from tracing (empty by default)
        - "connection:ping"
        - "connection:reset-session"

As a result, in your application trace spans attributes:

db.system: "mysql"
db.statement: "INSERT INTO foo (bar) VALUES (?)"
db.lastInsertId: 0
db.rowsAffected: 0
...

If needed, you can trace the SQL queries arguments with modules.sql.trace.arguments=true:

db.system: "mysql"
db.statement: "INSERT INTO foo (bar) VALUES (?)"
db.statement.arguments: "[{Name: Ordinal:1 Value:baz}]"
db.lastInsertId: 0
db.rowsAffected: 0
...

Testing

This module provide support for the sqlite databases, making your tests portable (in memory, no database required):

configs/config.test.yaml

modules:
  sql:
    driver: sqlite   # use sqlite driver
    dsn: ":memory:"  # in memory

The fxgomysqlserver Yokai contrib module is also available if you want to run an embed MySQL server for your tests.

You can then retrieve your components using the sql.DB, and make actual database operations:

internal/example_test.go

package internal_test

import (
    "testing"

    "github.com/ankorstore/yokai/fxsql"
    "github.com/foo/bar/internal"
    "github.com/foo/bar/internal/repository"
    "go.uber.org/fx"
)

func TestExample(t *testing.T) {
    var fooRepository repository.FooRepository

    internal.RunTest(
        t,
        // apply migrations in sqlite in-memory
        fxsql.RunFxSQLMigration("up"),
        // apply seeds in sqlite in-memory
        fxsql.RunFxSQLSeeds(),
        // retrieve your components
        fx.Populate(&fooRepository),
    )

    // ...
}