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