go-testfixtures/testfixtures

This approach involves creating a database template once, and then having each test create a new logical database cloned from that template. This can be done using helpers like github.com/peterldowns/pgtestdb or via database-specific SQL commands.

Pros:

• High isolation: the physical instance is shared, but logical databases are separate.
• Fast: assuming the template cloning process is efficient.
• Good for parallel execution.

Cons:

• Variable speed: cloning speed depends on the complexity of the template.
• Setup overhead: requires preparing a template, creating test databases from it, and cleaning them up.
• Engine specific: requires a database-specific library or manual implementation of cloning logic.

-- create and prepare a template database
CREATE DATABASE dbname TEMPLATE template;
-- use your dbname database during test

This approach creates a completely disposable database for every individual test. This is typically achieved using container orchestration tools like testcontainers-go or dockertest to spin up a fresh Docker container for each test.

Pros:

• Perfect isolation between tests.
• Excellent for parallel execution.
• Works with any database engine.

Cons:

• Slow setup: booting a database container can take anywhere from 1 second (e.g., postgres) to over 10 seconds.
• Infrastructure requirement: requires a docker runtime or similar container orchestration environment.

testfixtures supports several special data formats in your YAML files:

• JSON/Objects: YAML objects or arrays are converted to JSON and stored in native JSON types (like JSONB in PostgreSQL) or TEXT/VARCHAR columns.
• Binary Data: Represented as hexadecimal strings starting with 0x (e.g., 0x1234567890abcdef).
• Date/Time: Strings matching date/time formats are automatically converted to time.Time.
• Raw SQL: Prefix a value with RAW= to prevent conversion and execute the value as raw SQL (useful for functions like NOW() or uuid_generate_v4()).
• Preventing Time Conversion: Use the RAW= prefix to keep a date/time string as a literal string instead of converting it to time.Time.

This approach uses a single shared database connection for all tests. You store the database connection and the fixtures object in a global variable so each test can access them.

Pros:

• Simple setup: connect once and use everywhere.
• Performance: reusing the same fixtures object instance speeds up subsequent fixtures.Load() calls.
• Compatibility: works with any database engine.

Cons:

• No parallelization: this causes significant performance issues as the test suite grows.
• Global dependency: relies on global state.

Requirements for correctness:

• Do not use t.Parallel() in your database tests.
• If you have database tests in multiple packages, use go test -p 1 ./... to ensure only one package is tested at a time.

You can bootstrap test scenarios by dumping an existing database into YAML files using testfixtures.NewDumper. This is intended for small sample databases and may fail on large production databases.

dumper, err := testfixtures.NewDumper(
        testfixtures.DumpDatabase(db),
        testfixtures.DumpDialect("postgres"),
        testfixtures.DumpDirectory("tmp/fixtures"),
        testfixtures.DumpTables(
          "posts",
          "comments",
          "tags",
        ),
)
if err != nil {
        ...
}
if err := dumper.Dump(); err != nil {
        ...
}

Import the v3 package to use testfixtures in your Go project:

import (
        "github.com/go-testfixtures/testfixtures/v3"
)

The testfixtures CLI tool allows you to either load YAML fixtures into a database or dump the current state of a database into YAML files.

Loading Fixtures

To load fixtures, you must provide a database dialect and a connection string. You can specify the source of your fixtures using --dir (a directory), --files (specific YAML files), or --paths (a list of directories or files).

Dumping Fixtures

To dump the current database state into YAML files, use the --dump flag and specify a target directory with --dir.

Requirements

• You must provide either --dialect (-d) or --conn (-c).
• If using --dump, you must provide --dir (-D).
• If not dumping, you must provide --dir, --files, or --paths.

Supports SQL Server >= 2008 and handles IDENTITY column insertions. Ensure the user has ALTER TABLE permissions. Tested with mssql and sqlserver drivers from github.com/denisenkom/go-mssqldb.

testfixtures.New(
        ...
        testfixtures.Dialect("sqlserver"),
)

For SQLite, set the dialect to sqlite. It is recommended to create foreign keys as DEFERRABLE in your schema to prevent issues during fixture loading.

testfixtures.New(
        ...
        testfixtures.Dialect("sqlite"),
)