sqlc 简单试用

以前简单介绍过sqlc的功能，以下是一个简单试用

环境准备

docker-compose

提供db支持

services:
  pgmq:
      image: tembo.docker.scarf.sh/tembo/pg17-pgmq:latest
      ports:
        - "5432:5432"
      environment:
      - POSTGRES_PASSWORD=postgres

项目代码

sqlc.yaml

定义schema 以及query sql 以及一些db 信息, 插件使用了ts 的

version: '2'
servers:
- engine: postgresql
  uri: "postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable"
plugins:
- name: ts
  wasm:
    url: https://downloads.sqlc.dev/plugin/sqlc-gen-typescript_0.1.3.wasm
    sha256: 287df8f6cc06377d67ad5ba02c9e0f00c585509881434d15ea8bd9fc751a9368
sql:
- schema: "schema.sql"
  queries: "query.sql"
  engine: postgresql
  database:
    managed: true
    uri: postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable
  rules:
  - sqlc/db-prepare
  codegen:
  - out: src/authors
    plugin: ts
    options:
      runtime: node
      driver: postgres

schema.sql 主要定义table 的schema 信息，对于managed模式会基于此创建db 以及tables

CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  bio  text
);

query.sql

定义sql 操作，会基于此生成代码

-- name: GetAuthor :one
SELECT * FROM authors
WHERE id = $1 LIMIT 1;

-- name: ListAuthors :many
SELECT * FROM authors
ORDER BY name;

-- name: CreateAuthor :one
INSERT INTO authors (
  name, bio
) VALUES (
  $1, $2
)
RETURNING *;

-- name: UpdateAuthor :exec
UPDATE authors
  set name = $2,
  bio = $3
WHERE id = $1;

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

生成代码

命令

sqlc generate

效果

代码集成

基于typescript, 使用bun 运行

import postgres from "postgres"

import {createAuthor,listAuthors} from "./authors/query_sql"

let  sql = postgres("postgres://postgres:postgres@localhost:5432/sqlc_managed_c3dcc55a7b3e6d52?sslmode=disable");

(async () => {
    let inserted = await createAuthor(sql, {name: "Alice", bio: "Alice's bio"})
    console.log(inserted)
    let authors = await listAuthors(sql)
    console.log(authors)
})()