database

package
v1.8.3 Latest Latest
Warning

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

 Go to latest Published: Feb 6, 2026 License: BSD-3-Clause Imports: 20 Imported by: 0

Documentation

Overview

Package database provides a robust and flexible abstraction over database/sql, supporting SQLite, MySQL/MariaDB, and PostgreSQL as backend databases.

The package uses an instance-based design, allowing applications to manage multiple database connections simultaneously. Each Database instance maintains its own connection state, configuration, and migration history.

It offers features such as automatic connection handling, schema migrations, and version tracking. The package is designed to work with sqlc for type-safe SQL queries. It also allows registering custom on-connect handlers that are executed once the database connection is successfully established.

Core features:

  • Multiple database instances with independent connections
  • Automatic (re)connection with health checks and retry mechanism
  • Support for SQLite, MySQL/MariaDB, and PostgreSQL with configurable parameters
  • Schema management with SQL-based migrations
  • Versioning support using the go-core/version package
  • Connection lifecycle management (Connect, Disconnect, AwaitConnection)
  • Thread-safe access with `GetDB()` to retrieve the active connection
  • Registering on-connect hooks to perform actions like seeding or setup
  • Backup and restore functionality for SQLite, MySQL, and PostgreSQL
  • SQL middleware support for logging and monitoring all database operations
  • Debug mode for per-query logging (similar to GORM's Debug() method)

Example: 

package main

import (
	"context"
	"database/sql"
	"fmt"
	"time"

	"github.com/valentin-kaiser/go-core/database"
	"github.com/valentin-kaiser/go-core/flag"
	"github.com/valentin-kaiser/go-core/version"
	"your-project/internal/sqlc"
)

func main() {
	flag.Init()

	// Create a new database instance with sqlc integration
	db := database.New[sqlc.Queries](database.DriverSQLite, "main")

	// Register queries constructor
	db.RegisterQueries(sqlc.New)

	// Register migration steps for this instance
	db.RegisterMigrationStep(version.Release{
		GitTag:    "v1.0.0",
		GitCommit: "abc123",
	}, func(sqlDB *sql.DB) error {
		_, err := sqlDB.Exec(`CREATE TABLE IF NOT EXISTS users (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			name TEXT NOT NULL,
			email TEXT UNIQUE NOT NULL,
			password TEXT NOT NULL,
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP
		)`)
		return err
	})

	// Connect to the database using DSN string
	db.Connect(time.Second, "file:test.db")
	defer db.Disconnect()

	// Wait for connection to be established
	db.AwaitConnection()

	// Use Query method to execute type-safe sqlc queries
	ctx := context.Background()
	err := db.Query(func(q *sqlc.Queries) error {
		user, err := q.GetUser(ctx, 1)
		if err != nil {
			return err
		}
		fmt.Println("User:", user.Name, user.Email)
		return nil
	})
	if err != nil {
		panic(err)
	}
}

Multi-Instance Example: 

// Connect to multiple databases simultaneously with different sqlc Queries
postgres := database.New[pgSqlc.Queries](database.DriverPostgres, "postgres-main")
mysql := database.New[mysqlSqlc.Queries](database.DriverMySQL, "mysql-analytics")
sqlite := database.New[sqliteSqlc.Queries](database.DriverSQLite, "sqlite-cache")

postgres.Connect(time.Second, "postgres://postgres:secret@localhost:5432/maindb?sslmode=disable")

mysql.Connect(time.Second, "root:password@tcp(localhost:3306)/analytics")

sqlite.Connect(time.Second, ":memory:")

Middleware Example: 

// Create a new database instance with logging middleware
db := database.New[sqlc.Queries](database.DriverSQLite, "main")

// Register logging middleware to log all SQL statements
logger := logging.GetPackageLogger("database")
loggingMiddleware := database.NewLoggingMiddleware(logger)
db.RegisterMiddleware(loggingMiddleware)

// Connect to the database using DSN string
db.Connect(time.Second, "file:example.db")
defer db.Disconnect()

// All SQL statements will now be logged with timing information
ctx := context.Background()
db.Query(func(q *sqlc.Queries) error {
	return q.CreateUser(ctx, sqlc.CreateUserParams{
		Name:  "John Doe",
		Email: "john@example.com",
	})
})

Debug Mode Example: 

// Create a database instance and register LoggingMiddleware
db := database.New[sqlc.Queries](database.DriverSQLite, "main")

// LoggingMiddleware must be registered for Debug() to work
logger := logging.GetPackageLogger("database")
loggingMiddleware := database.NewLoggingMiddleware(logger)
loggingMiddleware.SetEnabled(false) // Disabled by default
db.RegisterMiddleware(loggingMiddleware)

db.Connect(time.Second, "file:example.db")
defer db.Disconnect()

ctx := context.Background()

// Regular query - no debug logging (middleware is disabled)
db.Query(func(q *sqlc.Queries) error {
	return q.GetUser(ctx, 1)
})

// Debug query - temporarily enables logging for this specific query
db.Debug().Query(func(q *sqlc.Queries) error {
	return q.GetUser(ctx, 1)
})

// You can also use Debug() with Execute
db.Debug().Execute(func(dbConn *sql.DB) error {
	_, err := dbConn.ExecContext(ctx, "UPDATE users SET active = ? WHERE id = ?", true, 1)
	return err
})

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

This section is empty.

Types

type Database added in v1.7.0 

type Database[Q any] struct {
	// contains filtered or unexported fields
}

Database represents a database connection instance with its own state and configuration. Multiple instances can be created to manage connections to different databases. The generic type parameter Q represents the sqlc-generated Queries type.

func New added in v1.7.0 

func New[Q any](driver Driver, name string) *Database[Q]

New creates a new Database instance with the given name for logging purposes. The name parameter is used to identify this database instance in logs. The generic type parameter Q represents the sqlc-generated Queries type.

func (*Database[Q]) AwaitConnection added in v1.7.0 

func (d *Database[Q]) AwaitConnection()

AwaitConnection will block until the database is connected

func (*Database[Q]) Backup added in v1.7.0 

func (d *Database[Q]) Backup(path string, schema string) error

Backup creates a backup of the database to the specified path. For SQLite: copies the database file For MySQL/MariaDB: creates an SQL dump For PostgreSQL: creates an SQL dump Returns an error if the database is not connected or if the backup fails.

func (*Database[Q]) Connect added in v1.7.0 

func (d *Database[Q]) Connect(interval time.Duration, dsn string)

Connect will try to connect to the database every interval until it is connected It will also check if the connection is still alive every interval and reconnect if it is not

func (*Database[Q]) Connected added in v1.7.0 

func (d *Database[Q]) Connected() bool

Connected returns true if the database is connected, false otherwise

func (*Database[Q]) Debug added in v1.7.0 

func (d *Database[Q]) Debug() *Database[Q]

Debug returns a new Database handle with debug logging enabled for all queries and executions. This method requires that a LoggingMiddleware has been registered with RegisterMiddleware. It temporarily enables debug-level logging for the specific query or execution that follows. The debug handle shares the same underlying connection and configuration as the parent instance. Example: db.Debug().Query(func(q *sqlc.Queries) error { return q.GetUser(ctx, id) })

func (*Database[Q]) Disconnect added in v1.7.0 

func (d *Database[Q]) Disconnect() error

Disconnect will stop the database connection and wait for the connection to be closed

func (*Database[Q]) Execute added in v1.7.0 

func (d *Database[Q]) Execute(call func(db *sql.DB) error) error

Execute executes a function with a database connection. It will return an error if the database is not connected or if the function returns an error.

func (*Database[Q]) Get added in v1.7.0 

func (d *Database[Q]) Get() *sql.DB

Get returns the active database connection. It will return nil if the database is not connected. Use this function to get the database connection for executing queries with sqlc or raw SQL.

func (*Database[Q]) Query added in v1.7.0 

func (d *Database[Q]) Query(call func(q *Q) error) error

Query executes a function with sqlc-generated Queries instance. It will return an error if the database is not connected or if the function returns an error. Example: db.Query(func(q *sqlc.Queries) error { return q.GetUser(ctx, id) })

func (*Database[Q]) QueryTransaction added in v1.7.0 

func (d *Database[Q]) QueryTransaction(call func(q *Q) error) error

QueryTransaction executes a function with a sqlc-generated Queries instance within a database transaction. It will return an error if the database is not connected or if the function returns an error. If the function returns an error, the transaction will be rolled back. Example: db.QueryTransaction(func(q *sqlc.Queries) error { return q.CreateUser(ctx, params) })

func (*Database[Q]) Reconnect added in v1.7.0 

func (d *Database[Q]) Reconnect(dsn string)

Reconnect will set the connected state to false and trigger a reconnect

func (*Database[Q]) RegisterMiddleware added in v1.7.0 

func (d *Database[Q]) RegisterMiddleware(middleware Middleware) *Database[Q]

RegisterMiddleware registers a middleware that will intercept and log SQL statements

func (*Database[Q]) RegisterOnConnectHandler added in v1.7.0 

func (d *Database[Q]) RegisterOnConnectHandler(handler func(db *sql.DB) error)

RegisterOnConnectHandler registers a function that will be called when the database connection is established

func (*Database[Q]) RegisterQueries added in v1.7.0 

func (d *Database[Q]) RegisterQueries(queries any) *Database[Q]

RegisterQueries registers the sqlc Queries constructor function. This allows you to set the queries constructor after creating the database instance. Example: db.RegisterQueries(sqlc.New)

func (*Database[Q]) Restore added in v1.7.0 

func (d *Database[Q]) Restore(backupPath string) error

Restore restores the database from a backup file at the specified path. For SQLite: replaces the current database file with the backup For MySQL/MariaDB: uses mysql client to restore from SQL dump For PostgreSQL: uses psql to restore from SQL dump Returns an error if the database is not connected or if the restore fails. WARNING: This will overwrite the current database. Ensure you have a backup before restoring.

func (*Database[Q]) TestConnection added in v1.7.1 

func (d *Database[Q]) TestConnection(dsn string) error

TestConnection tests the database connection by attempting to connect and ping the database.

func (*Database[Q]) Transaction added in v1.7.0 

func (d *Database[Q]) Transaction(call func(tx *sql.Tx) error) error

Transaction executes a function within a database transaction. It will return an error if the database is not connected or if the function returns an error. If the function returns an error, the transaction will be rolled back.

type Driver added in v1.8.0 

type Driver string
const (
	DriverSQLite   Driver = "sqlite3"
	DriverMySQL    Driver = "mysql"
	DriverPostgres Driver = "pgx"
)

type LoggingMiddleware added in v1.7.0 

type LoggingMiddleware struct {
	// contains filtered or unexported fields
}

LoggingMiddleware logs all SQL statements and their execution time

func NewLoggingMiddleware added in v1.7.0 

func NewLoggingMiddleware(logger logging.Adapter) *LoggingMiddleware

NewLoggingMiddleware creates a new logging middleware with the provided logger Logging is enabled by default

func (*LoggingMiddleware) AfterExec added in v1.7.0 

func (m *LoggingMiddleware) AfterExec(ctx context.Context, query string, args []d.NamedValue, result d.Result, err error)

AfterExec logs after executing a statement

func (*LoggingMiddleware) AfterQuery added in v1.7.0 

func (m *LoggingMiddleware) AfterQuery(ctx context.Context, query string, args []d.NamedValue, err error)

AfterQuery logs after executing a query

func (*LoggingMiddleware) BeforeExec added in v1.7.0 

func (m *LoggingMiddleware) BeforeExec(ctx context.Context, query string, args []d.NamedValue) context.Context

BeforeExec does nothing before executing a statement

func (*LoggingMiddleware) BeforeQuery added in v1.7.0 

func (m *LoggingMiddleware) BeforeQuery(ctx context.Context, query string, args []d.NamedValue) context.Context

BeforeQuery does nothing before executing a query

func (*LoggingMiddleware) BeginTx added in v1.7.0 

func (m *LoggingMiddleware) BeginTx(ctx context.Context, opts d.TxOptions) (d.Tx, error)

BeginTx is a no-op implementation to satisfy the Middleware interface

func (*LoggingMiddleware) IsEnabled added in v1.7.0 

func (m *LoggingMiddleware) IsEnabled() bool

IsEnabled returns true if logging is currently enabled

func (*LoggingMiddleware) SetEnabled added in v1.7.0 

func (m *LoggingMiddleware) SetEnabled(enabled bool) *LoggingMiddleware

SetEnabled enables or disables logging at runtime

func (*LoggingMiddleware) SetTrace added in v1.7.0 

func (m *LoggingMiddleware) SetTrace(enabled bool) *LoggingMiddleware

type Middleware added in v1.7.0 

type Middleware interface {
	// BeforeExec is called before executing a statement
	BeforeExec(ctx context.Context, query string, args []d.NamedValue) context.Context
	// AfterExec is called after executing a statement
	AfterExec(ctx context.Context, query string, args []d.NamedValue, result d.Result, err error)
	// BeforeQuery is called before executing a query
	BeforeQuery(ctx context.Context, query string, args []d.NamedValue) context.Context
	// AfterQuery is called after executing a query
	AfterQuery(ctx context.Context, query string, args []d.NamedValue, err error)
	// Optionally implement driver.ConnBeginTx if needed
	BeginTx(ctx context.Context, opts d.TxOptions) (d.Tx, error)
}

Middleware represents a SQL middleware that can intercept and log SQL statements

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.