Add Typescript support

[kyleconroy]

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"
    });
}

[cmoog]

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?

[g5becks]

@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.

[debackerl]

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:

1. either the developer works by prototyping his schema live in a local DB (for example), generate his code and migration files from there.
2. 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.

[guzmonne]

For PostgreSQL I would suggest Slonik.

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

[skabbes]

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).

[AIRTucha]

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

[bluebrown]

@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.

[kyleconroy]

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

[kyleconroy]

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.

[davideimola]

Hi @kyleconroy, I would like to contribute to the realization of the plugin for TypeScript, if you have any good-first-issue ping me back 😄

[stephen]

I wrote a prototype plugin for supporting typescript with sql.js in the browser, heavily forking the go codegen: https://github.com/stephen/sqlc-ts

The codegen emit is slightly different from the output outlined in #296 (comment), but could be adapted with some changes to the template.

Some notes from the implementation:

• I ended up copying some packages out of sqlc/internal so as to not reimplement them...
• It might be nice to expose github.com/kyleconroy/sqlc/internal/cmd outside of internal/, i.e. to run a go-only test harness for plugins..
• In order to make the typechecker happy (and also to properly narrow the type), I ended up adding checks:

  if (typeof row[0] !== "number") { throw new Error(`expected type number for column id, but got ${typeof row[0]}`) };
  if (typeof row[1] !== "string") { throw new Error(`expected type string for column name, but got ${typeof row[1]}`) };
  if (typeof row[2] !== "string" && row[2] !== null) { throw new Error(`expected type string | null for column bioText, but got ${typeof row[2]}`) };

I think these are good in that they both satisfy the typechecker and properly handle bad assumptions, but I wonder if we instead as any in a more performance-sensitive context.

[Nikola-Milovic]

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

[kyleconroy]

@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.

[marckong]

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

[kyleconroy]

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!

[earthboundkid]

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?

[dandee]

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".

[20manas]

@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:

• https://github.com/gzuidhof/tygo
• https://github.com/tkrajina/typescriptify-golang-structs

[dandee]

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.

[kyleconroy]

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.

[MyNameIsOka]

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