[jbaudanza]

I really like the idea of building TypeScript off of SQL types. But my ideal tool would be able to infer types from queries, not schemas. For example.. if I did:

   await db.query("SELECT user_id, COUNT(*) AS count FROM comments GROUP BY user_id");

I would like a TypeScript object that looks like:

  Array<{ user_id: number, count: number }>

[throwaway858]

This library does exactly what you want: https://github.com/MedFlyt/mfsqlchecker

and it also validates the types of the parameters that go into the query

[cldellow]

If I'm understanding you, you might like pgtyped: https://github.com/adelsz/pgtyped

(If you mean infer solely off of queries, I don't think that's possible unless you jam a bunch of type casts into the field selection part of the query.)

[yasserf]

Yup! I actually use that in my own node-postgres api.

So the code itself with typescript is here:

https://github.com/vramework/vramework/blob/main/backend-com...

But the general gist is we have generic crud operators that are away of what the table are and based on that know the types of what we are returned.

  const {viewId } = await database.crudInsert<UserJournal>(
  'user_journal', 
  { viewId, userId, srcOgg: src[0], sprite: JSON.stringify(sprite), duration }, 
  ['viewId']
  )

  await database.crudUpdate<UserJournal>('user_journal', { srcOgg: src[0], sprite: JSON.stringify(sprite), duration }, { viewId })

  const { srcOgg, sprite, duration } = await database.crudGet<UserJournal>(
  'user_journal', 
  ['srcOgg', 'sprite', 'duration'], 
  { viewId, userId }, 
  new UserJournalNotFoundError()
  )

[spiralx]

Here you go!

https://github.com/codemix/ts-sql

    import { Query } from "@codemix/ts-sql";
    
    const db = {
      things: [
        { id: 1, name: "a", active: true },
        { id: 2, name: "b", active: false },
        { id: 3, name: "c", active: true },
      ],
    } as const;
    
    type ActiveThings = Query<
      "SELECT id, name AS nom FROM things WHERE active = true",
      typeof db
    >;
    
    // ActiveThings is now equal to the following type:
    type Expected = [{ id: 1; nom: "a" }, { id: 3; nom: "c" }];

[ducaale]

It might be possible to have a babel macro function that inspects the passed SQL query and generate TS type from it.

However, the query might contain a wild card which means the macro function will also need access to the current DB schema.

[presentation]

If you’re ok with a query builder interface that matches postgres you might like Mammoth.

https://github.com/Ff00ff/mammoth

[oauea]

Just create your views from those queries, so you have schemas. And now your queries are reusable :)

[yasserf]

@jbaudanza sorry my response actually didn't reflect your suggestion at all!

Yeah I would love that sort of tool! I think theres some magic you can do with the es6 templating parsing, but that would be a bit complex to do as a supporting library

[jbaudanza]

No worries! I enjoyed checkout out your implementation!

I think such a tool would be complicated to implemented. I was thinking maybe of integrating with tagged template literals somehow. Maybe something like:

  db.query(sql`SELECT COUNT(*) FROM users`)

And then some tool could parse the AST to find all the SQL and generate types.

[guntars]

How about keeping the query as a separate, importable .sql file that can be analyzed by a tool and it could spit out the typings to a .sql.d.ts file? The editor support for mixing languages in a single file was not great, last time I checked.

[PudgePacket]

sqlx is able to do this, but using compile time macros. For a typescript project you'd possibly need a running process reading your source files for queries and probing the database for what will come back.

https://github.com/launchbadge/sqlx/#compile-time-verificati...

[febrilian]

doesn't it already exist? but it converts from your json https://quicktype.io/

[morelisp]

These barely work. Even their example shows it generating `type : string[]` but `weaknesses : Weakness[]`. You would need to start from a schema definition (JSON Schema, SQL CREATE TABLE, etc.) to make the result any good.

[yasserf]

Yeah the good thing about using such a low level tool is that everything remains typed until you reach the query statement. Postgres and typescript enums work great together.

For one of my projects I check in the generated types and replace 'unknown' (from jsonb) with the specific object manually. That way the concept of any or unknown gets pushed mostly out of the codebase (for simple crud. When joining we use normal SQL queries with some helper tools that verify the fields we are picking exist). Hopefully will manage to figure out how to do with with postgres comments at somepoint.