Fiber with sqlc

🎯 fiber + sqlc Example

Usage

1. Run Postgres

$ docker compose build

$ docker compose up

2. Wait 1-2 minutes

[+] Running 2/0
 ✔ Network sqlc_default       Created                                                                             0.1s
 ✔ Container postgres         Created                                                                             0.0s
Attaching to postgres
postgres  |
postgres  | PostgreSQL Database directory appears to contain a database; Skipping initialization
postgres  |
postgres  |
postgres  | 2023-09-28 09:17:50.737 UTC [1] LOG:  starting PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
postgres  | 2023-09-28 09:17:50.737 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
postgres  | 2023-09-28 09:17:50.737 UTC [1] LOG:  listening on IPv6 address "::", port 5432
postgres  | 2023-09-28 09:17:50.740 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres  | 2023-09-28 09:17:50.751 UTC [30] LOG:  database system was shut down at 2023-09-28 08:50:35 UTC
postgres  | 2023-09-28 09:17:50.770 UTC [1] LOG:  database system is ready to accept connections

3. You have to migrate the database.

🎯 It is a "database-first" ORM as opposed to "code-first" (like gorm/gorp). That means you must first create your database schema.

🎯 I used golang-migrate to proceed with the migrate.

1. Make Migration files

$ migrate create -ext sql -dir ./database/migrations -seq create_initial_table

sqlc/database/migrations/000001_create_initial_table.up.sql
sqlc/database/migrations/000001_create_initial_table.up.sql

2. Migrate

$ migrate -path database/migrations -database "postgresql://user:password@localhost:5432/fiber_demo?sslmode=disable" -verbose up

2023/09/28 20:00:00 Start buffering 1/u create_initial_table
2023/09/28 20:00:00 Read and execute 1/u create_initial_table
2023/09/28 20:00:00 Finished 1/u create_initial_table (read 24.693541ms, ran 68.30925ms)
2023/09/28 20:00:00 Finished after 100.661625ms
2023/09/28 20:00:00 Closing source and database

3. Rollback Migrate

$ migrate -path database/migrations -database "postgresql://user:password@localhost:5432/fiber_demo?sslmode=disable" -verbose down

2023/09/28 20:00:00 Are you sure you want to apply all down migrations? [y/N]
y
2023/09/28 20:00:00 Applying all down migrations
2023/09/28 20:00:00 Start buffering 1/d create_initial_table
2023/09/28 20:00:00 Read and execute 1/d create_initial_table
2023/09/28 20:00:00 Finished 1/d create_initial_table (read 39.681125ms, ran 66.220125ms)
2023/09/28 20:00:00 Finished after 1.83152475s

4. Use sqlc

1. Install

# Go 1.25 and above:
$ go install github.com/sqlc-dev/sqlc/cmd/sqlc@latest

# Legacy Go versions (<1.25):
go get github.com/sqlc-dev/sqlc/cmd/sqlc

2. Create a configuration file

Example

sqlc.yaml

version: "2"
sql:
  - engine: "postgresql"
    queries: "database/query"
    schema: "database/migrations"
    gen:
      go:
        package: "sqlc"
        out: "database/sqlc"

author.sql

-- name: GetAuthors :many
SELECT * FROM author;

-- name: GetAuthor :one
SELECT * FROM author WHERE id = \$1;

-- name: NewAuthor :one
INSERT INTO author (email, name) VALUES (\$1, \$2) RETURNING *;

-- name: UpdateAuthor :one
UPDATE author SET email = \$1, name = \$2 WHERE id = \$3 RETURNING *;

-- name: DeleteAuthor :exec
DELETE FROM author WHERE id = \$1;

post.sql

-- name: GetPosts :many
SELECT * FROM post;

-- name: GetPost :one
SELECT * FROM post WHERE id = \$1;

-- name: NewPost :one
INSERT INTO post (title, content, author) VALUES (\$1, \$2, \$3) RETURNING *;

-- name: UpdatePost :one
UPDATE post SET title = \$1, content = \$2, author = \$3 WHERE id = \$4 RETURNING *;

-- name: DeletePost :exec
DELETE FROM post WHERE id = \$1;

3. Generate

$ sqlc generate

sqlc/
├── author.sql.go
├── db.go
├── models.go
├── post.sql.go

5. Reference

sqlc document

API Endpoints

Authors

Method	Path	Description
GET	/authors	List all authors
GET	/authors/:id	Get an author by ID
POST	/authors	Create a new author
PUT	/authors/:id	Update an author
DELETE	/authors/:id	Delete an author

Posts

Method	Path	Description
GET	/posts	List all posts
GET	/posts/:id	Get a post by ID
POST	/posts	Create a new post
PUT	/posts/:id	Update a post
DELETE	/posts/:id	Delete a post