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:
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,
}
}
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"`
}
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.
Argentina
Australia
Austria
Belgium
Brazil
Bulgaria
Canada
Chile
Colombia
Cyprus
Czechia
Denmark
Ecuador
Egypt
Estonia
Finland
France
Germany
Great Britain
Greece
Hungary
India
Indonesia
Ireland
Italy
Japan
Latvia
Lithuania
Luxembourg
Malaysia
Malta
Mexico
Netherlands
New Zealand
Norway
Philippines
Poland
Portugal
Romania
Russia
Singapore
Slovakia
Slovenia
South Africa
South Korea
Spain
Sweden
Switzerland
Taiwan
Thailand
Turkey
Ukraine
United States
![Pentesting Web Applications: Testing real time web apps [Video]](https://content.packt.com/V07343/cover_image_large.png)