Potygen (Postgres typescript generator)

• 🛡️ Type Safe SQL inputs and outputs at compile time
• 📜 Tables, columns and types autocompletion
• 🔎 Inline table and column information on hover
• 🪶 Pure JS and no dependencies
• 💅 Prittier plugin

Prettier & Typescript showcase

https://user-images.githubusercontent.com/4976/159504807-0a92f810-e8c7-464c-bfa6-6bbe4bd110e3.mov

List of features

• [x] Load views types
• [x] Prettier plugin for formatting sql queries
• [x] Typescript language server extension to add autocomplete and quickinfo to the typescript LSP. Any editor implementing LSP is supported.
• [x] json_build_object parsing - will return the built json type as a object literal type, preserving its full shape
• [x] enums loading - will show enums in quick info, autocompletion as well as type generation
• [x] custom types loading - composite types are parsed and returned in typescript
• [x] custom functions - alongside all the built in postgres functions, typescript types will be generated for arguments and returns of custom functions too
• [x] load comments from tables, columns, views, types and functions. Show in both quickinfo and typescript types
• [x] for jsonb columns, generate generic types, which you can plug with your own types where appropriate
• [x] supports CTEs, subqueries, nested joins and recordsets
• [ ] alter table syntax
• [ ] truncate table syntax

Why ?

Looking to interact with your database, well you have several choices by the looks of it:

Plain SQL

const { rows } = await db.query(
  `
  UPDATE projects SET
    last_opened = NOW(),
    open_count = open_count + 1
  WHERE
    projects.id IN (
      SELECT project_id 
      FROM project_members WHERE user_id = $1
    )
  RETURNING *
`,
  [userId],
);

ORMs (Sequelize, TypeORM, ...)

const user = await User.findById(userId);
const projects = await user.getProjects();

const updatedProjects = await Promise.all(
  projects.map(async (project) => {
    project.last_opened = new Date(Date.now());
    project.open_count++;
    return project.save();
  }),
);

Query builder (Knex.js, Prisma, ...)

const usersProjects = await prisma.user({ id: userId }).projects();

const updatedProjects = await Promise.all(
  projects.map((project) =>
    prisma.updateProject({
      data: {
        last_opened: new Date(Date.now()),
        open_count: project.open_count + 1,
      },
      where: {
        id: project.id,
      },
    }),
  ),
);

Sql with potygen 🚀

const updateQuery = sql<UpdateQuery>`
  UPDATE projects
  SET
    last_opened = NOW(),
    open_count = open_count + 1
  WHERE
    projects.id IN (SELECT project_id FROM project_members WHERE user_id = $userId)
  RETURNING
    *
  `;
await updateQuery(db, { userId });

As the comment comment from Hacker News regarding "The Art of PostgreSQL" states:

It took my SQL from “the database is not much more than a place to persist application data” to “the application is not much more than a way to match commands to the database”.

Installation

For basic functionality you'll need a runtime dependency of @potygen/potygen

yarn add @potygen/potygen

And to get type generation, you'll need to install the cli tool:

yarn add --dev @potygen/cli

For prettier and typescript support refer to @package/prettier-plugin-pgsql and @package/typescript-pgsql-plugin

Example express app

An example very simple - examples/bookstore shows the type generation in action. Though the incoming types from express are usually just "any". For better safety you'll need to use a framework that gives you statically verifiable data.

examples/bookstore/src/app.ts

import * as express from 'express';
import * as bodyParser from 'body-parser';
import { sql, SqlDatabase } from '@potygen/potygen';
import { BooksSqlQuery, InsertBookSqlQuery } from './app.queries';

export const createApp = (db: SqlDatabase) => {
  const app = express();

  app.use(bodyParser.json());

  const booksSql = sql<BooksSqlQuery>`
    SELECT
      books.id,
      books.title,
      books.isbn,
      authors.name AS "authorName",
      COUNT(book_orders.amount) AS "copiesSold"
    FROM
      books
      JOIN authors
        ON authors.id = books.author_id
      LEFT JOIN book_orders
        ON book_orders.book_id = books.id AND book_orders.state = 'Paid'
    WHERE
      authors.id = $authorId!
    GROUP BY
      books.id,
      authors.name
    ORDER BY
      books.id ASC
    `;

  const insertBookSql = sql<InsertBookSqlQuery>`
    INSERT INTO books (
      isbn,
      title,
      author_id
    )
    VALUES
      (
        $isbn!,
        $title,
        $authorId
      )
    RETURNING
      id,
      isbn,
      title,
      author_id AS "authorId"
    `;

  app.get('/books/:authorId', async (req, res) => {
    const books = await booksSql(db, { authorId: Number(req.params.authorId) });
    res.json(books);
  });

  app.post('/books', async (req, res) => {
    const [book] = await insertBookSql(db, req.body);
    res.json(book);
  });

  return app;
};

State

Project state: Beta. It is used in production, but might not be able to parse some complex queries. In the sql folder you'll find the kinds of queries potygen was designed to deal with, and can decide whether it can handle the complexity that you need.

This project is being actively developed and its APIs might change. All issue reports, feature requests and PRs appreciated.

Development

To start up a local development you need Docker. Start it up.

To install all dependencies

yarn install

And to setup local editor development run as recommended by yarn:

yarn dlx @yarnpkg/sdks

Since some packages depend on others in the same repo, to run the tests you'll need to build them first:

yarn build

To check with prettier:

yarn lint

Since potygen supports different versions of postgres, and the version affects tets, you'll need to start and seed a specific pg version by

cd pg-version/v14
docker-compose up -d

Then to execute all non-version specific tests and then just the sepcific version ones run:

yarn test:general
yarn test:14