Cookies management by TermsFeed Cookie Consent
Russia has invaded Ukraine and already killed tens of thousands of civilians, with many more raped or tortured. It's a genocide. We need your help. Let's fight back against the Russian regime.
Help Ukraine! Fight the Russian regime!

Pure SQL or ORM

Please consider supporting us by disabling your ad blocker

2/21

Before we start installing PostgreSQL and writing code, we need to decide which way of accessing the database we prefer.

The database/sql package

Go has a built-in universal interface for operating on SQL databases in the database/sql package. Since this package provides universal functions, we need another component, a driver, to make it work for a particular database. The driver is a package compatible with the database/sql/driver interface and provides direct access to the database. In other words, a driver is a client of a specific database in Go, compatible with the database/sql/driver interfaces, and thanks to that, it can be used by the database/sql package.

The advantage of database/sql is its simplicity and versatility. If you want to start working with a new SQL database, you will almost certainly find an open source, well-tested driver for it. They are all compatible with the database/sql, so if you would like to swap one database for another, then all you need to do is replace the driver with little or no changes.

Also, working with the database/sql is about writing pure SQL commands, getting results from functions, and converting them to a specific format. You have to do all these steps by yourself. So if you want full control over the whole process of reading or writing data to a database, then the database/sql is a great choice.

A database client

Since the database/sql package is universal and offers the same functionality for all databases, you may be wondering how to use the specific features of a given database in Go.

For such cases, many databases provide a custom client package. Such a package is something like a non-standard driver extended with all the functionality of the database. Typically, clients are low-level, and because they are dedicated to a particular database, they can be faster than database/sql compatible drivers. So if you do not need the versatility that database/sql offers but rather specific database features, then using a dedicated client may be a good choice.

An ORM or other tools

The database/sql package and dedicated clients run at a low level, and not everyone likes this type of work. Especially if the database you want to implement has many tables, you expect some sort of speedup by not having to write every query by hand. In this case, the best solution would be to use ORM or other tools, such as those that generate code from SQL commands.

ORMs are packages that make things easier by providing automatic Struct-Table mapping and no need to write error-prone pure SQL statements. They often have features not included in the standard database/sql library, such as automatic migration, caching, pagination of results, and many others. They speed things up at the expense of a higher level of abstraction and less control over the data you get.

However, if you prefer to operate on SQL queries but still want to get some speedup over database/sql, a good solution is to use a code generator such as sqlc. It works by generating type-safe code from SQL: you write SQL commands, run sqlc, get a model, and well-defined Go functions that read from or write to the database.

What we are going to use

In this tutorial, we are going to show you three approaches to creating a database access layer. First, using the pgx PostgreSQL database/sql compatible driver as this is the simplest and most natural approach for Go. Second, using the pgx custom client that offers all the features of PostgreSQL in Go. As a bonus, we will also show you how to create the same functions using GORM ORM. As a complete Go programmer, you should know all these approaches and use the best one for a given use case.

Read more about the pgx driver and toolkit package, GORM, and other drivers and ORMs in our list of the best drivers and ORMs for PostgreSQL in Go.

Pros and cons of the database/sql

Pros
  • The built-in and universal method of operating on a database in Go

  • Availability of a large number of drivers for different databases

  • You have full control over reading and writing to the database as the database/sql is quite low-level

Cons
  • You have to write commands in pure SQL, which can be error-prone

  • No Struct-Table mapping, which makes it necessary to parse each result into the desired form

  • It is not possible to use features specific to a given database

Pros and cons of a custom database client

Pros
  • It usually has functions for all the specific features of a particular database

  • Because a client package is dedicated to a specific database, it is often faster than the database/sql compatible driver

  • You have full control over reading and writing to the database as such packages are usually low-level

Cons
  • It is a dedicated solution for a given database, so it is harder to migrate from one database to another

  • You have to write commands in pure SQL, which can be error-prone

  • No Struct-Table mapping, which makes it necessary to parse each result into the desired form

Pros and cons of ORM

Pros
  • ORMs usually support Struct-Table mapping, which allows you to read data directly into a structure without parsing or converting

  • No need to write pure SQL commands - Go functions are used instead

  • ORMs often have custom features to support common use cases such as auto migrations, pagination, and caching

Cons
  • No common ORM interface in Go, so each package works differently, and there is no easy way to migrate from one ORM to another

  • Not as much control over the data received as with the database/sql

  • Usually, ORMs are slower than the database/sql approach

2/21