This is currently under construction and incomplete.

Prelude

When working on a Go project, there comes a time where you application needs a database. As a developer, you have a plethora of options to pick from, some good, others not so much. As a new developer, making a decision can be difficult, especially when treading blind.

My goal with this post is to share some perspective and evaluate the different tools that are available in the Go ecosystem. Perhaps you will be able to find a new tool that helps reduce your code complexity, or gain more perspective about why some other libraries might not be as appealing as they seem.

What are my options?

I plan to compare a half dozen different libraries here that accomplish different goals. There may be other similar tools that accomplish similar goals, which I have omitted.

If you would like to suggest another tool, feel free to send me an email.

Apart from these tools, I strongly believe that looking for libraries that are compatible with the Go standard library (database/sqlite) is really important. All the tools I mentioned will work seamlessly with it.

  • Utility libraries

    • github.com/jmoiron/sqlx

      Quoting the README of the repository, sqlx is a library which provides extensions on Go’s standard database/sql library with marshalling from sql rows to Go types.

    • github.com/keengancsmith/sqlf

      Create SQL queries that are safe from injections with printf, making composing queries and working with dynamic number of arguments (more on this later).

    • github.com/Masterminds/squirrel

      A highly composable SQL generator for Go. Using a builder pattern, this library can really help writing SQL queries in a type safe manner.

  • Code generation libraries

    • github.com/xo/xo

      A code generation libraries that creates a model and CRUD operations from your database, and generate type-safe functions from queries. (Limited Documentation)

    • github.com/kyleconrow/sqlc

      Similar to xo, a different style code generation library that should be mentioned seperately. Generates code by parsing your SQL.

  • Object Relational Mappers - Map Go types in SQL and ORM likes.

What about GORM or XORM?

To much of my disappointment, many developers who work with Go fall in the trap of using an ORM. This is really, really tempting to do, however there are a number of flaws that most, if not all of these libraries exhibit, which can severely cripple your project.

What you Gain

Firstly, it is difficult to deny that these libraries do appeal thousands of developers. While not being the most accurate metric by any means, it is still a strong indicator. GORM has almost 25,000 stars (at the time of writing this post) on GitHub and Xorm has over 6,000 on its archived Github.

I will try to explain why many features ORMs are often celebrated and praised are in fact a source of never ending gotchas.

1. No need for SQL

Many beginners fall into the trap of using an ORM because it means they don’t need to learn SQL, which can often come across as being challenging or daunting. A complete new query language, which has its own quirks, learning about selects, updates, joins, indexes?

From their point of shoes, all of this programmer jargon can be easily avoided and ORMs are the way to go.

ORMs rules, SQL drools.

In the contrary, I believe that if you are writing a backend application, knowing SQL is a prerequisite. As an analogy, imagine trying to use a styling framework, such as Bootstrap or TailwindCSS, without knowing CSS. Most people would agree that attempting to use it without knowing CSS is a recipe for botched styling that is terrible to maintain, and generally a recipe for disaster.

Similarly, writing SQL with clean queries that tell both, yourself and your database exactly what to do ensure a healthy architecture that is both easy to debug, extend, and refactor.

2. Magically Simple

ORMs are magic. You can call db.Find(&users), and boom! All your rows are returned. A simple filter can be done trivially. db.First(&user, 10) will query the user whose primary key matches 10. However, there is a pain point that always surfaces when a project uses an ORM more and more.

As a user, writing calling functions like Find, Take, First, Last lead to a loss of control. You no longer have the ability to tune or modify your queries.

This works fine for simple use cases, however, when pushing these to their limits, (which is not very far).

For example, updating rows to the zero value of the Go type will simply be ignored. This usually makes sense, however, very quickly turns into messy design and hacks, such as resorting to pointer field types, or just returning back to good old plain SQL.

Complex functionality (SQL) abstracted with another “simpler” layer (your ORM) always poses the lingering risk of increasing complexity through complex wrappers and hacks.

3. Easy Migrations

ORMs always promote having the ability to automagically handle migrating your schema as your Go data structures change. While valid, again, these migrations are generally done under the hood, and leave you, the user with very little control.

Furthermore, many tools exist already that already handle database migrations significantly more effectively, and give you more fine-tuning power, so that you always are able to stay in control.

When migrations become messy or non-trivial, GORM has the potential to break, or simply make things worse. Using tools like migrate are much better suites, support more databases, has cleaner documentation, and is a more robust alternative use.

Pain Points

1. Finding Help

Continuing from the points one and two, when working with an ORM, finding help becomes monumentally more difficult.

Finding answers for generic SQL is significantly easier to do in your favorite search engine. You will be able to find a plethora of information about defining foreign keys, creating indexes, composite foreign keys, and any other SQL feature on Stack Overflow, or a database forum. When transitioning from one database to another, for example from MS SQL Server to PostgreSQL, or from Oracle DB to SQLite3, a large portion of your knowledge will be transferable.

On the other side of the coin, migrating from Hibernate to Gorm leads leads to very little directly transferable knowledge. Even if you are knowledgeable in a SQL itself, many times you will be at the mercy of the documentation for the ORM you are using to clearly define how to implement your use-case.

Attempting to find information about questions or concerns you have mean having to find not only a community specific to the language you are using, but a subset of that community that also has experience with the very specific library you use, which might mean the time between finding your answer being a few searches away to a few days, or being undocumented enough that it just may never be solved.

2. (Ab)using language features

When working in Gorm in particular, defining primary keys, indexes, and check constraints can be described lightly as a mess. Go struct tags are overloaded and stuffed to the brim to gain simple functionality. Here are a few examples from the Gorm documentation:

type SomeTable struct {
  // Constraints
  Name  string `gorm:"check:name_checker,name <> 'jinzhu'"`

  // Composite Primary Keys
  CategoryID uint64 `gorm:"primaryKey;autoIncrement:false"`
  TypeID     uint64 `gorm:"primaryKey;autoIncrement:false"`

  // Foreign Key
  CreditCard CreditCard `gorm:"foreignKey:UserName"`

  // Indexes
  Name4 string `gorm:"uniqueIndex"`
}

Now, imagine if your project required a combination of these. Having a foreign key alongside of an index, or attempting to define a many-to-many index. Abusing struct tags make it easy to make a mistake, force you to give up all compiler guarantees and with compile-time errors, and terrible to work with. As a user, you are at the complete whim of the Gorm documentation, and their support for these features, instead of having the full breadth of options and customizability one would expect.