Get started with SQLair¶

Imagine you are writing a Go program that handles your customers and their orders. The business logic is contained in the Go program but the customer and order data is stored in a SQL database. In this tutorial, you’ll see how easy SQLair makes it to map between the database and the Go program. Let’s get started!

Set things up¶

For this tutorial you will need Go version 1.18. To use SQLair you will need a Go project that sets up a database.

To create the project run:

mkdir tutorial 
cd tutorial
go mod init sqlair-tutorial

To set up the database, in the tutorial folder, create a new main.go file, open it in your favourite editor and copy in the code below:

package main

import (
    // database/sql is used to open the database and set up the schema.
    "database/sql"
    "fmt"

    // go-sqlite3 provides the SQLite driver. Importing it registers the driver 
    // so that sql.Open can find it.
    // SQLair can be used with any database/driver that works with the 
    // database/sql package.
    _ "github.com/mattn/go-sqlite3"
)

// CreateDB opens a SQLite database and adds a customer and order schema.
func CreateDB() (*sql.DB, error) {
    // sql.Open will create an in process, in memory database that will disappear 
    // when the program finishes running.
    sqlDB, err := sql.Open(
        "sqlite3", 
        "file:sqlair-tutorial.db?cache=shared&mode=memory",
    )
    if err != nil {
        return nil, fmt.Errorf("opening SQLite database: %w", err)
    }

    // Use database/sql to create the database schema.
    _, err = sqlDB.Exec(`
    CREATE TABLE customers (
        id integer PRIMARY KEY,
        name text,
        address text,
        city text, 
        postal_code text,
        country text
    );

    CREATE TABLE orders (
        id integer PRIMARY KEY,
        customer_id integer,
        order_date timestamp,
        FOREIGN KEY (customer_id) REFERENCES customer (id)
    );
`)
    if err != nil {
        return nil, fmt.Errorf("creating tables: %w", err)
    }
    
    return sqlDB, nil
}

func tutorial() error {
    // Create the database.
    _, err := CreateDB()
    if err != nil {
        return err
    }
    
    return nil
}

func main() {
    err := tutorial()
    if err != nil {
        fmt.Printf("ERROR: %s\n", err)
    }
    fmt.Println("Finished without errors!")
}

Now, to test the program, install the dependencies and run:

go mod tidy
go run .

You should get the output: "Finished without errors!".

Wrap the database with SQLair¶

To get started with SQLair, wrap your database/sql database with SQLair.

In main.go, at the top of the file, add SQLair to the imported functions.

import (
    ...
    "github.com/canonical/sqlair"
    ...
)

In main.go, replace the tutorial function with the new version below:

func tutorial() error {
    // Create the database.
    sqlDB, err := CreateDB()
    if err != nil {
    return err
    }

    // Wrap the DB with SQLair
    db := sqlair.NewDB(sqlDB)

    return nil
}

Install the dependencies and run:

go mod tidy
go run .

Again, you should get the output: "Finished without errors!".

SQLair-proof your types¶

Next, define structs to represent customers and orders. These correspond to the rows of the tables in the database.

In the tutorial project, in the main.go file add these types at the top of the file, below the imports. Note the db tags (e.g., db:"id") – SQLair will use them to work out how the struct fields map to the table columns in the database.

type Customer struct {
    ID         int    `db:"id"`
    Name       string `db:"name"`
    Address    string `db:"address"`
    City       string `db:"city"`
    PostalCode string `db:"postal_code"`
    Country    string `db:"country"`
}

type Order struct {
    ID         int       `db:"id,omitempty"`
    CustomerID int       `db:"customer_id"`
    OrderDate  time.Time `db:"order_date"`
}

Supported Go types and their format, Go | Struct Tags

Put your struct in the database¶

Now that you have the scaffolding let’s populate our database with some data. To do this build an insert statement.

To insert a Customer struct you can write the following SQLair query:

INSERT INTO customers (*) VALUES ($Customer.*)

The special syntax $Customer.* is called a SQLair input expression. These are marked by the dollar sign ($). This tells SQLair that we want to insert all tagged fields from the Customer struct. The asterisk (*) on the left had side of the VALUES keyword tells SQLair that you want to insert all the values listed on the right.

Now that we have written our query the next stage is to prepare it for execution with sqlair.Prepare.

In the main.go file, just above the tutorial function definition, paste the code below. Note that sqlair.Prepare also takes samples of all the Go types mentioned in the query.

func populateDB(db *sqlair.DB) error {
    // Prepare uses the reflection information from type samples to verify the
    // SQLair expressions and generate the SQL to send to the database.    
    insertCustomerStmt, err := sqlair.Prepare(`
        INSERT INTO customers (*) 
        VALUES ($Customer.*)
        `, Customer{},
    )
    if err != nil {
        return fmt.Errorf("preparing insert customer statement: %w", err)
    }
    
    return nil
}

Input expression syntax, Query the database

To insert the data into the database, create a Query on the database using the DB.Query method and run it with Query.Run.

In the populateDB function, under the previous code but above the return nil, paste the code below:

// Define a customer to insert.
customer := Customer{
    ID:         1,
    Name:       "Joe",
    Address:    "15 Westmeade Road",
    City:       "Hounslow",
    PostalCode: "TW4 7EY",
    Country:    "England",
}

// Create the query object on the database and Run it without expecting any 
// results back.
err = db.Query(context.Background(), insertCustomerStmt, customer).Run()
if err != nil {
    return fmt.Errorf("inserting initial customer: %w", err)
}

Congratulations! You have just learnt how to insert a struct into a database with SQLair.

Put all the structs in the database¶

To fill up our database, you don’t just want to insert a single customer, you want to insert ALL the customers. Instead of passing a single customer struct as an argument to DB.Query, you can pass a slice of customer structs, and SQLair will automatically turn your query into a bulk insert.

In main.go in the populateDB function, replace the customer variable declaration with the slices of customers below, and change the customer in the query to customers.

// Define the customers to insert.
customers := []Customer{{
    ID:         1,
    Name:       "Joe",
    Address:    "15 Westmeade Road",
    City:       "Hounslow",
    PostalCode: "TW4 7EY",
    Country:    "England",
}, {
    ID:         2,
    Name:       "Simon",
    Address:    "11 Pearch Avenue",
    City:       "Birmingham",
    PostalCode: "B37 5NA",
    Country:    "England",
}, {
    ID:         3,
    Name:       "Heather",
    Address:    "6 Moorland Close",
    City:       "Inverness",
    PostalCode: "IV1 6PA",
    Country:    "Scotland",
}}

// Insert the slice of customers.
err = db.Query(context.Background(), insertCustomerStmt, customers).Run()
if err != nil {
    return fmt.Errorf("inserting initial customers: %w", err)
}

To compile the code, in at the top of main.go in the import section, add the time and context packages.

import (
    // database/sql is used to open the database and set up the schema.
    "database/sql"
    "fmt"
    "time"
    "context"
    ...
)

Now delete the old tutorial function and replace it with the one below:

func tutorial() error {
    db, err := NewDB()
    if err != nil {
        return err
    }

    err = populateDB(db)
    if err != nil {
        return fmt.Errorf("populating database %w", err)
    }

    return nil
}

Now let’s check it works:

go run .

You should get the output: "Finished without errors!".

Put the orders in the database¶

Now you’ve added your customers, it’s time to add some orders!

At the bottom of the populateDB function but before the return nil add the order data.

orders = []Order{{
    CustomerID: 1,
    OrderDate:  time.Date(2024, time.January, 10, 0, 0, 0, 0, time.UTC),
}, {
    CustomerID: 1,
    OrderDate:  time.Date(2024, time.September, 20, 0, 0, 0, 0, time.UTC),
}, {
    CustomerID: 2,
    OrderDate:  time.Date(2024, time.August, 23, 0, 0, 0, 0, time.UTC),
}, {
    CustomerID: 3,
    OrderDate:  time.Date(2024, time.January, 3, 0, 0, 0, 0, time.UTC),
}, {
    CustomerID: 3,
    OrderDate:  time.Date(2024, time.April, 11, 0, 0, 0, 0, time.UTC),
}, {
    CustomerID: 3,
    OrderDate:  time.Date(2024, time.June, 8, 0, 0, 0, 0, time.UTC),
}}

It’s your turn now! Have a go at adding the orders to the database. It should look very similar to the adding the customers.

If you want to see the answer, click below.

Get structs from the database¶

Now that we have the customer and order data it is time to query it.

Get a customer¶

Somebody has asked you get all the information you have about the customer with the name "Joe". You need to put all his information in a Customer struct and return this to the user.

In main.go, in the tutorial function under the call to populateDB, add the code below.

stmt, err = sqlair.Prepare(`
    SELECT &Customer.* 
    FROM customers
    WHERE name = $Customer.name
`, Customer{})
if err != nil {
    return Customer{}, fmt.Errorf("preparing select customer statement: %w", err)
}

The special syntax in the query, &Customer.*, is an output expression. The ampersand (&) marks output expressions. This tells SQLair that you want to fetch all columns given in the tags of the Customer struct and use them to fill the struct in.

In the WHERE clause at the bottom, there is an input expression. This is telling SQLair to pass the value in the field tagged with name in Customer as an argument to the database.

Note

The column name from the tag rather than the field name is used when specifying a value in a struct e.g. $Customer.name.

To get the results out, we can use Query.Get. In the tutorial function, below the sqlair.Prepare, add the code below:

// Define a customer struct with only the name specified. This can be used for 
// both the input and output of the query.
joe := Customer{
    Name: "Joe",
}

// Get returns the first query result from the database.
err = db.Query(context.Background(), stmt, joe).Get(&joe)
if err != nil {
    return Customer{}, fmt.Errorf(
        "selecting customer %s from the database: %w", 
        name, err,
    )
}

fmt.Printf("Customer record of Joe: %#v\n", joe)

Run the program, and you should get the output below:

$ go run .
Customer record of Joe: main.Customer{ID:1, Name:"Joe", Address:"15 Westmeade Road", City:"Hounslow", PostalCode:"TW4 7EY", Country:"England"}
Finished without errors!

Output expression syntax, Input expression syntax, Query.Get

Get all the orders¶

You’ve just been asked to get all the orders ever made from the database. They want a slice of Order structs representing this information. Luckily, SQLair has your back with the Query.GetAll function.

Let’s first prepare a query to select the orders. In the tutorial function, copy the code below:

stmt, err := sqlair.Prepare(`
    SELECT &Order.*
    FROM Orders
`, Order{})
if err != nil {
    return fmt.Errorf("preparing select orders statement: %w", err)
}

// Define a slice of orders to hold all the orders from the database.
var orders []Order
// Get all the orders.
err = db.Query(context.Background(), stmt).GetAll(&orders)
if err != nil {
    return fmt.Errorf("getting all orders from the database: %w", err)
}
// Print out the orders.
for i, order := range orders {
    fmt.Printf("Order %d: %#v\n", i, order)
}

Now run the program and you should get the output below.

go run .

Order 0: main.Order{ID:1, CustomerID:1, OrderDate:time.Date(2024, time.January, 10, 0, 0, 0, 0, time.UTC)}
Order 1: main.Order{ID:2, CustomerID:1, OrderDate:time.Date(2024, time.September, 20, 0, 0, 0, 0, time.UTC)}
Order 2: main.Order{ID:3, CustomerID:2, OrderDate:time.Date(2024, time.August, 23, 0, 0, 0, 0, time.UTC)}
Order 3: main.Order{ID:4, CustomerID:3, OrderDate:time.Date(2024, time.January, 3, 0, 0, 0, 0, time.UTC)}
Order 4: main.Order{ID:5, CustomerID:3, OrderDate:time.Date(2024, time.April, 11, 0, 0, 0, 0, time.UTC)}
Order 5: main.Order{ID:6, CustomerID:3, OrderDate:time.Date(2024, time.June, 8, 0, 0, 0, 0, time.UTC)}
Finished without errors!

You have all the orders. Congratulations! You have learnt the basics of SQLair!

Query.GetAll

Tear things down¶

To restore your machine to the state it had before you started this tutorial, simply delete the directory. No further steps required.

cd ..
rm -r tutorial

Next steps¶

This tutorial has introduced you to the basics, but there is a lot more to explore!

How-to guides, Reference, pkg.go.dev