Caution
Here be dragons! This plugin is still in early access. Expect breaking changes, missing functionality, and sub-optimal output. Please report all issues and errors. Good luck!
version: '2'
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
codegen:
- out: src/authors
plugin: ts
options:
runtime: node
driver: postgres
This tutorial assumes that the latest version of sqlc is installed and ready to use.
We'll generate TypeScript here, but other language plugins are available. You'll need Bun (or Node.js) installed if you want to build and run a program with the code sqlc generates, but sqlc itself has no dependencies.
We'll also rely on sqlc's managed databases,
which require a sqlc Cloud project and auth token. You can get those from
the sqlc Cloud dashboard. Managed databases are
an optional feature that improves sqlc's query analysis in many cases, but you
can turn it off simply by removing the cloud
and database
sections of your
configuration.
Create a new directory called sqlc-tutorial
and open it up.
Initialize a new package.
$ bun init
sqlc looks for either a sqlc.(yaml|yml)
or sqlc.json
file in the current
directory. In our new directory, create a file named sqlc.yaml
with the
following contents:
version: "2"
cloud:
# Replace <PROJECT_ID> with your project ID from the sqlc Cloud dashboard
project: "<PROJECT_ID>"
plugins:
- name: ts
wasm:
url: https://downloads.sqlc.dev/plugin/sqlc-gen-typescript_0.1.3.wasm
sha256: 287df8f6cc06377d67ad5ba02c9e0f00c585509881434d15ea8bd9fc751a9368
sql:
- engine: "postgresql"
queries: "query.sql"
schema: "schema.sql"
database:
managed: true
codegen:
- out: db
plugin: ts
options:
runtime: node
driver: pg
Replace <PROJECT_ID>
with your project ID from the sqlc Cloud dashboard. It
will look something like 01HA8SZH31HKYE9RR3N3N3TSJM
.
And finally, set the SQLC_AUTH_TOKEN
environment variable:
export SQLC_AUTH_TOKEN="<your sqlc auth token>"
sqlc needs to know your database schema and queries in order to generate code.
In the same directory, create a file named schema.sql
with the following
content:
CREATE TABLE authors (
id BIGSERIAL PRIMARY KEY,
name text NOT NULL,
bio text
);
Next, create a query.sql
file with the following five queries:
-- 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;
If you prefer, you can alter the UpdateAuthor
query to return the updated
record:
-- name: UpdateAuthor :one
UPDATE authors
set name = $2,
bio = $3
WHERE id = $1
RETURNING *;
You are now ready to generate code. You shouldn't see any output when you run
the generate
subcommand, unless something goes wrong:
$ sqlc generate
You should now have a tutorial
subdirectory with three files containing Go
source code. These files comprise a Go package named tutorial
:
├── package.json
├── query.sql
├── schema.sql
├── sqlc.yaml
└── db
├── query_sql.ts
You can use your newly-generated code package from any TypeScript program.
Create a file named index.ts
and add the following contents:
import { Pool } from "pg";
import {
createAuthor,
deleteAuthor,
getAuthor,
listAuthors,
} from "./db/query_sql";
async function main() {
const client = new Pool({ connectionString: process.env["DATABASE_URL"] });
await client.connect();
// list all authors
const authors = await listAuthors(client);
console.log(authors);
// create an author
const author = await createAuthor(client, {
name: "Anders Hejlsberg",
bio: "Original author of Turbo Pascal and co-creator of TypeScript",
});
if (author === null) {
throw new Error("author not created");
}
console.log(author);
// get the author we just created
const anders = await getAuthor(client, { id: author.id });
if (anders === null) {
throw new Error("anders not found");
}
console.log(anders);
// delete the author
await deleteAuthor(client, { id: anders.id });
}
(async () => {
await main();
process.exit()
})();
Before this code will run you'll need to install the pg
package:
$ bun install pg
The program should compile without errors. To make that possible, sqlc generates
readable, idiomatic TypeScript code that you otherwise would've had to write
yourself. Take a look in db/query_sql.ts
.
Of course for this program to run successfully you'll need to run after setting
the DATABASE_URL
environment variable. And your database must have the
authors
table as defined in schema.sql
.
$ DATABASE_URL="$(sqlc createdb)" bun run index.ts
$ bun run index.ts
You should now have a working program using sqlc's generated TypeScript source code, and hopefully can see how you'd use sqlc in your own real-world applications.
version: '2'
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
codegen:
- out: db
plugin: ts
options:
runtime: node
driver: pg # npm package name
version: '2'
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
codegen:
- out: db
plugin: ts
options:
runtime: node
driver: postgres # npm package name
version: '2'
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
codegen:
- out: db
plugin: ts
options:
runtime: node
driver: mysql2 # npm package name
version: '2'
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: sqlite
codegen:
- out: db
plugin: ts
options:
runtime: node
driver: better-sqlite3 # npm package name