In this lesson, we're going to take a look at one of my favorite packages when it comes to Go: SQLC. This package will allow us to change our kind of raw SQL queries, such as what I'm doing here on line 72, into a type-safe construct, where we can use the repository design pattern to wrap our SQL queries. This gives us the benefit of allowing us to make use of Go's fantastic type system in order to be able to define both the input values and the output values as well.

However, rather than writing these repositories ourselves by hand, we're going to use SQLC in order to generate them, which means all we need to do is write these SQL queries and SQLC will generate us the code we need to conform to the repository design pattern.

I've already done a really comprehensive video on SQLC on my YouTube channel, which I'll link to in the description down below. This may feel like a bit of a cop-out, but that video was two weeks worth of research and planning, whereas this one is a lot shorter. So if you really want a full understanding of how SQLC works and the power you can use with it, I definitely recommend checking out that video.

For this one, however, I'm going to show you how we can get it working quite simply using SQLite, and adding it in to replace the following query that I have here. In fact, replacing all of the code from 72 to 87. In this case, what we're going to do is create a simple query to be able to list all of the posts from a SQLite database.

Installing SQLC

In order to begin, let's take a look at the SQLC documentation, which I'll leave a link to in the description down below. Here we can see there's a tutorial for getting started with SQLite, which is what we want to do when it comes to our own code. This tutorial assumes you have the latest version of SQLC installed and ready to use, so we're going to need to install it on our system.

On macOS you can do so using the following brew install command, let's make this a little bigger, ubuntu you can do so using snap, or you can just install it using go. Let's go ahead and install it on our system using the go toolchain, as this is going to be most consistent for all of our operating systems.

go install github.com/sqlc-dev/sqlc/cmd/sqlc@latest

As you can see, this will now go ahead and download SQLC and fetch and install all of the dependencies, as well as actually building it on our system. Okay, it should now be installed. It took a little bit of time for me because my installation is not that great. I'm actually upgrading my main computer at the moment, and this one for some reason doesn't seem to work very well when it comes to CGO.

However, this should now be working and you can see we have the SQLC command, which if you go ahead and type sqlc version should produce 1.28.0 or higher if you're watching this course in the future.

Setting Up SQLC Configuration

Okay, with the CGO package installed on our system, we can now go about actually setting it up to work with SQLite inside of our code. To do so, you can see here they're defining a new go module. I've already gone ahead and done that, or I'm using the existing go module I looked at when we added in migrations. I recommend doing this for your CMD or CMS application that we defined at the beginning of this module.

In any case, as you can see, SQLC looks for either a sqlc.yaml or sqlc.yml. I personally prefer YAML just because it makes sense to me. YML for some reason always feels a little short or sqlc.json. We're going to use YAML in our case.

As you can see here, this is the YAML that we want to go ahead and actually use. So let's go ahead and create a new SQLC file, sqlc.yaml. And we can go ahead and use it as follows:

version: 2
sql:
  - engine: sqlite
    queries: database/queries
    schema: database/migrations
    gen:
      go:
        package: repository
        out: repository

In this case, we're going to make a couple of changes. The first of which is actually going to be to the schema.sql. In order for SQLC to work, it needs to know the schema of our database so that it can generate the necessary types as we've defined inside of our schema. In this case, you can either define the schema in a single SQL file as it's defined here or we can make use of our migrations directory. So all we need to do is do database/migrations and then it will pull all of the SQL files out of this migrations directory.

Conversely, this is also going to be the same when it comes to queries. We can either define these queries inside of a single SQL file or we can go ahead and define them inside of a directory. So database/queries. This is what I like to do when it comes to working with SQLC as I feel like it's a very scalable solution.

Next, we then need to define where we want to generate this code to. In my case, I like to define the package as a repository. This for me just makes a lot more sense because I like to think of this as a data repository, which we can use. And then to actually define the output or the file or directory where we want to output, you can either do repository if you want to have it in kind of the base of this directory. Or in my case, I like to actually specify this as internal/repository, usually when it comes to larger packages, just to keep all of the code separated. However, for this lesson, I'm just going to go ahead and set this to be repository.

Creating Queries

Okay, with the SQLC schema defined, we're now ready to make use of it, which we can do by using the sqlc generate command. However, as you can see, we don't have a directory defined inside of the database/queries directory. So we can go ahead and do this by using a mkdir database/queries. And if we go ahead and run this, it should now work. Although it's telling us this time we have no queries contained within our database queries definition.

Okay, so what we need to do is actually go ahead and define our queries inside of this directory. If we take a look at the main.go file, here I have my query already defined for selecting all of the posts or all of the yeah, for selecting all of the details from the posts table. So let's go ahead and actually yank this, and we can define a new query inside of our database/queries directory, which has this query inside.

In my case, I like to group these files by the resource that I'm pulling from. So the resource type from the database. So let's go ahead and define a new SQL file called posts.sql, which is the data source that we're going to pull from. Then we can go ahead and just paste in this query. However, one of the nice things about SQLC, which I mentioned in my YouTube video, is we don't have to specify the columns that we want to pull out. Instead, we can go ahead and actually just specify * and SQLC will go ahead and smartly create the query or the create the type for us to be able to do this. It's a very, very cool feature.

SELECT * FROM posts;

Okay, with our query defined, the next thing we need to do is to go ahead and annotate this query using a comment. Here, we're going to specify what the name of this query should be when it comes to the generated function, as well as the type of query that it is. We can actually see these annotations here. So you can see some examples. In this case, this is name: GetAuthor and it returns one value. In this case, it's the name: ListAuthors. So it returns many values, etc, etc.

In my case, I'm going to go ahead and set this to be a name: ListPosts, let's say. And then we can go ahead and define the type of this as :many, because it's going to return many values.

-- name: ListPosts :many
SELECT * FROM posts;

These :many, :one and :exec names or :many, :one and :exec types relate to kind of the same queries that we saw before. If I go ahead and create a new tab and open this up in the main.go, you can see here we're using query. This is basically related to :many. If we were using db.exec, this would relate to :exec. And if we were using db.query_row, this would relate to :one or associate with :one. So pretty simple.

Okay, in this case, I'm defining SELECT * FROM posts, and we want this function to have the name of ListPosts. So it'd be repository.ListPosts, and it will return many values back. So let's go ahead and run the sqlc generate again this time and see if it works, which it does. We didn't get any errors.

Additionally, if I take a look at the directory, you can see we have the repository directory now created. And if we look inside, you can see it has a number of different values in there. If you want to see what these values or what these files contain, I recommend checking out my full YouTube video on SQLC.

Using the Generated Code

In any case, now that we have the repository designed, let's go ahead and replace this code to make use of it instead. To do so, the first thing we need to do, I'm going to go ahead and actually comment this out just so that we have it as a reference, but we're not actually using this code.

The first thing we need to do is to define a new repository type. So we're going to go ahead and call this repo, which is going to be of type repository. I may need to do an LSP restart just to get my LSP server. Okay, repository, which you can see is dreamsofcode.io/cms/repository. And then we can go ahead and do the New function. This function takes a repository.DBTX type, which is an interface that conforms to a few different db query types. In this case, we can go ahead and pass in the database/sql db type as it conforms to this interface.

repo := repository.New(db)

With that, we now have a new repository type, which if I go ahead and open up in my LSP, you can see we have a couple of methods. ListPosts, which is the one we defined, and WithTx. This is used for database transactions if you want to create a new repository.Queries type that is wrapped inside of a transaction. In our case, we're not going to touch on that in this lesson.

Instead, we're just going to go ahead and use the ListPosts method, which, as you can see, takes a context.Context because it uses the context pattern under the hood, which is a good thing. And also we'll return a slice of repository.Post and an error.

So let's go ahead and quickly create our context. We can just go ahead and do ctx, cancel and we'll set this to be context.With. Actually, we'll do signal.NotifyContext, context.Background, and we'll do signal. Actually, I think it's os.Interrupt, isn't it. Always confuses me a little between those two. And we'll just go ahead and do a defer cancel.

ctx, cancel := signal.NotifyContext(context.Background(), os.Interrupt)
defer cancel()

And we now have a context that we can go ahead and pass into this. So let's go ahead and pass in the context to the ListPosts method. And we want to go ahead and capture the posts as the return value and an error.

posts, err := repo.ListPosts(ctx)
if err != nil {
    log.Fatal(err)
}

Let's do a simple if error check. And we'll do log.Fatal line, error as so. Very good, very good. Now we have a slice of posts. That should return, that should be all of the values inside of our database store. So we can just go ahead and basically iterate over these posts using the for post := range posts. And we can then go ahead and actually call the printLine function. So printPost, printPost function, sorry. And I'll go ahead and do the tab writer and just pass the post in.

We need to go ahead and actually get rid of our custom post struct type that we defined, which is fine. Or you could go ahead and make this a little bit easier and just do type Post and just assign it to the value of repository.Post. That way you don't have to change any of the interfaces if you already have them. In our case, it's now just taking the same repository post and we can just go ahead and wrap this as follows.

for _, post := range posts {
    printPost(tabWriter, repository.Post(post))
}

I recommend either way works and there's benefits to each approach. Either just doing a type alias. We could actually just do this and then we don't even need to wrap it. These two syntaxes of defining a custom type. So either type Post = repository.Post or type Post repository.Post. This itself creates a new discrete type. So a type that doesn't necessarily conform to repository.Post. You have to cast it to it. Whereas this type just creates an alias. So basically in this case, Post is just an alias of repository.Post as being instead of being a discrete type. In this case, it's actually easier for us to go ahead and just set it to be an alias and then we don't have to cast it.

type Post = repository.Post

Okay. With that, we now have all of the codes here in order to be able to list all of the posts inside of our database table, which if I go ahead and run, you can see it works. In fact, if I go ahead and do sqlite3 and let's go ahead and insert a couple of posts. So we can go ahead and do INSERT INTO posts. Let's go ahead and do a title slug content and we'll do the values of "my next post". We'll do "next-post". Sorry, this is a bit tedious. "Even more interesting". Oh, it's posts, not post. Okay.

Now, if I go ahead and run this, you can see this time we are getting back two posts that come through. As you can see, this is a lot more simple than using kind of or writing our own SQL expressions and then having to do all of the casting of those rows into the concrete type that we want to use. This is all basically done for us and it's incredibly easy to add new queries to this now that we have everything set up.

Adding More Queries

For example, if we want to go ahead and modify this query so we can do like ORDER BY title or slug ASC, let's say, it's easy enough to do so. We can make the change inside of our actual SQL query and go ahead and use sqlc generate yet again. If you add this as like a CI/CD step, then you can always ensure that your queries are going to be up to date.

-- name: ListPosts :many
SELECT * FROM posts ORDER BY id ASC;

Not only is it easy to modify our existing queries, but it's also easy to add new ones. Let's say we want to go ahead and add in an insert post query and this will just be an exec. In this case, we can go ahead and do INSERT INTO posts and we could do title slug content and we'll do values of and we could go and say, make this parameterize. So we could do $1, $2, $3. So this will just be the three parameters that we define.

Now, if I go ahead and do sqlc generate, let's go ahead and we can't do this with SQLite. Sorry, we have to do question marks. Let's go ahead and try that again.

-- name: InsertPost :exec
INSERT INTO posts (title, slug, content) VALUES (?, ?, ?);

sqlc generate style does not exist title. Okay, this should now work. A couple of mistakes here. sqlc generate. Now, if I head on over to the main function and we call the repo method again, this time we can insert a post. But if I scroll up a little bit, but our InsertPost method takes a context.Context as the first parameter. That's expected. But then it also takes a repository.InsertPostParams as the second argument, which if we go ahead and take a look at repository.InsertPostParams is a struct that has a content, a slug and a type.

So you can just go ahead and actually pass this parameter type as the parameters to it rather than having to kind of add in multiple arguments. So you could do, say, title, "my third post", slug, say, "my-third-post". And we can go ahead and do let's do the contents just "testing". Let's say, okay, let's go ahead and actually call the we can do this as params. We'll capture this and then we can go ahead and do repository or repo.InsertPost. We'll do the context and we'll do the params.

params := repository.InsertPostParams{
    Title:   "My Third Post",
    Slug:    "my-third-post",
    Content: "Testing",
}
err = repo.InsertPost(ctx, params)
if err != nil {
    log.Fatal(err)
}

I'm going to ignore the error for the meantime because this just should work. Then let's actually go ahead and list the posts afterwards. And we should see that we have now a third post being added in. If we go ahead and run this, you can see that's the case, although the ordering is not that great. That's because we're not ordering by id.

As you can see, we've inserted this. I'm going to go ahead and get rid of this so we're not inserting it twice. But now if I go ahead and change this to be id ASC, you can see this time it works. I need to use the sqlc generate, but now it should work. Yep. As you can see, it's the id is now ascending and we can go ahead and do this as descending as well. Go run. There you go.

Benefits of SQLC

Okay. With that, we've taken a brief look at how SQLC can help to improve our code by removing the need for us to write all of this more complex querying and unmarshalling or, you know, turning the SQL query rows into an actual concrete data type. Instead, we can get rid of all of this and we can just make use of the repository design pattern that's generated by SQLC.

SQLC is one of my favorite packages when it comes to working with Go just because of how much time it actually saves. For me, it allows me to write SQL queries, which is how I like to interface with a SQL database rather than using an ORM. But at the same time, it also allows you to have typesafe code that you can easily generate, giving you the speed of an ORM, but with the flexibility of using SQL directly.

There are some caveats when it comes to working with SQLC such that it's more difficult to use dynamic queries with. But if you follow me on YouTube, I will be doing a video on how you can actually do that in the future.

Homework and Conclusion

In any case, that wraps up taking a quick look at SQLC and how we can integrate it with our code. As always, there's going to be some homework to add this to your own CMS tool, replacing any of the kind of hard coded SQL queries that you had with this new SQLC pattern, making it a lot easier and more type safe to use in your own code. As always, there will be some examples on how to do this in my own repo. So feel free to go ahead and take a look at it. Again, the link is in the description down below with the branch to where I added in SQLC to it.

In any case, that wraps up kind of the database interactions that we have when it comes to our CMS application. From the next lesson, we're going to start taking a look at some more UI based components when it comes to Go with some popular packages for creating UI resources such as color, UI elements such as progress bars and spinners. And eventually, we'll come on to forms where we can create dynamic forms that users can input data to. So I'm looking forward to that and I'll see you in the next lesson.