All you need to know to integrate a SQL database in Go

In my previous article about creating an fully functional API in Go under 5 minutes, I showed you how to create a web server using only the standard library. There, we learned that Go standard libs are powerful enough to meet our needs.

In this article, and in a similar way to the aforementioned one, I'll show you everything you need to know about the database/sql package in Go, to quickly start integrating a SQL database in your application.

Let's see some snippets! Let's start by how to connect to a database.

Connecting to a database

Connecting to the database can be done with the sql.Open function. This function receives two parameters: the driver name and the connection string (aka data source name, or DSN). The driver name is the name of the driver that you want to use to connect to the database. The DSN is a string that contains the information needed to connect to the database. The DSN format depends on the database that you are using, but an example for PostgreSQL would be postgres://user:password@host:port/database.

// Configure the connection to the database.
db, err := sql.Open("DRIVER_NAME", "CONNECTION_STRING")
if err != nil {
	log.Fatal(err)
}
defer db.Close()

However, the sql.Open function doesn't actually open a connection to the database. It just creates it, by returning a new *sql.DB object. The connection to the database is only opened once yo tries to operate on it for the first time. Therefore, if you have an error with your connection configuration (let's say, you introduced a wrong password or a typo in the database name), you won't know until you try to insert or query some data. How to avoid that? Is a common good practice to use the Ping method of the returned *sql.DB object just after its creation to check if the connection is working.

// Configure the connection to the database.
db, err := sql.Open("DRIVER_NAME", "CONNECTION_STRING")
if err != nil {
	log.Fatal(err)
}
defer db.Close()

// Use Ping() to actually open the connection and check for any errors.
if err = db.Ping(); err != nil {
	log.Fatal(err)
}

Executing a SQL statement

The obtained *sql.DB object has a generic method to execute SQL statements: the Exec method. This method receives a query string and a list of parameters. The query string is the parametrized SQL query that you want to execute, and the parameters are the values that you want to insert into the query string.

Why like that? Why not just passing an string with the SQL query already build? That's because the Exec method already escape the parameters to avoid SQL injection attacks.

Inserting data

The Exec method can be used to insert data into the database. Let's see an example for MySQL:

result, err := db.Exec("INSERT INTO customers (name) VALUES (?)", "Alice")

if err != nil {
	log.Fatal(err)
}

// Note: The LastInsertID() method is not supported by PostgreSQL.
// Check the next snipped for a PostgreSQL example.
id, err := result.LastInsertId()
if err != nil {
	log.Fatal(err)
}

affected, err := result.RowsAffected()
if err != nil {
	log.Fatal(err)
}

About PostgreSQL, it depends if we want to get the ID of the inserted row or not. If we don't want the ID, it's the same as MySQL, but with proper PostgreSQL syntax (parameters are $1, $2, instead of interrogations marks). Let's see it:

result, err := db.Exec("INSERT INTO customers (name) VALUES ($1)", "Alice")

if err != nil {
	log.Fatal(err)
}

affected, err := result.RowsAffected()

if err != nil {
	log.Fatal(err)
}

If we want to get the ID of the inserted row, we need to use the RETURNING clause in the query string, and instead of using the Exec method, we need to use the QueryRow method. This method is used to fetch data from the database, and we will see it in detail in the next section about querying the database. For now, let's see how to use it to get the ID of the inserted row:

var id int
err := db.QueryRow("INSERT INTO customers (name) VALUES ($1) RETURNING id", "Alice").Scan(&id)

if err != nil {
	log.Fatal(err)
}

Updating and deleting data

Obviously, we can use the Exec method to update and delete data too. We just have to take care to use the correct syntax for our database engine. Let's see some examples:

// UPDATE in a MySQL syntax.
result, err := db.Exec("UPDATE customers SET name = ? WHERE id = ?", "Alice", 1)

if err != nil {
	log.Fatal(err)
}

affected, err := result.RowsAffected()

if err != nil {
	log.Fatal(err)
}

// DELETE in a PostgreSQL syntax.
result, err := db.Exec("DELETE FROM customers WHERE id = $1", 1)

if err != nil {
	log.Fatal(err)
}

affected, err := result.RowsAffected()

if err != nil {
	log.Fatal(err)
}

Querying the database

The *sql.DB object also have methods for querying the database. The Query method is used to execute a query that returns multiple rows, and the QueryRow method is used to execute a query that returns a single row. Both methods receive a query string and a list of parameters, similar to the Exec method that we already saw, and return a *sql.Rows object and a *sql.Row object, respectively.

Querying a single row

Let's see an example of how to use the QueryRow method to query a single row from the database. The *sql.Row object returned by the QueryRow method has a Scan method that is used to scan the values of the returned row into variables. It receives a list of pointers to the variables where the values will be stored.

var name, surname string
err := db.QueryRow("SELECT name, surname FROM customers WHERE id = ?", 1).Scan(&name, &surname)
// err := db.QueryRow("SELECT name, surname FROM customers WHERE id = $1", 1).Scan(&name, &surname)

if err == sql.ErrNoRows {
	// There is not a row with the given ID.
	log.Fatal("no rows returned")
} else if err != nil {
	log.Fatal(err)
}

fmt.Printf("Name: %s, Surname: %s\n", name, surname)

Querying multiple rows

The Query method is used to execute a query that returns multiple rows. The *sql.Rows object returned by the Query method has a Next method to iterate over the returned rows. This method returns true if there is a new row to be scanned, and false if there are no more rows to be scanned. The *sql.Rows object also has a Scan method as *sql.Row has, and it's executed over the current row.

rows, err := db.Query("SELECT name FROM customers LIMIT 10")

if err != nil {
	log.Fatal(err)
}

// Don't forget to close the *sql.Rows when you are done.
defer rows.Close()
names := []string{}

for rows.Next() {
	var name string
	err := rows.Scan(&name)
	if err != nil {
		log.Fatal(err)
	}
	names = append(names, name)
}

if err = rows.Err(); err != nil {
	log.Fatal(err)
}

fmt.Printf("Names: %v\n", names)

Transactions

All statements in a transaction use the same database connection, provided by the *sql.DB object. This object has a Begin method to start a new transaction, that returns a *sql.Tx object. The *sql.Tx object has a Commit and a Rollback methods, used to commit and to rollback the transaction respectively. Let's see it.

// Start a new transaction.
tx, err := db.Begin()

if err != nil {
	log.Fatal(err)
}

// Execute some SQL statements inside the transaction.
_, err := tx.Exec("INSERT INTO customers ...")
if err != nil {
	// Rollback the transaction if any of the statements fails.
	tx.Rollback()
	log.Fatal(err)
}

// More SQL statements here
// ...

// Commit the transaction.
err = tx.Commit()
if err != nil {
	log.Fatal(err)
}

Wrapping up

And that's it. As you saw, the database/sql package has everything you need to integrate a SQL database into your golang application. You can connect to the database, insert and query data, and even use transactions, just using the standard library.

With this new knowledge, you are ready to implement a CRUD application in Golang. And if you want to make it a webapp (like an REST API), remember as I said at the beginning of this article that I wrote another one about the net/http package. Happy coding!