Introduction to SQL and Declarative Programming
The video transitions from database migrations to interacting with the database using SQL. SQL is introduced as a declarative language where developers specify what they want, and the database determines how to execute it.
Defining SQL Queries with SQLC
SQL queries are defined in .sql files with named annotations that instruct SQLC how to generate corresponding functions. Queries include fetching a single article by ID, retrieving multiple articles, and inserting a new article with a returned record.
Parameterized Queries and Security
Placeholders such as $1 are used for parameterized queries, which help prevent SQL injection. The placeholder syntax varies depending on the database system being used.
Insert Operations with Automatic Timestamps
The insert query specifies all required columns and uses database functions to generate timestamps automatically. The RETURNING * clause ensures the newly created record is returned after insertion.
Manual SQL Handling vs SQLC Automation
Without SQLC, developers would need to manually write SQL execution logic, map results to structs, and handle errors. This process is error-prone and introduces significant boilerplate code.
Code Generation with SQLC
Running SQLC generates Go code based on the defined SQL queries and configuration. It produces typed models, parameter structs, query methods, and a database interface to simplify and standardize database access.
Generated Database Interface and Usage
SQLC generates an interface with methods such as Exec, Query, and QueryRow, along with support for transactions. A Queries struct provides access to all generated query methods through a constructor function.
All right, so up until now, we have mainly focused on getting set up with migrations and building our data structure. Now it's actually time to talk to the database. And for that, we're going to be needing SQL. If you have never written any raw SQL before, don't worry. It's simply a way to describe what we want. And then the database figures out how to get as far as this is also what is known as declarative programming simply means
we say what we want, not how we want it. Let's start by creating our first query, and to do that, we're gonna go into database, queries, and here we're gonna create a file called articles.sql, and then we're gonna create our first query here. We're gonna say query, article, by ID. One, and we're gonna say select you from, or select star from articles, where ID equals to dollar sign,
one. So here we specify a name and that we want to get one record back. And this is purely for SQLC. So it will generate a function with this name, and it will know to only return one item. Next, we use this dollar sign one, which is a placeholder that SQLC will replace later with the actual ID that we passed to the function. It's
parameterized, which means that it's safe from SQL injection. And this syntax, this dollar sign here will change depending on the database you are using. So we're using something like SQL light. This will have been a question mark instead of a dollar sign. Next up, we need a way to get all the articles in the database. So we're going to create another query here. I'm going to call it query articles, specify many, and we're simply going to select
star from articles and no more. Here we are specifying that we want to have many records back instead of only one record back, so that SQLC knows that we have to return a slice from this function. And finally, we need a way to insert or create articles in our database. So we're gonna create another query here called insert article. We want to have one record back, the one we just created, and then we're gonna say insert,
into articles, articles. We want to insert into the ID, the created add, the updated add, the title, the excerpt, and finally the content column. And we want to have the values be argument one here. And now I'm going to pass something here, which is a function that makes it so that the database will actually insert the timestamp for us into the created add.
and also they updated that column. Then we also need to provide an argument for the title, for the excerpt, and finally for the contents. And we want to save a turning star, so we get the newly created record back from the statements. Okay, so I quickly want to show you what we would have to do if we did not use SQL C.
And this would be something like what you see here. We will create a function. We will still create our SQL statement. And then we would have to manually map the values returned from that statement into a struct that we define and handle all of the errors. Now, this can get error prone and messy quite quickly. There's a lot of boilerplates, so there's a lot of areas or places where we can make mistakes.
And by using SQL C, we get all of this taken care of, taken care of as far as, so we only need to focus on writing good SQL statements. Now, with our SQL statements written, we can just use the just command, say just generate queries. And SQL C will go in and look in the input folder that we specified in the config, find all the SQL files.
generate all the go code and then output in the output path that we also specified in the config. So if you go into models and go into internal DB, we can see we have three files here, all generated by SQL C. And if you check the one for articles, you can see we have the insert. It generated some params for us. It also generated our version of the article which maps to whatever is in the database.
We also have to query by ID or query all, and it handles all of the mapping and the error handling for us. It also generated this db.go file that contains an interface, which is basically all the methods for talking to the database. So we have exec, query, query row. It also lets us work with our normal connection or with a transaction if you want to perform multiple changes that has to all work or fail.
And then we also have a queries struct on which all of the method generated live. So we can simply use this new function here to create a new instance of this struct. And then we have access to all of the methods that SQLC has generated for us.
