Alternative Solution

[vitaly-t]

By the way, everything that you do here is easily achieved with the pair of pg-promise + schemats.

[phiresky]

@vitaly-t Maybe I misunderstand schemats, but from what I see it doesn't even come close to what this library is able to do.

For example, I can write this query:

SELECT
  customers.country,
  COUNT(orders.*) AS "orderCount"
FROM orders
  LEFT JOIN customers ON customers.id = "customerId"
GROUP BY country

as

query = sql.from(orders)
    .leftJoin(customers).on({ id: orders.customerId })
    .groupBy(customers.country)
    .select(customers.country)
    .select(orders.$all.count().as("customerCount"))

I get full autocompletion while I type, and the return type from that query is automatically and correctly inferred to be

const ret = await exec(query);
/*
typeof ret
{
    country: string;
    customerCount: number;
}[]
*/

Exactly those two properties that are actually returned by the specific select / join, not more or less.

It seems like with schemats I would have to write the query basically twice, once in the type level and once as SQL.

[vitaly-t]

Oh, I see. You are mostly right, I didn't get into it in details.

It seems like with schemats I would have to write the query basically twice...

No, within pg-promise you typically place all queries inside external query files, and then parameterize them to generate anything you want. See pg-promise-demo as an example.

[phiresky]

pg-promise-demo doesn't use schemats at all, correct? The functions in https://github.com/vitaly-t/pg-promise-demo/blob/master/TypeScript/db/repos/users.ts#L29 all seem to return any. Also there aren't any more complex queries (i.e. joins).

would have to write the query basically twice

What I meant with this is basically seen in this example from the schemats readme:

The type definition of the return of the query is manually written to match the actual SQL query, there is no check if this is correct. If the query is changed, the type needs to be changed. It also incentivizes just select *ing everything because otherwise you have to paintstakingly type every parameter manually. Parameter renames (or even just parameters that have case-sensitive names) become error-prone and there is no way to verify the correctness without running the function. If you refactor the database schema like rename a column, with this library your queries can automatically adjust, and conflicting columns are impossible.

Also, parameterized queries in pg-promise are completely unchecked, you can pass a Date or a unicorn to an integer column and it will blow up at runtime. Not even the parameter count or naming is checked, which has caused me problems in the past.

[vitaly-t]

pg-promise-demo doesn't use schemats at all, correct?

Yes, correct, as schemats, is a separate library.

Also there aren't any more complex queries (i.e. joins).

No, those examples can be found in the schemats itself.

As for the rest, it has no ORM features, as it is not an ORM, the library only formats and executes queries. And all the data changes are referred to tests to be detected.

[phiresky]

it has no ORM features, as it is not an ORM, the library only formats and executes queries.

True, though I'd say this library isn't ORM either, it does not do anything more at runtime than pg-promise except converting the typed query syntax, which is basically a more elaborate form of parameterized queries, to SQL text; no instantiation / deserialization or object management of any kind. Complete control over the DB schema and queries sent to the server remains with the user.

Anyways, thanks for stopping by :)

[vitaly-t]

except converting the typed query syntax

It is an ORM feature ;)

Anyways, thanks for stopping by :)

Cheers! :)

[hediet]

Yeah, I think all questions were answered pretty well - thank you @phiresky! The typed query syntax may be an ORM feature (as well as SQL queries in any form are an ORM feature), but this library is not nearly close to being an "Object Relational Mapper".