A comparison of popular Go SQL query builders. Provides feature list and benchmarks
- dbr: https://github.com/gocraft/dbr
- squirrel: https://github.com/lann/squirrel
- sqrl: https://github.com/elgris/sqrl
- gocu: https://github.com/doug-martin/goqu - just for SELECT query
- sqlf: https://github.com/leporo/sqlf
| feature | dbr | squirrel | sqrl | goqu | sqlf |
|---|---|---|---|---|---|
| SelectBuilder | + | + | + | + | + |
| InsertBuilder | + | + | + | + | + |
| UpdateBuilder | + | + | + | + | + |
| DeleteBuilder | + | + | + | + | + |
| PostgreSQL support | + | + | + | + | + |
| Custom placeholders | + | + | + | + | + |
| JOINs support | + | + | + | + | |
| Subquery in query builder | + | + | + | + | |
| Aliases for columns | + | + | + | ||
| CASE expression | + | + | + |
Some explanations here:
Custom placeholders- ability to use not only?placeholders, Useful for PostgreSQLJOINs support- ability to build JOINs in SELECT queries likeSelect("*").From("a").Join("b")Subquery in query builder- when you prepare a subquery with one builder and then pass it to another. Something like:
subQ := Select("aa", "bb").From("dd")
qb := Select().Column(subQ).From("a")Aliases for columns- easy way to alias a column, especially if column is specified by subquery:
subQ := Select("aa", "bb").From("dd")
qb := Select().Column(Alias(subQ, "alias")).From("a")CASE expression- syntactic sugar for CASE expressions
go test -bench=. -benchmem | column -t on 2.6 GHz i5 Macbook Pro:
BenchmarkDbrSelectSimple-4 2000000 782 ns/op 728 B/op 12 allocs/op
BenchmarkDbrSelectConditional-4 1000000 1156 ns/op 976 B/op 17 allocs/op
BenchmarkDbrSelectComplex-4 500000 3157 ns/op 2360 B/op 38 allocs/op
BenchmarkDbrSelectSubquery-4 500000 2545 ns/op 2160 B/op 29 allocs/op
BenchmarkDbrInsert-4 500000 2187 ns/op 1136 B/op 26 allocs/op
BenchmarkDbrUpdateSetColumns-4 1000000 2306 ns/op 1297 B/op 28 allocs/op
BenchmarkDbrUpdateSetMap-4 1000000 2464 ns/op 1296 B/op 28 allocs/op
BenchmarkDbrDelete-4 2000000 801 ns/op 496 B/op 12 allocs/op
BenchmarkGoquSelectSimple-4 300000 5323 ns/op 3360 B/op 38 allocs/op
BenchmarkGoquSelectConditional-4 200000 5848 ns/op 3804 B/op 49 allocs/op
BenchmarkGoquSelectComplex-4 100000 18142 ns/op 9464 B/op 169 allocs/op
BenchmarkSqlfSelectSimple-4 5000000 275 ns/op 0 B/op 0 allocs/op
BenchmarkSqlfSelectSimplePostgreSQL-4 3000000 401 ns/op 0 B/op 0 allocs/op
BenchmarkSqlfSelectConditional-4 3000000 411 ns/op 0 B/op 0 allocs/op
BenchmarkSqlfSelectConditionalPostgreSQL-4 2000000 595 ns/op 0 B/op 0 allocs/op
BenchmarkSqlfSelectComplex-4 2000000 719 ns/op 0 B/op 0 allocs/op
BenchmarkSqlfSelectComplexPostgreSQL-4 2000000 962 ns/op 0 B/op 0 allocs/op
BenchmarkSqlfSelectSubquery-4 2000000 839 ns/op 0 B/op 0 allocs/op
BenchmarkSqlfSelectSubqueryPostgreSQL-4 1000000 1182 ns/op 0 B/op 0 allocs/op
BenchmarkSqlfInsert-4 2000000 1028 ns/op 0 B/op 0 allocs/op
BenchmarkSqlfInsertPostgreSQL-4 1000000 1114 ns/op 0 B/op 0 allocs/op
BenchmarkSqlfUpdateSetColumns-4 3000000 520 ns/op 0 B/op 0 allocs/op
BenchmarkSqlfUpdateSetColumnsPostgreSQL-4 2000000 674 ns/op 0 B/op 0 allocs/op
BenchmarkSqlfDelete-4 5000000 286 ns/op 0 B/op 0 allocs/op
BenchmarkSqlfDeletePostgreSQL-4 5000000 346 ns/op 0 B/op 0 allocs/op
BenchmarkSqrlSelectSimple-4 1000000 1124 ns/op 704 B/op 15 allocs/op
BenchmarkSqrlSelectConditional-4 1000000 1426 ns/op 848 B/op 18 allocs/op
BenchmarkSqrlSelectComplex-4 200000 8461 ns/op 4352 B/op 87 allocs/op
BenchmarkSqrlSelectSubquery-4 300000 6439 ns/op 3352 B/op 65 allocs/op
BenchmarkSqrlSelectMoreComplex-4 200000 11943 ns/op 6961 B/op 138 allocs/op
BenchmarkSqrlInsert-4 1000000 1706 ns/op 992 B/op 17 allocs/op
BenchmarkSqrlUpdateSetColumns-4 1000000 2133 ns/op 1056 B/op 25 allocs/op
BenchmarkSqrlUpdateSetMap-4 500000 2568 ns/op 1130 B/op 27 allocs/op
BenchmarkSqrlDelete-4 3000000 551 ns/op 272 B/op 8 allocs/op
BenchmarkSquirrelSelectSimple-4 200000 5731 ns/op 2512 B/op 49 allocs/op
BenchmarkSquirrelSelectConditional-4 200000 9630 ns/op 3756 B/op 79 allocs/op
BenchmarkSquirrelSelectComplex-4 50000 25307 ns/op 10162 B/op 224 allocs/op
BenchmarkSquirrelSelectSubquery-4 100000 22823 ns/op 8642 B/op 182 allocs/op
BenchmarkSquirrelSelectMoreComplex-4 50000 39758 ns/op 17155 B/op 384 allocs/op
BenchmarkSquirrelInsert-4 200000 6682 ns/op 3040 B/op 67 allocs/op
BenchmarkSquirrelUpdateSetColumns-4 200000 10834 ns/op 4465 B/op 103 allocs/op
BenchmarkSquirrelUpdateSetMap-4 200000 10996 ns/op 4545 B/op 105 allocs/op
BenchmarkSquirrelDelete-4 200000 6547 ns/op 2592 B/op 63 allocs/op
If your queries are very simple, pick sqlf or dbr, the fastest ones.
If really need immutability of query builder and you're ready to sacrifice extra memory, use squirrel, the slowest but most reliable one.
If you like those sweet helpers that squirrel provides to ease your query building or if you plan to use the same builder for PostgreSQL, take sqrl as it's balanced between performance and features.
goqu has LOTS of features and ways to build queries. Although it requires stubbing sql connection if you need just to build a query. It can be done with sqlmock. Disadvantage: the builder is slow and has TOO MANY features, so building a query may become a nightmare. But if you need total control on everything - this is your choice.