3 ms·

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

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 }>

febrilian 5y ago
doesn't it already exist? but it converts from your json https://quicktype.io/ https://quicktype.io/
- morelisp 5y ago
  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 5y ago
    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.
yasserf 5y ago
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-common/src/services/database/database-postgres-client.ts#L27 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() )
yasserf 5y ago
@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 5y ago
  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.
  - deleted 5y ago
    [deleted]
  - guntars 5y ago
    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.
cldellow 5y ago
If I'm understanding you, you might like pgtyped: https://github.com/adelsz/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.)
ducaale 5y ago
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 5y ago
If you’re ok with a query builder interface that matches postgres you might like Mammoth. https://github.com/Ff00ff/mammoth https://github.com/Ff00ff/mammoth
PudgePacket 5y ago
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-verification https://github.com/launchbadge/sqlx/#compile-time-verificati...
oauea 5y ago
Just create your views from those queries, so you have schemas. And now your queries are reusable :)
throwaway858 5y ago
This library does exactly what you want: https://github.com/MedFlyt/mfsqlchecker https://github.com/MedFlyt/mfsqlchecker and it also validates the types of the parameters that go into the query
spiralx 5y ago
Here you go! https://github.com/codemix/ts-sql 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" }];

febrilian 5y ago

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

morelisp 5y ago
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 5y ago
  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.

yasserf 5y ago

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-common/src/services/database/database-postgres-client.ts#L27 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() )

yasserf 5y ago

@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 5y ago
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.
- deleted 5y ago
  [deleted]
- guntars 5y ago
  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.

cldellow 5y ago

If I'm understanding you, you might like pgtyped: https://github.com/adelsz/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.)

ducaale 5y ago

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 5y ago

If you’re ok with a query builder interface that matches postgres you might like Mammoth. https://github.com/Ff00ff/mammoth https://github.com/Ff00ff/mammoth

PudgePacket 5y ago

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-verification https://github.com/launchbadge/sqlx/#compile-time-verificati...

oauea 5y ago

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

throwaway858 5y ago

This library does exactly what you want: https://github.com/MedFlyt/mfsqlchecker https://github.com/MedFlyt/mfsqlchecker and it also validates the types of the parameters that go into the query

spiralx 5y ago

Here you go! https://github.com/codemix/ts-sql 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" }];