SQLC

SQLC is an amazing Go program that generates Go code from SQL queries. It's not exactly an ORM, but rather a tool that makes working with raw SQL easy and type-safe.

We will be using Goose to manage our database migrations (the schema). We'll be using SQLC to generate Go code that our application can use to interact with the database (run queries).

Assignment

Install SQLC.

SQLC is just a command line tool, it's not a package that we need to import. I recommend installing it using go install. Installing Go CLI tools with go install is easy and ensures compatibility with your Go environment.

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

Then run sqlc version to make sure it's installed correctly.

Configure SQLC. You'll always run the sqlc command from the root of your project. Create a file called sqlc.yaml in the root of your project. Here is mine:

version: "2"
sql:
  - schema: "sql/schema"
    queries: "sql/queries"
    engine: "postgresql"
    gen:
      go:
        out: "internal/database"

We're telling SQLC to look in the sql/schema directory for our schema structure (which is the same set of files that Goose uses, but sqlc automatically ignores "down" migrations), and in the sql/queries directory for queries. We're also telling it to generate Go code in the internal/database directory.

Write a query to create a user. Inside the sql/queries directory, create a file called users.sql. Here's the format:

-- name: CreateUser :one
INSERT INTO users (id, created_at, updated_at, email)
VALUES (
    ...
)
RETURNING *;

We'll be using UUIDs for ID values, so you can use gen_random_uuid() to generate a new UUID.
The created_at and updated_at fields should be set to the current timestamp. In Postgres, you can use NOW() to get the current timestamp.
The email should be passed in by our application. Use $1 to represent the first parameter passed into the query. (in future queries, we'll use $2, $3, etc. for additional parameters)

The :one at the end of the query name tells SQLC that we expect to get back a single row (the created user).

Keep the SQLC postgres docs handy, you'll probably need to refer to them again later.

Generate the Go code. Run sqlc generate from the root of your project. It should create a new package of go code in internal/database. You'll notice that the generated code relies on Google's uuid package, so you'll need to add that to your module:

go get github.com/google/uuid

Import a PostgreSQL driver.

We need to add and import a Postgres driver so our program knows how to talk to the database. Install it in your module:

go get github.com/lib/pq

Add this import to the top of your main.go file:

import _ "github.com/lib/pq"

This is one of my least favorite things working with SQL in Go currently. You have to import the driver, but you don't use it directly anywhere in your code. The underscore tells Go that you're importing it for its side effects, not because you need to use it.

Create a .env file in the root of your project:

DB_URL="YOUR_CONNECTION_STRING_HERE"

Add it to your .gitignore file. It's incredibly insecure to commit secret keys to a Git repo.

You would never use a plain text .env file in a production environment, but for local development of a personal project, you're fine.

Add a query parameter to the end of the connection string to disable SSL, e.g. postgres://wagslane:@localhost:5432/chirpy?sslmode=disable.

go get github.com/joho/godotenv, then call godotenv.Load() at the beginning of your main() function to load the .env file into your environment variables. Then you can use os.Getenv to get the DB_URL from the environment:

dbURL := os.Getenv("DB_URL")

Next, sql.Open() a connection to your database:

db, err := sql.Open("postgres", dbURL)

Make sure all packages used are imported at the top.

Use your SQLC generated database package to create a new *database.Queries, and store it in your apiConfig struct so that handlers can access it:

dbQueries := database.New(db)

Run and submit the CLI tests.