TypeScript types for returned values

[bfelbo]

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.

[bfelbo]

Only supporting simple column selects would already be amazing. For instance, here's a common query:

await sql`
  select api_level, system, brand from device
`

Knowing that api_level is a number, system is an enum and brand is a string would help catch many bugs.

[JAForbes]

This is relevant to my interests 🎈

[JAForbes]

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?

[Minigugus]

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 * and join), it seems possible, but not painless, and the gain is limited. 😕 Looks like a good idea anyway, a bit off-topic though 😁

Meanwhile, #8 is on the way 😉

[bfelbo]

@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. SELECT u.name, d.phone FROM user u INNER JOIN device d ...), which is good practice anyway. Similarly, * doesn't seem that problematic to me if you have the schema available. This is all static analysis so you don't have to worry about the schema changing underneath.

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 sql. That's needed because it's an independent plugin, which has to work with any Postgres library. However, if a TypeScript plugin was supplied as part of this library, then that wouldn't be needed 😉

[JAForbes]

since the structure of the database is unknown

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.

[bfelbo]

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?

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.

[bfelbo]

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.

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).

[Minigugus]

Why is the structure of the database unknown?

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. 😆

Perhaps a good solution would be make it a separate repo that works only with Postgres.js?

I agree, as that will allow specific query detection with no configuration from the user. It may also be possible to reuse postgres() parameters if statically but I digress 😄

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 😊

[JAForbes]

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 any for anything that isn't trivial.

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.

[JAForbes]

Actually, maybe a replica DB isn't necessary and one can simply use prepared statements and then get the metadata from it without execution

[bfelbo]

Actually, maybe a replica DB isn't necessary and one can simply use prepared statements and then get the metadata from it without execution

That would be amazing!

[GavinRay97]

Super interested in this as well! (and #8)

[moltar]

@slonik/typegen has an interesting approach - worth a look.