Replies: 18 comments
-
Only supporting simple column selects would already be amazing. For instance, here's a common query:
Knowing that |
Beta Was this translation helpful? Give feedback.
-
This is relevant to my interests 🎈 |
Beta Was this translation helpful? Give feedback.
-
How would we handle table joins? Do we need to manually combine fields with union types? Or are you imagining it could be automatic somehow? |
Beta Was this translation helpful? Give feedback.
-
I guess you mean a TypeScript plugin, like ts-graphql-plugin but for SQL ? Despite the fact that SQL information is partial in queries since the structure of the database is unknown (a problem for Meanwhile, #8 is on the way 😉 |
Beta Was this translation helpful? Give feedback.
-
@Minigugus Good point. I guess the right approach would be a TypeScript plugin. I actually think the benefit would be huge. You'll often want to keep complex logic in a Postgres function, which would be easy to convert to TypeScript types. It should also be fairly painless to generate types for table joins if you always specify the table name or an alias (e.g. To put it in differently, I don't see anything in our fairly large node SQL application that wouldn't be covered if functions and column selects with joins were supported, and that doesn't seem that hard to do. I've found https://github.com/xialvjun/ts-sql-plugin, but it's trying to do more than simply return types, which makes it a bit messy. Moreover, it requires wrapping all SQL statements in |
Beta Was this translation helpful? Give feedback.
-
Why is the structure of the database unknown? We can just connect to an instance specified in config, download all the type/column metadata from information_schema/pg_tables and the plugin can reference that. Then if we can parse the queries we can reference the tables in the extracted metadata and get the type information. It's rare for a SQL queries type to be ambiguous, pg will usually complain in that instance. I think this is out of scope for this library. It should be a separate ts plugin that can connect to pg database, obtain schema information, parse queries in source and then annotate the types accordingly. It could be used with any PG lib, and also with https://github.com/hashql/hashql on the client. |
Beta Was this translation helpful? Give feedback.
-
Oh, I just understood what you meant. I'm imagining it would be automatic somehow. I'd have to look more into TypeScript on how to do it, but there's so many type generation libraries/plugins that I would be surprised if it isn't possible to do automatically. |
Beta Was this translation helpful? Give feedback.
-
I get why it would be cool to add as a separate repo that could be used with any PG lib. However, as shown by https://github.com/xialvjun/ts-sql-plugin it's really hard to do without making a very clunky. Perhaps a good solution would be make it a separate repo that works only with Postgres.js? That way you could seamlessly support types for returned values. If it catches on, it would also be easy to extend it to do static type checking of other variables passed to queries without the developer having to change anything (as opposed to e.g. https://github.com/xialvjun/ts-sql-plugin). |
Beta Was this translation helpful? Give feedback.
-
I thought that relying on what is currently in the database is not really good for reproducible results, as the database may not reflect the migrations of the project. But you're right, it's really better for real-time diagnostics and especially for a TypeScript plugin. 😆
I agree, as that will allow specific query detection with no configuration from the user. It may also be possible to reuse import postgres from 'postgres'
const sql = () => 'a trap'
const notSQL = postgres(`postgres://postgres:postgres@localhost:5432/postgres`)
// The plugin can consider the result of the call to the `postgres` function as
// the sql template tag function thanks to the static import of `postgres`.
// Then, `postgres://postgres:postgres@localhost:5432/postgres`
// is used as the connection string :D
const checkedQuery = notSQL`SEL€CT 1 AS x` // Should be checked
const notCheckedQuery = sql`SEL€CT 1 AS x` // Should NOT be checked`sql` If I can find some time, I'll look into it. 😉 Anyway, I'll be happy to contribute 😊 |
Beta Was this translation helpful? Give feedback.
-
I think this is very doable and I'm interested in having a go and working with whoever else is interested. I think the hardest part will be interpreting/parsing expressions that aren't direct mappings to schema columns. Because it requires this plugin/extension to know the pg standard library and any functions / expressions used by 3rd party extensions. Not to mention nested queries and composite fields via I guess the first version can just default to But all this makes me think, it might be easier and more precise (if a little hacky) to have the extension spin up a replica database with no data, so it can execute all queries the first time it sees them, and then simply read the types from the empty result set. Then we don't need to parse the SQL at all, and it will always work with any new PG feature. It shouldn't be a perf issue either if we cache the types against say the hash of the query. |
Beta Was this translation helpful? Give feedback.
-
Actually, maybe a replica DB isn't necessary and one can simply use prepared statements and then get the metadata from it without execution |
Beta Was this translation helpful? Give feedback.
-
That would be amazing! |
Beta Was this translation helpful? Give feedback.
-
Super interested in this as well! (and #8) |
Beta Was this translation helpful? Give feedback.
-
@slonik/typegen has an interesting approach - worth a look. |
Beta Was this translation helpful? Give feedback.
-
@bfelbo @JAForbes You might want to checkout PgTyped, which supports generating strictly typed TS code for SQL queries of any complexity. It also supports two usage modes: SQL queries can be defined directly in TS files using an |
Beta Was this translation helpful? Give feedback.
-
I just created a TypeScript plugin for the EDIT: Here is the plugin repo: https://github.com/Minigugus/postgres-ts-plugin |
Beta Was this translation helpful? Give feedback.
-
@frigus02 also has created It's part of the VS Code Extension "SQL tagged template literals" https://marketplace.visualstudio.com/items?itemName=frigus02.vscode-sql-tagged-template-literals |
Beta Was this translation helpful? Give feedback.
-
Seems like references the other way don't work but a different approach can be seen in #276. There you have to specify the types ahead of time in a javascript object and then get typechecking and a check at runtime that the types actually still match. May not be what you want depending on your use case. |
Beta Was this translation helpful? Give feedback.
-
It would be amazing to combine this library with something like https://github.com/SweetIQ/schemats or https://github.com/nettofarah/postgres-schema-ts to get types for queries.
The other libraries could do the heavy lifting with Postgres.js looking up the types from the TypeScript interfaces that those libraries generate.
Beta Was this translation helpful? Give feedback.
All reactions