Pure SQL or ORM
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
pgxdriver 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
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/sqlis quite low-level
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
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/sqlcompatible driverYou have full control over reading and writing to the database as such packages are usually low-level
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
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
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/sqlUsually, ORMs are slower than the
database/sqlapproach