fxsql

Fx SQL Module

Fx module for sql.

• Installation
• Features
• Documentation
  • Dependencies
  • Loading
  • Configuration
  • Pool
  • Migrations
  • Seeds
  • Hooks
  • Testing

Installation

go get github.com/ankorstore/yokai/fxsql

Features

This module provides a *sql.DB to your Fx application with:

• automatic SQL requests logging and tracing
• possibility to define and apply database migrations (based on Goose)
• possibility to register database hooks
• possibility to register and execute database seeds

Documentation

Dependencies

This module is intended to be used alongside:

• the fxconfig module
• the fxlog module
• the fxtrace module

Loading

To load the module in your Fx application:

package main

import (
	"database/sql"
	
	"github.com/ankorstore/yokai/fxconfig"
	"github.com/ankorstore/yokai/fxlog"
	"github.com/ankorstore/yokai/fxsql"
	"github.com/ankorstore/yokai/fxtrace"
	"go.uber.org/fx"
)

func main() {
	fx.New(
		fxconfig.FxConfigModule, // load the module dependencies
		fxlog.FxLogModule,
		fxtrace.FxTraceModule,
		fxsql.FxSQLModule,       // load the module
		fx.Invoke(func(db *sql.DB) {
			// use the DB
			res, _ := db.Exec(...)
		}),
	).Run()
}

Configuration

This module provides the possibility to configure the SQL driver:

• mysql with go-sql-driver/mysql
• postgres with lib/pq
• sqlite with mattn/go-sqlite3

Configuration reference:

# ./configs/config.yaml
app:
  name: app
  env: dev
  version: 0.1.0
  debug: false
modules:
  sql:
    driver: mysql                                               # primary database driver (empty by default)
    dsn: "user:password@tcp(localhost:3306)/db?parseTime=true"  # primary database DSN (empty by default)
    migrations: 
      path: db/migrations       # migrations path (empty by default)
      stdout: true              # to print in stdout the migration 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"
    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"
    auxiliaries:                # auxiliary databases configurations (empty by default)
      postgres:
        driver: postgres
        dsn: "postgres://user:password@localhost:5432/db?sslmode=disable"
      sqlite:
        driver: sqlite
        dsn: ":memory:"

For security reasons, you should avoid to hardcode DSN sensible parts (like the password) in your config files, you can use the env vars placeholders instead:

# ./configs/config.yaml
modules:
  sql:
    driver: mysql
    dsn: "${MYSQL_USER}:${MYSQL_PASSWORD}@tcp(${MYSQL_HOST}:${MYSQL_PORT})/${MYSQL_DATABASE}?parseTime=True"

Available SQL operations:

• 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

Pool

This module offers the possibility to handle optionally several databases connections, via the DatabasePool:

• the primary database connection can be retrieved via Primary()
• auxiliary databases connections can be retrieved via Auxiliary(name string)

This module automatically injects the primary database connection into your Fx application as *sql.DB.

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:
        driver: postgres
        dsn: "postgres://user:password@localhost:5432/db?sslmode=disable"
      sqlite:
        driver: sqlite
        dsn: ":memory:"

• to retrieve the postgres auxiliary database connection, inject the DatabasePool and use Auxiliary("postgres")
• to retrieve the sqlite auxiliary database connection, inject the DatabasePool and use Auxiliary("sqlite")

Migrations

This module provides the possibility to run your DB schemas migrations, using Goose under the hood.

First, configure the path for your migration files:

# ./configs/config.yaml
modules:
  sql:
    migrations: db/migrations

You can then create a migration file in this path.

For example, db/migrations/00001_create_foo_table.sql:

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

-- +goose Down
DROP TABLE IF EXISTS foo;

You can then run the migrations with RunFxSQLMigration, by specifying a migration command:

package main

import (
	"database/sql"

	"github.com/ankorstore/yokai/fxconfig"
	"github.com/ankorstore/yokai/fxlog"
	"github.com/ankorstore/yokai/fxsql"
	"github.com/ankorstore/yokai/fxtrace"
	"go.uber.org/fx"
)

func main() {
	fx.New(
		fxconfig.FxConfigModule,       // load the module dependencies
		fxlog.FxLogModule,
		fxtrace.FxTraceModule,
		fxsql.FxSQLModule,             // load the module
		fxsql.RunFxSQLMigration("up"), // run migration command "up"
	).Run()
}

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

If you want to automatically shut down your Fx application after the migrations, you can use RunFxSQLMigrationAndShutdown.

Seeds

This module provides the possibility to register several Seed implementations to seed the database.

This is done via:

• the AsSQLSeed() function to register seeds
• the RunFxSQLSeeds() function to execute seeds

package main

import (
	"context"
	"database/sql"

	"github.com/ankorstore/yokai/fxconfig"
	"github.com/ankorstore/yokai/fxlog"
	"github.com/ankorstore/yokai/fxsql"
	"github.com/ankorstore/yokai/fxtrace"
	"go.uber.org/fx"
)

// example SQL seed
type ExampleSeed struct{}

func NewExampleSeed() *ExampleSeed {
	return &ExampleSeed{}
}

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 (?)", "baz")

  return err
}

// usage
func main() {
	fx.New(
		fxconfig.FxConfigModule,          // load the module dependencies
		fxlog.FxLogModule,
		fxtrace.FxTraceModule,
		fxsql.FxSQLModule,                // load the module
		fxsql.AsSQLHook(NewExampleSeed),  // register the ExampleSeed
		fxsql.RunFxSQLSeeds(),            // run all registered seeds
	).Run()
}

You can also use AsSQLSeeds() to register several seeds at once.

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

The dependencies of your seeds constructors will be autowired.

Hooks

This module provides the possibility to register several Hook implementations to extend the logic around the SQL operations.

This is done via the AsSQLHook() function:

package main

import (
	"context"

	"github.com/ankorstore/yokai/fxconfig"
	"github.com/ankorstore/yokai/fxlog"
	"github.com/ankorstore/yokai/fxsql"
	"github.com/ankorstore/yokai/fxtrace"
	"github.com/ankorstore/yokai/sql"
	"go.uber.org/fx"
)

// example SQL hook
type ExampleHook struct{}

func NewExampleHook() *ExampleHook {
	return &ExampleHook{}
}

func (h *ExampleHook) Before(ctx context.Context, event *sql.HookEvent) context.Context {
	// before SQL operation logic

	return ctx
}

func (h *ExampleHook) After(ctx context.Context, event *sql.HookEvent) {
	// after SQL operation logic
}

// usage
func main() {
	fx.New(
		fxconfig.FxConfigModule,         // load the module dependencies
		fxlog.FxLogModule,
		fxtrace.FxTraceModule,
		fxsql.FxSQLModule,               // load the module
		fxsql.AsSQLHook(NewExampleHook), // register the ExampleHook
	).Run()
}

You can also use AsSQLHooks() to register several hooks at once.

The dependencies of your hooks constructors will be autowired.

Testing

This module supports the sqlite driver, allowing the usage of an in-memory database, avoiding your tests to require a real database instance to run.

In your testing configuration:

# ./configs/config.test.yaml
modules:
  sql:
    driver: sqlite
    dsn: ":memory:"

In test mode, the module won't automatically close the database connection on shutdown, to allow database manipulation after the RunTest() execution.

You can find tests examples in this module own tests.