Use SQL database in Go

In this article, I listed how to use Go to perform common SQL database operations.

Introduction`database/sql`

Go provides a clean SQL database API in its standard librarydatabase/sqlSoftware package, but a specific database driver must be installed separately.

This is a wise approach because it provides a common interface that almost every database driver implements.

If you want to use MySQL, you can usehttps://github.com/go-sql-driver/mysql.

If you are using PostgreSQL, usehttps://github.com/lib/pq.

You just need to include the lib with the followingimport _withdatabase/sqlThe API will be configured to enable the driver:

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

Open database connection

Although the goal is to abstract it, there are still differences in some aspects, such as the way to connect to the database:

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

db, err := sql.Open(“mysql”, “theUser:[email protected]/theDbName”)
if err != nil {
panic(err)
}

import “database/sql”
import _ “github.com/lib/pq”
//…

db, err := sql.Open(“postgres”, “user=theUser dbname=theDbName sslmode=verify-full”)
if err != nil {
panic(err)
}

But most of the actual APIs are not related to the database, and can be easily interchanged (not talking about SQL here, only refers to the database API).

Close database connection

Where it makes sense, you should always close the database connection.

You can use as usualdeferClose it at the end of the function that opens the database connection:

db, err := sql.Open("postgres", psqlInfo)
defer db.Close()

Extract data from the database

Select single line

The lookup table is divided into two steps. First you calldb.QueryRow(), Then you callScan()result.

example:

id := 1
var col string
sqlStatement := `SELECT col FROM my_table WHERE id=$1`
row := db.QueryRow(sqlStatement, id)
err := row.Scan(&col)
if err != nil {
    if err == sql.ErrNoRows {
        fmt.Println("Zero rows found")
    } else {
        panic(err)
    }
}

db.QueryRow()Used to query a single value in the table.

signature:

func (db *DB) QueryRow(query string, args ...interface{}) *Row

It returns a pointer todb.Rowvalue.

(*Row) ScanScan the row and copy the column values into the parameters passed to it.

signature:

func (r *Row) Scan(dest ...interface{}) error

If multiple rows are returned, only the first row is scannedAnd ignore the rest.

If no rows are returned, return oneErrNoRowserror.

var ErrNoRows = errors.New("sql: no rows in result set")

Select multiple rows

In order to query a single row, we usedb.QueryRow(). To query multiple rows, we usedb.Query(), It returns a*Rowsvalue.

From the documentation:

//Rows is the result of a query. Its cursor starts before  the first row of the result set. Use Next to advance through the rows:

    rows, err := db.Query("SELECT ...")
    ...
    defer rows.Close()
    for rows.Next() {
        var id int
        var name string
        err = rows.Scan(&id, &name)
        ...
    }
     err = rows.Err() // get any error encountered ing iteration
    ...

// Err returns the error (if any) encountered during the iteration. // Err can be called after an explicit or implicit Close.

We need to iteraterows.Next(), Which allows us to callrows.Scan()Enter the loop.

If any error occurs while preparing the next line, the loop ends and we can get the error by callingrows.Err():

type Timeline struct {
    Id int
    Content string
}
rows, err := db.Query(`SELECT id, content FROM timeline`)
if err != nil {
    panic(err)
}
defer rows.Close()
for rows.Next() {
    timeline := Timeline{}
    err = rows.Scan(&timeline.Id, &timeline.Content)
    if err != nil {
        panic(err)
    }
    fmt.Println(timeline)
}
err = rows.Err()
if err != nil {
    panic(err)
}