sqlair

package module
v0.0.0-...-20ce58d Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Nov 14, 2025 License: Apache-2.0 Imports: 8 Imported by: 119

README

SQLair

Go Reference Documentation Status Go Report Card CI License

SQLair is a Go package and an extension to SQL that makes it easy to map between Go types and a SQL database.

Contents

✈ Fly SQLair

Reading and writing to a database in Go can be hard. The solutions out there are often either too prescriptive (e.g., Object-Relational Mapping (ORM) libraries where you lose the ability to write your own SQL) or too basic (e.g., database/sql, where you have to manually iterate over each row you get back from the database).

SQLair fills the gap in the middle – automatic type mapping plus the ability to write any SQL you wish. Write your types directly into the query with SQLair expressions and SQLair will automatically map them to the query arguments and inject them with the query results.

If you’re looking to streamline your database game, go get SQLair today.

🎯 Features

  • Maps database inputs and outputs to Go types
  • Simple API
  • Database agnostic
  • Automatic statement caching

⚡ Get Started

Get started with the SQLair tutorial.

📖 Documentation

See the documentation on Read the Docs and in the Go Package Reference.

💫 Contributing

See our CONTRIBUTING.md.

Documentation

Index

Examples

Constants

This section is empty.

Variables

View Source 
var ErrNoRows = sql.ErrNoRows

Functions

This section is empty.

Types

type DB 

type DB struct {
	// contains filtered or unexported fields
}

func NewDB 

func NewDB(sqldb *sql.DB) *DB

NewDB creates a new sqlair.DB from a sql.DB.

func (*DB) Begin 

func (db *DB) Begin(ctx context.Context, opts *TXOptions) (*TX, error)

Begin starts a transaction. A transaction must be ended with a TX.Commit or TX.Rollback.

func (*DB) PlainDB 

func (db *DB) PlainDB() *sql.DB

PlainDB returns the underlying database object.

func (*DB) Query 

func (db *DB) Query(ctx context.Context, s *Statement, inputArgs ...any) *Query

Query builds a new query from a context, a Statement and the input arguments. The query is run on the database when one of Query.Iter, Query.Run, Query.Get or Query.GetAll is executed.

A new Query object should be created every time the statement is run against the database. The Query is designed to be used immediately and run once.

type Iterator 

type Iterator struct {
	// contains filtered or unexported fields
}

Iterator is used to iterate over the results of the query.

func (*Iterator) Close 

func (iter *Iterator) Close() error

Close finishes the iteration and returns any errors encountered. Close can be called multiple times on the Iterator and the same error will be returned.

func (*Iterator) Get 

func (iter *Iterator) Get(outputArgs ...any) (err error)

Get decodes the result from the previous Iterator.Next call into the provided output arguments.

Before the first call of Iterator.Next a pointer to an empty Outcome struct may be passed to Get as the only argument to fill it information about query execution.

func (*Iterator) Next 

func (iter *Iterator) Next() bool

Next prepares the next row for Iterator.Get. If an error occurs during iteration it will be returned with Iterator.Close.

type M 

type M map[string]any

type Outcome 

type Outcome struct {
	// contains filtered or unexported fields
}

Outcome holds metadata about executed queries, and can be provided as the first output argument to any of the Get methods to populate it with information about the query execution.

func (*Outcome) Result 

func (o *Outcome) Result() sql.Result

Result returns a sql.Result containing information about the query execution. If no result is set then Result returns nil.

type Query 

type Query struct {
	// contains filtered or unexported fields
}

Query represents a query on a database. It is designed to be run once and used immediately since it contains the query context.

func (*Query) Get 

func (q *Query) Get(outputArgs ...any) error

Get runs the query and decodes the first row returned into the provided output arguments. It returns ErrNoRows if output arguments were provided but no results were found.

A pointer to an empty Outcome struct may be provided as the first output variable to fill it with information about query execution.

Example 
package main

import (
	"context"
	"database/sql"
	"fmt"

	"github.com/canonical/sqlair"
)

func main() {
	type Employee struct {
		ID   int    `db:"employee_id"`
		Name string `db:"name"`
		Team string `db:"team_name"`
	}

	db, err := employeeDB()
	if err != nil {
		return
	}

	stmt, err := sqlair.Prepare("SELECT &Employee.* FROM employees", Employee{})
	if err != nil {
		return
	}

	var e Employee
	err = db.Query(context.Background(), stmt).Get(&e)
	if err != nil {
		return
	}

	fmt.Printf("Employee: %+v", e)

}

func employeeDB() (*sqlair.DB, error) {
	type Employee struct {
		ID   int    `db:"employee_id"`
		Name string `db:"name"`
		Team string `db:"team_name"`
	}

	sqldb, err := sql.Open("sqlite3", "file:example.db?mode=memory")
	if err != nil {
		return nil, err
	}
	db := sqlair.NewDB(sqldb)

	createStmt, err := sqlair.Prepare(`CREATE TABLE employees (employee_id integer, team_name string, name text);`)
	if err != nil {
		return nil, err
	}

	err = db.Query(context.Background(), createStmt).Run()
	if err != nil {
		return nil, err
	}

	insertStmt, err := sqlair.Prepare(`INSERT INTO employees (*) VALUES ($Employee.*)`, Employee{})
	if err != nil {
		return nil, err
	}

	alastair := Employee{ID: 1, Name: "Alastair", Team: "Juju"}
	alberto := Employee{ID: 2, Name: "Alberto", Team: "OCTO"}

	err = db.Query(context.Background(), insertStmt, alastair).Run()
	if err != nil {
		return nil, err
	}

	err = db.Query(context.Background(), insertStmt, alberto).Run()
	if err != nil {
		return nil, err
	}

	return db, nil
}

Output:

Employee: {ID:1 Name:Alastair Team:Juju}
Example (WithInput) 
package main

import (
	"context"
	"database/sql"
	"fmt"

	"github.com/canonical/sqlair"
)

func main() {
	type Employee struct {
		ID   int    `db:"employee_id"`
		Name string `db:"name"`
		Team string `db:"team_name"`
	}

	type Team struct {
		ID   int    `db:"team_id"`
		Name string `db:"name"`
	}

	db, err := employeeDB()
	if err != nil {
		return
	}

	stmt, err := sqlair.Prepare(
		"SELECT &Employee.* FROM employees WHERE team_name = $Team.name",
		Employee{}, Team{},
	)
	if err != nil {
		return
	}

	var e Employee
	team := Team{Name: "Juju", ID: 1}
	err = db.Query(context.Background(), stmt, team).Get(&e)
	if err != nil {
		return
	}

	fmt.Printf("Employee: %+v", e)

}

func employeeDB() (*sqlair.DB, error) {
	type Employee struct {
		ID   int    `db:"employee_id"`
		Name string `db:"name"`
		Team string `db:"team_name"`
	}

	sqldb, err := sql.Open("sqlite3", "file:example.db?mode=memory")
	if err != nil {
		return nil, err
	}
	db := sqlair.NewDB(sqldb)

	createStmt, err := sqlair.Prepare(`CREATE TABLE employees (employee_id integer, team_name string, name text);`)
	if err != nil {
		return nil, err
	}

	err = db.Query(context.Background(), createStmt).Run()
	if err != nil {
		return nil, err
	}

	insertStmt, err := sqlair.Prepare(`INSERT INTO employees (*) VALUES ($Employee.*)`, Employee{})
	if err != nil {
		return nil, err
	}

	alastair := Employee{ID: 1, Name: "Alastair", Team: "Juju"}
	alberto := Employee{ID: 2, Name: "Alberto", Team: "OCTO"}

	err = db.Query(context.Background(), insertStmt, alastair).Run()
	if err != nil {
		return nil, err
	}

	err = db.Query(context.Background(), insertStmt, alberto).Run()
	if err != nil {
		return nil, err
	}

	return db, nil
}

Output:

Employee: {ID:1 Name:Alastair Team:Juju}

func (*Query) GetAll 

func (q *Query) GetAll(sliceArgs ...any) (err error)

GetAll iterates over the query and scans all rows into the provided slices. sliceArgs must contain pointers to slices of each of the output types. A pointer to an empty Outcome struct may be provided as the first output variable to get information about query execution.

ErrNoRows will be returned if no rows are found.

Example 
package main

import (
	"context"
	"database/sql"
	"fmt"

	"github.com/canonical/sqlair"
)

func main() {
	type Employee struct {
		ID   int    `db:"employee_id"`
		Name string `db:"name"`
		Team string `db:"team_name"`
	}

	db, err := employeeDB()
	if err != nil {
		return
	}

	stmt, err := sqlair.Prepare("SELECT &Employee.* FROM employees", Employee{})
	if err != nil {
		return
	}

	var es []Employee
	err = db.Query(context.Background(), stmt).GetAll(&es)
	if err != nil {
		return
	}

	fmt.Printf("Employees: %+v", es)

}

func employeeDB() (*sqlair.DB, error) {
	type Employee struct {
		ID   int    `db:"employee_id"`
		Name string `db:"name"`
		Team string `db:"team_name"`
	}

	sqldb, err := sql.Open("sqlite3", "file:example.db?mode=memory")
	if err != nil {
		return nil, err
	}
	db := sqlair.NewDB(sqldb)

	createStmt, err := sqlair.Prepare(`CREATE TABLE employees (employee_id integer, team_name string, name text);`)
	if err != nil {
		return nil, err
	}

	err = db.Query(context.Background(), createStmt).Run()
	if err != nil {
		return nil, err
	}

	insertStmt, err := sqlair.Prepare(`INSERT INTO employees (*) VALUES ($Employee.*)`, Employee{})
	if err != nil {
		return nil, err
	}

	alastair := Employee{ID: 1, Name: "Alastair", Team: "Juju"}
	alberto := Employee{ID: 2, Name: "Alberto", Team: "OCTO"}

	err = db.Query(context.Background(), insertStmt, alastair).Run()
	if err != nil {
		return nil, err
	}

	err = db.Query(context.Background(), insertStmt, alberto).Run()
	if err != nil {
		return nil, err
	}

	return db, nil
}

Output:

Employees: [{ID:1 Name:Alastair Team:Juju} {ID:2 Name:Alberto Team:OCTO}]

func (*Query) Iter 

func (q *Query) Iter() *Iterator

Iter returns an Iterator to iterate through the results row by row. Iterator.Close must be run once iteration is finished.

Example 
package main

import (
	"context"
	"database/sql"
	"fmt"

	"github.com/canonical/sqlair"
)

func main() {
	type Employee struct {
		ID   int    `db:"employee_id"`
		Name string `db:"name"`
		Team string `db:"team_name"`
	}

	db, err := employeeDB()
	if err != nil {
		return
	}

	stmt, err := sqlair.Prepare("SELECT &Employee.* FROM employees", Employee{})
	if err != nil {
		return
	}

	var es []Employee
	iter := db.Query(context.Background(), stmt).Iter()
	for iter.Next() {
		var e Employee
		err := iter.Get(&e)
		if err != nil {
			return
		}
		es = append(es, e)
	}
	err = iter.Close()
	if err != nil {
		return
	}

	fmt.Printf("Employees: %+v", es)
}

func employeeDB() (*sqlair.DB, error) {
	type Employee struct {
		ID   int    `db:"employee_id"`
		Name string `db:"name"`
		Team string `db:"team_name"`
	}

	sqldb, err := sql.Open("sqlite3", "file:example.db?mode=memory")
	if err != nil {
		return nil, err
	}
	db := sqlair.NewDB(sqldb)

	createStmt, err := sqlair.Prepare(`CREATE TABLE employees (employee_id integer, team_name string, name text);`)
	if err != nil {
		return nil, err
	}

	err = db.Query(context.Background(), createStmt).Run()
	if err != nil {
		return nil, err
	}

	insertStmt, err := sqlair.Prepare(`INSERT INTO employees (*) VALUES ($Employee.*)`, Employee{})
	if err != nil {
		return nil, err
	}

	alastair := Employee{ID: 1, Name: "Alastair", Team: "Juju"}
	alberto := Employee{ID: 2, Name: "Alberto", Team: "OCTO"}

	err = db.Query(context.Background(), insertStmt, alastair).Run()
	if err != nil {
		return nil, err
	}

	err = db.Query(context.Background(), insertStmt, alberto).Run()
	if err != nil {
		return nil, err
	}

	return db, nil
}

Output:

Employees: [{ID:1 Name:Alastair Team:Juju} {ID:2 Name:Alberto Team:OCTO}]

func (*Query) Run 

func (q *Query) Run() error

Run is used to run a query on a database and disregard any results. Run is an alias for Query.Get that takes no arguments.

Example 
package main

import (
	"context"
	"database/sql"
	"fmt"

	"github.com/canonical/sqlair"
)

func main() {
	type Employee struct {
		ID   int    `db:"employee_id"`
		Name string `db:"name"`
		Team string `db:"team_name"`
	}

	db, err := employeeDB()
	if err != nil {
		return
	}

	insertStmt, err := sqlair.Prepare(`INSERT INTO employees (*) VALUES ($Employee.*)`, Employee{})
	if err != nil {
		return
	}

	alastair := Employee{ID: 1, Name: "Alastair", Team: "Juju"}

	err = db.Query(context.Background(), insertStmt, alastair).Run()
	if err != nil {
		return
	}

	fmt.Printf("Employee inserted: %+v", alastair)

}

func employeeDB() (*sqlair.DB, error) {
	type Employee struct {
		ID   int    `db:"employee_id"`
		Name string `db:"name"`
		Team string `db:"team_name"`
	}

	sqldb, err := sql.Open("sqlite3", "file:example.db?mode=memory")
	if err != nil {
		return nil, err
	}
	db := sqlair.NewDB(sqldb)

	createStmt, err := sqlair.Prepare(`CREATE TABLE employees (employee_id integer, team_name string, name text);`)
	if err != nil {
		return nil, err
	}

	err = db.Query(context.Background(), createStmt).Run()
	if err != nil {
		return nil, err
	}

	insertStmt, err := sqlair.Prepare(`INSERT INTO employees (*) VALUES ($Employee.*)`, Employee{})
	if err != nil {
		return nil, err
	}

	alastair := Employee{ID: 1, Name: "Alastair", Team: "Juju"}
	alberto := Employee{ID: 2, Name: "Alberto", Team: "OCTO"}

	err = db.Query(context.Background(), insertStmt, alastair).Run()
	if err != nil {
		return nil, err
	}

	err = db.Query(context.Background(), insertStmt, alberto).Run()
	if err != nil {
		return nil, err
	}

	return db, nil
}

Output:

Employee inserted: {ID:1 Name:Alastair Team:Juju}

type S 

type S []any

S is a slice type that, as with other named slice types, can be used with SQLair to pass a slice of input values.

type Statement 

type Statement struct {
	// contains filtered or unexported fields
}

Statement represents a parsed SQLair statement ready to be run on a database. A statement can be used with any DB.

func MustPrepare 

func MustPrepare(query string, typeSamples ...any) *Statement

MustPrepare is the same as Prepare except that it panics on error.

func Prepare 

func Prepare(query string, typeSamples ...any) (*Statement, error)

Prepare takes a query containing SQLair expressions along with samples of all the types referenced in these SQLair expressions. It generates a Statement which can be run on the database.

The type samples passed after the query must contain an instance of every type mentioned in the SQLair expressions in the query. These are used only for type information and can be the zero value of the type.

type TX 

type TX struct {
	// contains filtered or unexported fields
}

TX represents a transaction on the database.

func (*TX) Commit 

func (tx *TX) Commit() error

Commit commits the transaction.

func (*TX) Query 

func (tx *TX) Query(ctx context.Context, s *Statement, inputArgs ...any) *Query

Query builds a new query from a context, a Statement and the input arguments. The query is run on the database when one of Query.Iter, Query.Run, Query.Get or Query.GetAll is executed.

A new Query object should be created every time the statement is run against the transaction. The Query is designed to be used immediately and run once.

func (*TX) Rollback 

func (tx *TX) Rollback() error

Rollback aborts the transaction.

type TXOptions 

type TXOptions struct {
	// Isolation is the transaction isolation level.
	// If zero, the driver or database's default level is used.
	Isolation sql.IsolationLevel
	ReadOnly  bool
}

TXOptions holds the transaction options to be used in DB.Begin.

Source Files

View all Source files

Directories

Path Synopsis
internal
expr
Package expr processes the SQLair query string, generates the SQL, and maps the SQLair query’s input/output arguments to the generated SQL query’s parameters/results.
 Package expr processes the SQLair query string, generates the SQL, and maps the SQLair query’s input/output arguments to the generated SQL query’s parameters/results.
typeinfo
Package typeinfo contains code relating to Go types and their processing in SQLair.
 Package typeinfo contains code relating to Go types and their processing in SQLair.

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.