version: '2'plugins: - name: tswasm: url: https://downloads.sqlc.dev/plugin/sqlc-gen-typescript_0.1.3.wasmsha256: 287df8f6cc06377d67ad5ba02c9e0f00c585509881434d15ea8bd9fc751a9368sql: - schema: "schema.sql"queries: "query.sql"engine: postgresqlcodegen: - out: src/authorsplugin: tsoptions: runtime: nodedriver: postgres

• PostgreSQL via pg or postgres.
• MySQL via mysql2.

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 dashboardproject: "<PROJECT_ID>"plugins: - name: tswasm: url: https://downloads.sqlc.dev/plugin/sqlc-gen-typescript_0.1.3.wasmsha256: 287df8f6cc06377d67ad5ba02c9e0f00c585509881434d15ea8bd9fc751a9368sql: - engine: "postgresql"queries: "query.sql"schema: "schema.sql"database: managed: truecodegen: - out: dbplugin: tsoptions: runtime: nodedriver: 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:

CREATETABLEauthors ( id BIGSERIALPRIMARY KEY, name textNOT NULL, bio text );

Next, create a query.sql file with the following five queries:

-- name: GetAuthor :oneSELECT*FROM authors WHERE id = $1LIMIT1; -- name: ListAuthors :manySELECT*FROM authors ORDER BY name; -- name: CreateAuthor :oneINSERT INTO authors ( name, bio ) VALUES ( $1, $2 ) RETURNING *; -- name: UpdateAuthor :execUPDATE authors set name = $2, bio = $3WHERE id = $1; -- name: DeleteAuthor :execDELETEFROM authors WHERE id = $1;

If you prefer, you can alter the UpdateAuthor query to return the updated record:

-- name: UpdateAuthor :oneUPDATE authors set name = $2, bio = $3WHERE 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";asyncfunctionmain(){constclient=newPool({connectionString: process.env["DATABASE_URL"]});awaitclient.connect();// list all authorsconstauthors=awaitlistAuthors(client);console.log(authors);// create an authorconstauthor=awaitcreateAuthor(client,{name: "Anders Hejlsberg",bio: "Original author of Turbo Pascal and co-creator of TypeScript",});if(author===null){thrownewError("author not created");}console.log(author);// get the author we just createdconstanders=awaitgetAuthor(client,{id: author.id});if(anders===null){thrownewError("anders not found");}console.log(anders);// delete the authorawaitdeleteAuthor(client,{id: anders.id});}(async()=>{awaitmain();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: tswasm: url: https://downloads.sqlc.dev/plugin/sqlc-gen-typescript_0.1.3.wasmsha256: 287df8f6cc06377d67ad5ba02c9e0f00c585509881434d15ea8bd9fc751a9368sql: - schema: "schema.sql"queries: "query.sql"engine: postgresqlcodegen: - out: dbplugin: tsoptions: runtime: nodedriver: pg # npm package name

version: '2'plugins: - name: tswasm: url: https://downloads.sqlc.dev/plugin/sqlc-gen-typescript_0.1.3.wasmsha256: 287df8f6cc06377d67ad5ba02c9e0f00c585509881434d15ea8bd9fc751a9368sql: - schema: "schema.sql"queries: "query.sql"engine: postgresqlcodegen: - out: dbplugin: tsoptions: runtime: nodedriver: postgres # npm package name

version: '2'plugins: - name: tswasm: url: https://downloads.sqlc.dev/plugin/sqlc-gen-typescript_0.1.3.wasmsha256: 287df8f6cc06377d67ad5ba02c9e0f00c585509881434d15ea8bd9fc751a9368sql: - schema: "schema.sql"queries: "query.sql"engine: postgresqlcodegen: - out: dbplugin: tsoptions: runtime: nodedriver: mysql2 # npm package name

version: '2'plugins: - name: tswasm: url: https://downloads.sqlc.dev/plugin/sqlc-gen-typescript_0.1.3.wasmsha256: 287df8f6cc06377d67ad5ba02c9e0f00c585509881434d15ea8bd9fc751a9368sql: - schema: "schema.sql"queries: "query.sql"engine: sqlitecodegen: - out: dbplugin: tsoptions: runtime: nodedriver: better-sqlite3 # npm package name