Ent With PostgreSQL MySQL and SQLite and Their Differences

Ent advertises support for PostgreSQL, MySQL, SQLite and a few others, and it keeps that promise at the API level: the same schema and the same builder code run on all of them. What the promise cannot cover is what the databases do with the data. This article runs one schema, with a time, an enum, JSON, a unique string and a bool, on PostgreSQL 18.6, MySQL 8.4 and an in memory SQLite, and compares the generated tables and the behaviour. It is the Ent counterpart of the GORM dialect article.

For choosing between engines, see choosing a database for Go. The Ent basics are in the tutorial and the schema options in the schema article.

Opening each dialect

The dialect and the driver are the only things that change. All three go through database/sql and Ent's entsql.OpenDB:

• PostgreSQL: dialect.Postgres with the pgx stdlib driver ("pgx").
• MySQL: dialect.MySQL with go-sql-driver/mysql, with parseTime=true and loc=UTC in the DSN so times scan into time.Time.
• SQLite: dialect.SQLite with a pure Go driver (modernc.org/sqlite, registered as "sqlite"), and _pragma=foreign_keys(1) so foreign keys are enforced.

package main

import (
	"context"
	"database/sql"
	"fmt"
	"os"
	"strings"
	"time"

	"entgo.io/ent/dialect"
	entsql "entgo.io/ent/dialect/sql"
	"example.test/lab/ent"
	"example.test/lab/ent/person"
	_ "github.com/go-sql-driver/mysql"
	_ "modernc.org/sqlite"
)

func openAll(name string) *ent.Client {
	switch name {
	case "postgres":
		return open("l0743")
	case "mysql":
		admin, _ := sql.Open("mysql", "root:secret@tcp(127.0.0.1:33099)/lab")
		admin.Exec("drop database if exists l0743")
		admin.Exec("create database l0743")
		admin.Close()
		db, err := sql.Open("mysql", "root:secret@tcp(127.0.0.1:33099)/l0743?parseTime=true&loc=UTC")
		must(err)
		return ent.NewClient(ent.Driver(entsql.OpenDB(dialect.MySQL, db)))
	default:
		db, err := sql.Open("sqlite", "file:l0743?mode=memory&cache=shared&_pragma=foreign_keys(1)")
		must(err)
		return ent.NewClient(ent.Driver(entsql.OpenDB(dialect.SQLite, db)))
	}
}

func main() {
	ctx := context.Background()
	seen := time.Date(2024, 1, 2, 3, 4, 5, 123456789, time.UTC)
	for _, name := range []string{"postgres", "mysql", "sqlite"} {
		client := openAll(name)
		fmt.Println("==", name)
		var ddl strings.Builder
		must(client.Schema.WriteTo(ctx, &ddl))
		for _, l := range strings.Split(ddl.String(), "\n") {
			l = strings.TrimSpace(l)
			if strings.HasPrefix(l, "\"name\"") || strings.HasPrefix(l, "`name`") || strings.Contains(l, "seen") || strings.Contains(l, "role") || strings.Contains(l, "tags") || strings.Contains(l, "admin") {
				fmt.Println("  ddl:", strings.TrimSuffix(l, ","))
			}
		}
		must(client.Schema.Create(ctx))

		p, err := client.Person.Create().SetName("Ada").SetSeen(seen).SetTags([]string{"go", "db"}).Save(ctx)
		must(err)
		back := client.Person.GetX(ctx, p.ID)
		fmt.Println("  nanoseconds after round trip:", back.Seen.Nanosecond(), "| tags:", back.Tags, "| role:", back.Role)

		n := client.Person.Query().Where(person.NameEQ("ADA")).CountX(ctx)
		f := client.Person.Query().Where(person.NameEqualFold("ADA")).CountX(ctx)
		fmt.Println("  NameEQ('ADA') matches 'Ada':", n == 1, "| NameEqualFold:", f == 1)

		_, err = client.Person.Create().SetName("Ada").SetSeen(seen).Save(ctx)
		fmt.Println("  duplicate is a constraint error:", ent.IsConstraintError(err))

		_, err = client.Person.Create().SetName("Role").SetSeen(seen).SetRole("owner").Save(ctx)
		fmt.Println("  invalid enum rejected by Ent before the database:", err != nil)

		err = client.Person.Create().SetName("Ada").SetSeen(seen).OnConflictColumns(person.FieldName).UpdateNewValues().Exec(ctx)
		fmt.Println("  upsert error:", err)

		_ = os.Stdout
		client.Close()
	}
}

package main

import (
	"database/sql"
	"log"

	"entgo.io/ent/dialect"
	entsql "entgo.io/ent/dialect/sql"
	"example.test/lab/ent"
	_ "github.com/jackc/pgx/v5/stdlib"
)

const base = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"

// open recreates a schema and returns a client whose connections use it.
func open(schema string, opts ...ent.Option) *ent.Client {
	admin, err := sql.Open("pgx", base)
	if err != nil {
		log.Fatal(err)
	}
	admin.Exec("drop schema if exists " + schema + " cascade")
	admin.Exec("create schema " + schema)
	admin.Close()
	db, err := sql.Open("pgx", base+"&search_path="+schema)
	if err != nil {
		log.Fatal(err)
	}
	drv := entsql.OpenDB(dialect.Postgres, db)
	return ent.NewClient(append([]ent.Option{ent.Driver(drv)}, opts...)...)
}

func must(err error) {
	if err != nil {
		log.SetFlags(0)
		log.Fatal(err)
	}
}

package ent

//go:generate go run -mod=mod entgo.io/ent/cmd/ent generate --feature sql/upsert ./schema

package schema

import (
	"entgo.io/ent"
	"entgo.io/ent/schema/field"
)

type Person struct{ ent.Schema }

func (Person) Fields() []ent.Field {
	return []ent.Field{
		field.String("name").Unique().MaxLen(50),
		field.Bool("admin").Default(false),
		field.Time("seen"),
		field.Enum("role").Values("reader", "editor").Default("reader"),
		field.JSON("tags", []string{}).Optional(),
		field.Text("notes").Optional(),
	}
}

set -e
GOFLAGS=-mod=mod go generate ./ent
go mod tidy > /dev/null 2>&1
go run .

== postgres
  ddl: CREATE TABLE "persons" ("id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY, "name" character varying NOT NULL, "admin" boolean NOT NULL DEFAULT false, "seen" timestamptz NOT NULL, "role" character varying NOT NULL DEFAULT 'reader', "tags" jsonb NULL, "notes" text NULL, PRIMARY KEY ("id"));
  nanoseconds after round trip: 123456000 | tags: [go db] | role: reader
  NameEQ('ADA') matches 'Ada': false | NameEqualFold: true
  duplicate is a constraint error: true
  invalid enum rejected by Ent before the database: true
  upsert error: <nil>
== mysql
  ddl: CREATE TABLE `persons` (`id` bigint NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `admin` bool NOT NULL DEFAULT false, `seen` timestamp NOT NULL, `role` enum('reader','editor') NOT NULL DEFAULT 'reader', `tags` json NULL, `notes` longtext NULL, PRIMARY KEY (`id`), UNIQUE INDEX `name` (`name`)) CHARSET utf8mb4 COLLATE utf8mb4_bin;
  nanoseconds after round trip: 0 | tags: [go db] | role: reader
  NameEQ('ADA') matches 'Ada': false | NameEqualFold: true
  duplicate is a constraint error: true
  invalid enum rejected by Ent before the database: true
  upsert error: <nil>
== sqlite
  ddl: CREATE TABLE `persons` (`id` integer NOT NULL PRIMARY KEY AUTOINCREMENT, `name` text NOT NULL, `admin` bool NOT NULL DEFAULT (false), `seen` datetime NOT NULL, `role` text NOT NULL DEFAULT ('reader'), `tags` json NULL, `notes` text NULL);
  nanoseconds after round trip: 123456789 | tags: [go db] | role: reader
  NameEQ('ADA') matches 'Ada': false | NameEqualFold: true
  duplicate is a constraint error: true
  invalid enum rejected by Ent before the database: true
  upsert error: <nil>

The Person schema has a unique name of at most 50 characters, a bool with a default, a required time, an enum with a default, an optional JSON list and an optional text field. The generated client is the same for all three.

The tables

Schema.WriteTo prints the DDL for each dialect:

Reading across:

• Length limits. MaxLen(50) appears as varchar(50) on MySQL. On PostgreSQL the column is character varying with no length and Ent enforces the limit in Go; on SQLite types are advisory anyway. A second writer to the table is bound only by MySQL's column.
• Enums. Only MySQL gets a native enum. On the other two, the allowed values live in Ent's validator, and the database will accept any string from a script. If an invalid enum value would be a problem, add a check constraint on PostgreSQL and SQLite.
• Time. timestamptz on PostgreSQL stores an instant. MySQL's timestamp and SQLite's datetime hold a wall clock value; the zone you saw comes from the driver settings (loc=UTC).
• JSON. jsonb on PostgreSQL supports indexing and the containment operators. json on MySQL is validated text with functions. SQLite stores text and offers JSON functions. Reading and writing through Ent works the same.

Time precision

The program stores 2024-01-02 03:04:05.123456789 UTC and reads it back:

• PostgreSQL: 123456000 nanoseconds. Microsecond precision.
• MySQL: 0. Ent's MySQL DDL used timestamp without fractional seconds, so the sub second part was dropped. Compare this with GORM, which creates datetime(3) and keeps milliseconds (GORM's result). If you need sub second precision on MySQL with Ent, set the column type: field.Time("seen").SchemaType(map[string]string{dialect.MySQL: "datetime(6)"}).
• SQLite: 123456789. The full value, stored as text.

Any test that round trips a time and compares with == passes on SQLite and fails on the other two. Truncate to the coarsest precision you support before comparing, or use Time.Equal after truncation. The same hazard exists with every ORM, and it is worth knowing that Ent's defaults differ from GORM's on MySQL.

Case sensitivity

NameEQ("ADA") against the stored Ada returned no match on all three, and NameEqualFold("ADA") matched on all three. That is the opposite of what a MySQL developer expects, where comparisons are normally case insensitive. The reason is in the MySQL DDL line: CHARSET utf8mb4 COLLATE utf8mb4_bin. Ent creates tables with the binary collation, which compares bytes. A migrated MySQL table that you created yourself with the default utf8mb4_0900_ai_ci collation behaves differently, so Ent code that works against its own tables can change behaviour when pointed at an existing database.

EqualFold is the portable way to ask for a case insensitive match. Its SQL differs per dialect (a LOWER() comparison or ILIKE), and neither uses a plain index. For case insensitive lookups at scale, normalise on write (store a lowercased copy) and index that.

Constraints and errors

A second Ada failed the unique index on all three and ent.IsConstraintError(err) was true each time. That is Ent's value here: you do not match three drivers' error codes. A bad enum value (owner) was rejected before reaching any database, so invalid enum rejected by Ent before the database: true for all three.

The upsert builder, enabled by the sql/upsert feature, ran with OnConflictColumns(person.FieldName).UpdateNewValues() and returned no error everywhere. The SQL differs (ON CONFLICT ... DO UPDATE on PostgreSQL and SQLite, ON DUPLICATE KEY UPDATE on MySQL), and so does the meaning of the conflict target: MySQL fires on any unique key, regardless of the columns you name.

Other differences that matter

• Transactions after an error. PostgreSQL aborts the transaction; MySQL and SQLite continue (Ent transactions).
• DDL. PostgreSQL rolls back schema changes in a transaction. MySQL commits implicitly on DDL.
• Concurrency. SQLite allows one writer, so a service with concurrent writes needs WAL mode and a busy timeout (SQLite WAL).
• Foreign keys. SQLite ignores them unless the pragma is set on each connection.
• Migrations. Atlas supports all three, and a versioned migration is generated per dialect, so a project that targets two engines needs two directories.

Using this

If you ship against one engine, develop and test on it. SQLite in tests of code that depends on any of the above passes for the wrong reasons (the testing article). If you ship a product that runs on several engines, run the whole suite on each in CI, normalise data (case, time precision) at the boundary, and avoid dialect specific field types unless you set SchemaType for each.

Checklist

• Change the dialect and driver, and nothing in the builders.
• Set the DSN options that make times behave: parseTime, loc, SQLite's foreign key pragma.
• Expect time precision to differ, and set SchemaType when it matters.
• Use EqualFold for case insensitive matches, and know that Ent's MySQL tables are case sensitive.
• Enforce enums and limits in the database too if other writers exist.

The dialect support is described in the Ent supported databases list and the field type overrides in the schema fields documentation.