Using sqlc

First, let’s take a look at the different commands provided by sqlc and how they work.

Commands	Explanation
compile	This command helps check SQL syntax and reports any typing errors.
completion	This command is to generate an auto-completion script for your environment. The following are the supported environments: Bash, Fish, PowerShell, and zsh.
generate	A command to generate the .go files based on the provided SQL statements. This will be the command that we will be using a lot for the application.
init	This command is the first command that is used to initialize your application to start using this tool.

The following will show how to get started with using sqlc to set up a project. Create a directory inside chapter1 – for example, dbtest – and change the directory to the new directory (dbtest). Next, we will run sqlc with the init command:

sqlc init

This will automatically generate a file called sqlc.yaml, which contains a blank configuration as shown here:

version: "1"
project:
    id: ""
packages: []

The sqlc.yaml contains configuration information that sqlc will use to generate all the relevant .go code for our SQL statements.

Let’s take a look at the structure of the .yaml file to understand the different properties. The following shows an example of a completed structure:

version: "1"
packages:
 - name: "db"
   path: "db"
   queries: "./sqlquery"
   schema: "./sqlquery/schema/"
   engine: "postgresql"
   sql_engine: "database/sql"
   emit_db_tags: "true"
   emit_prepared_queries: 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: "snake"
   output_db_file_name: "db.go"
   output_models_file_name: "dbmodels.go"
   output_querier_file_name: "dbquerier.go"
   output_files_suffix: "_gen"

The following table explains the different fields:

Tag Name	Description
Name	Any string to be used as the package name.
Path	Specifies the name of the directory that will host the generated .go code.
Queries	Specifies the directory name containing the SQL queries that sqlc will use to generate the .go code.
Schema	A directory containing SQL files that will be used to generate all the relevant .go files.
Engine	Specifies the database engine that will be used: sqlc supports either MySQL or Postgres.
emit_db_tags	Setting this to true will generate the struct with db tags – for example: type ExerciseTable struct { ExerciseID int64 `db:"exercise_id" ExerciseName string `db:"exercise_name" }
emit_prepared_queries	Setting this to true instructs sqlc to support prepared queries in the generated code.
emit_interface	Setting this to true will instruct sqlc to generate the querier interface.
emit_exact_table_names	Setting this to true will instruct sqlc to mirror the struct name to the table name.
emit_empty_slices	Setting this to true will instruct sqlc to return an empty slice for returning data on many sides of the table.
emit_exported_queries	Setting this to true will instruct sqlc to allow the SQL statement used in the auto-generated code to be accessed by an outside package.
emit_json_tags	Setting this to true will generate the struct with JSON tags.
json_tags_case_style	This setting can accept the following – camel, pascal, snake, and none. The case style is used for the JSON tags used in the struct. Normally, this is used with emit_json_tags.
output_db_file_name	Name used as the filename for the auto-generated database file.
output_models_file_name	Name used as the filename for the auto-generated model file.
output_querier_file_name	Name used as the filename for the auto-generated querier file.
output_files_suffix	Suffix to be used as part of the auto-generated query file.

We have looked at the different parameters available in the tool, along with how to use the .yaml file to specify the different properties used to generate the relevant Go files. In the next section, we will set up our sample app database.