Add Typescript support

sqlc, like protoc, aims to generate code for multiple languages. The second language we're looking to add is TypeScript.

Getting started

Warning

The TypeScript plugin (sqlc-gen-typescript) is under active development. We make no guarantees about backwards compatibility. Please try it out and give us feedback, but don't use it in production just yet.

Create a new directory. Inside, create a sqlc.yaml with these contents. Add your database schema to schema.sql and your queries to query.sql. If you don't have any to try out, skip to the provided example.

version: "2"
plugins:
- name: ts
  wasm:
    url: "https://downloads.sqlc.dev/plugin/alpha/sqlc-gen-typescript_0.0.0_c6bc663.wasm"
    sha256: c6bc663cb5064ef33023f24d96c378512f1d6a0fadd7c9b5bba45a24cbfbb894
sql:
- schema: "schema.sql"
  queries: "query.sql"
  engine: "postgresql"
  codegen:
  - plugin: ts
    out: src/db
    options:
      driver: "pg"
      runtime: "node"

Running sqlc generate will output TypeScript into src/db. Please let us know what you think by leaving a comment on this issue.

Example

With the following schema and queries, sqlc will generate code with zero dependencies (beyond the necessary database driver).

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

-- 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: DeleteAuthor :exec
DELETE FROM authors
WHERE id = $1;

import { QueryArrayConfig, QueryArrayResult } from "pg";

interface Client {
    query: (config: QueryArrayConfig) => Promise<QueryArrayResult>;
}

export const getAuthorQuery = `-- name: GetAuthor :one
SELECT id, name, bio FROM authors
WHERE id = $1 LIMIT 1`;

export interface GetAuthorArgs {
    id: string;
}

export interface GetAuthorRow {
    id: string;
    name: string;
    bio: string | null;
}

export async function getAuthor(client: Client, args: GetAuthorArgs): Promise<GetAuthorRow | null> {
    const result = await client.query({
        text: getAuthorQuery,
        values: [args.id],
        rowMode: "array"
    });
    if (result.rows.length !== 1) {
        return null;
    }
    const row = result.rows[0];
    return {
        id: row[0],
        name: row[1],
        bio: row[2]
    };
}

export const listAuthorsQuery = `-- name: ListAuthors :many
SELECT id, name, bio FROM authors
ORDER BY name`;

export interface ListAuthorsRow {
    id: string;
    name: string;
    bio: string | null;
}

export async function listAuthors(client: Client): Promise<ListAuthorsRow[]> {
    const result = await client.query({
        text: listAuthorsQuery,
        values: [],
        rowMode: "array"
    });
    return result.rows.map(row => {
        return {
            id: row[0],
            name: row[1],
            bio: row[2]
        };
    });
}

export const createAuthorQuery = `-- name: CreateAuthor :one
INSERT INTO authors (
  name, bio
) VALUES (
  $1, $2
)
RETURNING id, name, bio`;

export interface CreateAuthorArgs {
    name: string;
    bio: string | null;
}

export interface CreateAuthorRow {
    id: string;
    name: string;
    bio: string | null;
}

export async function createAuthor(client: Client, args: CreateAuthorArgs): Promise<CreateAuthorRow | null> {
    const result = await client.query({
        text: createAuthorQuery,
        values: [args.name, args.bio],
        rowMode: "array"
    });
    if (result.rows.length !== 1) {
        return null;
    }
    const row = result.rows[0];
    return {
        id: row[0],
        name: row[1],
        bio: row[2]
    };
}

export const deleteAuthorQuery = `-- name: DeleteAuthor :exec
DELETE FROM authors
WHERE id = $1`;

export interface DeleteAuthorArgs {
    id: string;
}

export async function deleteAuthor(client: Client, args: DeleteAuthorArgs): Promise<void> {
    await client.query({
        text: deleteAuthorQuery,
        values: [args.id],
        rowMode: "array"
    });
}

With typescript, there are a few additional considerations in terms of which driver to use. Here is my first pass at what the sqlc output could look like:

https://gist.github.com/cmoog/c7b612f6aa829c426ec665ad7cfcb7cb

any thoughts?

@kyleconroy

@kyleconroy

As someone who comes from using mostly Typescript for backend work, I'm not sure what sqlc can add to that landscape?

I'm sure you "could" do it and the implementation would be great, but there are already so many best of breed options that do the same thing for typescript.

PgTyped
Zapatos, (my personal favorite)
Prisma, (the most popular) .

For Kotlin, theres Jooq and Exposed that I know of, which are both VERY mature.

I'd love to see sqlc committed to providing the best Go experience there is since both Typescript and Kotlin have great options for providing the same functionality already.

I had a look at PgTyped and Zapatos, but that quite different than what sqlc proposes: they both seem to require an existing database where the schema already has been deployed, and then only, the CLI tool can pull the schema and generate code.

sqlc is parsing the DDL SQL files only, no database needed, so it depends how the developer works:

either the developer works by prototyping his schema live in a local DB (for example), generate his code and migration files from there.
or the developer maintains his migration files first, iterate quickly, and only deploy to a DB once it's stable enough. What makes sqlc unique here is its use of pg_query as a robust query parser.

I wouldn't push for one style or another, however, sqlc is clearly for case 2, and the other tools doesn't help there. I fall in case 2, using Sqitch to handle my migration files in a more language-agnostic fashion.

For PostgreSQL I would suggest Slonik.

It doesn't depend on a database.
Wraps node-pg with security layers.

I think for Typescript / Javascript in general, there is too much fragmentation in the ecosystem to realistically support each code generator in "sqlc core". Greenfield projects might be able to use "sqlc everywhere" (and whatever library / orm / actual connector lib is chosen), but I think projects as they grow / become successful, will need a way to iteratively evolve (no one can keep up with Javascript ecosystem changes). This point of view is what made Typescript become as ubiquitous as it is (extremely easy adoption to existing projects).

I'm not sure what the "sqlc vision" is here - but think it should aim to be "as stable as Go", and "a permanent as SQL itself" (which basically means, no Javascript haha).

For example, here are the ORMs I know off, and any legacy projects would likely prefer the sqlc output to "play nicely" with the paradigms of that ORM.

https://github.com/gajus/slonik
https://sequelize.org/ (7 separate versions to target)
https://www.prisma.io/
https://bookshelfjs.org/ (or http://knexjs.org/ raw)
https://github.com/adelsz/pgtyped (though I'd say this is more of a sqlc competitor)
https://deno.land/x/postgres (for deno, not node.js)

That's why I pretty strongly prefer the WASM and JSON options (instead of TS generation directly here)
#1470
#1565

I think maybe someone here could give an initial pass using the JSON output. And just do it in whatever your preferred flavor of Javascript (JS, Typescript, Flow, Deno, etc) and ORM / db client is. No matter which one is chosen, it will expose some rough edges here in sqlc core or additinoal metadata that needs to be exposed, and start to "know-ify" all the unknown unknowns (which would be great for obvious reasons).

Do you need any help in implementing TypeScript support? I would be happy to contribute.

@skabbes

@skabbes, I experimented with the JSON output. I think there are a couple infos missing in the generated json:

primary key indicatior
unique inidicator
foreign key contraints

I am generating TypeORMentities with go templates.

Now that plugin support has landed, I've started sketching out what this might look like for simple queries, based on the authors example. I'm imaging that the generated code would look something like this:

import { Client } from 'pg';

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

export type GetAuthorParams = {
    id: bigint | null;
}

export type GetAuthorRow = {
    id: bigint;
    name: string;
    bio: string | null;
}

export async function getAuthor(client: Client, args: GetAuthorParams): Promise<GetAuthorRow | null> {
  const result = await client.query({
    text: getAuthorQuery,
    values: [args.id],
    rowMode: 'array',
  })
  if (result.rows.length !== 1) {
    return null
  }
  const row = result.rows[0]
  return {
    id: row[0],
    name: row[1],
    bio: row[2],
  }
}

const listAuthorsQuery = `-- name: ListAuthors :many
SELECT * FROM authors
ORDER BY name;
`

export type ListAuthorsRow = {
    id: bigint;
    name: string;
    bio: string | null;
}


export async function listAuthors(client: Client): Promise<ListAuthorsRow[]> {
  const result = await client.query({
    text: listAuthorsQuery,
    rowMode: 'array',
  })
  return result.rows.map(row => {
    return {
      id: row[0],
      name: row[1],
      bio: row[2],
    }
  })
}

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

export type CreateAuthorParams = {
    name: string | null;
    bio: string | null;
}

export type CreateAuthorRow = {
    id: bigint;
    name: string;
    bio: string | null;
}

export async function createAuthor(client: Client, args: CreateAuthorParams): Promise<CreateAuthorRow | null> {
  const result = await client.query({
    text: createAuthorQuery,
    values: [args.name, args.bio],
    rowMode: 'array',
  })
  if (result.rows.length !== 1) {
    return null
  }
  const row = result.rows[0]
  return {
    id: row[0],
    name: row[1],
    bio: row[2],
  }
}

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

export type DeleteAuthorParams = {
    id: bigint | null;
}

export async function deleteAuthor(client: Client, args: DeleteAuthorParams): Promise<void> {
  await client.query(deleteAuthorQuery, [args.id])
}

A few design notes:

All fields in parameter objects can be nullable
Since TypeScript can easily infer between overlapping types, each query returns a different type, even if they're the same type

I started out writing this in Rust (https://github.com/tabbed/sqlc-gen-node-pg), but I think I can actually write this in Go. If anyone wants to take a crack at the plugin, be my guest.

Any plans/ ETA on getting this officially supported by SQLC?

@stephen

@stephen whoa! I completely missed this comment when you posted in a few months ago. I'll have to take a look at it over the next week.

@kyleconroy

@kyleconroy @stephen I am super excited about this direction!

Hey everyone, excited to announce early access to our TypeScript support. I added a step-by-step guide above to help you get started. I want to stress that this is a preview release. Many features don't work and breaking changes should be expected. However, it's a great time to try it out if you'd like to influence the direction of our development.

The preview generates code that works with Node.js and pg. This week I'm planning on adding support for Postgres.js and Bun.

Enjoy!

The thing that I want from TypeScript is just a way to validate my client request and response objects against a database model / query argument object. I don’t actually want to use TypeScript on the backend to talk to the DB. Is there some way to do that with the plug-in as it exists?

@carlmjohnson

I've got pretty much the same use case as @carlmjohnson - I just need models for request/response objects to be used in the frontend.

BTW: what are the possible values for "driver" and "runtime" options? I'd love to see "none" available for "driver" and "browser" for "runtime".

@carlmjohnson

@carlmjohnson @dandee

The thing that I want from TypeScript is just a way to validate my client request and response objects against a database model / query argument object. I don’t actually want to use TypeScript on the backend to talk to the DB. Is there some way to do that with the plug-in as it exists?

I don't understand what you mean by validating request using TypeScript, but if you want types in typescript for types generated by sqlc, then perhaps these projects can allow you to do that:

Sorry for not being too technically clear: by validate I mean compile time validation that the model objects I create for requests match the backend ones. And thanks for the links! I've already found out the second one and planning to try it out.

Alright everyone, today's the day! Excited to announce the preview release of sqlc-gen-typescript. Read the announcement post (https://sqlc.dev/posts/2023/12/04/preview-typescript-support-with-sqlc-gen-typescript/) and clone / fork / star the repository (https://github.com/sqlc-dev/sqlc-gen-typescript).

As I mention in the post, this is a preview release. This issue will remain open until https://github.com/sqlc-dev/sqlc-gen-typescript hits 1.0.

Please, consider dropping bun: https://dev.to/thejaredwilcurt/bun-hype-how-we-learned-nothing-from-yarn-2n3j

I’m looking forward to the release of sqlc-gen-typescript@1.0.0, but I’ve had the chance to try out earlier versions. It works great but based on the issues I got the impression that it reimplements the parser and analyzer from scratch so its behavior might differ from sqlc-go (please correct me if I’m wrong).

Because of this I tried using the sqlc binaries directly and created a wrapper to call it with my queries from TypeScript files retrieve the JSON output and generate TypeScript types from it. I added some docs implemented a watch mode and published it here: 🔗 sqlc-typescript

In my opinion, the TypeScript version could be more convenient because of some TypeScript features like const string literals, allowing you to get types for queries right away without explicitly specifying query names.

I actively use this approach in production projects and it works great but I only recently published the library so it may still have some bugs. If you want to try this approach, I’d be happy to help and hear any feedback!

How it works

You write queries in typescript files using sqlc function (and add a special comment /* sql */ if you want syntax highlighting + formatting in your IDE).

const result = await sqlc(/* sql */ `
    SELECT customer_id, first_name, last_name
    FROM customer 
    WHERE customer_id = @customer_id
`).exec(client, {
    customer_id: 1,
});

In the background you run npx sqlc-typescript watch to watch for file changes.
Every time you save a file the script:
- scans all files in the directory (you can specify a glob)
- extracts queries calculates hash and combine them into a single file
- runs sqlc with the generated file and saves the output to a codegen_request.json file
```
{
    "version": "2",
    "sql": [
        {
            "engine": "postgresql",
            "schema": "schema.sql",
            "queries": "queries.sql",
            "gen": { "json": { "out": "generated" } }
        }
    ]
}
```
- generates typescript types from the codegen_request.json file
- stores the types in a sqlc.ts file which is imported in your files
- this file contains a map of types where the whole query is a key and the value is a typed object with a single exec method that accepts db client, parameters and returns a promise with the result
- the db client implements simple interface so the library can be adapted to any driver but works with node-pg out of the box
```
type QueryClient = {
    query: (
        query: string,
        params: unknown[],
    ) => Promise<{
        rows: Array<Record<string, unknown>>;
    }>;
};
```

I've been watching this issue with interest for some time but it doesn't seem likely that sqlc-gen-typescript will ever go stable, so I built a different SQL inference tool that's TypeScript-first. It relies on content mappers (introduced in TypeScript 7.1) to let you directly import .sql files from TypeScript with full type information (no codegen). https://github.com/colinhacks/reylo

-- get_author.sql
select u.id, u.name, count(p.id)::int as post_count
from users u
left join posts p on p.user_id = u.id
where u.id = :id::int
group by u.id

import postgres from "postgres";
import getAuthor from "./get_author.sql";
const db = postgres(process.env.DATABASE_URL);

const author = await getAuthor.run(db, { id: 1 });
// =>  { id: number; name: string; post_count: number; } | null

Navigation Menu

Repository navigation

Add Typescript support #296

Getting started

Example

2 remaining items

How it works

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Add Typescript support #296

Description

Getting started

Example

Activity

cmoog commented on Jan 24, 2020

g5becks commented on Dec 9, 2020

debackerl commented on Apr 1, 2021

guzmonne commented on Mar 29, 2022

skabbes commented on Apr 23, 2022

AIRTucha commented on Jun 5, 2022

bluebrown commented on Jun 25, 2022

kyleconroy commented on Sep 18, 2022

kyleconroy commented on Nov 9, 2022

2 remaining items

Nikola-Milovic commented on Aug 19, 2023

kyleconroy commented on Sep 22, 2023

marckong commented on Nov 5, 2023

kyleconroy commented on Nov 13, 2023

earthboundkid commented on Nov 16, 2023

dandee commented on Nov 23, 2023

20manas commented on Nov 27, 2023

dandee commented on Nov 27, 2023

kyleconroy commented on Dec 4, 2023

MyNameIsOka commented on Feb 16, 2024

seralexeev commented on Feb 9, 2025

How it works

colinhacks commented on Oct 22, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions