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 and views are both implemented and tested end-to-end on Postgres, MySQL, and SQLite — 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. Procedures are designed but not yet built — see the design notes below.

	Postgres	MySQL	SQLite	SQL Server	Oracle
Triggers	✅	✅	✅	planned	planned
Views	✅	✅	✅	planned	planned
Procedures	planned	planned	planned	planned	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")

How it's built

Each object kind (trigger, and eventually 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)

Testing

Unit tests run with no setup:

go test ./...

Integration tests connect to real Postgres and MySQL 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 and MySQL as service containers, and SQLite directly, on every push.

License

MIT

Documentation

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

Types

type Client

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

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, and later view/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
)