A diagram showing four components connecting to a central database server

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 the SQL language. Of the many available SQL databases, the most popular are MySQL, PostgreSQL, Microsoft SQL Server and SQLite.

Functionality for interacting with databases in Go can be found in the database / sql package, part of the standard library.

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

Getting started with SQL databases in Go

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

Thankfully, you do not have to worry about specific implementations beyond the driver. The Database / sql package handles database operations regardless of the server you are connecting to.

Some of the most popular Go database drivers are:

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

Install and import Go Database drivers

After creating a Go workspace and initiating a Go module 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 putting an underscore before the package. For example, to import the MySQL driver along 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 and perform operations on the database.

Connect to a SQL database with Go

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

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

db, err := sql.Open("mysql", "user:password@/dbname")

After trying to open the connection, remember to check for an error:

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

Depending on your database system, Open method can return an error if the database does not exist. Once you have connected to a database, you can run queries and prepare statements using the database instance such as Open returns.

Execute SQL commands

You can executes SQL commands use Prepare method for your database instance. The Prepare method takes in an SQL command and returns a prepared statement for execution along 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 statement above creates a table with the name sign in, if it does not already exist. The new table has named fields User name and Passwordeach of type TEXT.

If you insert values ​​from your program into your queries, you can use the question mark (?) Notation to denote placeholders and then send the parameters for executing the statement.

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

Once you have created a prepared statement, you can run it using it Exec method. This method allows you to send parameter values ​​from your program:

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

if err != nil {
return
}

The first value that Exec () returns are the result of the SQL query on your database. This query result allows you to check the number of rows concerned or the most recently inserted ID:

affected, err := exec.RowsAffected()

if err != nil {
return
}

fmt.Println(affected)

id, err := exec.LastInsertId()

if err != nil {
return
}

fmt.Println(id)

Retrieves query results

The Database / sql package allows you to query database results using Question method for a database instance:

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

if err != nil {
return
}

The Question method returns one Lines structure that you can use to work with your set of results. You can use, for example Next method for your row instance to iterate over 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—User name and Password—Represent each column value. The Scan method decodes the current line to the corresponding variables.

SQL databases are always at hand

Using databases in Go is easy with the database / sql package. You can use it to query and execute SQL commands in Go with ease.

SQL databases are at the core of many applications, especially those that handle large or complex amounts of data. You can use databases like the SQLite database in memory for your simple projects like web scraping and building bots.

Proper knowledge of SQL and database management systems is crucial to be able to use them effectively in your programs. But if you choose not to learn SQL, you can learn how to use ORM to interact with SQL databases in Go.

#SQL #database

Leave a Comment

Your email address will not be published.