Avoid ORM in Go — Use Pure SQL Instead
Stop learning a new ORM syntax for every language
If you are a software engineer who likes to try out many different languages and frameworks, you may have experienced the pain of having to learn the syntax of a new ORM for each language. This is a huge drawback that will either slow your initial productivity or drain your motivation.
On the other hand, if you already know SQL, you can use that knowledge and transfer it to many different languages by just following a few indications or simple tutorials like this one.
First of all, we will set up our environment. In order to follow the tutorial, you can install Postgres and get it running on your machine. Alternatively, you can use Docker. The following docker-compose file will get everything up and running without having to worry about anything else:
If you place this file at the root of your project, you just have to run the following command to set everything up:
docker-compose upAs you can see, we have defined a volume in the db service that shares an SQL script that will be run on docker-compose startup. Note that you must map it to the docker-entrypoint-initdb.d directory so that the Postgres container runs it on startup. The init.sql includes the definition of the database and the table we are going to use in this tutorial, which is just an example of what a todos database could look like:
When the db container is started, it will run the script above and create both the database and the todos table (if they haven’t already been created).
Once we have our environment ready, we will move into the coding part.
First, we will define how our Go application will configure the access to our database. The only package we need to import is the postgres driver.
The first good practice that we are going to follow is to get all our database credentials from environment variables, as we don’t want them to be included in our code, which we may expose to the public.
Although this is not strictly necessary, we will define the following constants to avoid having hardcoded strings in some parts of our code. Note that the names of environment variables tend to be more descriptive in real-world scenarios.
Then, we will use those constants to get the environment variables:
Once we have all that, we will include the rest of our code inside the init() function. In Go, the init function gets called the first time a package is used, and it is not called again even if that package is used in a different package. In this function, we will just open the connection to the database and then check if the database is actually working properly by calling the Ping() method.
Now we will move on to our model. We will use the DAO and DTO patterns to define our model.
First, we will create the todos_dto.go file within the model/todos directory. The DTO pattern is used to transfer data between different modules of your application. Essentially, it is the abstraction through which information is passed between the DAO and the business services. In Go, you can implement it by creating a struct and defining how each field would be encoded in JSON. Note that this file shouldn’t contain any business logic that your application may contain.
On the other hand, the DAO pattern separates the business logic from the data access logic. It provides methods for creating, retrieving, updating, and deleting the data present in your database.
In this tutorial, we will use prepared statements over just calling the Exec() or Query() methods directly. Although there are arguments about why to use one approach over the other, there are some benchmarks indicating that prepared statements are more performant in Go.
Once we call the Prepare() method with the query that we want to execute, we check the error returned and then defer the closing of the prepared statement. If we forget to do so, it will be bound to our connection forever. Because of that, it is important to always defer the stmt.Close() method call after preparing a statement.
Once we have all that, in the Save() method, we will execute the QueryRow method and Scan() the id returned. As the ID is autogenerated by the database, it will be missing in the INSERT query that we execute. In order to retrieve it from the database, some databases such as MySQL offer the possibility to call the LastInsertId() method from the result obtained in the query. However, as we are using Postgres, we can just explicitly declare our query that we want to return the ID generated.
INSERT INTO todos(description, priority, status) VALUES($1, $2, $3) RETURNING id;After checking that no errors have arisen, we assign the obtained ID to our Todo struct and we are done with the insertion.
As we have followed an approach of returning the todo ID in the insert query, the Get() method procedure will be analogous but simpler.
In order to test that everything is working as expected, we will use the following main.go file:
It is a simple main that inserts two todos and then retrieves the todo with ID 1. Considering the logging that we have added to our DAO, the output of this main would be similar to this (note that I get IDs 5 and 6 because I had executed it a couple of times before):
If you get an output like this, then everything is working properly and you have succeeded at interacting with a database without using an ORM. You can find the code for this tutorial in this GitHub repository.
