Say `SELECT * FROM user LEFT JOIN post ON user.id = post.id` would be mapped to `[{userId: 1, name: renke1, posts: [{postId: 2, title: "foo"]]`.
You probably need some kind of meta data to figure out how tables and thus objects relate to each other though.
Basically, I want to be able to leverage the full power of modern databases without being constrainted by typical ORM limitations. Also, I don't need features like lazy loading, sessions, caches and things like that.
A great advantage is that you can (provided you have some test data) easily test your queries while you develop a new feature (think IntelliJ IDEA where you can simply execute an SQL query on the fly).
And the real issue is how to define and where to place a single source of truth for the schema an operations. So far we saw approaches where:
- GraphQL schema is generated from SQL tables. Makes total sense for a project or a company that looks to capitalize on customers with existing databases (e.g., PostGraphile, Hasura);
- SQL schema is generated from a GraphQL schema;
- SQL schema and TypeScript CRUD resolvers are generated from GraphQL schema (graphback);
- a language is introduced and GraphQL and SQL are generated from that language (Prisma);
- a library and a set of decorators are used to define both GraphQL schema and a typed ORM schema within a standard language (e.g, TypeGraphQL + TypeScript + some ORM such as TypeORM).
hasura.io
The few times I tried it (mostly in tests to check that the ORM is working properly) the #1 thing I was missing is a prettier-plugin that automatically formats SQL in the same way it currently works for `html` tagged templates.
I completely agree to the 'constrained by ORM' and 'useless features' part though. Postgres `json_agg` is a godsend and I love to be able to reason over simple joins and queries.
BTW, my own approach to use `json_agg`, `json_build_object` and json columns within a typesafe query-building DSL is this: https://github.com/hoeck/typesafe-query-builder
But its mostly for replacing simple ORM fetches, it wont do complex analytical queries. For that I'd like to write SQL directly as query-DSLs tend to quickly stop being usable in that situation.
With either library, you aren't writing naked SQL, but something like `query.where("updated_at", ">", Date.now())`. Tsc comes into play when you send typed objects into your .where, .insert, ... methods and what you get back from the query.
It's close enough to SQL that you don't lose expressiveness, you're not behind walls of magick due to orm, and you don't have to worry about Bobby Droptables.
It’s pretty awesome.
The API could be something like this:
Query.sql
SELECT * FROM user LEFT JOIN post ON user.id = post.id
const results = await Query()
const nested = nest(results, {
parentFields: ['userId', 'name'],
childFields: ['postId', 'title'],
childName: 'posts'
)
This is what I'm constantly wondering. At what point does it stop being good to return the user table results again and again and just switch to, for example, an IN query to get the posts?
const result = await db.select(‘user’, db.all, { lateral: { posts: db.select(‘post’, { userId: db.parent(‘id’) }) } }).run(pool);
(Sorry, can’t manage helpful indentation from my phone).
See: https://jawj.github.io/zapatos/index.html#joins-as-nested-js...
Will grouping fields by tables they belong to good enough? Or is there some different grouping logic you have in mind?
…but let's say I have this result (from an arbitrary query).
| user.id | user.name | post.id | post.title |
+---------+-----------+---------+------------+
| 1 | renke1 | 1 | first |
| 2 | alde | 2 | second |
| 2 | alde | 3 | third |
Of course I don't want to write `SELECT user.id, user.name … FROM …` but just `SELECT * FROM …` (because a table may have a dozen of columns and I don't want to spell out every single one). So the query might have to be rewritten on-the-fly to make the correct projection (otherwise it would be hard to know to which object a value belongs).
I am not sure if that's something your library should do though.
And thinking even more about it, I think this approach wouldn't really work for views (and probably other things) where it's not really clear from which tables the data actually comes from (at least not by only looking at the query).
I guess what I really want is library that takes my SQL query, reads my mind and gives me back some nested objects… and let's not talk about inserts…
SELECT user.*, json_agg(post) as posts FROM user LEFT JOIN post ON user.id = post.id GROUP BY user.id
It's not as simple as you'd like (and you have to wrap the whole thing in a subquery + another json_agg if you really want Postgres to return a single JSON string) but it's not actually a lot more complicated, and avoids having to rely on magic that's likely brittle and hard to debug (or only really useful in a small subset of cases).
See tsql string interpolation with typed result sets!
While nice to have, preventing bugs with static SQL is usually easy to do by writing a few tests. Most of the SQL related bugs I have encountered were due to queries with dynamic/conditional joins, filters and sorting - and almost every project using a database needs those.
Approaches like this don't help there. That requires heavy-weight solutions that are more cumbersome to use and need a strong type system, like diesel [2] (Rust), Slick [3] (Scala) and some similar Haskell projects.
[1] https://github.com/launchbadge/sqlx
I've heard the same argument about TypeScript vs JavaScript and it's something dynamic typing proponents often say but in practice I find immense value in having the types autocompleted and checked in the editor - and I've worked plenty on both sides, current project is substantial RoR codebase, I've worked with Python and node.js backends on mature codebases. Eventually all these languages have some sort of static type hinting efforts to improve tooling - typescript being most successful.
The best thing I saw in this space was F# type providers which didn't require a pre-build step - the language had a mechanism for writing custom type providers that would look up the data source during compilation - unfortunately I didn't get to use it on any real world projects.
https://github.com/Zaid-Ajaj/Npgsql.FSharp.Analyzer
https://github.com/aaronpowell/FSharp.CosmosDb#fsharpcosmosd...
PgTyped and some similar libs try to solve a simpler problem (typing static queries) and can be used to build more complex solutions when needed.
Writing query result/param type assertions by hand and using tests to guarantee type synchronization between DB and code wasn't maintainable on most projects I have seen.
[1] https://en.wikipedia.org/wiki/Object-relational_impedance_mi...
The join problems I've seen are either the joined table has changed, or altered. The return type of a field may change.
The hard problem I encountered was doing things like json aggs, multiple joins, etc. I'm trying to address this by doing type safe aggregate/join functions. Secondly is query compilation. Compiling the output record of advanced queries into an automatic data class.
Why not both?
I've been relatively satisfied with TypeORM, but one thing that's been a hurdle for me to some extent is its reliance on experimental decorators, and the resulting incompatibility with Babel - which in turn makes it harder to integrate with the wider ecosystem, e.g. Next.js.
As far as I can see on first glance, there's nothing here yet that makes it incompatible with Babel, so my tip would be to make it an explicit goal to keep it that way :)
Pandora's box has been opened though. Majority of the JS community seem to have adopted Babel in their workflow.
Even as I question it, I must admit it's pretty sweet to have a transpiler in the buid toolchain. It frees one from the browser/backward compatibility question, more or less, and opens up the language to be extensible - for better or worse.
For example, Babel macros ¹ is an interesting concept, using the language to extend the language during compile time.
To bring it back to the topic at hand (PgTyped, Typesafe SQL) - one thing that I'd like to see in TSC is a way to output type definitions (either inlined metadata or external JSON schema), to be consumed for run-time type checking. That would "complete the circle" for me.
TypeScript "compiler plugins" seem to be on the roadmap, or at least under consideration ². Some may see that as opening a proverbial can of worms, down a similar road to Babel. I wouldn't disagree, but it'd be so useful!
Another one in a similar vein with strict typing and really nice SQL interpolation for Postgres: https://github.com/gajus/slonik
- better separation of concerns
- better integration with SQL tools (syntax highlighting, autocompletion, etc)
- way easier to run/test/debug your queries into a database client
- better languages analysis of your projects (e.g. % of SQL in your GitHub/GitLab repo)
--
If anyone interested in applying this approach in your Python projects, I recommend this package: https://github.com/mcfunley/pugsql
I like the idea of code generation instead of doing the work at runtime (like in ORMs). This is like making your database schema the IDL spec.
Also hugsql for clojure and pugsql for python.
sqlc currently has great support for Go and experimental support for Kotlin. I'm planning on adding TypeScript support in the future, so it's great to see that others in the TypeScript community find this workflow useful.
It has a different approach from PgTyped, which generates type-safe TypeScript code from SQL, whereas Go-Jet generates type-safe SQL from Go code
I'd love to try something along the lines of PgTyped and see how the two solutions compare though
Let's have some Show HN etiquette.
If you're looking for the ability to generate type-safe SQL – given you write SQL correctly – this project is pretty good.
Aalso a fan of SQLBoiler (https://github.com/volatiletech/sqlboiler) for Golang, for simple type safety:
`models.Accounts(models.AccountWhere.ID.EQ(id)).One(ctx, db)`.
Though SQLBoiler breaks with left joins, as it auto-generates your structs and maps results 1-1 with table definitions. In this case you have to custom type something, either using sqlc or squirrel.