13/03/2020 - GO, MYSQL
This example separates CRUD operations into two sections which are 'write' and 'read'. The 'write' section covers INSERT, UPDATE and DELETE queries. The 'read' section covers SELECT queries. We are also using context.WithTimeout to terminate long running queries. Pay attention to SELECT queries because they depend on something called MAX_EXECUTION_TIME.
Prefer this if you are not 100% sure that the query arguments are SQL Injection free.
It is a slow operation as it triples the number of network round-trips (Prepare, Execute and Close). The query uses ? argument placeholders.
Both examples below will work in exactly the same way so I suggest using the second one as it is cleaner. Also, whether the query arguments are dynamic or static, it still triples the number of network round-trips.
func Create(args ...interface{}) error {
ctx, cancel := context.WithTimeout(context.Background(), 3 * time.Second)
defer cancel()
stmt, err := db.PrepareContext(ctx, `
INSERT INTO leagues
(uuid, name, address, int_rank, is_active, founded_at, created_at)
VALUES
(?, ?, ?, ?, ?, ?, UTC_TIMESTAMP())
`)
if err != nil {
return err
}
defer stmt.Close()
res, err := stmt.ExecContext(ctx, args...)
if err != nil {
return err
}
tot, err := res.RowsAffected()
if err != nil {
return err
}
if tot != 1 {
return errors.New("no rows were affected")
}
return nil
}
func Create(args ...interface{}) error {
ctx, cancel := context.WithTimeout(context.Background(), 3 * time.Second)
defer cancel()
q := `
INSERT INTO leagues
(uuid, name, address, int_rank, is_active, founded_at, created_at)
VALUES
(?, ?, ?, ?, ?, ?, UTC_TIMESTAMP())
`
res, err := db.ExecContext(ctx, q, args...)
if err != nil {
return err
}
tot, err := res.RowsAffected()
if err != nil {
return err
}
if tot != 1 {
return errors.New("no rows were affected")
}
return nil
}
2020-03-13T13:14:36.035537Z 9 Prepare INSERT INTO leagues
(uuid, name, address, int_rank, is_active, founded_at, created_at)
VALUES
(?, ?, ?, ?, ?, ?, UTC_TIMESTAMP())
2020-03-13T13:14:36.038073Z 9 Execute INSERT INTO leagues
(uuid, name, address, int_rank, is_active, founded_at, created_at)
VALUES
('6a980454-0727-4260-a757-ce619e79af83', 'League 1', 'Address', 2, 0, '2001-01-01', UTC_TIMESTAMP())
2020-03-13T13:14:36.052590Z 9 Close stmt
Prefer this if you are 100% sure that the query arguments are SQL Injection free.
It is a fast operation as it issues a single network round-trip (Query). The query is formatted with fmt.Sprintf function using appropriate argument formatters.
func Create(args ...interface{}) error {
ctx, cancel := context.WithTimeout(context.Background(), 3 * time.Second)
defer cancel()
q := fmt.Sprintf(`
INSERT INTO leagues
(uuid, name, address, int_rank, is_active, founded_at, created_at)
VALUES
('%s', '%s', '%s', %d, %t, '%s', UTC_TIMESTAMP())
`,
args...,
)
res, err := db.ExecContext(ctx, q)
if err != nil {
return err
}
tot, err := res.RowsAffected()
if err != nil {
return err
}
if tot != 1 {
return errors.New("no rows were affected")
}
return nil
}
2020-03-13T13:30:26.812083Z 15 Query INSERT INTO leagues
(uuid, name, address, int_rank, is_active, founded_at, created_at)
VALUES
('2250b0d9-365a-4655-a116-1b092f6d6f20', 'League 1', 'Address', 2, false, '2001-01-01', UTC_TIMESTAMP())
Although we are using context.WithTimeout to cancel long running queries, MySQL won't actually terminate underlying queries because of its protocol nature. Cancellation only takes place at Go level. However, you can solve this issue for SELECT queries by using MySQL's MAX_EXECUTION_TIME query hint in order to tell MySQL server to terminate execution if it runs longer than given time limit. If so, it will produce Error 3024: Query execution was interrupted, maximum statement execution time exceeded error.
If you are not 100% sure that the query arguments are SQL Injection free.
It is a slow operation as it triples the number of network round-trips (Prepare, Execute and Close). The query uses ? argument placeholders.
Whether the query arguments are dynamic or static, it still triples the number of network round-trips.
type League struct {
ID sql.NullInt64
UUID sql.NullString
Name sql.NullString
Address sql.NullString
IntRank sql.NullInt64
IsActive sql.NullBool
FoundedAt mysql.NullTime
CreatedAt mysql.NullTime
DeletedAt mysql.NullTime
}
func Read(uuid string) (League, bool, error) {
ctx, cancel := context.WithTimeout(context.Background(), 3 * time.Second)
defer cancel()
row := db.QueryRowContext(ctx, `
SELECT
/*+ MAX_EXECUTION_TIME(3000) */
id, uuid, name, int_rank, address, is_active, founded_at, created_at, deleted_at
FROM leagues
WHERE
uuid = ?
LIMIT 1`,
uuid,
)
var model League
err := row.Scan(
&model.ID,
&model.UUID,
&model.Name,
&model.IntRank,
&model.Address,
&model.IsActive,
&model.FoundedAt,
&model.CreatedAt,
&model.DeletedAt,
)
switch {
case err == sql.ErrNoRows:
return model, false, nil // 404
case err != nil:
return model, false, err // 500
default:
return model, true, nil // 200
}
}
2020-03-13T16:00:11.624465Z 16 Prepare SELECT
id, uuid, name, int_rank, address, is_active, founded_at, created_at, deleted_at
FROM leagues
WHERE
uuid = ?
LIMIT 1
2020-03-13T16:00:11.643215Z 16 Execute SELECT
id, uuid, name, int_rank, address, is_active, founded_at, created_at, deleted_at
FROM leagues
WHERE
uuid = '2250b0d9-365a-4655-a116-1b092f6d6f20'
LIMIT 1
2020-03-13T16:00:11.674942Z 16 Close stmt
If you are 100% sure that the query arguments are SQL Injection free.
It is a fast operation as it issues a single network round-trip (Query). The query is formatted with fmt.Sprintf function using appropriate argument formatters.
func Read(uuid string) (League, bool, error) {
ctx, cancel := context.WithTimeout(context.Background(), 3 * time.Second)
defer cancel()
q := fmt.Sprintf(`
SELECT
/*+ MAX_EXECUTION_TIME(3000) */
id, uuid, name, int_rank, address, is_active, founded_at, created_at, deleted_at
FROM leagues
WHERE
uuid = '%s'
LIMIT 1
`,
uuid,
)
row := db.QueryRowContext(ctx, q)
var model League
err := row.Scan(
&model.ID,
&model.UUID,
&model.Name,
&model.IntRank,
&model.Address,
&model.IsActive,
&model.FoundedAt,
&model.CreatedAt,
&model.DeletedAt,
)
switch {
case err == sql.ErrNoRows:
return model, false, nil // 404
case err != nil:
return model, false, err // 500
default:
return model, true, nil // 200
}
}
2020-03-13T16:06:49.526538Z 18 Query SELECT
id, uuid, name, int_rank, address, is_active, founded_at, created_at, deleted_at
FROM leagues
WHERE
uuid = '2250b0d9-365a-4655-a116-1b092f6d6f20'
LIMIT 1
If you are not 100% sure that the query arguments are SQL Injection free.
It is a slow operation as it triples the number of network round-trips (Prepare, Execute and Close). The query uses ? argument placeholders.
Whether the query arguments are dynamic or static, it still triples the number of network round-trips.
type League struct {
ID sql.NullInt64
UUID sql.NullString
Name sql.NullString
Address sql.NullString
IntRank sql.NullInt64
IsActive sql.NullBool
FoundedAt mysql.NullTime
CreatedAt mysql.NullTime
DeletedAt mysql.NullTime
}
func Read(limit, offset int) ([]League, error) {
ctx, cancel := context.WithTimeout(context.Background(), 3 * time.Second)
defer cancel()
rows, err := db.QueryContext(ctx, `
SELECT
/*+ MAX_EXECUTION_TIME(3000) */
id, uuid, name, int_rank, address, is_active, founded_at, created_at, deleted_at
FROM leagues
LIMIT ?
OFFSET ?`,
limit,
offset,
)
var models []League
if err != nil {
return models, err // 500
}
defer rows.Close()
for rows.Next() {
var model League
err := rows.Scan(
&model.ID,
&model.UUID,
&model.Name,
&model.IntRank,
&model.Address,
&model.IsActive,
&model.FoundedAt,
&model.CreatedAt,
&model.DeletedAt,
)
if err != nil {
return models, err // 500
}
models = append(models, model)
}
if err = rows.Err(); err != nil {
return models, err // 500
}
return models, nil
}
2020-03-13T16:35:51.216389Z 22 Prepare SELECT
id, uuid, name, int_rank, address, is_active, founded_at, created_at, deleted_at
FROM leagues
LIMIT ?
OFFSET ?
2020-03-13T16:35:51.223332Z 22 Execute SELECT
id, uuid, name, int_rank, address, is_active, founded_at, created_at, deleted_at
FROM leagues
LIMIT 10
OFFSET 0
2020-03-13T16:35:51.245423Z 22 Close stmt
If you are 100% sure that the query arguments are SQL Injection free.
It is a fast operation as it issues a single network round-trip (Query). The query is formatted with fmt.Sprintf function using appropriate argument formatters.
func Read(limit, offset int) ([]League, error) {
ctx, cancel := context.WithTimeout(context.Background(), 3 * time.Second)
defer cancel()
q := fmt.Sprintf(`
SELECT
/*+ MAX_EXECUTION_TIME(3000) */
id, uuid, name, int_rank, address, is_active, founded_at, created_at, deleted_at
FROM leagues
LIMIT %d
OFFSET %d
`,
limit,
offset,
)
rows, err := db.QueryContext(ctx, q)
var models []League
if err != nil {
return models, err // 500
}
defer rows.Close()
for rows.Next() {
var model League
err := rows.Scan(
&model.ID,
&model.UUID,
&model.Name,
&model.IntRank,
&model.Address,
&model.IsActive,
&model.FoundedAt,
&model.CreatedAt,
&model.DeletedAt,
)
if err != nil {
return models, err // 500
}
models = append(models, model)
}
if err = rows.Err(); err != nil {
return models, err // 500
}
return models, nil
}
2020-03-13T16:39:42.933817Z 23 Query SELECT
id, uuid, name, int_rank, address, is_active, founded_at, created_at, deleted_at
FROM leagues
LIMIT 10
OFFSET 0