Welcome back to the backend master class! In the last lecture, we have learned how to write migration script to create the database schema for our simple
bank project. Today we will learn how to write golang codes to perform CRUD operations on the database. OK so what is CRUD? Well, they are 4 basic operations: C stands for Create, or insert new records to the database. R is for Read, which means retrieving the records from the
database. U is Update, to change the content of the records in the
database And D is delete, to remove the records from the database. There are several ways to implement CRUD operations
in golang. The 1st one is to use the low-level standard
library database/sql package. As you can see in this example, We just use the QueryRowContext() function, And pass in the raw SQL query and some parameters. Then we scan the result into target variables. The main advantage of this approach is it runs very fast, And writing codes is pretty straightforward. However, its downside is we have to manually map the SQL fields to
variables, which is quite boring and easy to make mistakes. If somehow the order of variables doesn’t
match, or if we forget to pass some arguments to
the function call, the errors will only show up at runtime. Another approach is to use Gorm, which is a high-level object-relational-mapping
library for golang. It is super convenient to use because all CRUD operations are already implemented. So our production code will be very short, as we only need to declare the models and call the functions that gorm provided. As you can see in these example codes, We have the NewRecord and Create function
for record creation And several functions for retrieving the data, Such as First, Take, Last, Find. It looks pretty cool, but the problem is: we must learn how to write queries using gorm’s
provided functions. It will be annoying if we don’t know which
functions to use. Especially when we have some complex queries
that require joining tables, We have to learn how to declare associations
tags to make gorm understand the relationships
between tables, So that it can generate the correct SQL query. For me, I prefer writing the SQL query myself, It’s more flexible, and I have a complete control of what I want
the database to do. One major concern when using gorm is that It runs very slowly when the traffic is high. There are some benchmarks on the internet which shows that gorm can runs 3-5 times slower than the standard
library. Because of this, many people are switching to a middle-way
approach, which is using sqlx library. It runs nearly as fast as the standard library. And it’s very easy to use. The fields mapping are done via either the
query text or struct tags. It provides some functions like Select() or
StructScan(), which will automatically scan the result into
the struct fields, so we don’t have to do the mapping manually like in the database/sql package. This will help to shorten the codes, and reduce potential mistakes. However, the code that we have to write is
still quite long, And any errors in the query will only be caught
at runtime. So is there any better way? The answer is sqlc! It runs very fast, just like database/sql And it’s super easy to use, The most exciting thing is, we just need to write SQL queries, then golang CRUD codes will be automatically
generated for us. As you can see in this example, We simply pass the db schema and SQL queries
to sqlc, Each query have 1 comment on top of it to tell sqlc to generate the correct function
signature. Then sqlc will generate idiomatic Golang codes, which uses the standard database/sql library. And becauses sqlc parses the SQL queries to
understand what it does in order to generate the codes for us, so any errors will be caught and report right
away. Sounds amazing, right? The only issue that I found in sqlc is that, At the moment, It only fully supports Postgres. MySQL is still experimental. So if you’re using Postgres in your project, I think sqlc is the right tool to use. Otherwise, I would suggest to stick with sqlx. Alright, Now I’m gonna show you how to install and
use sqlc to generate CRUD codes for our simple bank
project. First we go to sqlc.dev And click on this link to open its github
page Then search for installation I’m on a mac, so I will use Homebrew. Let’s copy this brew install command And run it in the terminal. OK, sqlc is now installed. We can run sqlc version to see what version
it is running. In my case, it’s version 1.4.0 Let’s run sqlc help to learn how to use
it. First we have the compile command to check
the SQL syntax and type errors. Then the most important command is generate. It will do both error checking and generating golang codes from SQL queries
for us. We also have the init command to create an empty slqc.yaml settings file. Now I’m gonna go to the simple bank project
folder that we’ve been working on in previous lectures. Run sqlc init And open it with visual studio code. OK, here we can see the sqlc.yaml file. Right now, it’s kind of empty. So let’s go back to the sqlc github page Select branch with tag v1.4.0 And search for settings. Here’s the list of settings that we can
config. Let’s copy it, And paste it to the sqlc.yaml file. We can tell sqlc to generate multiple Go packages. But to be simple, I’m just gonna use 1 single
package for now. The “name” option here is to tell sqlc what is the name of the Go package that will
be generated. I think “db” is a good package name. Next, we have to specify the path to the folder to store the generated golang code files. I’m gonna create a new folder “sqlc”
inside the db folder And change this string to “./db/sqlc” Then we have the “queries” option to tell
sqlc where to look for the SQL query files. Let’s create a new folder “query” inside
the “db” folder. Then change this value to “./db/query”. Similarly, this schema option should point
to the folder containing the database schema or migration
files. In our case, it is “./db/migration”. The next option is “engine” to tell sqlc what database engine we would like to use. We’re using postgresql for our simple bank
project, If you want to experiment with mysql, You can change this value to “mysql” instead. Here we set the emit_json_tags to “true” because we want sqlc to add JSON tags to the
generated structs. The emit_prepared_queries tells sqlc to generate codes that work with prepared
statement. At the moment, we don’t need to optimize
performance yet, So let’s set this to false to make it simple. Then the emit_interface option to tell sqlc to generate Querier interface for the generated
package. It might be useful later if we want to mock the db for testing higher-level functions. For now let’s just set it to false. The final option is emit_exact_table_names. By default, this value is false, Sqlc will try to singularize the table name
to use as the model struct name. For example “accounts” table will become
“Account” struct. If you set this option to true, the struct
name will be “Accounts” instead. I think singular name is better because 1 object of type “Accounts” in
plural form might be confused as multiple objects. OK now let’s open the terminal And run sqlc generate. We have an error because there are no queries
in the query folder yet. We will write the queries in a moment. For now, let’s add a new sqlc command to
the make file. It will help our team mates to easily find
all commands that can be used for development in one place. OK it’s working. So now let’s write our first SQL query to
CREATE an account. I’m gonna create a new account.sql file
inside the query folder. Then go back to the sqlc github page And search for getting started. Here we see a few examples of how the SQL
query should look like. Let’s copy this CreateAuthor command And paste it to our account.sql file. It’s just a basic INSERT query. The only special thing is the comment on top
of it. This comment will instruct sqlc how to generate the Golang function signature
for this query. In our case, the name of the function will be CreateAccount. And it should return 1 single Account object, So we have the “:one” label here. Then the SQL query is INSERT INTO accounts, Let’s take a look at the schema, We don’t need to provide the id because it’s an auto increment column. Everytime a new record is inserted, the db will automatically increase the account
id sequence number, and use it as the value of the id column. The “created_at” column will also be automatically
filled with the default value, which is the time when the record is created. So, we only need to provide values for the
owner, balance, and currency. There are 3 columns, so we need to pass 3 arguments into the VALUES
clause. Finally the RETURNING * clause is used to tell postgresql to return the value of
all columns after inserting the record into accounts table, This is very important, because after the account is created, we will always want to return its ID to the
client. Alright, now let’s open the terminal And run make sqlc. No errors! Now get back to visual studio code. In the db/sqlc folder, we can see 3 new generated files. The 1st one is models.go This file contains the struct definition of
3 models: Account, Entry and Transfer. They all have JSON tags because we’re setting emit_json_tags to
true in sqlc.yaml The Amount field of Entry and Transfer struct
also has a comment on top because we added them in the database schema
definition in previous lecture. The 2nd file is db.go This file contains the dbtx interface. It defines 4 common methods that both sql.DB
and sql.Tx object has. This allows us to freely use either a db or
a transaction to execute a query. As you can see here, the New() function takes a DBTX as input and
returns a Queries object. so we can pass in either a sql.DB or sql.Tx
object depends on whether we want to execute just
1 single query, or a set of multiple queries within a transaction. There’s also a method WithTx, which allows a Queries instance to be associated
with a transaction. We will learn more about this in another lecture
about transaction. Now let’s take a look at the account.sql.go
file The package name is “db” as we defined
in the sqlc.yaml file. At the top, we can see the create account
SQL query. It looks almost the same as the one we’ve
written in the account.sql file Except for the RETURN clause, Sqlc has replaced RETURN * with the name of
all columns explicitly. This makes the query clearer, and avoid scanning values in incorrect order. Then we have the CreateAccountParams struct, which contains all columns that we want to
set when we create a new account. The CreateAccount function is defined as a
method of the Queries object. It has this name because we have instructed
sqlc with the comment in our SQL query. This function takes a context and a CreateAccountParams
object as input And it returns an Account model object or
an error. Visual studio code is showing some red lines
here because we haven’t initialize the module
for our project yet. Let’s open the terminal And run go mod init The module name is github.com/techschool/simplebank. OK, the go.mod file is generated for us Let’s run go mod tidy to install any dependencies Looks like our current codes don’t use any
external library yet. Alright, now get back to the account.sql.go
file All the red lines are gone. In the CreateAccount function, we call QueryRowContext to execute the create
account SQL query, This function belongs to the DBTX interface
that we’ve seen before. We pass in the context, the query, and 3 arguments: owner, balance, and currency The function returns a row object that we can use to scan the value of each
column into correct variables. This is the basic code that we often have
to write manually if we use the standard database/sql library. But how cool it is to have it automatically
generated for us. Awesome, right? One more amazing thing about sqlc is: it checks the SQL query syntax before generating
the codes. So here if I try to remove the 3rd argument
in the query, and run make sqlc again, An error is reported: INSERT has more target columns than expressions. Because of this, if sqlc successfully generates
the codes, we can be confident that there’s no silly
mistake in our SQL queries. One important thing when working with sqlc
is We should not modify the content of the generated
files, Because everytime we run make sqlc, all of those files will be regenerated, and our changes will be lost. So make sure to create new files if you want to add more codes to the db package. Alright, now we know how to create records
in the database. Let’s move to the next operation: READ. In this example, There are 2 basic data retrieval queries:
get and list. Let’s copy them to our account.sql file. The get query is used to get 1 account record
by id. So I’m gonna change this name to GetAccount. And the query will be SELECT FROM accounts WHERE id equals to the 1st input argument. We use LIMIT 1 here because we just want to
select 1 single record. The next operation is ListAccounts. It will return multiple accounts records, So we use the :many label here. Similar to the get query, we select from accounts table. then order the records by their IDs. Because there can be a lot of accounts in
the database, we should not select all of them at once. Instead, we will do pagination. So we use LIMIT to set the number of rows
we want to get, And use OFFSET to tell postgres to skip this many rows before starting to
return the result. And that’s it! Now let’s run make sqlc to regenerate the
codes. And open the account.sql.go file. Here we go, The GetAccount and ListAccounts functions
are generated. Just like before, sqlc has replace SELECT * with explicit column
names for us. The GetAccount function just take a context
and an ID as input And inside, it just calls QueryRowContext with the raw SQL query and the account id. It scans the row into an account object And return it to the caller. Pretty simple! The ListAccounts function is a little bit
more complex It takes a context, a limit and offset parameters
as input and returns a list of Account objects. Inside, it calls QueryContext, pass in the list accounts query together with the limit and offset. This function returns a rows object. It works like an iterator, which allows us to run through the records
one by one, and scan each record into an account object, append it to the list of items, and finally close the rows to avoid leaking
db connection. We also check if there are any errors or not before returning the items to the caller. The codes look quite long, but easy to understand, The bottom line is: it runs very fast! And we don’t have to worry about making
silly mistakes in the code because sqlc already guarantees that the generated codes will work perfectly. OK, let’s move to the next operation: UPDATE Here it is. Let’s copy this code to our account.sql
file And change the function name to UpdateAccount Here we use a new label :exec because this command doesn’t return any
data, it just updates 1 row in the database. Let’s change this table name to accounts. And let’s say we only allow updating the
account balance, The account owner and currency should not
be changed. We use the WHERE clause to specify the id
of the account we want to update. And that’s it! Now run make sqlc in the terminal to regenerate
the codes. Voila, we have the UpdateAccount function. It takes a context, the account id and balance
parameters as input. All it does is calling ExecContext with the
query and input arguments then return the error to the caller. Sometimes, it is useful to have the updated
account object being returned as well In that case, we can change this :exec label
to :one and add RETURNING * at the end of this update
query. Then regenerate the code. Now the SQL query has changed, And the UpdateAccount function is returning the updated Account together with the error Cool! The last operation is DELETE. It’s even simpler than update. Let’s copy this example query And change the function name to DeleteAccount. I don’t want to postgres to return the deleted
record So let’s keep this :exec label. Then change the table name to accounts And regenerate the code. Oops, we have an error. Oh that’s because I mistyped the table name. It should be accounts with an s. Now make sqlc again Successful! And we have the DeleteAccount function in
the code. So basically we have learned how to generate a full CRUD operations for
our accounts table. You can try to do the same thing for the 2
remaining tables: entries and transfers by yourself as an exercise. I will push the code to github and put the
link in the description so that you can have a reference in case you want to take a look. And that’s it for today’s lecture. Thanks a lot for watching! See you guys in the next one!
