Schemable provides basic struct mapping against a database, using the squirrel package.
NOTE: Only works on go 1.18, since it uses generics.
Schemable works with annotated structs, and schemers that bind those structs to tables.
type ComicTitle struct {
ID int64 `db:"id, PRIMARY KEY, AUTO INCREMENT"`
Name string `db:"name"`
Volume int `db:"volume"`
}
var ComicTitles = schemable.Bind[ComicTitle]("comic_titles")
Initialize the client and store in a context. This lets queries take advantage of context cancellation and timeouts.
// calls sql.Open(...)
client := schemable.New("sqlite3", "connection")
client.SetLogger(...) // optional, for logging queries
ctx := schemable.WithClient(context.Background(), client)
Schemers can list and delete multiple records:
import sq "github.com/Masterminds/squirrel"
recorders, err := ComicTitles.ListWhere(ctx, func(q sq.SelectBuilder) sq.SelectBuilder {
return q.Limit(10)
})
// Target is the actual *ComicTitle instance
recorders[0].Target
sqlResult, err := ComicTitles.DeleteWhere(ctx, func(q sq.DeleteBuilder) sq.DeleteBuilder {
return q.Where(sq.Eq{"id": 1})
})
Records are managed in Recorders that can Load, Insert, Update, and Delete. Updating only updates fields that have changed.
// initialize an empty instance
newRec := ComicTitles.Record(nil)
newRec.Target.Name = "The X-Men"
newRec.Target.Volume = 1
err := newRec.Insert(ctx)
// load record by primary key
rec := ComicTitles.Record(&ComicTitle{ID: 1})
ok, err := rec.Exists(ctx)
err = rec.Load(ctx)
// only updates name column
rec.Target.Name = "The Uncanny X-Men"
err = rec.Update(ctx)
// deletes record
err = rec.Delete(ctx)
Schemable works with db transactions too:
// TxOptions is optional and can be nil
txclient, err := client.Begin(ctx, &sql.TxOptions{...})
tctx := schemable.WithClient(ctx, txclient)
// alternatively, begin the transaction directly from the context:
// (*sql.TxOptions is still optional)
tctx, txclient, err := schemable.WithTransaction(ctx, nil)
txRec := ComicTitles.Record(nil)
txRec.Target.Title = "The Immortal X-Men"
err = txRec.Insert(tctx)
err = txclient.Commit() // or txclient.Rollback()
Both *DBClient
and *TxnClient
offer custom query support through squirrel:
q, args, err := client.Builder().Select("id").From("comic_titles").ToSql()
q, args, err := txclient.Builder().Delete("comic_titles").ToSql()
txclient.Exec(ctx, qu, args...)
txclient.Rollback() // whew!
In an effort to keep go.mod
tidy, the tests are implemented in the
schemabletest
package, designed to run in packages like
sqlitetest:
# both schemable and schemable_sqlitetest need to be in the same dir for
# bin/test.
$ git clone https://github.com/refractionist/schemable
$ git clone https://github.com/refractionist/schemable_sqlitetest
$ cd schemable_sqlitetest
# add -v to see the raw sql queries
$ bin/test
PASS
ok github.com/refractionist/schemable_sqlitetest 0.419s
- verify sqlite3 support
- verify mysql support
- verify postgres support
- GitHub Actions for supported databases
Heavily inspired by the structable package, and this Facilitator pattern for Go generics.