Inserts, Updates, Upserts & Deletes

After 0.1 release, the builder is decentralized into separate query builders such as Insert, Update, Upsert, Delete and Select. This change allows more strict restrictions to build queries. It also prevents errors while building queries.

Suppose you have the following table defined;

Go

users := qb.Table("user",
		qb.Column("id", qb.Varchar().Size(36)),
		qb.Column("email", qb.Varchar().Unique().NotNull()),
		qb.PrimaryKey("id"),
	)

Insert

There are 2 ways to build insert queries in expression api;

Go

ins := qb.Insert(users).Values(map[string]interface{}{
		"id":    "3af82cdc-4d21-473b-a175-cbc3f9119eda",
		"email": "[email protected]",
	}).Build(db.Dialect())

fmt.Println(ins.SQL())
fmt.Println(ins.Bindings())

In this instance, the following output would be printed;

SQL

INSERT INTO user(id, email)
VALUES($1, $2);
[3af82cdc-4d21-473b-a175-cbc3f9119eda [email protected]]

Another option would be to generate the insert statement using table object;

Go

ins := users.Insert().Values(map[string]interface{}{
		"id":    "3af82cdc-4d21-473b-a175-cbc3f9119eda",
		"email": "[email protected]",
	}).Build(db.Dialect())

fmt.Println(ins.SQL())
fmt.Println(ins.Bindings())

🚧

Not recommended

Although it is possible to build insert statements using qb.Insert() function, it is not recommended to use because the table based users.Insert().Values() clause is much simpler.

The output would be the same as the previous example above.

Update

The update syntax is very similar to insert.

Go

upd := users.
		Update().
		Values(map[string]interface{}{
			"email": "[email protected]",
		}).
		Where(users.C("id").Eq("3af82cdc-4d21-473b-a175-cbc3f9119eda")).
		Build(db.Dialect())

fmt.Println(upd.SQL())
fmt.Println(upd.Bindings())

📘

Recommended

Building queries using table functions is highly recommended.

The output would be the following;

SQL

UPDATE user
SET email = $1
WHERE user.id = $2;
[[email protected] 3af82cdc-4d21-473b-a175-cbc3f9119eda]

As you might notice in this example, there exists a where clause using table's C() function. This functions is the column selector function of table struct.

Upsert

The upsert statement generates dialect specific sql statement for each driver. The following example shows a simple Upsert statement in postgres.

Go

ups := users.
		Upsert().
		Values(map[string]interface{}{
			"id":    "3af82cdc-4d21-473b-a175-cbc3f9119eda",
			"email": "[email protected]",
		}).
		Build(db.Dialect())

fmt.Println(ups.SQL())
fmt.Println(ups.Bindings())

The output would be following in postgres driver;

SQL

INSERT INTO user(id, email)
VALUES($1, $2)
ON CONFLICT (id) DO UPDATE SET id = $3, email = $4;
[3af82cdc-4d21-473b-a175-cbc3f9119eda [email protected] 3af82cdc-4d21-473b-a175-cbc3f9119eda [email protected]]

❗️

The upsert statement requires to have primary key field for postgres.

If no primary key is defined for the table, it'll produce meaningless results.

In sqlite, the example above would output the following;

SQL

REPLACE INTO user(id, email)
VALUES(?, ?);
[3af82cdc-4d21-473b-a175-cbc3f9119eda [email protected]]

Lastly in mysql, the output would be the following;

SQL

INSERT INTO user(id, email)
VALUES(?, ?)
ON DUPLICATE KEY UPDATE id = ?, email = ?;
[3af82cdc-4d21-473b-a175-cbc3f9119eda [email protected] 3af82cdc-4d21-473b-a175-cbc3f9119eda [email protected]]

Delete

Lastly delete statements can be built by the following;

Go

del := users.
		Delete().
		Where(users.C("id").Eq("3af82cdc-4d21-473b-a175-cbc3f9119eda")).
		Build(db.Dialect())

fmt.Println(del.SQL())
fmt.Println(del.Bindings())

The output would be the following;

SQL

DELETE FROM user
WHERE (user.id = ?);
[3af82cdc-4d21-473b-a175-cbc3f9119eda]