Generating CRUD with sqlc
CRUD stands for Create, Read, Update, and Delete, which refers to all the major functions that are inherent to relational databases. In this section, we will do the following for the application:
- Complete the sqlc configuration file
- Create SQL query files
Once done, we will be able to autogenerate the different files required to allow us to perform CRUD operations to the database from the application. First, open sqlc.yaml and enter the following configuration:
--- version: '1' packages: - name: chapter1 path: gen schema: db/ queries: queries/ engine: postgresql emit_db_tags: true emit_interface: false emit_exact_table_names: false emit_empty_slices: false emit_exported_queries: false emit_json_tags: true json_tags_case_style: camel output_files_suffix: _gen emit_prepared_queries: false
Our application is now complete with all that we need for the database, and sqlc will autogenerate the .go files. The following is how the application directory and files will look:
. ├── db │ └── schema.sql ├── go.mod ├── queries │ └── query.sql └── sqlc.yaml
We can run sqlc to generate the .go files using the following command:
sqlc generate
By default, sqlc will look for the sqlc.yaml file. If the filename is different, you can specify it using the -f flag as follows:
sqlc generate -f sqlc.yaml
Once the operation completes, there will be no output; however, a new directory called gen will be generated as shown here:
./gen/ ├── db.go ├── models.go └── query.sql_gen.go
We have completed the auto-generation process using sqlc; now, let’s take a look at the schema and queries that sqlc uses to generate the code.
The following is a snippet of the schema.sql file that is used by sqlc to understand the structure of the database:
CREATE SCHEMA IF NOT EXISTS gowebapp; CREATE TABLE gowebapp.users ( User_ID BIGSERIAL PRIMARY KEY, User_Name text NOT NULL, .... ); .... CREATE TABLE gowebapp.sets ( Set_ID BIGSERIAL PRIMARY KEY, Exercise_ID BIGINT NOT NULL, Weight INT NOT NULL DEFAULT 0 );
The other file sqlc uses is the query file. The query file contains all the relevant queries that will perform CRUD operations based on the database structure given here. The following is a snippet of the query.sql file:
-- name: ListUsers :many -- get all users ordered by the username SELECT * FROM gowebapp.users ORDER BY user_name; ... -- name: DeleteUserImage :exec -- delete a particular user's image DELETE FROM gowebapp.images i WHERE i.user_id = $1; ... -- name: UpsertExercise :one -- insert or update exercise of a particular id INSERT INTO gowebapp.exercises (Exercise_Name) VALUES ($1) ON CONFLICT (Exercise_ID) DO UPDATE SET Exercise_Name = EXCLUDED.Exercise_Name RETURNING Exercise_ID; -- name: CreateUserImage :one -- insert a new image INSERT INTO gowebapp.images (User_ID, Content_Type, Image_Data) values ($1, $2, $3) RETURNING *; ...
Using query.sql and schema.sql, sqlc will automatically generate all the relevant .go files, combining information for these two files together and allowing the application to perform CRUD operations to the database by accessing it like a normal struct object in Go.
The last piece that we want to take a look at is the generated Go files. As shown previously, there are three auto-generated files inside the gen folders: db.go, models.go, and query.sql_gen.go.
Let’s take a look at each one of them to understand what they contain and how they will be used in our application:
db.go:
This file contains an interface that will be used by the other auto-generated files to make SQL calls to the database. It also contains functions to create a Go struct that is used to do CRUD operations.
A new function is used to create a query struct, passing in a DBTX struct. The DBTX struct implementation is either sql.DB or sql.Conn.
The WithTx function is used to wrap the Queries object in a database transaction; this is useful in situations where there could be an update operation on multiple tables that need to be committed in a single database transaction:
func New(db DBTX) *Queries {
return &Queries{db: db}
}
func (q *Queries) WithTx(tx *sql.Tx) *Queries {
return &Queries{
db: tx,
}
}
models.go:
This file contains the struct of the tables in the database:
type GowebappExercise struct {
ExerciseID int64 `db:"exercise_id"
json:"exerciseID"`
ExerciseName string `db:"exercise_name"
json:"exerciseName"`
}
...
type GowebappWorkout struct {
WorkoutID int64 `db:"workout_id"
json:"workoutID"`
UserID int64 `db:"user_id" json:"userID"`
SetID int64 `db:"set_id" json:"setID"`
StartDate time.Time `db:"start_date"
json:"startDate"`
}
query.sql_gen.go:
This file contains CRUD functions for the database, along with the different parameters struct that can be used to perform the operation:
const deleteUsers = `-- name: DeleteUsers :exec
DELETE FROM gowebapp.users
WHERE user_id = $1
`
func (q *Queries) DeleteUsers(ctx context.Context,
userID int64) error {
_, err := q.db.ExecContext(ctx, deleteUsers, userID)
return err
}
...
const getUsers = `-- name: GetUsers :one
SELECT user_id, user_name, pass_word_hash, name, config, created_at, is_enabled FROM gowebapp.users
WHERE user_id = $1 LIMIT 1
`
func (q *Queries) GetUsers(ctx context.Context, userID int64) (GowebappUser, error) {
row := q.db.QueryRowContext(ctx, getUsers, userID)
var i GowebappUser
err := row.Scan(
&i.UserID,
&i.UserName,
&i.PassWordHash,
&i.Name,
&i.Config,
&i.CreatedAt,
&i.IsEnabled,
)
return i, err
}
...
Now that the database and auto-generated data to perform CRUD operations are complete, let’s try all this by doing a simple insert operation into the user table.
The following is a snippet of main.go:
package main
import (
...
)
func main() {
...
// Open the database
db, err := sql.Open("postgres", dbURI)
if err != nil {
panic(err)
}
// Connectivity check
if err := db.Ping(); err != nil {
log.Fatalln("Error from database ping:", err)
}
// Create the store
st := chapter1.New(db)
st.CreateUsers(context.Background(),
chapter1.CreateUsersParams{
UserName: "testuser",
PassWordHash: "hash",
Name: "test",
})
}
The app is doing the following:
- Initializing the URL and opening the database
- Pinging the database
- Creating a new user using the
CreateUsers(..)function
Make sure you are in the chapter1 directory and build the application by running the following command:
go build -o chapter1
The compiler will generate a new executable called chapter1. Execute the file, and on a successful run, you will see the data inserted successfully into the users table:
2022/05/15 16:10:49 Done! Name : test, ID : 1
We have completed setting up everything from the database and using sqlc to generate the relevant Go code. In the next section, we are going to put everything together for ease of development.