How to Work with SQL Databases in Go

SQL, or Structured Query Language, is the standard for managing and manipulating relational databases. In Go, the built-in database/sql package provides a convenient way to interact with SQL databases, allowing you to easily perform tasks such as inserting, updating, and querying data.

The article will discuss the use of the database/sql package in Go to connect to a database, execute SQL statements, and handle results. It will also cover topics like prepared statements, transactions, and connection pooling. The goal of the article is to provide a solid understanding of how to work with SQL databases in Go by the end of it.

Prerequisites

To follow along with this article, ensure that you have the latest version of Go installed on your machine. If you are missing Go, you can find the installation instructions here.

You'll also need the latest version of PostgreSQL installed locally. Ensure to follow the instructions specific to your operating system.

This article also assumes that you have a sufficient understanding of SQL to follow along. Regardless, we have prepared a primer on databases and SQL for your reference.

Setting up the demo project

Once you've installed PostgreSQL on your machine, create a new database called gda through the psql interface. You can refer to this resource for how to accomplish that. When you type \l into the psql terminal, you should observe an entry for gda as shown below:

Afterward, clone the demo repository to access the code that demonstrates the concepts introduced in this article.

git clone https://github.com/betterstack-community/go-database-access.git

cd go-database-access/

A simple CLI is provided to access the examples shown in this article. To gain access to this CLI, build the project using the command below. Afterward, you'll observe that a gda binary is now present at the root of the project.

go build

Next, let's configure the connection string to access the gda database created earlier. Rename the .env.example file provided in the root of the repository to .env and change the CONN_STR environment variable to your intended connection string.

CONN_STR="postgres://<user>:<password>@<hostname>:<port>/gda?sslmode=disable"

For example, if your username and password is postgres, and your postgres instance is running at localhost:5432 (the default), you can use the following connection string:

postgres://postgres:postgres@localhost:5432/gda?sslmode=disable

Once you've updated the PostgreSQL connection string in the .env file, enter the following command to automatically set up and populate the gda database:

./gda setup

The above command populates the gda database with the following sample data (note that the ids will be different on your instance of the database as they are randomly generated):

Customer table:

Order table:

The following is the basic Entity-Relationship Diagram (ERD) that is used for the examples in this article.

A brief primer on databases

According to Oracle, a database is an organized collection of data stored electronically in a computer system. There are two primary types of databases: relational and non-relational.

Relational databases are structured so the data that enters and resides in tables, rows, and columns so that the relationship between each data point is clearly defined. Non-relational databases, on the other hand, do not have such rigid structures.

This article will explore relational databases only and focus on the most common interfacing language used to write and query data in such databases (SQL).

What is SQL?

Structured Query Language (SQL) is a domain-specific language used to work with relational databases. There are many dialects of SQL, but for this article, we are focusing on PostgreSQL, a widely used open-source dialect.

Common queries

This list is non-exhaustive. For more information, visit the PostgreSQL tutorial for a detailed guide on PostgreSQL. You can also refer to the official PostgreSQL documentation for more information about PostgreSQL’s syntax. To understand database design, refer to Microsoft’s guide to database design basics.

Transactions

Due to the concurrent nature of reads and writes in databases, a series of related queries should be executed as a batch (a.k.a. transaction). Transactions minimize data inconsistencies caused by external reads or writes, and they preserve the integrity of the database when executing a series of related queries.

A transaction is committed if, and only if, every query within the transaction is successful. If any queries fail, the entire transaction is aborted to avoid creating inconsistencies. Formally, this process is referred to as "rolling back a transaction". For more information about transactions, refer to Linode’s primer on transactions.

With the basics out of the way, let's start learning about working with SQL in Go programs.

Database access with database/sql

Go provides a built-in database access package (database/sql) which provides the necessary tools for working with SQL databases. It is the foundation for many other database access tools that we will be discussing later on in tutorial. Therefore, a firm understanding of how the database/sql package works is a must!

The database/sql package exposes a sql.DB type that provides a layer of abstraction over the SQL database. However, it does not directly interact with the underlying database; that is the role of the database drivers, which we will discuss shortly.

Through database/sql, uniform database access is achieved, regardless of the underlying database, which makes it easy to switch from one SQL dialect to another if necessary.

The following is an illustration of how database/sql works.

For more information about how database/sql works, please refer to its documentation.

Database drivers in Go

Before you can access whatever database you're using in your Go program, a corresponding database driver must be installed. This driver is the concrete link between your Go code and the database.

A list of database drivers for various SQL dialects can be found on the official Go GitHub Wiki. To install a driver in your project, use the following command:

go get <driver package>

If you're working with the demo project, you don't need to run the command able as it already includes pgx, one of the most popular PostgreSQL drivers. Since pgx is already installed, you can proceed with connecting to the gda database in the next section.

Connecting to the database

Every database, regardless of language or platform, has a standard format for its connection string. For PostgreSQL, the connection string format is shown below:

postgresql://<username>:<password>@<hostname>:<port>/<dbname>

For instance, the connection string to the gda database that you created earlier would be:

postgresql://postgres:root@localhost:5432/gda?sslmode=disable

It is considered a best practice to store this connection string in the execution environment to avoid leaking your database credentials in the code.

Note that our connection string includes an additional query parameter: sslmode=disable. It is used to disable SSL when connecting to the database since we are accessing it locally, but you should definitely enable it in production to ensure that your database connections are secure.

Our demo project provides a utility function for retrieving the connection string from the environment found in the internal/utility/utility.go file:

func ConnectionString() string {
    connStr, status := os.LookupEnv("CONN_STR")
    if !status {
        log.Fatalln("Missing environment variable CONN_STR")
    }

    return connStr
}

To connect to the gda database in your Go code, the sql.Open() function should be used as shown below:

package example

import (
    "database/sql"
    _ "github.com/jackc/pgx/v5/stdlib"
    "woojiahao.com/gda/internal/utility"
)

func Connect() {
    connStr := utility.ConnectionString()
    db, err := sql.Open("pgx", connStr)
    if err != nil {
        log.Fatalf("Unable to connect to database because %s", err)
    }

    if err = db.Ping(); err != nil {
        log.Fatalf("Cannot ping database because %s", err)
    }

    log.Println("Successfully connected to database and pinged it")
}

The pgx driver is imported and prefixed with a blank identifier solely for its side effect of registering the driver without importing any other functions. This allows us to connect to the database. Do note that this step is only required once per package.

Once the driver is registered, the sql.Open() method is subsequently used to connect to the database, and the Ping() method is used to verify that the database connection is alive.

Notice that we did not have to use defer to close the database connection. This is intentional as according to the official documentation of the Open() function, the returned *sql.DB is safe for concurrent use and it does not need to be closed manually.

This example can be tested by running the following command:

./gda example connect

2023/01/10 12:24:29 Successfully connected to database and pinged it

Now that you've established a successful database connection, you can now start working with the database using the methods provided on the sql.DB type as you'll see in the following sections.

Querying for data

One of the most common tasks for any program interacting with a database is to query for data that is already present in the database. In Go, you can query for data through the QueryRowContext() and QueryContext() methods on the sql.DB type in conjunction with the SELECT SQL query.

Single row queries

The QueryRowContext() method retrieves a single row from the database. This is useful when only a single result is needed, such as when you are query for user record through their unique user id.

func SingleRowQuery() {
 // Connecting to database...
    var johnDoeId string
    row := db.QueryRowContext(context.TODO(), `SELECT id FROM customer WHERE name = 'John Doe';`)
    err = row.Scan(&johnDoeId)
    switch {
    case err == sql.ErrNoRows:
        log.Fatalf("Unable to retrieve anyone called 'John Doe'")
    case err != nil:
        log.Fatalf("Database query failed because %s", err)
    default:
        log.Printf("John Doe has an ID of %s", johnDoeId)
    }
}

The QueryRowContext() method returns a *sql.Row type, which has a Scan() method to map the values returned from the database to variables. In our case, we are mapping the result of the SELECT statement to the johnDoeId variable since we expect a string result.

When we successfully scan the row returned from the database, the variable johnDoeId will hold the id returned from the database. Notice that we passed a pointer reference of johnDoeId to Scan(). If a pointer reference is unused, there will be an error.

Run the command below to see the code in action:

./gda example single

2022/12/22 11:56:58 John Doe has an ID of c9d36f13-c508-4cd0-a849-5022b94a9c86

There is a special type of error that is returned when Scan() is called but the database returns no rows: sql.ErrNoRows. The suggested switch structure is a typical pattern employed when using QueryRowContext() to handle the aforementioned error properly .

You can see this in action by changing the SQL statement from "John Doe" to "Joe Doe". When you build the binary and execute the single command once more, you will observe the following result:

go build && ./gda example single

2022/12/22 12:16:37 Unable to retrieve anyone called 'Joe Doe'

Multiple rows queries

Multiple rows of data can be retrieved from the database through the QueryContext() method. This is useful when your SELECT query might return multiple rows that match a given condition.

func MultiRowQuery() {
    // Connecting to database...

    orderQuantities := make(map[string]int)
    rows, err := db.QueryContext(context.TODO(), `SELECT food, sum(quantity) FROM "order" GROUP BY food;`)
    if err != nil {
        log.Fatalf("Database query failed because %s", err)
    }

    defer rows.Close()

    for rows.Next() {
        var food string
        var totalQuantity int
        err = rows.Scan(&food, &totalQuantity)
        if err != nil {
            log.Fatalf("Failed to retrieve row because %s", err)
        }
    orderQuantities[food] = totalQuantity
    }


    if err := rows.Err(); err != nil {
      log.Fatalf("Error encountered while iterating over rows: %s", err)
    }

    log.Printf("Total order quantity per food %v", orderQuantities)
}

Unlike thee QueryRowContext() method, QueryContext() returns both *sql.Rows and the error that occurred during the query (if any). After ascertaining that no error occurred, you can proceed to access the rows of data through the pattern prescribed in the example above. The Scan() method is still available, and is used to map every row to a given set of values, as seen in the example.

./gda example multi

2022/12/22 12:27:18 Total order quantity per food map[Fish and Chips:1 Pie:5 Pudding:5 Soup of the Day:2]

Specifying contexts

Aside from the QueryRowContext() and the QueryContext() methods, we also have the QueryRow() and Query() methods which perform exactly the same functions except that the latter two do not allow you to specify a context.

In Go, contexts carry deadline and cancellation signals (among others) across API boundaries and between processes so that you can control how long a task is allowed to take.

When working with databases, this context can be used to inform the database service to cancel a query if too much time has elapsed to prevent performance degradation.

Imagine the following: you are building an API, and an endpoint requires access to the database. Unfortunately, the query for the database takes an abnormally long time to return any results. Due to this delay, the client has to wait for an equally abnormal amount of time for the API’s response. With contexts, however, you can instruct the database to cancel a query if too much time has elapsed to avoid forcing the client to wait.

Therefore, you should eschew the usage of the QueryRow() and Query() methods in favor of their context-aware alternatives. In the examples above, the context.TODO() method is used, which returns an empty context that allows the query to run for as long as it needs. However, we will demonstrate query timeouts using contexts later on.

Parameterized queries

A common practice and defense against SQL injection is using parameterized queries where the parameters of such a query are replaced with placeholders. The necessary arguments are subsequently injected into these placeholders at execution time.

The database/sql package supports parameterized queries as part of its core API. We can modify our example for QueryRowContext() to use a parameterized query instead of hardcoding "John Doe" as the target. This way, we can design a function that is flexible with finding a user with any given name.

func ParameterisedQuery(target string) {
    // Connecting to database...

    var id string
    row := db.QueryRowContext(context.TODO(), `SELECT id FROM customer WHERE name = $1;`, target)
    err = row.Scan(&id)
    switch {
    case err == sql.ErrNoRows:
        log.Fatalf("Unable to retrieve anyone called %s", target)
    case err != nil:
        log.Fatalf("Database query failed because %s", err)
    default:
        log.Printf("%s has an ID of %s", target, id)
    }
}

The arguments of a parameterized query are supplied after the query string in the exact order specified in the query.

./gda example parameterised

2022/12/22 13:45:32 Mary Anne has an ID of 587208e9-5287-4b23-9ec4-6ea1b8c82144

Note that the syntax for placeholders differs between SQL dialects. For PostgreSQL, placeholders are specified as: $1, $2, etc, but others might use ? to signify placeholders. Ensure to use the appropriate placeholder syntax for your SQL dialect of choice.

Handling nullable types

Some columns in your database might allow NULL values. To properly scan and assign such values in your code, you can use the null types provided by the database/sql package.

func NullTypeQuery() {
    // Connecting to database...

    var allergies []sql.NullString
    rows, err := db.QueryContext(context.TODO(), `SELECT allergy FROM customer;`)
    if err != nil {
        log.Fatalf("Unable to retrieve customer allergies because %s", err)
    }

    for rows.Next() {
        var allergy sql.NullString
        err = rows.Scan(&allergy)
        if err != nil {
            log.Fatalf("Failed to scan for row because %s", err)
        }
        allergies = append(allergies, allergy)
    }
    log.Printf("Customer allergies are %v", allergies)
}

In this case, as the allergy column allows NULL values, we use the sql.NullString type. database/sql intelligently assigns the column's value to the variable if it is not NULL.

./gda example null

2022/12/22 14:03:08 Customer allergies are [{ false} {Cheese true} { false}]

The list of null types in database/sql can be found in the official documentation.

Modifying data

The three primary statements used to change the data in an SQL database are INSERT, UPDATE, and DELETE. In all three scenarios, the ExecContext() method on the sql.DB type should be used to execute the queries.

func InsertQuery() {
    // Connecting to database...

    _, err = db.ExecContext(context.TODO(), `INSERT INTO customer(name, allergy) VALUES('John Adams', 'Seafood');`)
    if err != nil {
        log.Fatalf("Unable to insert new customer because %s", err)
    }

    ParameterisedQuery("John Adams")
}

The ExecContext() method returns the query's sql.Result and an error (if any). Notice that no *sql.Rows or *sql.Row is returned as ExecContext() is meant to be used for queries that do not produce any values. This is why ExecContext() should not be used with SELECT queries.

./gda example insert

2022/12/22 14:34:20 John Adams has an ID of 85b14220-10a6-42d1-8587-db41ee2f94a7

Using SQL transactions

Transactions execute a series of related queries so that the entire series of changes is undone if one of the queries fails. This mechanism exists to minimize inconsistencies in the database.

To use transactions in database/sql, you need to start the transaction first, then execute the queries within the transaction, and finally, commit the transaction to confirm the changes. The transaction rolls back if an error occurs at any point within the transaction block.

func Transaction() {
    // Connecting to database...

    tx, err := db.BeginTx(context.TODO(), nil)
    if err != nil {
        log.Fatalf("Unable to begin transaction because %s", err)
    }
    defer tx.Rollback()

    var johnDoeId string
    err = tx.QueryRowContext(context.TODO(), `SELECT id FROM customer WHERE name = 'John Doe';`).Scan(&johnDoeId)
    if err != nil {
        log.Fatalf("Unable to retrieve John Doe because %s", err)
    }

    _, err = tx.ExecContext(
        context.TODO(),
        `INSERT INTO "order"(food, quantity, customer_id) VALUES('Mac and Cheese', 3, $1)`,
        johnDoeId,
    )
    if err != nil {
        log.Fatalf("John Doe was not able to order because %s", err)
    }

    var macAndCheeseQuantity int
    err = tx.QueryRowContext(
        context.TODO(),
        `SELECT sum(quantity) FROM "order" WHERE food = 'Mac and Cheese';`,
    ).Scan(&macAndCheeseQuantity)
    if err != nil {
        log.Fatalf("Failed to retrieve any Mac and Cheese orders because %s", err)
    }

    log.Printf("There are %d Mac and Cheese orders", macAndCheeseQuantity)
    tx.Commit()
}

The BeginTx() method is used to begin a transaction. You can call the scope of the Transaction() function as the transaction block where we can execute the same methods mentioned before (but using tx instead of db).

Notice that tx.Rollback() is deferred, ensuring that if the function returns early due to an error, the transaction rolls back without saving any of the changes. At the end of the function, we call tx.Commit(), which commits the changes of the transaction block. Note that the deferred tx.Rollback() does not have any effect when this occurs as the commit occurs first and saves the changes, leaving nothing to rollback. It is a typical pattern when working with transactions in database/sql.

This example is accessible through the following command. Feel free to play around with the transaction queries to test how database/sql handles invalid queries.

./gda example transaction

2022/12/22 15:01:55 There are 3 Mac and Cheese orders

Common database access patterns in Go

There are some commonly occurring patterns when working with SQL databases in Go. This is not an exhaustive list, but this will cover some of the core patterns that you can employ to improve your workflow.

Storing database results as structs

You can assign the values of the returned results of a database to a struct which is useful when working with the database results as logical units, rather than discrete fields.

For instance, if you want to work with the customers from the database as an entire entity, we can create a customer struct and assign the values of each field when scanning through a row of results.

type customer struct {
    id      string
    name    string
    allergy sql.NullString
}

func Struct() {
    // Connecting to database...

    var customers []customer
    rows, err := db.QueryContext(context.TODO(), `SELECT * FROM customer;`)
    if err != nil {
        log.Fatalf("Unable to retrieve customers because %s", err)
    }

    for rows.Next() {
        var c customer
        err = rows.Scan(&c.id, &c.name, &c.allergy)
        if err != nil {
            log.Fatalf("Unable to scan row for customer because %s", err)
        }
        customers = append(customers, c)
    }

    log.Printf("Customers in the system are %v", customers)
}

Notice that the field types are the same as the types of the individual variables in previous sections.

./gda example struct

2022/12/22 16:02:06 Customers in the system are [{6ed77f7b-57c8-4933-9a52-f7657b6bd2d1 John Doe { false}} {76ad552d-942b-4f1f-bb15-6068b11f8fe4 Mary Anne {Cheese true}} {c1714c8a-5d29-4318-bbea-54eec810ffec Jason Borne { false}} {85b14220-10a6-42d1-8587-db41ee2f94a7 John Adams {Seafood true}}]

Returning results when changing data

When building an API with database access, you might want to return the updated row of data after changing it. In SQL, RETURNING * is used to achieve this.

Since such queries change the data while returning values, QueryRowContext() and QueryContext() should be used instead of ExecContext() as they will still execute the changes while returning the data. This pattern allows you to avoid calling SELECT again after changing the data.

func Returning() {
    // Connecting to database...

    var allergy sql.NullString
    err = db.QueryRowContext(
        context.TODO(),
        `INSERT INTO customer(name, allergy) VALUES('Megan', 'Seafood') RETURNING allergy;`,
    ).Scan(&allergy)
    if err != nil {
        log.Fatalf("Failed to insert new customer Megan because %s", err)
    }

    if a, err := allergy.Value(); err != nil {
        log.Fatalf("Cannot read Megan's allergy because %s", err)
    } else {
        log.Printf("Newly add customer Megan has a %s allergy", a)
    }
}

Note that the choice between QueryRowContext() and QueryContext() still applies. When inserting a single row of data, QueryRowContext() might be ideal. When updating a set of rows that satisfy some condition, QueryContext() is more appropriate.

./gda example return

2022/12/22 16:23:16 Newly add customer Megan has a Seafood allergy

Prepared statements

There are times when you may wish to reuse a query, and this is where prepared statements shine. They are beneficial when you aim to reuse a parameterized query with different parameters but the same query structure.

The methods used earlier apply to these statements.

func Prepared() {
    // Connecting to database...

    stmt, err := db.PrepareContext(context.TODO(), `SELECT id FROM customer WHERE name = $1;`)
    if err != nil {
        log.Fatalf("Unable to prepare statement because %s", err)
    }
    defer stmt.Close()

    var johnDoeId string
    err = stmt.QueryRowContext(context.TODO(), "John Doe").Scan(&johnDoeId)
    if err != nil {
        log.Fatalf("Failed to retrieve John Doe's ID because %s", err)
    }

    var maryAnneId string
    err = stmt.QueryRowContext(context.TODO(), "Mary Anne").Scan(&maryAnneId)
    if err != nil {
        log.Fatalf("Failed to retrieve Mary Anne's ID because %s", err)
    }

    log.Printf("John Doe's id is %s and Mary Anne's id is %s", johnDoeId, maryAnneId)
}

Note that the statement created must be closed manually through a defer.

./gda example prepared

2022/12/22 16:38:58 John Doe's id is 6ed77f7b-57c8-4933-9a52-f7657b6bd2d1 and Mary Anne's id is 76ad552d-942b-4f1f-bb15-6068b11f8fe4

Enforcing query timeouts with contexts

As mentioned earlier, when querying a database, it is a best practice to enforce a time limit on each query to avoid a situation where the query hangs forever. You can enforce time constraints for queries using contexts as shown below:

func Timeout() {
    // Connecting to database...

    ctx, cancel := context.WithTimeout(context.TODO(), 5*time.Second)
    defer cancel()

    res, err := db.ExecContext(ctx, `SELECT pg_sleep(10);`)
    if err != nil {
        log.Fatalf("Failed to execute command because %s", err)
    }

    log.Printf("Result is %v", res)
}

Providing the timed context (ctx) as an argument to the ExecContext() method ensures that the query is canceled if it takes longer than the stipulated time (5 seconds). In such cases, an error is returned from the ExecContext() method. In the above example, the pg_sleep(10) function mimics a slow-running query by sleeping for 10 seconds so that the query is ultimately canceled after the 5-second limit:

./gda example timeout

2023/01/12 08:21:36 Failed to execute command because timeout: context deadline exceeded

Configuring max/idle connections

The database/sql package aims to minimize the creation of a brand new connection to the database by reusing existing connections when they are not in use. These are referred to as idle connections. Idle connections, together with connections that are actively performing queries, constitute the open connection pool.

By default, database/sql manages the open connection pool internally. However, you can configure this connection pool to optimize your use case. There are four ways to configure the connection pool:

The SetMaxOpenConns method

By default, database/sql does not restrict the number of open connections that can exist. This is the number of open connections (active + idle) that are allowed to exist at once.

However, if you have a limited amount of resources available for the database, it might be wise to set a fixed threshold using SetMaxOpenConns() as an infinitely large open connection pool size can result in performance penalties. In the example below, we set the maximum number of open connections to be 15.

func MaxOpenConns() {
    // Connecting to database

    db.SetMaxOpenConns(15)
}

The SetMaxIdleConns method

The second configuration you can make is to modify the maximum number of idle connections. As mentioned, the total number of idle connections is equals to max open conns - current active conns. By default, this value is set to 2 but you can set this to a higher value using SetMaxIdleConns().

Theoretically, if you increase the maximum number of idle connections allowed, it can decrease the waiting time for an idle connection which improves the performance. However, as with the previously discussed configuration, more is not always better and you should always configure the maximum number of idle connections with your use case in mind. In the example below, we've set the maximum number of idle connections to be 5.

func MaxIdleConns() {
    // Connecting to database

    db.SetMaxIdleConns(5)
}

The SetConnMaxLifetime and SetConnMaxIdleTime methods

You can also configure the maximum number of time a connection may be reused using SetConnMaxLifetime() and the maximum amount of time a connection can remain in the idle pool before being closed using SetConnMaxIdleTime().

For both configurations, values that are set too low might result in inefficient connection pools as connections will be repeatedly created from scratch as they would timeout too quickly or be destroyed after too little reuses. In the example below, we set the maximum idle time to be 5 minutes and the maximum time it can be reused to be 100 milliseconds. Note that we are using the time package to compute the duration.

package example

import (
    // Other imports
    "time"
)

func Lifecycle() {
    // Connecting to database

    db.SetConnMaxLifetime(100 * time.Millisecond)
    db.SetConnMaxIdleTime(5 * time.Minute)
}

As a general rule of thumb, more is not always better and in fact, less can be more. As such, do not go overboard with trying to maximize the connection pool. Instead, you should test your values thoroughly through benchmarking and logging and optimize as needed.

Database tools

Building your entire SQL database integration using database/sql alone can be time-consuming and unideal for rapid prototyping. As such, a slew of database tools built on top of database/sql exists to improve the overall quality of life when working with SQL databases in Go.

There are three categories of such tools, with each one providing a different level of abstraction for database access.

1. Database migration

Database migration migrates data from one source database to another target database. It also can migrate data from one schema to another schema (of the same database) while preserving the original data from the initial schema. It is useful when adding fields to an existing table of the database. Without a proper database migration system, such changes entail interacting with the data stored in the database, creating a point of failure.

Database schema migration tools exist to ease this process. Examples include Goose, migrate, darwin, and others.

Beyond database migrations, these tools do not help with the other aspects of working with SQL databases so you still have to manage other aspects of working with the database. However, having database migration handled automatically for you is an incentive to consider using such tools.

2. Query builders

Query builders generate functions for querying the data based on a set of SQL queries written by the developer. They remove the boilerplate for writing database access queries. For instance, with error handling generated, you will not miss an error handling block by accident, causing silent failures.

However, as these query builders often follow a fixed generation pattern, the generated functions might be less than optimal for complex queries. Thus, while the boilerplate has been taken care of for you, you would still need to manually check the generated functions to ensure that they are optimal and abide by the intended behavior.

Examples of query builders include sqlc, and jet.

3. ORMs

Object-Relational Mappers or ORMs are libraries that allow you to query and manipulate the data from a database using an object-oriented paradigm. In Go, structs represent the data from a database in most ORMs.

ORMs provide a hands-off experience when working with databases. Through the many utility methods provided by the ORM, you can write little to no SQL.

However, like query builders, this automatic handling of querying comes with the drawback of a lack of optimization for complex queries. As ORMs employ a fixed generation patterns, the queries it uses might not be optimal. Thus, if you observe significant performance issues when using an ORM, it is advisable that you write the query separately and execute it.

Examples of ORMs include GORM, and reform.

The usage of ORMs is a highly debated topic in the Go community, and they should not be the first tool you reach for when working with SQL databases in Go. We propose a general framework for deciding which tool to use for database access, depending on your project and the amount of control you require.

How to choose the right tool

The framework we are proposing aims to provide some guidance on the tools you should consider based on the scope of your project.

If the project requires a lot of complex queries that are performance sensitive, it would be a good decision to avoid ORMs and query builders. ORMs are not a good choice for such projects as the generated queries are not entirely transparent from the get-go, and as such, debugging which queries are the most time-consuming can be a tedious process.

Query builders remove some of this abstraction, but it is important to note that work still needs to go into revising and double-checking the generated queries to ensure they are optimal. In such a case, you should build a lightweight wrapper around the database/sql package and write the queries manually. This option provides you with the most amount of control over the queries that you use.

If the project has a lot of relatively simple queries, and you do not wish to use an ORM, query builders might be a good middle ground as they handle as much of the boilerplate.

If the project is relatively small scale and does not require many complex queries, then ORMs and query builders are good options as they eliminate much of the boilerplate for working with databases.

Regardless of the project, it is always advisable to use database schema migration tools as they alleviate a pain point when working with databases in Go. They also help to provide ease of mind when migrating the data across different databases or even when changing the database schema.

Note that this framework is not definitive. You should exercise some personal judgment when deciding the type of tools to use to access databases in Go.

Final thoughts

The built-in database/sql package in Go provides a good starting point for database access in Go. Many of the features of database access are abstracted away from developers. As such, you can focus on working with the data from the database. It also does not shy away from providing developers with sufficient control over the database they are working with, such as configuring the connection pool.

However, while database/sql is very powerful, there are times when this power is unnecessary. Thus, database tools like query builders or ORMs are viable alternatives as they remove the need to write boilerplate code to access the database.

As database/sql is relatively barebones in terms of helping you to optimize your database access performance, a lot of the optimizations still lie in your ability to write efficient SQL queries.

Thanks for reading, and happy coding!

This article was contributed by guest author Woo Jia Hao, a Software Developer from Singapore! He is an avid learner who loves solving and talking about complex and interesting problems. Lately, he has been working with Go and Elixir!

Article by

Woo Jia Hao

Woo Jia Hao is a software developer from Singapore. He is an avid learner who loves solving and talking about complex and interesting problems. Lately, he has been working with Go and Elixir!

Got an article suggestion? Let us know

Next article

Redis Caching in Go: A Beginner's Guide

Follow this step-by-step tutorial to learn how to use Redis caching in Golang to improve application performance

→

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.