Databases

ant runs on five databases. PostgreSQL gets everything. The others run queries, writes, relations, change tracking, and transactions, but don't have migrations yet.

Support

	PostgreSQL	MySQL	MariaDB	SQLite	SQL Server
Tested on	13 to 18	8.4	11.8	modernc 1.61	2022
Dialect	postgres.Dialectvar Dialect dialect.DialectDialect is the PostgreSQL dialect.	mysql.Dialectvar Dialect dialect.DialectDialect is the MySQL dialect, for version 8.0 or later.	mysql.MariaDBvar MariaDB dialect.DialectMariaDB is the MariaDB dialect, for version 10.5 or later, which adds INSERT and DELETE ...	sqlite.Dialectvar Dialect dialect.DialectDialect is the SQLite dialect, for version 3.39 or later.	sqlserver.Dialectvar Dialect dialect.DialectDialect is the SQL Server dialect, for version 2019 or later.
Driver name	pgx	mysql	mysql	sqlite	sqlserver
Queries, relations, tracking, transactions	Yes	Yes	Yes	Yes	Yes
Migrations, EnsureCreatedfunc (c *Context) EnsureCreated(ctx context.Context) errorEnsureCreated creates the tables, indexes, and foreign keys of models that have no table yet, directly from the structs, and leaves existing tables as they are., SchemaOf	Yes	No	No	No	No
Whole feature suite	Yes	No	No	No	No
Conformance suite	Yes	Yes	Yes	Yes	Yes
Upsert	Yes	Yes	Yes	Yes	No
MERGE	From 15	No	No	No	Yes
Row locks	Yes	Yes	ForUpdatefunc (q *Query[T]) ForUpdate() *Query[T]ForUpdate locks the selected rows until the transaction ends. only	No	No
FULL JOIN	Yes	No	No	Yes	Yes
Lateral joins	Yes	Yes	No	No	No
UpdateReturningfunc (q *Query[T]) UpdateReturning(ctx context.Context, a ...Assignment) ([]*T, error)UpdateReturning is Update, returning the changed rows.	Yes	No	No	Yes	Yes
DeleteReturningfunc (q *Query[T]) DeleteReturning(ctx context.Context) ([]*T, error)DeleteReturning is Delete, returning the removed rows., Toggle	Yes	No	Yes	Yes	Yes
Today	Yes	Yes	Yes	No	No
DatePart	Yes	No	No	No	No
ILike, FILTER, NULLS FIRST	Native	Emulated	Emulated	ILike emulated	Emulated

Only PostgreSQL runs the full feature suite, whose tables are written in its own DDL. The conformance suite covers what each dialect writes differently, on every database, but it's smaller, so expect a few rough edges off PostgreSQL that nobody has hit yet.

When something is missing

ant refuses before it sends any SQL, so the error tells you what's missing. To check ahead of time, ask the dialect.

_, err := db.Users.Where(ant.Eq(&U.ID, id)).DeleteReturning(ctx)

if errors.Is(err, ant.ErrUnsupported) {
	log.Println("not on this database")
}

if dialect.Supports(db.Dialect(), dialect.Merge) {
	log.Println("MERGE is available")
}

Drivers

Import a driver in your application. ant doesn't choose one for you, with one exception: dialect/mysql imports go-sql-driver/mysql, because it has no other way to read the driver's error numbers.

import _ "github.com/jackc/pgx/v5/stdlib"  // PostgreSQL
import _ "github.com/go-sql-driver/mysql"  // MySQL and MariaDB
import _ "modernc.org/sqlite"              // SQLite
import _ "github.com/microsoft/go-mssqldb" // SQL Server

Use parseTime=true in a MySQL DSN, or times come back as bytes. Use _pragma=foreign_keys(1) in a SQLite DSN, or foreign keys aren't enforced.

What differs

Generated keys. MySQL has no RETURNING, so a key comes from LastInsertId, and other database-filled columns are read back with a second query. Rows with generated keys go in one at a time there, in both SaveChangesfunc (c *Context) SaveChanges(ctx context.Context) errorSaveChanges writes every change to tracked entities in one transaction: inserts and updates with parents first, then deletes with children first. and InsertManyfunc (t *Table[T]) InsertMany( ctx context.Context, entities []*T, options ...InsertOption, ) (int64, error)InsertMany writes many rows quickly, in multi-row statements inside one transaction, and returns how many were inserted..

ON DUPLICATE KEY on MySQL and MariaDB. It has no target: any unique key triggers it, not only the columns you name. If a different key is the one that collides, Upsertfunc (t *Table[T]) Upsert(ctx context.Context, e *T, conflict *Conflict) (bool, error)Upsert inserts e, or follows conflict if a unique constraint already holds the row. can't find the row by your columns, and reports that it wrote nothing. Name the only unique key that can collide.

OUTPUT on SQL Server. SQL Server refuses it on a table with triggers, so a model that needs a key back can't be inserted into one.

Upserts on SQL Server. There's no ON CONFLICT. Use ant.Mergefunc Merge[T, S any](t *Table[T], source *Query[S], on ...Cond) *Merger[T]Merge combines source rows into T's table, deciding per row what to do, which Upsert cannot: one statement can update, insert, and delete..

Paging on SQL Server. OFFSET ... FETCH needs an ORDER BY, so ant adds a meaningless one if the query has none. SQL Server refuses a Limitfunc (t *Table[T]) Limit(n int) *Query[T]Limit starts a query capped at n rows. of zero.

Times. SQLite stores times as text, and SQL Server's DATETIME2 has no zone, so ant binds UTC on both to keep one clock. MySQL's driver does the same. ant.Pastfunc Past(field *time.Time) CondPast matches rows where a time column is before the database's current time. and ant.Futurefunc Future(field *time.Time) CondFuture matches rows where a time column is after the database's current time. compare with the server's own clock, so run the server in UTC. On SQLite and MySQL, CURRENT_TIMESTAMP has whole seconds.

Parameters. Limits differ: 65,535 on PostgreSQL and MySQL, 32,766 on SQLite, and about 2,100 on SQL Server, where ant stays at 2,000. InsertMany, SaveChanges, and Includefunc (t *Table[T]) Include[R any](rel *[]R, shape ...func(*Query[R]) *Query[R]) *Query[T]Include starts a query that loads a one-to-many relation. split their work to fit. Your own In with a long list doesn't.

Types. postgres.Arraytype Array[] []VArray stores a slice in a PostgreSQL array column of text, numbers, or booleans., postgres.JSONSetfunc JSONSet[V any](field *V, value any, path ...string) ant.Expr[V]JSONSet returns a jsonb column with the value at path replaced, or added if the path's parent exists, for updating part of a document in one statement:, postgres.Vectortype Vector []float32Vector is a column of the pgvector extension, for similarity search., and the rest of dialect/postgres need PostgreSQL. Collate quotes its name, which SQL Server doesn't accept.

Adding a dialect

A dialect implements dialect.Dialecttype Dialect interface{ … }Dialect adapts ant to one database.. Its Render calls dialect.Renderfunc Render(d Dialect, stmt qb.Statement) (string, []any, error)Render converts a statement to SQL using d's placeholders and quoting., and its Syntax method lists what differs from PostgreSQL, so it states only its differences. dialect.RegisterErrorsfunc RegisterErrors(state func(error) string)RegisterErrors adds a function that turns a driver's error into a SQLSTATE code, or "", for a database whose driver does not report one. connects its error codes. Migrations are a separate interface, schema.Dialecttype Dialect interface{ … }Dialect is what a database implements to take part in migrations.. See dialect/sqlite/sqlite.go, which is about 70 lines.