dbobjects

README

gorm-dbobjects

Manage database-side objects — triggers, views, stored procedures — as Go code, across multiple database engines, on top of gorm.

Why

gorm gets you most of the way to a working schema: AutoMigrate handles tables, columns, indexes. It has nothing for the things that live inside the database itself — triggers, views, stored procedures. Most teams end up hand-writing that SQL, checking it into a migrations/ folder, and hoping it stays in sync with the Go structs it's built around. gorm-dbobjects treats those objects as first-class, type-checked Go values instead: build them against your existing gorm models, and let a dialect layer generate the correct DDL for whichever engine you're actually connected to.

tr := trigger.BeforeUpdate(&User{}).
    Set("updated_at", trigger.Now())

if err := client.Register(ctx, tr); err != nil {
    log.Fatal(err)
}

Set("updated_at", ...) is checked against User's real gorm schema at build time — a typo'd column name fails before any SQL is generated, not as a runtime error against a live database.

Status

Triggers, views, and stored procedures are all implemented and tested end-to-end on Postgres, MySQL, and SQL Server (triggers and views also on SQLite, which has no stored procedure concept at all) — including a real, verified rollback path for engines where DDL isn't transactional, and a view built from a live gorm query callback, not just a raw SQL string. Release history: CHANGELOG.md.

	Postgres	MySQL	SQLite	SQL Server	Oracle
Triggers	✅	✅	✅	✅	planned
Views	✅	✅	✅	✅	planned
Procedures	✅	✅	n/a	✅	planned

Install

go get github.com/bg12345/gorm-dbobjects

Quickstart

package main

import (
    "context"
    "log"

    dbobjects "github.com/bg12345/gorm-dbobjects"
    "github.com/bg12345/gorm-dbobjects/trigger"
    "github.com/bg12345/gorm-dbobjects/view"
    "gorm.io/driver/postgres"
    "gorm.io/gorm"
)

type User struct {
    gorm.Model
    Name   string
    Email  string
    Active bool
}

func main() {
    db, err := gorm.Open(postgres.Open("your-dsn"), &gorm.Config{})
    if err != nil {
        log.Fatal(err)
    }

    // Wrap your *gorm.DB once, wherever you already set it up.
    client := dbobjects.NewClient(db)

    tr := trigger.BeforeUpdate(&User{}).
        Set("updated_at", trigger.Now())

    if err := client.Register(context.Background(), tr); err != nil {
        log.Fatal(err)
    }

    // Tear it back down if you ever need to.
    _ = client.Drop(context.Background(), tr)

    // Or just see the generated SQL without touching the DB.
    stmts, _ := client.Render(dbobjects.Idempotent, tr)
    for _, s := range stmts {
        log.Println(s)
    }
}

Register/Drop work the same way regardless of which engine db is connected to — the dialect that generates the actual DDL is resolved from the connection itself. Client is a thin wrapper around your *gorm.DB; construct one per connection rather than sharing global state, which also makes it safe to use from parallel tests.

All six BEFORE/AFTER × INSERT/UPDATE/DELETE combinations are supported. Set()/SetColumns() assign NEW columns and work on any INSERT/UPDATE trigger — including AfterInsert/AfterUpdate, where dbobjects renders them as a follow-up UPDATE under the hood, since NEW itself isn't assignable once an AFTER trigger fires:

tr := trigger.AfterUpdate(&User{}).
    Set("updated_at", trigger.Now())

DELETE triggers have no NEW row to assign into at all, so they use Body() — a raw-SQL escape hatch also available on any other trigger for logic Set() can't express:

tr := trigger.AfterDelete(&User{}).
    Body("INSERT INTO user_audit(user_id, action) VALUES (OLD.id, 'deleted');")

Views work the same way, built from a gorm query callback instead of a raw SQL string — dbobjects reuses gorm's own dialect layer to resolve it, so the same view definition renders correctly on whichever engine it's registered against:

v := view.New("active_users").
    Query(func(tx *gorm.DB) *gorm.DB {
        return tx.Model(&User{}).Where("active = ?", true)
    })

if err := client.Register(context.Background(), v); err != nil {
    log.Fatal(err)
}

For the one query gorm's builder can't express — Oracle's ROWNUM instead of LIMIT, say — Raw(sql) overrides Query with a literal string when you need the escape hatch:

v := view.New("recent_signups").
    Query(func(tx *gorm.DB) *gorm.DB {
        return tx.Model(&User{}).Order("created_at desc").Limit(100)
    }).
    Raw("SELECT * FROM (SELECT * FROM users ORDER BY created_at DESC) WHERE ROWNUM <= 100")

Stored procedures generate the signature/wrapper ceremony the same way — CREATE OR REPLACE PROCEDURE, DROP IF EXISTS + CREATE, CREATE OR ALTER PROCEDURE — around a raw SQL body, since control flow (loops, cursors, exception handling) can't be abstracted portably across engines the way a flat column assignment can:

proc := procedure.New("recalc_balances").
    Param("user_id", procedure.Int).
    Body("UPDATE accounts SET balance = balance + 1 WHERE id = user_id;")

if err := client.Register(context.Background(), proc); err != nil {
    log.Fatal(err)
}

Param types are either a portable constant/constructor (procedure.Int, Text, Bool, Time, Varchar(n), Char(n), Decimal(p,s), Float, Bytes, JSON), procedure.Raw(sqlType) for anything engine-specific (Postgres JSONB, SQL Server UNIQUEIDENTIFIER), or procedure.TypeOf(&Account{}, "ID") to derive one from an existing model field so it tracks the column instead of drifting if the column's type changes later. Not supported on SQLite, which has no stored procedure concept at all.

For more than one or two params, Params takes them all at once instead of chaining Param repeatedly — a slice, not a map, since a procedure's params are positional (CALL/EXEC binds arguments by declaration order):

proc := procedure.New("sp_set_user_name").
    Params(
        procedure.Param{Name: "user_id", Type: procedure.Int},
        procedure.Param{Name: "new_name", Type: procedure.Varchar(100)},
    ).
    Body("UPDATE user_master SET name = new_name WHERE id = user_id;")

How it's built

Each object kind (trigger, view, procedure) is its own small package with a fluent builder and zero knowledge of SQL dialects — trigger.BeforeUpdate(&User{}).Set(...) only knows about gorm schemas. A separate dialect layer, resolved from the connected *gorm.DB's driver name, turns that dialect-agnostic definition into the actual CREATE TRIGGER (or CREATE VIEW, CREATE PROCEDURE) statement for whichever engine is connected — Postgres's separate trigger function vs. MySQL's inline trigger body vs. SQL Server's inserted/deleted pseudo-tables are all real, structural differences the dialect layer accounts for, not just find-and-replaced SQL syntax.

A real example of what that buys you: Register batches multiple objects into one call. On engines with transactional DDL (Postgres, SQL Server, SQLite), the whole batch is wrapped in one transaction — a failure partway through rolls everything back for free. On engines that implicitly commit DDL statements (MySQL, Oracle), a real transaction isn't possible at all, so Register instead falls back to a best-effort compensating rollback — reverse-order cleanup of whatever already succeeded, using a context that survives the original call's cancellation. Both paths are covered by integration tests against real databases, not just unit tests against mocked SQL.

Migration-tool interop

Register/Drop apply DDL directly — the right tool for local dev, tests, and CI, or for projects with no separate migration tool. Teams already using a schema-diffing tool like Atlas shouldn't call Register at boot the same way they shouldn't run AutoMigrate there: it mutates the database outside of Atlas's own diffed, versioned migration history, and the next atlas migrate diff would see the trigger/view/procedure as unmanaged drift. Render exists for that case instead — it returns the generated DDL as plain strings without touching the database, meant to feed an external-schema source the same way atlas-provider-gorm already does for gorm's own table schema, so Atlas can diff and version triggers/views/procedures alongside the rest of your schema instead of around it.

Render has two modes. Idempotent is the same conditional DDL Register executes (CREATE OR REPLACE, DROP ... IF EXISTS) — safe to review or re-run directly. Declarative strips that down to bare CREATE statements with no conditionals, since a structural tool like Atlas parses the DDL itself and computes its own diff — conditional SQL just gets in its way:

stmts, _ := client.Render(dbobjects.Declarative, tr)

Worth knowing before wiring this up: Atlas gates the object kinds this library generates — views, triggers, and stored procedures/functions — behind its paid Pro plan, across every database engine Atlas supports, not a free-tier feature. Combining dbobjects's schema with gorm's own table schema also needs Atlas's composite_schema data source, which is separately Pro-gated regardless of what it's combining. Render itself has no Atlas dependency and works with any consumer of plain SQL strings — Atlas is one option for using that output, not the only one, and not a free one for what this library actually produces.

Known limitations

Real gaps in what's shipped today, not just missing engines — worth knowing before you hit them rather than after.

Triggers

• Set() on AfterInsert/AfterUpdate needs a table with exactly one primary key column (it renders as a follow-up UPDATE keyed by PK) — composite or missing-PK tables can't use it; Body() is the escape hatch. On SQL Server this restriction applies to every Set() call, not just AFTER, since SQL Server has no BEFORE DML trigger at all and Set() always renders AFTER there.
• No column-scoped triggers (UPDATE OF col1, col2) and no portable conditional-trigger (WHEN (...)) API — every trigger fires on any write to the table; scope it yourself inside Body() if you need to.
• Body()/Raw() content is unvalidated SQL — a typo surfaces as a live database error at Register() time, not a build-time check.
• On SQL Server, Body() on a BEFORE-declared trigger is a hard render-time error — SQL Server has no BEFORE DML trigger to keep, and there's no library-supported workaround (a raw INSTEAD OF trigger issued outside this library is the only path).

Views

• Query(fn) accepts an arbitrary gorm callback, so there's no single-table column list to validate a typo'd name against, the way Set() does for triggers.
• No materialized view support on Postgres — view.New(...) only ever renders a plain CREATE VIEW.

Procedures

• A Uint-typed Go field has no portable ParamType via TypeOf — Postgres and SQL Server have no native unsigned integer type at all. Use Raw() explicitly for it.
• Not supported on SQLite, which has no stored procedure concept at all.

Cross-cutting

• No introspection API — Client only has Register/Drop/Render; there's no way to ask "what does this library currently think it owns" short of querying each engine's own system catalog directly.
• Render(Declarative) output is handed back as plain strings with no syntax validation — a typo inside Body()/Raw() flows straight through to whatever consumes it.

Testing

Unit tests run with no setup:

go test ./...

Integration tests connect to real Postgres, MySQL, and SQL Server instances, configured via a .env file at the repo root (see .env.example) — they skip themselves automatically if no database is reachable. SQLite integration tests need no configuration or service at all (embedded, file-based) and run unconditionally. CI runs Postgres, MySQL, and SQL Server as service containers, and SQLite directly, on every push. See CONTRIBUTING.md for local setup, including SQL Server's Docker one-liner.

Contributing

See CONTRIBUTING.md — local setup, how the test suite is organized, and how to add support for a new database engine.

License

MIT

Documentation

Overview

Package dbobjects manages database-side objects -- triggers, views, and stored procedures -- as type-checked Go code on top of gorm, across multiple database engines (Postgres, MySQL, SQLite, SQL Server).

Build a trigger, view, or procedure with that kind's own fluent builder (the trigger, view, or procedure package), then apply it against a connected *gorm.DB through a Client:

client := dbobjects.NewClient(db)
tr := trigger.BeforeUpdate(&User{}).Set("updated_at", trigger.Now())
err := client.Register(ctx, tr)

Register/Drop apply the generated DDL directly -- the right fit for local development, tests, and CI, or projects with no separate migration tool. Render returns the same DDL as plain strings without touching the database, for feeding an external-schema source like Atlas instead.

Example

Example needs a real, live connection (Register applies DDL, Render resolves the connected dialect), so it's compiled but never executed -- there's no // Output: comment for go test to run against.

package main

import (
	"context"
	"log"

	dbobjects "github.com/bg12345/gorm-dbobjects"
	"github.com/bg12345/gorm-dbobjects/trigger"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
)

type User struct {
	gorm.Model
	Name string
}

// Example needs a real, live connection (Register applies DDL, Render
// resolves the connected dialect), so it's compiled but never executed
// -- there's no // Output: comment for go test to run against.
func main() {
	db, err := gorm.Open(postgres.Open("your-dsn"), &gorm.Config{})
	if err != nil {
		log.Fatal(err)
	}

	// Wrap your *gorm.DB once, wherever you already set it up.
	client := dbobjects.NewClient(db)

	tr := trigger.BeforeUpdate(&User{}).
		Set("updated_at", trigger.Now())

	if err := client.Register(context.Background(), tr); err != nil {
		log.Fatal(err)
	}
}

Index

• type Client
  • func NewClient(conn *gorm.DB) *Client
  • func (c *Client) Drop(ctx context.Context, objects ...DBObject) error
  • func (c *Client) Register(ctx context.Context, objects ...DBObject) error
  • func (c *Client) Render(mode RenderMode, objects ...DBObject) ([]string, error)
• type DBObject
• type RenderMode

Examples

• Package

Types

type Client

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

Client wraps a *gorm.DB connection and resolves the dialect that matches it. Immutable after NewClient -- safe for concurrent use from multiple goroutines, since gorm's own *gorm.DB already is.

func NewClient

func NewClient(conn *gorm.DB) *Client

func (*Client) Drop

func (c *Client) Drop(ctx context.Context, objects ...DBObject) error

Drop removes each object's corresponding DB-side definition (trigger, view, or procedure) from the Client's DB. Safe to call for objects that were never registered -- dialect DropTrigger implementations use DROP ... IF EXISTS.

func (*Client) Register

func (c *Client) Register(ctx context.Context, objects ...DBObject) error

Register builds each object's definition and executes the resulting DDL against the Client's DB, using the dialect that matches that DB's driver (see dialect.go).

func (*Client) Render

func (c *Client) Render(mode RenderMode, objects ...DBObject) ([]string, error)

Render returns the DDL for each object without executing it. Takes mode before objects, matching Register/Drop's shape (their fixed ctx param first, variadic objects last).

type DBObject

type DBObject interface {
	Kind() string
}

type RenderMode

type RenderMode int

RenderMode selects the DDL flavor Render produces.

const (
	// Idempotent is safe to execute directly and repeatedly -- CREATE OR
	// REPLACE, DROP ... IF EXISTS. The same DDL Register/Drop execute.
	Idempotent RenderMode = iota
	// Declarative strips conditionals down to bare CREATE statements (no
	// OR REPLACE, no DROP), for tools that parse and diff DDL
	// structurally themselves (e.g. Atlas as an external schema source)
	// rather than executing conditional DDL directly.
	// Not meant for repeated direct execution: re-running the same
	// Declarative output twice against a live DB will error on the
	// second run (object already exists) -- that's correct, expected
	// behavior for this mode, not a bug. Use Idempotent for anything
	// that needs to be safely re-runnable.
	Declarative
)