Coding With Patrik

Posted on May 14 • Originally published at codingwithpatrik.dev

How to Use SQL in Go

#sql #go #tutorial #programming

Learn how to use SQL in Go (Golang) using SQLite! This tutorial covers connecting to a database, writing queries, creating tables, CRUD operations, and handling context and timeouts. You will be using exec, query, and queryrow, scan methods.

Setting up the project

Start by opening a terminal.

Initialize a new project:
```
go mod init sql-in-go
```
Install the dependencies:

Install SQLite:
```
go get -u github.com/mattn/go-sqlite3
```

Connecting to the database

To connect to the database create a new file called main.go and add the following code:

package main

import (
    "database/sql"
    "log"

    _ "github.com/mattn/go-sqlite3"
)

func main() {
    db, err := sql.Open("sqlite3", "./shop.db")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()
}

We use the sql.Open function to open a connection to the database. The first argument is the driver name and the second argument is the database file path or the database connection string depending on the database.

We check if there is an error in opening the database connection. If there is an error, we log the error and exit the program.

The defer db.Close() ensures the database connection is closed when we are done.

Common database operations

Here is a list of the queries we will use:

Operation	Description	Example
Create	Create a new table if it does not exist	`CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, product TEXT, amount INTEGER);`
Insert	Insert new data into a table	`INSERT INTO orders (product, amount) VALUES ('Laptop', 1000);`
Query all	Retrieve all data from a table	`SELECT * FROM orders;`
Query with condition	Retrieve rows matching a condition from a table	`SELECT * FROM orders WHERE id = 1;`
Update	Modify existing data in a table	`UPDATE orders SET amount = 1500 WHERE id = 1;`
Delete	Remove data from a table	`DELETE FROM orders WHERE id = 1;`

Working with Orders & Database

Creating the OrderRepository struct

Create new folder called database and inside create a file called orders.go.

package database

import "database/sql"

type OrderRepository struct {
    Db *sql.DB
}

type Order struct {
    Id      int
    Product string
    Amount  int
}

We created a OrderRepository struct that holds the Db connection. This allows us to use it in the methods we will create. We also created an Order struct that will hold the order data.

Creating the order table

Let's add a method to create the order table.

func (r *OrderRepository) CreateTable() error {
    _, err := r.Db.Exec(`CREATE TABLE IF NOT EXISTS orders (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        product TEXT,
        amount INTEGER
    )`)

    return err
}

We use the Exec method to execute the query. The Exec method is used for queries that don't return any data.

CREATE TABLE IF NOT EXISTS: Creates a table only if it doesn't already exist.
INTEGER PRIMARY KEY AUTOINCREMENT: Sets up a unique identifier for each row that automatically increases.
TEXT: Represents a column for text data.
INTEGER: Represents a column for numeric data.

Viewing the orders table in a GUI

You can use a GUI tool like TablePlus to view the orders table.

Download it from here.

You should now be able to see the orders table in the GUI.

Inserting an order

Add a method to insert order data into the database.

func (r *OrderRepository) Insert(order Order) error {
    _, err := r.Db.Exec("INSERT INTO orders (product, amount) VALUES (?, ?)", order.Product, order.Amount)
    return err
}

INSERT INTO orders (product, amount) VALUES (?, ?): Inserts a new row into the orders table with the product and amount values.

The question marks are placeholders for the values we want to insert.

Always use placeholders instead of concatenating strings to avoid SQL injection.

Get all orders

func (r *OrderRepository) GetAll() ([]Order, error) {
    rows, err := r.Db.Query("SELECT * FROM orders")
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var orders []Order
    for rows.Next() {
        var order Order
        err := rows.Scan(&order.Id, &order.Product, &order.Amount)
        if err != nil {
            return nil, err
        }
        orders = append(orders, order)
    }
    return orders, nil
}

First we query the rows from the database using the Query method. The query method is used for queries that return data. The * is a wildcard it means that we want to select all columns.

We use defer rows.Close() to close the rows after we are done with them. This is to avoid memory leaks.

Then we iterate over the rows and scan the data into the Order struct. We check if there is an error in scanning the data and return it if there is one.

We append each order to the orders slice.

Finally we return the orders.

Retrieve a single order by id

func (r *OrderRepository) GetById(id int) (Order, error) {
    var order Order
    err := r.Db.QueryRow("SELECT * FROM orders WHERE id = ?", id).Scan(&order.Id, &order.Product, &order.Amount)
    if err != nil {
        return Order{}, err
    }
    return order, nil
}

Here we use the QueryRow method. Difference between the Query method and the QueryRow method is that the Query method returns multiple rows and the QueryRow method returns a single row. Then we pass the id as a placeholder to the where clause and scan the data into the Order struct.

If there is an error we return an empty Order and the error otherwise we return the order.

Update an order

func (r *OrderRepository) Update(order Order) error {
    _, err := r.Db.Exec("UPDATE orders SET product = ?, amount = ? WHERE id = ?", order.Product, order.Amount, order.Id)
    return err
}

UPDATE orders SET product = ?, amount = ? WHERE id = ?: Updates the product and amount columns for the row with the given id.

Delete an order

func (r *OrderRepository) Delete(id int) error {
    _, err := r.Db.Exec("DELETE FROM orders WHERE id = ?", id)
    return err
}

DELETE FROM orders WHERE id = ?: Deletes the row with the given id.

Testing create tables

Let's try it out. Add the following code to the main.go file:

package main

import (
    "database/sql"
    "log"
    "sql-in-go/database"

    _ "github.com/mattn/go-sqlite3"
)

func main() {
    dbConnection, err := sql.Open("sqlite3", "./shop.db")
    if err != nil {
        log.Fatal("Error opening database:", err)
    }
    defer dbConnection.Close()

    orderRepository := &database.OrderRepository{Db: dbConnection}

    err = orderRepository.CreateTable()
    if err != nil {
        log.Fatal("Error creating orders table:", err)
    }
}

We create the OrderRepository struct and passed the database connection to it.

After that we call the CreateTable method for the repository to create the table and check if there are any errors.

Open a terminal and run the following command:

go run main.go

It should create the shop.db file and the orders table inside it.

Inserting and retrieving data

Update the main.go file to insert data into the database.

package main

import (
    "database/sql"
    "log"
    "sql-in-go/database"

    _ "github.com/mattn/go-sqlite3"
)

func main() {
    dbConnection, err := sql.Open("sqlite3", "./shop.db")
    if err != nil {
        log.Fatal("Error opening database:", err)
    }
    defer dbConnection.Close()

    orderRepository := &database.OrderRepository{Db: dbConnection}

    err = orderRepository.CreateTable()
    if err != nil {
        log.Fatal("Error creating orders table:", err)
    }

    err = orderRepository.Insert(database.Order{Product: "Laptop", Amount: 10})
    if err != nil {
        log.Fatal("Error inserting order:", err)
    }

    err = orderRepository.Insert(database.Order{Product: "Keyboard", Amount: 50})
    if err != nil {
        log.Fatal("Error inserting order:", err)
    }

    orders, err := orderRepository.GetAll()
    if err != nil {
        log.Fatal("Error getting orders:", err)
    }

    log.Println(orders)
}

Updating & Deleting data

The only thing left to do is to update and delete data.

Update the main.go file to update and delete data.

package main

import (
    "database/sql"
    "log"
    "sql-in-go/database"

    _ "github.com/mattn/go-sqlite3"
)

func main() {
    dbConnection, err := sql.Open("sqlite3", "./shop.db")
    if err != nil {
        log.Fatal("Error opening database:", err)
    }
    defer dbConnection.Close()

    orderRepository := &database.OrderRepository{Db: dbConnection}

    err = orderRepository.CreateTable()
    if err != nil {
        log.Fatal("Error creating orders table:", err)
    }

    err = orderRepository.Insert(database.Order{Product: "Laptop", Amount: 10})
    if err != nil {
        log.Fatal("Error inserting order:", err)
    }

    err = orderRepository.Insert(database.Order{Product: "Keyboard", Amount: 50})
    if err != nil {
        log.Fatal("Error inserting order:", err)
    }

    orders, err := orderRepository.GetAll()
    if err != nil {
        log.Fatal("Error getting orders:", err)
    }

    log.Println(orders)

    order, err := orderRepository.GetById(orders[0].Id)
    if err != nil {
        log.Fatal("Error getting order:", err)
    }

    order.Amount = 1500
    err = orderRepository.Update(order)
    if err != nil {
        log.Fatal("Error updating order:", err)
    }

    orders, err = orderRepository.GetAll()
    if err != nil {
        log.Fatal("Error getting orders:", err)
    }

    log.Println(orders)

    err = orderRepository.Delete(order.Id)
    if err != nil {
        log.Fatal("Error deleting order:", err)
    }

    orders, err = orderRepository.GetAll()
    if err != nil {
        log.Fatal("Error getting orders:", err)
    }

    log.Println(orders)
}

Context & Timeout

You can use context and timeout to set a deadline for the query execution.

The Exec, Query, and QueryRow methods also have a context version that takes a context as an argument.

ExecContext, QueryContext, and QueryRowContext.

The reason why we use context and timeout is to avoid blocking the database connection for too long.

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

err := r.Db.QueryContext(ctx, "SELECT * FROM orders")

Here we get the context from the context.Background() function and set a timeout of 5 seconds for the query execution. If the query execution takes longer than 5 seconds, it will be canceled.

Conclusion

In this tutorial, you learned how to use SQL in Go (Golang) with SQLite. You learned how to create, insert, retrieve, update, and delete data from the database.

Full source code can be found here Github

I hope you enjoyed this tutorial and learned something new.

Originally published on Coding With Patrik

DEV Community