INSERT statements

SQLair has support for type mapping in INSERT statements. For example, you can write such queries as:

INSERT INTO people (*) VALUES ($Person.*)

The new syntax allows you to insert all the fields with a db tag into the database. You simply pass the struct as an argument when you execute the query and SQLair inserts the field values into the relevant columns.

This blog post takes you through the different forms that these new input expressions can take as well as giving an overview of some upcoming enhancements. If you need a refresh on the core features of SQLair the see the SQLair introduction.

INSERT statements in action

Below is a simple example of using an insert statement to insert a struct into a database:

type Person struct {
	ID       int    `db:"id"`
	Name     string `db:"name"`
	Postcode int    `db:"postal_code"`
}

stmt, err := sqlair.Prepare(
	"INSERT INTO person (*) VALUES ($Person.*)",
	Person{},
)
fred := Person{ID: 1, Name: "Fred", Postcode: 1000}
err := db.Query(ctx, stmt, fred).Run()

The Person struct is tagged with the columns that the fields correspond to. These are used to generate the SQL that is sent to the database when the SQLair query is prepared. The query is created on the database with db.Query and then executed with Run. The variable fred which is of the type Person is passed as a parameter to the query. When the query is run it will insert 1 into column the id, "Fred" into the column name, and 1000 into the column postal_code. If you are wondering about the functionality of any of the other SQLair methods see the go package docs and the SQLair introduction.

The different forms of SQLair insert statements

SQLair input expressions within insert statements take a similar form to the SQLair output expressions. The columns are specified in the set of brackets before the "VALUES" keyword and the Go types that they correspond to are specified in the brackets after.

Note that the dollar sign $ is used for inputs expression where as the & sign is used for output expression.

Here are a few different examples:

• INSERT INTO employees (*) VALUES ($Person.*, $Address.* $Manager.manager_id) This will insert all tagged fields from Person and Address as well as the field tagged with manager_id from Manager. Each field is inserted into the columns specified by the db tag.

• INSERT INTO employees (name, id) VALUES ($Person.*) This will insert only the fields tagged with name and id from Person.

• INSERT INTO teams (team_lead, manager_id) VALUES ($Person.id, $Manager.id) This will insert the field in Persontaggedidinto the columnteam_leadand the field inManagertagged withidinto the columnmanager_id`.

Maps can also be used with insert statements. For example if M is a map type (such as sqlair.M) then you could write the statement: INSERT INTO employees (*) AS ($Person.*, $M.update_timestamp) They are useful for passing values that need to be inserted but are not naturally stored in a struct.

Omitting primary keys

It is possible to omit primary keys when they are set to zero by adding the omitempty tag to the db tags in the struct, e.g.

type Person struct {
	ID         int    `db:"id, omitempty"`
	Name       string `db:"name"`
}

If the column ID is inserted using an asterisk input expression and the ID field is zeroed when the value is passed to Query at execution time, then, the ID column will not be inserted into the database.

This makes SQLair easy to use with auto-increment columns and the like.

Bulk Inserts

It is often the case that you want insert a slice of values into the database. In regular SQL it is possible to do a bulk insert e.g. INSERT INTO t (col1, col2) VALUES (1,2), (3,4), (5,6). The problem is you have to explicit spell out exactly how many rows you want to insert in the query itself, and then flatten your slice of arguments to pass to the query. Thankfully, SQLair has a better way.

With SQLair you can create a regular insert statement e.g.

stmt, err := sqlair.Prepare(
	"INSERT INTO person (*) VALUES ($Person.*)",
	Person{},
)

Then, to insert a slice of people instead of a single person, you simple pass the slice to Query and SQLair takes care of the rest, automatically turning it into a bulk insert.

var people []Person
...
err := db.Query(ctx, stmt, people).Run()

Its as simple as that! This means that any SQL insert statement can already be a bulk insert, you simple need to pass it a slice.

Full example

Below is a full, compilable, example of using insert statements with SQLair to add values to a database then verify that they have been correctly added with a select statement:

type Person struct {
	ID       int    `db:"id"`
	Name     string `db:"name"`
	Postcode int    `db:"postal_code"`
}

createPerson := sqlair.MustPrepare(`
	CREATE TABLE person (
		id integer,
		name text,
		postal_code integer
	);`,
)
insertPerson := sqlair.MustPrepare(
	"INSERT INTO person (*) VALUES ($Person.*)",
	Person{},
)
getAllPeople := sqlair.MustPrepare(
	"SELECT * AS &Person.* FROM person",
	Person{},
)
dropTable := sqlair.MustPrepare(
	"DROP TABLE person",
)

sqldb, err := sql.Open("sqlite3", "file:exampleOmitTest.db?cache=shared&mode=memory")
if err != nil {
	panic(err)
}
db := sqlair.NewDB(sqldb)

fred := Person{ID: 1, Name: "Fred", Postcode: 1000}
mark := Person{ID: 2, Name: "Mark", Postcode: 1500}
mary := Person{ID: 3, Name: "Mary", Postcode: 3500}
dave := Person{ID: 4, Name: "Dave", Postcode: 4500}
allPeople := []Person{fred, mark, mary, dave}

err = db.Query(context.Background(), createPerson).Run()
if err != nil {
	panic(err)
}
defer db.Query(context.Background(), dropTable).Run()

for _, p := range allPeople {
	err := db.Query(context.Background(), insertPerson, p).Run()
	if err != nil {
		panic(err)
	}
}

outPeople := []Person{}
err = db.Query(context.Background(), getAllPeople).GetAll(&outPeople)
if err != nil {
	panic(err)
}
fmt.Println(outPeople)

// Output:
// [{1 Fred 1000} {2 Mark 1500} {3 Mary 3500} {4 Dave 4500}]