GORM With MySQL and SQLite and the Differences to Expect

GORM advertises that you can switch databases by changing one line, and the line does change: postgres.Open, mysql.Open or sqlite.Open. The code compiles and the tests pass. What does not change is how each database behaves, and an application that works on one can have different results, or a different set of bugs, on another. This article runs one model against all three on the same machine (PostgreSQL 18.6, MySQL 8.4 and an in memory SQLite) and compares what comes back.

It continues from the GORM tutorial and models. If you are choosing between engines, choosing a database for Go is the decision article, and SQLite with modernc covers the driver used here. For tests that use SQLite in place of PostgreSQL, read GORM testing next.

The lab

The program opens each database, migrates the same Person model, and prints one block of results. TranslateError: true is set on all three, so the driver specific errors are mapped to GORM's own.

package main

import (
	"errors"
	"fmt"
	"log"
	"time"

	"github.com/glebarez/sqlite"
	"gorm.io/driver/mysql"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/clause"
	"gorm.io/gorm/logger"
)

type Person struct {
	ID    uint
	Name  string `gorm:"size:50;uniqueIndex"`
	Admin bool
	Seen  time.Time
	Notes string
}

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

func open(name string) *gorm.DB {
	cfg := &gorm.Config{Logger: logger.Discard, TranslateError: true}
	var db *gorm.DB
	var err error
	switch name {
	case "postgres":
		base := "host=127.0.0.1 port=55432 user=postgres password=secret dbname=lab sslmode=disable"
		admin, e := gorm.Open(postgres.Open(base), cfg)
		must(e)
		admin.Exec("drop schema if exists l0723 cascade")
		admin.Exec("create schema l0723")
		db, err = gorm.Open(postgres.Open(base+" search_path=l0723"), cfg)
	case "mysql":
		admin, e := gorm.Open(mysql.Open("root:secret@tcp(127.0.0.1:33099)/lab"), cfg)
		must(e)
		admin.Exec("drop database if exists l0723")
		admin.Exec("create database l0723")
		db, err = gorm.Open(mysql.Open("root:secret@tcp(127.0.0.1:33099)/l0723?parseTime=true&loc=UTC"), cfg)
	default:
		db, err = gorm.Open(sqlite.Open("file:l0723?mode=memory&cache=shared"), cfg)
	}
	must(err)
	return db
}

func main() {
	seen := time.Date(2024, 1, 2, 3, 4, 5, 123456789, time.UTC)
	for _, name := range []string{"postgres", "mysql", "sqlite"} {
		db := open(name)
		must(db.AutoMigrate(&Person{}))
		fmt.Println("==", name)

		types, _ := db.Migrator().ColumnTypes(&Person{})
		line := ""
		for _, c := range types {
			line += c.Name() + ":" + c.DatabaseTypeName() + " "
		}
		fmt.Println("  columns:", line)

		p := Person{Name: "Ada", Admin: true, Seen: seen, Notes: "x"}
		must(db.Create(&p).Error)
		var back Person
		db.First(&back, p.ID)
		fmt.Println("  id filled:", p.ID, "| nanoseconds after round trip:", back.Seen.Nanosecond())

		var n int64
		db.Model(&Person{}).Where("name like ?", "ada").Count(&n)
		fmt.Println("  LIKE 'ada' matches 'Ada':", n == 1)
		db.Model(&Person{}).Where("name = ?", "ADA").Count(&n)
		fmt.Println("  = 'ADA' matches 'Ada':", n == 1)

		err := db.Create(&Person{Name: "Ada", Seen: seen}).Error
		fmt.Println("  duplicate is ErrDuplicatedKey:", errors.Is(err, gorm.ErrDuplicatedKey))

		res := db.Clauses(clause.OnConflict{DoNothing: true}).Create(&Person{Name: "Ada", Seen: seen})
		fmt.Println("  DoNothing error:", res.Error, "rows:", res.RowsAffected)

		// What happens to the rest of a transaction after a failed statement?
		err = db.Transaction(func(tx *gorm.DB) error {
			tx.Create(&Person{Name: "Ada", Seen: seen}) // fails, error ignored
			return tx.Create(&Person{Name: "Bob" + name, Seen: seen}).Error
		})
		fmt.Println("  insert after a failed insert in one transaction:", err == nil)
	}
}

== postgres
  columns: id:int8 name:varchar admin:bool seen:timestamptz notes:text 
  id filled: 1 | nanoseconds after round trip: 123456000
  LIKE 'ada' matches 'Ada': false
  = 'ADA' matches 'Ada': false
  duplicate is ErrDuplicatedKey: true
  DoNothing error: <nil> rows: 0
  insert after a failed insert in one transaction: false
== mysql
  columns: id:bigint name:varchar admin:tinyint seen:datetime notes:longtext 
  id filled: 1 | nanoseconds after round trip: 123000000
  LIKE 'ada' matches 'Ada': true
  = 'ADA' matches 'Ada': true
  duplicate is ErrDuplicatedKey: true
  DoNothing error: <nil> rows: 0
  insert after a failed insert in one transaction: true
== sqlite
  columns: id:integer name:text admin:numeric seen:datetime notes:text 
  id filled: 1 | nanoseconds after round trip: 123456789
  LIKE 'ada' matches 'Ada': true
  = 'ADA' matches 'Ada': false
  duplicate is ErrDuplicatedKey: true
  DoNothing error: <nil> rows: 0
  insert after a failed insert in one transaction: true

Column types

Migrator().ColumnTypes reported what each database built from the same struct:

Nothing here is a bug; it is the dialect's best match. The consequences are practical. MySQL stores a bool as tinyint(1), so a raw query comparing admin = true works but a driver that returns an integer needs the GORM layer to convert. SQLite has dynamic typing: a column's declared type is advice, and the size:50 limit is not enforced, so a 5000 character name is stored. PostgreSQL's timestamptz stores an absolute instant, while MySQL's datetime stores a wall clock value with no zone, which is why the MySQL DSN in the lab sets loc=UTC and parseTime=true. Leave either out and times come back as the wrong zone or as []byte.

Case sensitivity

The lab stored Ada and searched three ways:

PostgreSQL compares text exactly, so LIKE is case sensitive (use ILIKE or lower(name)). MySQL's default collation is case insensitive for both = and LIKE, so searches match more than you expect, and a unique index treats Ada and ada as duplicates. SQLite's LIKE ignores case for ASCII letters while = does not.

This difference does the most damage in tests. A login that looks up by email passes on MySQL with [email protected] and fails on PostgreSQL. A fix that holds on all three is to normalise at the boundary (lowercase emails on the way in) and store the normalised value. A citext column or a lower(email) index does it in PostgreSQL.

Time precision

The stored time was 2024-01-02 03:04:05.123456789. The nanoseconds that came back:

• PostgreSQL: 123456000. It stores microseconds and truncates the rest.
• MySQL: 123000000. GORM creates datetime(3), so the precision is milliseconds.
• SQLite: 123456789. It stores the full text form the driver wrote.

Any test that creates a row and then compares the time with == against the original passes on SQLite and fails on the other two. Compare with time.Equal after Truncate(time.Millisecond), or round to the coarsest precision you support. The same precision story applies to updated_at comparisons in optimistic locking code.

In my first draft of this lab, I created rows with a zero time.Time. PostgreSQL and SQLite accepted 0001-01-01. MySQL in strict mode refused it with Error 1292 (22007): Incorrect datetime value: '0000-00-00'. A struct with a time field you never set is a bug on MySQL and a quiet default elsewhere.

Duplicate keys

A second Ada violates the unique index. With TranslateError: true, all three printed duplicate is ErrDuplicatedKey: true. Without that flag, you would match three different driver errors: a *pgconn.PgError with code 23505, a *mysql.MySQLError with number 1062, and a SQLite message. The flag is the thing to turn on for portable code. Note that MySQL's collation made Ada match in the first place, so the duplicate there depends on the collation.

OnConflict{DoNothing: true} worked on all three and reported 0 rows inserted, but the SQL differs (ON CONFLICT DO NOTHING on PostgreSQL and SQLite, an INSERT ... ON DUPLICATE KEY UPDATE no-op on MySQL). Upserts with DoUpdates need the conflict column named on PostgreSQL and SQLite, and ignore it on MySQL, which fires on any unique key. Read the SQL with Debug() the first time you port one.

Transactions after an error

The last check ran two inserts in one transaction, ignoring the first one's duplicate key error. The second insert (a different name) succeeded on MySQL and SQLite and failed on PostgreSQL:

insert after a failed insert in one transaction: false on PostgreSQL, true on the other two.

PostgreSQL marks the whole transaction aborted after any error, and every later statement fails with current transaction is aborted until rollback (transactions covers it). MySQL and SQLite let the transaction continue. Code that catches a constraint error and proceeds works on two of the three. Code written against PostgreSQL's rule is stricter and also works on the others, so develop against PostgreSQL and the surprise runs the safe way.

Other differences worth knowing

• Returning values. PostgreSQL and SQLite (3.35+) support RETURNING; MySQL does not, and GORM reads LastInsertId instead. Batch inserts on MySQL get only the first id from the driver and compute the rest, which is right for auto increment under the default lock mode and wrong in some replication setups.
• Concurrency. SQLite allows one writer at a time. A write heavy service with several connections gets database is locked unless you use WAL mode and a busy timeout (SQLite WAL).
• DDL in transactions. PostgreSQL rolls back schema changes with the transaction. MySQL commits implicitly on DDL, so a failed migration leaves a half applied schema.
• Foreign keys. SQLite ignores them until you enable the pragma per connection.
• Identifier quoting. GORM quotes for you, with double quotes on PostgreSQL and SQLite and backticks on MySQL. Raw SQL must do it by hand.

What to do with this

1. Pick one engine for production and test against it. SQLite in tests is fast and lies about everything above. GORM testing shows the PostgreSQL schema per test approach.
2. Normalise data at the boundary (case, time zone, precision) so no query depends on a collation.
3. Turn on TranslateError and match gorm.ErrDuplicatedKey.
4. If you really must support several databases (a self hosted product), run your whole test suite against each in CI.

Checklist

• Do not assume LIKE and = mean the same on every database.
• Compare times after truncating to the lowest precision in use.
• Set loc=UTC and parseTime=true on the MySQL DSN, and never leave a required time zero.
• Match gorm.ErrDuplicatedKey with TranslateError: true.
• Remember that only PostgreSQL aborts a transaction on error.

The dialect behaviour is covered in the GORM drivers on gorm.io and in the MySQL collation documentation.