sqlink

README

SQLink

SQLink is an ORM built on database/sql and Go 1.27 generic methods. Start an execution session with orm.Builder(db), then query typed models with builder.Query[User](). SQLite, MySQL/MariaDB, and PostgreSQL dialects are included.

Installation

Requires Go 1.27 or later. The project selects Go 1.27.3 as its preferred toolchain.

go get github.com/Kaguya233qwq/sqlink

Register the driver you use: github.com/glebarez/go-sqlite (sqlite), github.com/go-sql-driver/mysql (mysql), or github.com/jackc/pgx/v5/stdlib (pgx).

Getting started

package main

import (
    "context"
    "fmt"
    "log"
    "time"

    "github.com/Kaguya233qwq/sqlink/orm"
    _ "github.com/glebarez/go-sqlite"
)

type User struct {
    ID   orm.Field[int]    `db:"id"`
    Name orm.Field[string] `db:"name"`
    Age  orm.Field[int]    `db:"age"`
}

func run() error {
    db, err := orm.OpenDB("sqlite", "users.db")
    if err != nil {
        return err
    }
    defer db.Close()

    ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
    defer cancel()
    builder := orm.Builder(db).WithContext(ctx)

    if err := builder.AutoMigrate(User{}); err != nil {
        return err
    }
    user := User{
        Name: orm.Value("alice"),
        Age:  orm.Value(25),
    }
    if err := builder.Query[User]().Create(&user); err != nil {
        return err
    }
    q := builder.Query[User]()
    users, err := q.Where(q.Schema.Age.Gte(18)).OrderBy("age").All()
    if err != nil {
        return err
    }
    fmt.Println("adults:", len(users))
    return nil
}

func main() {
    if err := run(); err != nil {
        log.Fatal(err)
    }
}

Field[T] represents a nullable model column. Valid: true writes its Value; unset fields are omitted by model inserts and updates. Create backfills generated IDs. An int or int64 id column defaults to an auto-increment primary key; explicit tags include db:"code,primarykey" and db:"id,primarykey,autoincrement". Models may implement TableName() string.

Field write states

row.Name = orm.Value("Alice") // write a value
row.Age = orm.Value(0)        // write zero, not omit
row.Name = orm.Null[string]() // write SQL NULL
row.Name = orm.Field[string]{} // omit the column

Existing Field[T]{Value: v, Valid: true} literals remain supported. Valid takes precedence over an earlier explicit NULL intent. Reading a value or NULL clears write intent; a scanned NULL has Valid == false and is omitted on subsequent model writes unless explicitly assigned orm.Null[T](). SQL NULL and failed scans continue to preserve the old Value; inspect Valid before reading it.

Create, Save, UpdateModel, Upsert and CreateInBatches use the same supplied/NULL/value distinction. Primary keys cannot be explicitly NULL. Batch inserts group adjacent records with matching supplied columns, preserving input order and database defaults. All-omitted records remain an error, as in Create.

Behavior changes: batch inserts previously wrote NULL for unspecified non-key fields; they now omit those columns. Upsert now defaults to updating only supplied non-key columns, preserving omitted columns on conflict. Explicit Upsert update columns must exist and be supplied. A key-only upsert assigns the key to itself on conflict, so database update triggers may still run.

Named schema operations

err := builder.Transaction(func(tx *orm.Session) error {
    schema := tx.Schema()
    legacy, err := schema.HasColumn("users", "qq_id")
    if err != nil {
        return err
    }
    if legacy {
        return schema.RenameTable("users", "users_legacy_qq")
    }
    return nil
})

HasTable(name), HasColumn(table, column) and RenameTable(from, to) operate on dynamic table names without inventing model types for archived tables. They inherit the session's context, logger and transaction. Identifiers are quoted by the dialect; metadata values use bound parameters. Names identify tables in the current database/schema, not schema-qualified SQL fragments. MySQL/MariaDB DDL may implicitly commit; wrapping DDL in Transaction does not override the database's transaction rules.

Field conversion

Field[T].Scan keeps checked native numeric paths and uses Go 1.27's sql.ConvertAssign for extended conversions. This adds text/byte numeric and boolean inputs, named numeric destinations, pointers, and wrapped sql.Scanner types such as Field[sql.NullString], subject to the standard converter's supported combinations.

Compatibility rules:

• SQL NULL and failed conversions set Valid = false and leave the old Value unchanged. Read Value only when Valid is true.
• Exact-type inputs are assigned directly, as before. Byte slices received from drivers are copied into owned storage, including Field[[]byte] and byte values in Field[any].
• Checked float-to-integer conversion accepts integral values within range; fractions, NaN, infinities, negative unsigned values, and overflow return errors. Float destinations continue to accept NaN and infinities.
• The existing integer-to-bool rule is preserved: every nonzero int64 becomes true. Textual bool conversion follows the standard converter, for example "true" and "1" succeed, "2" fails.
• Time text keeps RFC3339/RFC3339Nano and 2006-01-02 15:04:05[.fraction][-07:00] parsing. Successful parsing assigns the time directly.
• When conversion is needed, custom Scanner methods take priority over reflection on named scalars. They run on a fresh zero value, committed only on success; scanner errors remain discoverable through errors.Is/errors.As. NULL is consumed by Field without invoking the wrapped scanner. Exact-type assignment still bypasses conversion.
• Custom scanners that retain reference-valued input must copy it according to the sql.Scanner contract. For standalone owned byte data, use []byte; implicit conversions to sql.RawBytes and pointer variants are rejected because they require a live row scan context. Recursive pointer destinations are also rejected.

These rules affect reads. Model write/driver.Valuer handling is unchanged.

Sessions and context

DB owns the connection pool and defaults. Session holds an execution context, a logger, and either the pool or an active transaction. Q[T] holds one model query's clauses.

base := orm.Builder(db)
request := base.WithContext(ctx).WithLogger(requestLogger)
users, err := request.Query[User]().All()

• Builder snapshots database defaults and does not reserve a connection.
• WithContext and WithLogger return new sessions. Existing sessions and queries keep their configuration. WithLogger(nil) disables logging locally.
• Context cancellation and deadlines apply to reads, writes, preloads, raw SQL, schema operations, and transactions. A shared parent context cancels all sessions derived from it. The caller owns cancellation resources.
• Set db.Logger before constructing sessions. Request loggers receive the request context for correlation. Custom loggers must support concurrent calls when shared.
• Sessions support concurrent query creation. Every Query[T]() starts with independent conditions. Query instances are mutable; use separate queries or Clone() per goroutine. Configure a template before cloning it concurrently. Custom expressions and parameter values must be treated as immutable.
• Only db.Close() closes the pool. Transaction sessions are valid only inside their callback.

Configure pooling through pool, err := db.DB(). orm.OpenWithDB(pool, "sqlite") wraps an existing *sql.DB.

Typed reads and writes

Operation	Result / input
q.All()	([]T, error); empty results return a non-nil empty slice
q.First()	(T, error); missing rows return ErrRecordNotFound and wrap sql.ErrNoRows
q.Count()	(int64, error); respects distinct/grouping/having, ignores ordering and pagination
q.Create(&model)	*T; inserts and backfills generated IDs
q.Save(&model)	*T; inserts with an unset primary key, otherwise updates supplied fields by primary key
q.UpdateModel(&model)	*T; updates supplied fields by primary key
q.DeleteByPrimaryKey(&model)	*T; deletes by primary key
q.Upsert(&model, "name")	*T; updates specified columns on conflict, supplied non-key columns by default
q.CreateInBatches(models, 500)	[]T; inserts in a new or existing transaction

Create, Save, UpdateModel, and DeleteByPrimaryKey run applicable Before/After lifecycle hooks. Bulk operations and Upsert do not run per-record hooks. Batch inserts do not backfill generated IDs; all records must consistently set or omit the auto-increment primary key. Hook errors after a write require an enclosing transaction if the write should roll back.

Conditions include Is, Ne, Gt, Gte, Lt, Lte, Between, Like, In, and NotIn. Combine them with orm.And, orm.Or, and orm.Not.

q := builder.Query[User]()
users, err := q.Where(orm.Or(
    q.Schema.Name.Is("alice"),
    q.Schema.Age.Between(20, 30),
)).OrderBy("age", true).Limit(20).All()

Select, Distinct, GroupBy, Having, OrderBy, Limit, and Offset compose query clauses. ToSQL() returns SQL and bind arguments without executing. Invalid construction is recorded in q.Error; terminal methods return that error. LastSQL() returns the latest statement and a copy of its argument slice.

Branch query templates with Clone():

q := builder.Query[User]()
baseQuery := q.Where(q.Schema.Age.Gte(18))
young, err := baseQuery.Clone().Where(q.Schema.Age.Lt(30)).All()
seniors, err := baseQuery.Clone().Where(q.Schema.Age.Gte(65)).All()

Condition-based updates and deletes require a Where clause:

q := builder.Query[User]()
err := q.Where(q.Schema.Name.Is("alice")).Update(map[string]any{"age": 26})

q = builder.Query[User]()
err = q.Where(q.Schema.Name.Is("bob")).Delete()

Projection, aggregates, and raw SQL

Generic methods select the return type explicitly:

type Summary struct {
    Name string `db:"name"`
    Age  int    `db:"age"`
}

rows, err := builder.Query[User]().Select("name", "age").Scan[Summary]()
names, err := builder.Query[User]().Pluck[string]("name")
total, err := builder.Query[User]().Sum[int64]("age")
average, err := builder.Query[User]().Avg[float64]("age")

Min[R] and Max[R] also return (R, error). Result conversions are checked by the SQL scanner at runtime. To represent nullable results, use orm.Field[R] or a sql.Null* type, for example Sum[orm.Field[int64]]("age") for an empty table. Scalar aggregates return one value; use raw SQL and RawScan for multiple grouped aggregate rows.

Scan[R], RawScan[R], and ScanRows[R] accept structs or map[string]any. Struct fields map through db tags or field names, without case sensitivity; extra SQL columns are ignored. Scan[R] does not support preloading associations; use All or First.

rows, err := builder.RawScan[Summary]("SELECT name, age FROM users")
result, err := builder.Exec("DELETE FROM users WHERE age < ?", 18) // SQLite/MySQL

Raw SQL uses the database driver's native placeholders (? for SQLite/MySQL, $1 for PostgreSQL). Raw returns *sql.Rows that the caller must close; ScanRows[R](rows) scans externally managed rows. RawScan owns and closes its rows and logs scan errors.

Transactions

err := builder.Transaction(func(tx *orm.Session) error {
    if err := tx.Query[User]().Create(&user); err != nil {
        return err
    }
    return tx.Query[Order]().CreateInBatches(orders, 500)
})

The callback inherits context and logging configuration. Return an error to roll back all writes; panics also roll back and propagate unchanged. Batch inserts and preloads use the callback's transaction. Batch inserts outside a transaction create their own transaction and roll back earlier batches on failure. Nested Transaction calls return an error.

Schema and associations

Schema operations use the session's context and logger: AutoMigrate, HasTable, DropTable, AddColumn, RenameColumn, and CreateIndex accept model values.

err := builder.AutoMigrate(User{})
exists, err := builder.HasTable(User{})
err = builder.CreateIndex(User{}, "idx_user_name", "name")

AutoMigrate creates missing tables and adds missing ordinary columns to existing tables. New columns are nullable and existing rows receive NULL. It does not inspect or change existing column types, defaults or constraints, create indexes, or remove or rename columns. Adding a missing primary-key or auto-increment column requires an explicit migration and is rejected before any ordinary columns are added. Existing SQLite tables created without an intended primary-key constraint require a separate table migration.

Run migrations from one process at a time: concurrent migrations may race. Adding multiple columns is not necessarily atomic; a later error can leave earlier columns in place, particularly with MySQL/MariaDB's implicit DDL commits. Use explicit, reviewed migrations for destructive changes or data conversion.

Declare HasMany[T], HasOne[T], or BelongsTo[T] fields with an fk tag naming the foreign-key column:

type Author struct {
    ID    orm.Field[int]    `db:"id"`
    Name  orm.Field[string] `db:"name"`
    Books orm.HasMany[Book] `fk:"author_id"`
}
type Book struct {
    ID       orm.Field[int]        `db:"id"`
    AuthorID orm.Field[int]        `db:"author_id"`
    Author   orm.BelongsTo[Author] `fk:"author_id"`
}

authors, err := builder.Query[Author]().Preload("Books").All()

Preloads inherit the session. When combining Select and Preload, include the parent primary key (has-many/has-one) or foreign key (belongs-to). Unknown associations and missing selected keys return errors.

PostgreSQL time columns

New PostgreSQL tables map Field[time.Time] to TIMESTAMPTZ so input offsets preserve the represented instant. PostgreSQL retains the instant, not the original zone name or offset, and timestamp precision is microseconds. See the PostgreSQL datetime documentation.

Previously created TIMESTAMP columns are unchanged by AutoMigrate. Migrating existing data requires knowing the historical input timezone; changing the column type alone cannot recover discarded offsets automatically.

Migrating from the previous API

This is a breaking API change. The query entry point is orm.Builder(db); package-level orm.Query[T](db) and db.Query[T]() have been removed.

Before	Now
orm.Query[User](db) / db.Query[User]()	orm.Builder(db).Query[User]()
q.Find(&users)	users, err := q.All()
q.First(&user)	user, err := q.First()
q.Count(&n)	n, err := q.Count()
q.Pluck("name", &names)	names, err := q.Pluck[string]("name")
q.Sum("age", &total)	total, err := q.Sum[int64]("age")
q.WithContext(ctx)	builder.WithContext(ctx).Query[User]()
db.Begin(func(tx *orm.DB) error { ... })	builder.Transaction(func(tx *orm.Session) error { ... })
db.RawScan(ctx, &rows, sql, args...)	builder.WithContext(ctx).RawScan[Row](sql, args...)
db.ExecWithContext(ctx, sql, args...)	builder.WithContext(ctx).Exec(sql, args...)
db.AutoMigrate(User{})	builder.AutoMigrate(User{})

Model writes accept *T, and batch inserts accept []T. Code passing another model type, a value instead of a pointer, or []*T must be updated.

Testing

go test ./...
go vet ./...
go test -race -coverpkg=./orm ./orm ./tests

Unit tests and internal microbenchmarks live beside the implementation in orm/. Live database tests, public API benchmarks, and handwritten SQL comparisons live in tests/. See tests/README.md for package-specific commands and combined coverage.

PostgreSQL through Docker

No host PostgreSQL installation is needed. Run:

./scripts/test-postgres.sh
# All ORM tests, including live PostgreSQL, with the race detector:
./scripts/test-postgres.sh -race -count=1 -timeout=2m
# Live PostgreSQL throughput, P95, and allocation benchmarks:
./scripts/test-postgres.sh -run '^$' -bench '^BenchmarkPostgres$' -benchmem -benchtime=1s -count=3

The project-specific compose.postgres.yml binds 127.0.0.1:55432, stores disposable data in tmpfs, and mounts no host directory. The default image is docker.1ms.run/library/postgres:18-alpine, using the 1ms mirror. Override the image or port per invocation:

SQLINK_TEST_POSTGRES_IMAGE=postgres:18-alpine ./scripts/test-postgres.sh
SQLINK_TEST_POSTGRES_PORT=55433 ./scripts/test-postgres.sh

SQLINK_TEST_POSTGRES_PASSWORD overrides the local test credential. The helper derives the published port, encodes the password for the connection URI, overrides any external PostgreSQL test DSN, and disables external MySQL tests for this local-only workflow. New PostgreSQL cases use random schemas with automatic cleanup. The original smoke suite resets its known tables inside this disposable local test database.

Stop and remove the test instance, including its temporary data:

docker compose -f compose.postgres.yml down

Go 1.27 integration tests verify pgx's direct scan path, its legacy fallback, and native DTO projections for PostgreSQL arrays/JSON. See performance benchmarks for measurements and reproduction commands.

Existing database servers

Live MySQL/MariaDB and PostgreSQL tests require DSNs; otherwise they skip:

SQlink_TEST_MYSQL_DSN="user:pass@tcp(127.0.0.1:3306)/sqlink?parseTime=true" \
SQlink_TEST_POSTGRES_DSN="postgres://user:pass@127.0.0.1:5432/sqlink" \
go test ./tests -run TestIntegration -v

go run . compares individual, transactional, and batch inserts using temporary databases. See performance benchmarks for measurements and reproduction commands.

License

MIT.

Standard slog output

db.Logger = orm.SlogLogger{
    Resolve: func(ctx context.Context) *slog.Logger {
        return requestLogger(ctx)
    },
}

SlogLogger owns the SQLink-to-slog mapping. Resolve is optional (defaults to slog.Default) and may select the request backend and attributes without dependencies on an application framework. It emits query duration and error type, omitting SQL text, arguments and raw driver error messages. Successful statements use DEBUG, slow statements WARN (200ms by default, configurable through SlowThreshold), failures ERROR, and canceled/deadline-exceeded requests INFO. LevelSilent suppresses the record before resolving a logger.