dbtestify

package module
v0.2.1 Latest Latest
Warning

This package is not in the latest version of its module.

Go to latest
Published: Jul 25, 2026 License: AGPL-3.0 Imports: 16 Imported by: 0

README

DBTestify: test helper tool for the system that uses database

icon

Go Reference GitHub Workflow Status

Install

Binary

Binary is available for Linux, macOS, and Windows. You can download the latest release from GitHub Releases.

Container

Install:

$ docker pull ghcr.io/shibukawa/dbtestify:latest 

Execute: :

# Execute CLI command
$ docker run --rm -it -e "DBTESTIFY_CONN=(url)" ghcr.io/shibukawa/dbtestify:latest dbtestify [command] [args...]

# Publish API Server
$ docker run --rm -it -p 8080:8000-e "DBTESTIFY_CONN=(url)" ghcr.io/shibukawa/dbtestify:latest dbtestify http -p 8000
For Go users

Install:

$ go get -tool github.com/shibukawa/dbtestify/cmd/dbtestify

Execute:

$ go tool dbtestify [command] [args...]

Usage

Database Connection

It supports PostgreSQL, MySQL, SQLite. Set the following use the following connection string to DBTESTIFY_CONN environment variable (or --db flag in CLI).

  • PostgreSQL: detail
    • postgres://user:pass@localhost:5432/dbname?sslmode=disable
  • MySQL: detail
    • mysql://root:pass@tcp(localhost:3306)/foo?tls=skip-verify
  • SQLite: detail
    • sqlite://file:dbfilename.db

Then prepare the data set file in YAML format. This file is used for seeding the database and asserting the data in the database.

member:
- { id: 1, name: Frank, email: frank@example.com }
- { id: 2, name: Grace, email: grace@example.com }
- { id: 3, name: Heidi, email: heidi@example.com }
- { id: 4, name: Ivan }

belonging:
- { member_id: 1, group_id: 1 }
- { member_id: 2, group_id: 1 }
- { member_id: 3, group_id: 2 }
- { member_id: 4, group_id: 2 }

group:
- { id: 1, name: "Group A" }
- { id: 2, name: "Group B" }

You can seed/assert the data set from CLI, HTTP API, Go library

CLI

Use the following command to seed the data set into the database:

$ dbtestify seed testdata/users.yaml
$ dbtestify assert testdata/users.yaml
HTTP API

http subcommand launches a HTTP server.

$ dbtestify http -p 8000 ../testdata
dbtestify API server

        GET  http://localhost:8000/api/list                    : Show data set file list
        POST http://localhost:8000/api/seed/{data set path}    : Seed database content with the specified data set
        GET  http://localhost:8000/api/assert/{data set path}  : Assert database content with the specified data set
        start receiving at :8000
$ curl -X POST http://localhost:8000/api/seed/users.yaml
$ curl http://localhost:8000/api/assert/users.yaml
const DBTESTIFY_URL = 'http://localhost:8000/api';

test.beforeEach(async ({ request }) => {
    await request.post(`${DBTESTIFY_URL}/api/seed/users.yaml`)
});

test("add user", async ({ page, request }) => {
    await page.getByRole("button", { name: "Add User" }).click();
    await request.get(`${DBTESTIFY_URL}/api/assert/users.yaml`);
});
Go Unit Tests

github.com/shibukawa/dbtestify/assertdb packages provides a helper for Go unit tests. Just calling assertdb.SeedDataSet and assertdb.AssertDB functions in your test code.

import (
	"embed"
	"os"
	"testing"

	"github.com/shibukawa/dbtestify/assertdb"
)

//go:embed dataset/*
var dataSet embed.FS

const dbtestifyConn = "sqlite://file:database.db"

func TestUsage(t *testing.T) {
    assertdb.SeedDataSet(t, dbtestifyConn, dataSet, "initial.yaml", nil)

    // some logic that modifies the database

    assertdb.AssertDB(t, dbtestifyConn, dataSet, "expect.yaml", nil)
Using an existing *sql.DB

The core github.com/shibukawa/dbtestify package registers no database/sql driver. Opening a connection from a connection string now lives in github.com/shibukawa/dbtestify/connect:

import "github.com/shibukawa/dbtestify/connect"

dbc, err := connect.NewDBConnector(ctx, "sqlite://file:database.db")

Breaking change: dbtestify.NewDBConnector moved to connect.NewDBConnector. Only the import path and the package qualifier change; the signature and behavior are identical.

When your application or framework already owns the pool, hand it over instead. dbtestify never closes a pool it did not open, and this is the only way to reach an in-memory SQLite database:

dbc, err := dbtestify.NewDBConnectorFromDB(db, dbtestify.SQLiteDialect)

// or, from the unit test helpers
assertdb.SeedDataSetDB(t, db, dbtestify.SQLiteDialect, dataSet, "initial.yaml", nil)
assertdb.AssertDBWithDB(t, db, dbtestify.SQLiteDialect, dataSet, "expect.yaml", nil)

dbtestify.ParseDialect derives the dialect from a DSN scheme or driver name.

Data Set Reference

Data set definition is a key feature of dbtestify. Data set is defined in YAML format. Basic structure is like this:

member:
- { id: 1, name: Frank, email: frank@example.com }
- { id: 2, name: Grace, email: grace@example.com }
- { id: 3, name: Heidi, email: heidi@example.com }
- { id: 4, name: Ivan }

belonging:
- { member_id: 1, group_id: 1 }
- { member_id: 2, group_id: 1 }
- { member_id: 3, group_id: 2 }
- { member_id: 4, group_id: 2 }

group:
- { id: 1, name: "Group A" }
- { id: 2, name: "Group B" }

Data set is used in two purposes. There are special options:

  • For seeding of test data in unit tests This data set is imported into the database before running tests.
  • For assertion the data in database This data set is compared with the data in the actual database.
Data Set for Seeding

There are several options for seeding

  • clear-insert(default): Truncate the table then insert data.
  • insert: Just insert data into the table.
  • upsert: Insert data into the table, or update if the row already exists.
  • truncate: Just truncate the table.
  • delete: Delete rows in the table that matches the dataset's primary keys.
_operations:
  user: clear-insert
  access_log_2025_*: truncate
  login_history: delete

user:
- { id: 10, name: Frank, email: frank@example.com }
- { id: 11, name: Grace, email: grace@example.com }
- { id: 12, name: Heidi, email: heidi@example.com }
- { id: 13, name: Ivan}

login_history
- { user_id: 10, time: 2024-12-14 }
Data Set for Assertion

There are two options for matching rules.

  • exact(default): The table should have exact same rows with the data set. But if the fields not in the data set is ignored (like system fields).
  • sub: If the table has extra rows that are not in the data set, it is still valid.

And there are special field values for matching:

  • [null]: It assumes the value is NULL. it is as same as null.
  • [notnull]: It assumes the value is not NULL.
  • [any]: It matches any value.
  • [currentdate, <duration>]: It matches when the actual value is within the specified duration from the current time. The duration is optional and defaults to 1m (Go duration format, e.g., 30s, 2m, 1h). The actual value can be time.Time or a string parseable by RFC3339 or common SQL timestamp formats.
  • [regexp, <pattern>]: It matches when the stringified actual value matches the given regular expression pattern.
_match:
  user: exact
  access_log_2025_*: sub

user:
- { id: 10, name: Frank, email: null }

login_history
- { user_id: 10,. time: [notnull]}

audit_log:
- { id: 1, created_at: [currentdate, 2m], message: [regexp, "^User .+ logged in$"] }
Tags

Each row can have tags. You can filter the rows by tags when loading:

member:
- { id: 1, name: Frank, _tags: [admin, user] }
- { id: 2, name: Grace, _tags: [user] }
- { id: 3, name: Ivy, _tags: [ex_user] }
For CLI:
# you can use `-i`/`-e` as a shortcut 
$ dbtestify seed testdata/users.yaml --include-tag=user --exclude-tag=admin
$ dbtestify assert testdata/users.yaml --include-tag=user --exclude-tag=admin
For HTTP API:

You can use Form data or JSON to specify the tags. For curl command, use form data is easier than JSON. But if request fromfetch or request(Playwright), you should feel using JSON is easier.

$ curl -X POST -d "i=user" -d "" http://localhost:8000/api/seed/users.yaml
$ curl -X POST -H "Content-Type: application/json" -d '{"include_tags": ["user"], "exclude_tags": ["admin"]}' http://localhost:8000/api/seed/users.yaml
$ curl -d "i=user" -d "" http://localhost:8000/api/assert/users.yaml
$ dbtestify assert testdata/users.yaml --tags user

License

  • AGPL-3.0

Documentation

Overview

dbtestify package provides core logic of dbtestify.

It is public but not intended to be used directly by users. github.com/shibukawa/dbtestify/assertdb provides API for database seeding and assertion for testing.

Index

Constants

This section is empty.

Variables

View Source
var DefaultBatchSize = 50

DefaultBatchSize is the default number of rows to process in a single batch during seeding.

View Source
var ErrInvalidDBDriver = errors.New("invalid db driver")
View Source
var ErrInvalidDialect = errors.New("invalid dialect")

ErrInvalidDialect is returned when a dialect name is not supported.

Functions

func DumpDiffCLICallback

func DumpDiffCLICallback(showTableName, quiet bool) func(result AssertTableResult)

DumpDiffCLICallback returns an AssertOpt.DiffCallback that writes colored output to os.Stdout.

It is equivalent to DumpDiffCallback(os.Stdout, DiffFormat{ShowTableName: showTableName, Quiet: quiet}).

func DumpDiffCallback added in v0.2.0

func DumpDiffCallback(w io.Writer, format DiffFormat) func(result AssertTableResult)

DumpDiffCallback returns an AssertOpt.DiffCallback that writes each table result to w.

func FormatTableDiff added in v0.2.0

func FormatTableDiff(result AssertTableResult, format DiffFormat) string

FormatTableDiff renders one AssertTableResult as text.

It returns an empty string when the table matches and format.Quiet is set without format.ShowTableName. Use format.NoColor to obtain plain text suitable for a test log or an error message.

func Seed

func Seed(ctx context.Context, dbc DBConnector, data *DataSet, opt SeedOpt) error

Seed initializes the database with the provided dataset, applying the specified operations.

Types

type AssertOpt

type AssertOpt struct {
	IncludeTags  []string                                                            // Tags to filter rows of dataset.
	ExcludeTags  []string                                                            // Tags to filter rows of dataset.
	TargetTables []string                                                            // Only specified tables will be processed. If empty, all tables will be processed.
	Callback     func(targetTable string, mode MatchStrategy, start bool, err error) // Callback function to report progress and errors during the assertion process.
	DiffCallback func(result AssertTableResult)                                      // Callback function to report differences in rows during the assertion process.
}

MatchStrategy defines the strategy for matching rows in a table.

type AssertResult

type AssertResult struct {
	Tables []AssertTableResult
}

AssertResult represents the result of an assertion operation on a dataset.

type AssertStatus

type AssertStatus string

AssertStatus defines the status of an assertion result.

const (
	Match        AssertStatus = "match"
	NotMatch     AssertStatus = "not-match"
	OnlyOnExpect AssertStatus = "only-e"
	OnlyOnActual AssertStatus = "only-a"
	WrongDataSet AssertStatus = "wrongDataSet" // primary keys are missing
)

type AssertTableResult

type AssertTableResult struct {
	Name        string
	PrimaryKeys []string
	Rows        []RowDiff
	Status      AssertStatus
}

AssertTableResult represents the result of an assertion on a single table in AssertResult.

func Assert

func Assert(ctx context.Context, dbc DBConnector, expected *DataSet, opt AssertOpt) (bool, []AssertTableResult, error)

Assert performs an assertion on the provided dataset against the database.

type DBConnector

type DBConnector interface {
	TableNames(ctx context.Context, schema ...string) ([]string, error)
	PrimaryKeys(ctx context.Context, table string) ([]string, error)
	Insert(ctx context.Context, tx *sql.Tx, tableName string, columns []string, values []any) error
	Delete(ctx context.Context, tx *sql.Tx, tableName string, columns []string, values []any) error
	Upsert(ctx context.Context, tx *sql.Tx, tableName string, columns, pKeys []string, values []any) error
	Truncate(ctx context.Context, tx *sql.Tx, tableName string) error
	DB() *sql.DB
}

DBConnector is an interface for database operations that is used from dbtestify package. It absorbs the database driver specific operations like getting table names, primary keys, and performing CRUD operations.

func NewDBConnectorFromDB added in v0.2.0

func NewDBConnectorFromDB(db *sql.DB, dialect Dialect) (DBConnector, error)

NewDBConnectorFromDB creates a DBConnector on top of a *sql.DB the caller already owns.

dbtestify never closes db and never changes its pool settings, so the caller stays responsible for its lifecycle. Use this when the pool is owned by an application framework, or when the database is unreachable from a second pool such as an in-memory SQLite database.

dialect selects the SQL flavor because a *sql.DB does not expose the name it was opened with. Use ParseDialect or SplitSource to derive it from a DSN.

This package registers no database/sql driver. To open a connection from a connection string instead, use github.com/shibukawa/dbtestify/connect.

type DataSet

type DataSet struct {
	Operation map[string]Operation
	Match     map[string]MatchStrategy
	Tables    []*Table
}

DataSet represents a collection of tables and their associated operations and match strategies.

func ParseYAML

func ParseYAML(r io.Reader) (*DataSet, error)

ParseYAML reads a YAML formatted dataset from the provided reader and returns a DataSet object.

type Dialect added in v0.2.0

type Dialect string

Dialect identifies the SQL flavor a DBConnector speaks.

It is independent from the database/sql driver name so callers that already own a *sql.DB can select the connector behavior explicitly.

const (
	PostgresDialect Dialect = "postgres"
	MySQLDialect    Dialect = "mysql"
	SQLiteDialect   Dialect = "sqlite"
)

func ParseDialect added in v0.2.0

func ParseDialect(name string) (Dialect, error)

ParseDialect resolves a connection string scheme or a database/sql driver name to a Dialect.

Accepted names are postgres, postgresql, pgx, mysql, sqlite, and sqlite3.

func SplitSource added in v0.2.0

func SplitSource(source string) (Dialect, string, error)

SplitSource splits a "scheme://dsn" connection string into its Dialect and the DSN to hand to sql.Open.

PostgreSQL DSNs keep their scheme because pgx expects a full URL; the other dialects drop it.

func (Dialect) String added in v0.2.0

func (d Dialect) String() string

type Diff

type Diff struct {
	Key    string       `json:"key"`
	Expect any          `json:"expect"`
	Actual any          `json:"actual"`
	Status AssertStatus `json:"status"`
}

Value represents a single value in a row, including its key and value.

type DiffFormat added in v0.2.0

type DiffFormat struct {
	ShowTableName bool // print a "Table: name" header
	Quiet         bool // print nothing for a matching table
	NoColor       bool // render plain text without ANSI escapes
}

DiffFormat controls how an AssertTableResult is rendered.

type ErrMissingPrimaryKey

type ErrMissingPrimaryKey struct {
	MissingKeys []string
	Dump        string
}

ErrMissingPrimaryKey is an error type that indicates that some primary keys are missing from a row.

func (ErrMissingPrimaryKey) Error

func (e ErrMissingPrimaryKey) Error() string

type MatchStrategy

type MatchStrategy string

MatchStrategy defines how to match rows in the database.

const (
	ExactMatchStrategy   MatchStrategy = "exact"
	SubMatchStrategy     MatchStrategy = "sub"
	InvalidMatchStrategy MatchStrategy = "invalid"
)

func (MatchStrategy) String

func (s MatchStrategy) String() string

type NormalizedTable

type NormalizedTable struct {
	Name string
	Rows [][]Value
}

NormalizedTable represents a normalized version of a table with its name and sorted rows.

type Operation

type Operation string

Operation represents the type of operation to be performed on the database.

const (
	ClearInsertOperation Operation = "clear-insert"
	InsertOperation      Operation = "insert"
	UpsertOperation      Operation = "upsert"
	DeleteOperation      Operation = "delete"
	TruncateOperation    Operation = "truncate"
	InvalidOperator      Operation = "invalid"
)

func (Operation) String

func (o Operation) String() string

type RowDiff

type RowDiff struct {
	Fields []Diff       `json:"fields"`
	Status AssertStatus `json:"status"`
}

RowDiff represents the difference in a row between the expected and actual data.

type SeedOpt

type SeedOpt struct {
	BatchSize    int                                                   // default: 50
	Operations   map[string]Operation                                  // Operations to apply to each table. If empty, defaults to ClearInsertOperation.
	IncludeTags  []string                                              // Tags to filter rows of dataset.
	ExcludeTags  []string                                              // Tags to filter rows of dataset.
	TargetTables []string                                              // Only specified tables will be processed.
	Callback     func(targetTable, task string, start bool, err error) // Callback function to report progress and errors during the seeding process.
}

SeedOpt defines options for the seeding process.

type Table

type Table struct {
	Name string
	Rows []map[string]any
	Tags [][]string
}

Table represents a single table in the dataset, including its name, rows, and tags.

func (Table) SortAndFilter

func (t Table) SortAndFilter(primaryKeys, includeTags, excludeTags []string) (*NormalizedTable, error)

Table.SortAndFilter sorts the rows of the table based on the provided primary keys and filters them based on include and exclude tags.

type Value

type Value struct {
	Key   string
	Value any
}

Value represents a key-value pair in a row of a table.

Directories

Path Synopsis
assertdb is a helper for testing package.
assertdb is a helper for testing package.
cmd
dbtestify command
Package connect opens a dbtestify.DBConnector from a connection string.
Package connect opens a dbtestify.DBConnector from a connection string.
examples

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL