0 / 2 embers
0 / 3000 xp
click for more info
Complete a lesson to start your streak
click for more info
Difficulty: 7
click for more info
No active XP Potion
Accept a Quest
Login to submit answers
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).
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.
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.
-- name: CreateUser :one
INSERT INTO users (id, created_at, updated_at, email)
VALUES (
...
)
RETURNING *;
gen_random_uuid()
to generate a new UUID.created_at
and updated_at
fields should be set to the current timestamp. In Postgres, you can use NOW()
to get the current timestamp.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.
go get github.com/google/uuid
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.
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
.
dbURL := os.Getenv("DB_URL")
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.
The Boot.dev CLI requires you to be signed in to submit your solution!
Copy/paste one of the following commands into your terminal:
Run
bootdev run e5bddf3d-d96b-487e-97e6-7a5aa06b1ee1
Submit
bootdev run e5bddf3d-d96b-487e-97e6-7a5aa06b1ee1 -s
To run and submit the tests for this lesson, you must have an active Boot.dev membership
Become a member to view solution
Using the Bootdev CLI
The Bootdev CLI is the only way to submit your solution for this type of lesson. We need to be able to run commands in your environment to verify your solution.
You can install it here. It's a Go program hosted on GitHub, so you'll need Go installed as well. Instructions are on the GitHub page.