How to use any SQL database in Go

SQL databases store collections of data in rows and columns. You can retrieve and update data in a relational database management system (RDBMS) using SQL. Of the many SQL databases available, the most popular are MySQL, PostgreSQL, Microsoft SQL Server, and SQLite.

The functionality to interact with databases in Go is in the database/sql package, which is part of the standard library.

The database/sql package interacts with SQL databases using drivers. You can import an appropriate driver package for your RDBMS and use it to interact with the database.

Get started with SQL databases in Go

The database/sql package is a generic interface for relational databases. To work with a specific database server, you will need to use one of the many drivers available.

Fortunately, you don’t have to worry about specific implementations beyond the driver. The database/sql package handles database operations independently of the server you are connecting to.

Some of the most popular Go database drivers are:

You can use the List of LibHunt Drivers to find equivalents for other types of databases. The list also shows the relative popularity of each database system:

Installing and Importing Go Database Drivers

Once you have created a Go workspace and initialized a Go modules file, install the driver that matches your database system. For example, run one of the following commands in your workspace directory to install the MySQL or SQLite driver:

go get -u github.com/go-sql-driver/mysql
go get github.com/mattn/go-sqlite3

After installing your driver, import it for side effects by prefixing an underscore before the package. For example, to import the MySQL driver with the database/sql package:

import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
)

By importing the driver package for side effects, you can use it to connect to the database and perform operations on it.

Connect to an SQL database with Go

After importing the database drivers, you can create a database connection using Open method of database/sql package. This method takes the driver name and the database path (for SQLite) or a connection string (for MySQL). For example, use one of the following options:

db, err := sql.Open("sqlite3", "models/testdb.db") 

db, err := sql.Open("mysql", "user:[email protected]/dbname")

Once you attempt to open the connection, remember to check for an error:

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

Depending on your database system, the Open The method may return an error if the database does not exist. Once connected to a database, you can execute queries and prepare statements using the database instance which Open Return.

Executing SQL commands

You can run SQL commands using To prepare method of your DB instance. The To prepare The method takes an SQL command and returns a prepared statement for execution with an error object. For example, if you want to create a new table:

command, err := db.Prepare("CREATE TABLE IF NOT EXISTS login(username TEXT, password TEXT)")

The above statement creates a table named connection, if it does not already exist. New table has named fields username and the passwordeach type TEXT.

If you insert values ​​from your program into your queries, you can use question mark (?) notation to indicate placeholders and then pass the parameters when the statement executes.

command, err := db.Prepare("INSERT INTO login(username, password) values(?,?)")

Once you have created a prepared statement, you can execute it using its Executive method. This method allows you to pass parameter values ​​from your program:

exec, err := command.Exec(value1, Value2)

if err != nil {
return
}

The first value that Exec() returns is the result of the SQL query against your database. Using this query result, you can check the number of affected rows or the last inserted ID:

affected, err := exec.RowsAffected()

if err != nil {
return
}

fmt.Println(affected)

id, err := exec.LastInsertId()

if err != nil {
return
}

fmt.Println(id)

Retrieving query results

The database/sql package allows you to query database results using the Query method of a database instance:

rows, err := db.Query("SELECT * FROM User")

if err != nil {
return
}

The Query method returns a Lines struct that you can use to work with your result set. For example, you can use the Next method of your rows instance to loop through it and work with individual rows:

var username, password string

for rows.Next() {
err := rows.Scan(&username, &password)

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

fmt.Println(username, password)
}

In the example above, two string variables—username and the password— represent each column value. The To analyse decodes the current line into these corresponding variables.

SQL databases are still useful

Working with databases in Go is simple with the database/sql package. You can use it to easily query and execute SQL commands in Go.

SQL databases are at the heart of many applications, especially those dealing with large or complex data sets. You can use databases like SQLite in-memory database for your simple projects like web scraping and bot building.

A good knowledge of SQL and database management systems is essential to use them effectively in your programs. However, if you choose not to learn SQL, you can learn to use ORMs to interact with SQL databases in Go.

Maria H. Underwood