Fixed benchmark and results from the https://blog.jetbrains.com/go/2023/04/27/comparing-db-packages/ blog post

benchmark.go

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"time"

	"gorm.io/gorm"

	"github.com/glebarez/sqlite"
	"github.com/jmoiron/sqlx"
	sqlc "github.com/rexfordnyrk/go-db-comparison/benchmarks/sqlc_generated"
	//_ "modernc.org/sqlite"
)

func init() {
	var err error

	db, err = sql.Open("sqlite", "file::memory:?cache=shared")
	if err != nil {
		panic(err)
	}

	dbx, err = sqlx.Open("sqlite", "file::memory:?cache=shared")
	if err != nil {
		panic(err)
	}

	db2, err = sql.Open("sqlite", "file::memory:?cache=shared")
	if err != nil {
		panic(err)
	}
	dbc = sqlc.New(db2)

	gdb, err = gorm.Open(sqlite.Open("file::memory:?cache=shared"), &gorm.Config{})
	if err != nil {
		panic(err)
	}
}

var (
	gdb *gorm.DB
	db  *sql.DB
	db2  *sql.DB
	dbx *sqlx.DB
	dbc *sqlc.Queries
)

func setup(db *sql.DB) {
	clear()
	table := `CREATE TABLE students (
		id integer,
		first_name varchar(50) not null,
		last_name varchar(50) not null,
		date_of_birth datetime not null,
		email varchar(50) not null,
		address varchar(50) not null,
		gender varchar(50) not null
	);`
	_, err := db.Exec(table)
	if err != nil {
		panic(err)
	}

	affected, err := db.Exec(records)
	if err != nil {
		panic(err)
	}
	fmt.Printf("Affected rows: %d\n", affected)
}

func clear() {
	_, err := db.Exec(`DROP TABLE if exists students`)
	if err != nil {
		panic(err)
	}
}

type Student struct {
	ID          int64
	Fname       string `db:"first_name"`
	Lname       string `db:"last_name"`
	DateOfBirth time.Time `db:"date_of_birth"`
	Email       string `db:"email"`
	Address     string `db:"address"`
	Gender      string `db:"gender"`
}

func DbSqlQueryStudentWithLimit(limit int) int {
	var students []Student
	rows, err := db.Query("SELECT * FROM students limit ?", limit)
	if err != nil {
		log.Fatalf("DbSqlQueryStudentWithLimit %d %v", limit, err)
	}
	defer rows.Close()

	// Loop through rows, using Scan to assign column data to struct fields.
	for rows.Next() {
		var s Student
		if err := rows.Scan(&s.ID, &s.Fname, &s.Lname, &s.DateOfBirth, &s.Email, &s.Address, &s.Gender); err != nil {
			log.Fatalf("DbSqlQueryStudentWithLimit %d %v", limit, err)
		}
		students = append(students, s)
	}
	if err := rows.Err(); err != nil {
		log.Fatalf("DbSqlQueryStudentWithLimit %d %v", limit, err)
	}
	return len(students)
}

func SqlxQueryStudentWithLimit(limit int) int {
	var students []Student
	err := dbx.Select(&students, "SELECT * FROM students LIMIT ?", limit)
	if err != nil {
		log.Fatalf("SqlxQueryStudentWithLimit %d %v", limit, err)
	}
	return len(students)
}

func SqlcQueryStudentWithLimit(limit int) int {
	students, err := dbc.FetchStudents(context.Background(), int32(limit))
	if err != nil {
		log.Fatalf("SqlcQueryStudentWithLimit %d %v", limit, err)
	}
	return len(students)
}

func GormQueryStudentWithLimit(limit int) int {
	var students []Student
	if err := gdb.Limit(limit).Find(&students).Error; err != nil {
		log.Fatalf("GormQueryStudentWithLimit %d %v", limit, err)
	}
	return len(students)
}

benchmark_test.go

package main

import (
	"fmt"
	"testing"
)

func Benchmark(b *testing.B) {
	setup(db)
	setup(dbx.DB)
	setup(db2)
	setup(db)
	defer clear()

	// Benchmark goes in here
	limits := []int{
		1,
		10,
		100,
		1000,
		10000,
		15000,
	}

	for _, lim := range limits { // Fetch varying number of rows

		fmt.Printf("================================== BENCHMARKING %d RECORDS ======================================\n", lim)
		// Benchmark Database/sql
		b.Run(fmt.Sprintf("Database/sql limit:%d ", lim), func(b *testing.B) {
			for i := 0; i < b.N; i++ {
				if DbSqlQueryStudentWithLimit(lim) != lim {
					b.Fatalf("DbSqlQueryStudentWithLimit did not return %d records", lim)
				}
			}
		})

		// Benchmark Sqlx
		b.Run(fmt.Sprintf("Sqlx limit:%d ", lim), func(b *testing.B) {
			for i := 0; i < b.N; i++ {
				if SqlxQueryStudentWithLimit(lim) != lim {
					b.Fatalf("SqlxQueryStudentWithLimit did not return %d records", lim)
				}
			}
		})

		// Benchmark Sqlc
		/*
		b.Run(fmt.Sprintf("Sqlc limit:%d ", lim), func(b *testing.B) {
			for i := 0; i < b.N; i++ {
				if SqlcQueryStudentWithLimit(lim) != lim {
					b.Fatalf("SqlcQueryStudentWithLimit did not return %d records", lim)
				}
			}
		})*/

		// Benchmark GORM
		b.Run(fmt.Sprintf("GORM limit:%d ", lim), func(b *testing.B) {
			for i := 0; i < b.N; i++ {
				if GormQueryStudentWithLimit(lim) != lim {
					b.Fatalf("GormQueryStudentWithLimit did not return %d records", lim)
				}
			}
		})

		fmt.Println("=================================================================================================")
	}
}

results.txt

Macbook-16-M1Pro% go test -bench=. .
================================== BENCHMARKING 1 RECORDS ======================================
goos: darwin
goarch: arm64
pkg: temp
Benchmark/Database/sql_limit:1_-10                 94777             12491 ns/op
Benchmark/Sqlx_limit:1_-10                         87447             13437 ns/op
Benchmark/GORM_limit:1_-10                         68102             17199 ns/op
=================================================================================================
================================== BENCHMARKING 10 RECORDS ======================================
Benchmark/Database/sql_limit:10_-10                33517             35899 ns/op
Benchmark/Sqlx_limit:10_-10                        31227             38302 ns/op
Benchmark/GORM_limit:10_-10                        27505             43593 ns/op
=================================================================================================
================================== BENCHMARKING 100 RECORDS ======================================
Benchmark/Database/sql_limit:100_-10                4564            255637 ns/op
Benchmark/Sqlx_limit:100_-10                        4304            277219 ns/op
Benchmark/GORM_limit:100_-10                        3711            311985 ns/op
=================================================================================================
================================== BENCHMARKING 1000 RECORDS ======================================
Benchmark/Database/sql_limit:1000_-10                484           2448140 ns/op
Benchmark/Sqlx_limit:1000_-10                        457           2594044 ns/op
Benchmark/GORM_limit:1000_-10                        400           2969418 ns/op
=================================================================================================
================================== BENCHMARKING 10000 RECORDS ======================================
Benchmark/Database/sql_limit:10000_-10                46          24620349 ns/op
Benchmark/Sqlx_limit:10000_-10                        45          26265074 ns/op
Benchmark/GORM_limit:10000_-10                        39          29704668 ns/op
=================================================================================================
================================== BENCHMARKING 15000 RECORDS ======================================
Benchmark/Database/sql_limit:15000_-10                31          36463359 ns/op
Benchmark/Sqlx_limit:15000_-10                        27          39348892 ns/op
Benchmark/GORM_limit:15000_-10                        26          43955534 ns/op
=================================================================================================
PASS
ok      temp    24.824s

The context:

• https://blog.jetbrains.com/go/2023/04/27/comparing-db-packages/
• https://www.reddit.com/r/golang/comments/130kxaw/comparing_databasesql_gorm_sqlx_and_sqlc/

I have found out that the benchmark contains a bug in benchmark.go line 65. There is incorrectly handled error which hides one significant problem which is that the setup function responsible for loading the example data fails with: SQL logic error: table students has no column named first_name

Therefore the whole benchmark is performed on an empty table, there is literally no data so all this tests are roundtrips to database with empty results and this is repeated over and over again. No mapping is performed since there is no data, all tested functions also return empty slices.

After I fixed this, another issue is that in the example data, id is not unique. Also are these real e-mails of real people?! Anyways, after removal of the primary key which is unused the benchmark now shows numbers which are closer to what I would expect (tested on sqlite3 with in-memory database):

• database/sql the fastest
• sqlx slightly behind
• sqlc I did not test I was lazy to regenerate the code
• gorm lagging 20% behind the winner

Please do not make any conclusions from this "benchmark", I was just researching a bug in the benchmark code. This is NOT how you should benchmark SQL libraries.