[HN Gopher] Show HN: PgTyped - Typesafe SQL in TypeScript and Po...
___________________________________________________________________
Show HN: PgTyped - Typesafe SQL in TypeScript and Postgres
Author : alde
Score : 181 points
Date : 2020-05-25 16:29 UTC (6 hours ago)
(HTM) web link (github.com)
(TXT) w3m dump (github.com)
| hombre_fatal wrote:
| Almost every top-level comment is someone shilling another
| project, usually in Golang as if that's even related.
|
| Let's have some Show HN etiquette.
| shrumm wrote:
| Looks a little like the Typescript equivalent of Xo
| (https://github.com/xo/xo) for Go. Especially with Go, getting
| help with some initial scaffolding can be a huge timesaver. I'm
| assuming it's a similar gain for Typescript.
| tadasv wrote:
| A better equivalent in Go is https://github.com/kyleconroy/sqlc
| shrumm wrote:
| thanks! I'll try this - one of my pet peeves with Xo is
| handling nullable types and more advanced types like JSONB
| required editing the generated code significantly to make it
| work. Hopefully sqlc solves that.
| Vinnl wrote:
| Lots of comments here about similar projects in a different
| language, but the fact that this targets TypeScript is explicitly
| what makes it interesting to me. Using regular Javascript
| database libraries, even ones that have type definitions, require
| a lot of double typing.
|
| 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 :)
| smallnamespace wrote:
| As usual for Babel, there's a plugin for that:
| https://github.com/leonardfactory/babel-plugin-transform-typ...
| mikewhy wrote:
| Opening a can of worms for sure, but the reliance on Babel in
| the JS community is not a good thing to me. It's another reason
| why I prefer TypeScript, as in TSC, not whatever equivalent
| babel happens to support.
| zelly wrote:
| Is there anything like this for Rust or C++?
|
| 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.
| K0nserv wrote:
| There's Diesel[0] for Rust which is a full ORM. It's by Sian
| Griffin[1] who, as I understand it, is also behind a lot of how
| rail's ActiveRecord works.
|
| 0: https://diesel.rs/
|
| 1: https://twitter.com/sgrif
| status_quo69 wrote:
| Just to clarify a bit for other readers since I've worked
| with diesel for while, diesel isn't a "full" orm, as there
| are no real helpers provided to you outside of "we can map
| the result of a db query into a struct(s) that you specify"
| and some really nice guarantees for compile time queries.
| Other than that, your struct is a pretty dumb mapped
| representation and it's on the implementers of the
| application code to provide sugar for better access patterns.
| For people coming from something like active record, this is
| (in my opinion) closer to Arel than ActiveRecord, or closer
| to sqlalchemy core than sqlalchemy orm. As an example, you
| won't necessarily be able to do `MyStruct.join(OtherStruct)`
| and have it magically figure out how to query the database
| and map the results out of the box.
| status_quo69 wrote:
| Clarification: compile time query building, not querying.
| Due to inlining from the compiler, you can almost entirely
| construct the query at compile time and shave it down to a
| few string concatenations.
| aganame wrote:
| https://github.com/launchbadge/sqlx perhaps
|
| Also hugsql for clojure and pugsql for python.
| alde wrote:
| Sqlx looks nice! I had some ideas about porting pgtyped to
| rust and utilizing macros to do query type inference at build
| time, but was worried that such db-connected macros will slow
| down the build process. Nice to see that it worked out for
| sqlx.
| emanuelez wrote:
| You might also want to check Kanel out!
| https://github.com/kristiandupont/kanel
| garrybelka wrote:
| How is it different from Slonik? https://github.com/gajus/slonik
| SenpaiHurricane wrote:
| lol. This reminds me Hibernate xml mappings :D
| JBReefer wrote:
| So basically Dapper TS? I'm very interested!
| tonyhb wrote:
| This is similar to sqlc for Golang:
| https://github.com/kyleconroy/sqlc
|
| 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.
| ksashikumar wrote:
| Looks cool! And the header image looks awesome! Did you use any
| tool to do it?
| alde wrote:
| Thanks! Not really, just basic vector shapes and an isometric
| projection grid to make sure perspective is right.
| the_duke wrote:
| There are some similar projects, like sqlx [1] for Rust. My
| problem with these is that they don't help to solve the actually
| hard problems.
|
| 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
|
| [2] https://github.com/diesel-rs/diesel
|
| [3] https://scala-slick.org/
| sradman wrote:
| Actually, after my quick scan of the readme, I think pgTyped
| takes a different approach than the other tools listed. It is a
| YeSQL-style tool with build-time code generation. The code
| generation is based on prepared statement metadata rather than
| table metadata. Like other YeSQL tools, the function name comes
| from a DocComment annotation in the .sql but the query params
| and the result set columns come from the prepared statement.
| soulnothing wrote:
| I'd be curious to hear more about the issues with joins, and
| dynamic conditionals. I've been working on a type provider lib
| for sql in kotlin[1].
|
| 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.
|
| 1: https://gitlab.com/AnimusDesign/kotlin-frm
| rubber_duck wrote:
| > preventing bugs with static SQL is usually easy to do by
| writing a few tests
|
| 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.
| alde wrote:
| ORMs like Diesel are definitely very useful. The problem I have
| with them is that their ORM abstraction often leaks. Fixing
| these abstraction leaks is a hard problem [1]. Ofc, there has
| been attempts to reconcile relational DBs with OOP like
| languages, but they are not very popular. [2]
|
| 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...
|
| [2] https://en.wikipedia.org/wiki/The_Third_Manifesto
| Dowwie wrote:
| While the scenario you present _is_ a legitimate reason for
| using a query builder, it doesn 't justify the investment in
| using a query builder for the vast majority of db calls. The
| edge case is not an appropriate goal to impose the cost of
| using a DSL for the majority of cases. Instead, custom roll an
| implementation for those complicated calls and stick with a
| parameterized sql library.
| conroy wrote:
| As a maintainer of a similar project[0], it's great to see
| another entry in this space.
|
| 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.
|
| [0] https://github.com/kyleconroy/sqlc
| Allezxandre wrote:
| My favorite SQL library has been Go-Jet in Go:
| https://github.com/go-jet/jet
|
| 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
| eyelidlessness wrote:
| I came here to mention a similar approach, which last time I
| looked was a very compelling experiment[1], but its original
| author has actually built out a real library, Zapatos[2] which
| looks very very good.
|
| [1]: https://github.com/jawj/mostly-ormless
|
| [2]: https://jawj.github.io/zapatos/
| alde wrote:
| Looks like Zapatos still requires the user to manually specify
| param/result types for custom SQL queries?
| gmac wrote:
| Zapatos author here. Yes, it does. But for most of what you'd
| use an ORM for, you probably won't need custom queries.
| eyelidlessness wrote:
| Hey I'm not sure this is the best venue, but I'm trying to
| make the case for getting my org off of sequelize, and your
| library is right in line with my goals. The hardest sell is
| going to be publicly visible test coverage. Would you
| welcome a dedicated effort from an early adopter to
| introduce tests?
| renke1 wrote:
| Looks pretty cool. What I really want though is a library that
| let's me write plain SQL queries which are then mapped into
| nested objects in a smart way without too much manual work (I
| know Postgres can do JSON stuff, but the queries look pretty
| complicated for what little they actually do).
|
| 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).
| timmy-turner wrote:
| Writing literal SQL in JS would IMO need more tools than just a
| preprocessor like you describe.
|
| 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.
| [deleted]
| cyral wrote:
| One of my favorite features of WebStorm is the (official)
| database plugin which highlights SQL queries inside JS
| strings AND has autocomplete and refactoring support that
| actually uses the live database schema.
| hyeomans wrote:
| You never mentioned the language, so maybe Dapper?
| https://github.com/StackExchange/Dapper/
| chatmasta wrote:
| We've accomplished this with a combination of postgraphile and
| graphql-codegen. Our setup is basically the same as the
| postgraphile starter app. [0]
|
| It's pretty awesome.
|
| [0] https://github.com/graphile/starter
| tlarkworthy wrote:
| It exists! You want Scala Slick plain SQL http://scala-
| slick.org/doc/3.0.0/sql.html
|
| See tsql string interpolation with typed result sets!
| raziel2p wrote:
| You'd be constrained by whatever is trying to parse SQL queries
| to figure out what object structure to return instead.
| bijection wrote:
| If you were willing to give up a bit of magic, you could
| probably build this as a thin layer over PgTyped.
|
| The API could be something like this:
|
| Query.sql SELECT * FROM user LEFT JOIN post ON
| user.id = post.id
|
| Application.ts const results = await Query()
| const nested = nest(results, { parentFields: ['userId',
| 'name'], childFields: ['postId', 'title'],
| childName: 'posts' )
|
| If you wanted, you wouldn't really have to specify child
| fields, since they'd just whatever wasn't a parent field. It'd
| take a bit more work to get it to do multiple levels of
| nesting, but after a point it doesn't make sense to write
| queries that return so much duplicate data anyway.
| alde wrote:
| Thanks! A grouping feature will definitely be useful, I have
| been thinking about a good way to add it to pgtyped.
|
| Will grouping fields by tables they belong to good enough? Or
| is there some different grouping logic you have in mind?
| renke1 wrote:
| That's hard to say, because I am not sure what I really want.
|
| ...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 |
|
| Now I would like to tell the library: hey, an user can have
| many posts (1:n), please map this to nested objects.
|
| 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...
| gigatexal wrote:
| but select * is an anti-pattern. SQL queries should only
| return the columns that you need not that you might need --
| usually. Lazy loading and sessions etc., like SQLAlchemy
| does to get around the N+1 query problem be damned.
| renke1 wrote:
| I didn't mean the `SELECT STAR` in a literal sense, but
| more like, please select only the stuff we need. But
| indeed with PgTyped, as far as I understand it, it
| wouldn't work because it creates interface from SQL
| queries.
|
| To achieve what I want you need to do it like all the
| other ORMs where you have some kind of description of
| your model and how it maps to tables and columns. Unlike
| the usual ORM I want to write SQL queries which are then
| rewritten in an intelligent manner.
|
| So something like query(`SELECT STAR FROM user LEFT JOIN
| post ...`, UserWithPostsModel). Since the library would
| know the target model, it could rewrite the `SELECT STAR`
| to something that only asks for the data it needs.
|
| In other words I want to execute arbitrary queries that a
| mapped into ad-hoc models (unlike typical ORMs where the
| model usually maps directly to tables).
| STAR = *
| gigatexal wrote:
| Ahh I understand you now.
| garrybelka wrote:
| GrahpQL might be an answer.Though an incomplete answer at this
| point due to a mismatch between plain GraphQL and SQL;
|
| 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).
| chrischen wrote:
| See https://typeorm.io/#/
| eyelidlessness wrote:
| I mean no disrespect to the TypeORM author(s), but I was
| burned several times by unexpected behavior that resulted in
| serious data integrity issues. I would caution that anyone
| adopting this library test any usage heavily, especially
| anything to do with relations
| rak wrote:
| MongoDB support is a work in progress for them too but
| otherwise a fun library.
| gmac wrote:
| Zapatos can generate the hairy JSON stuff for you, including
| lateral joins that are equivalent to your example query, which
| would be: const result = await
| db.select('user', db.all, { lateral: { posts: db.select('post',
| { userId: db.parent('id') }) } }).run(pool);
|
| And result will have the structure you asked for, and be
| automatically typed as such.
|
| (Sorry, can't manage helpful indentation from my phone).
|
| See: https://jawj.github.io/zapatos/index.html#joins-as-nested-
| js...
| renke1 wrote:
| Thanks, Zapatos looks really nice. That library seems to be
| basically what I want (aside from writing SQL for joins, but
| I think it's just not possible in the way I imagined). The
| documentation is really nice. Much of what is written in it
| aligns with my thinking. I'll definitely give it a try!
| goliatone wrote:
| You should check objection.js[1] as I think it would get you
| what you describe and probably more. It is a relational query
| builder built on top of knex, which is a query builder.
|
| [1] https://vincit.github.io/objection.js/
| mceachen wrote:
| I wrote the original typescript bindings for objection. One
| of the library authors is a contributor to knex.js, which
| objection uses for query building. Both are quite nice.
|
| 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.
| renke1 wrote:
| Thanks, I'll check it out. But on first glance it doesn't
| seem to let me write actual SQL queries. Also, it doesn't
| seem to be typesafe, or is it?
| chrischen wrote:
| It says it has typescript support.
| renke1 wrote:
| I didn't check those typings, but I don't think the model
| itself is type safe from the looks of it.
| mceachen wrote:
| It is. See my ggp reply.
| adriancooney wrote:
| I really like the unique approach of the annotated SQL files and
| can definitely see some use cases where it would be good to
| declutter the SQL from the code. For me personally, I'd be
| hesitant to add another build tool to my already bloated
| toolchain. Could create a special Babel-style "import" type that
| automatically transforms your code (JIT)? It could remove some of
| the friction in adoption (for Babel users at least).
|
| Another one in a similar vein with strict typing and really nice
| SQL interpolation for Postgres: https://github.com/gajus/slonik
| ggregoire wrote:
| Same, I really like this approach. Some of the benefits are:
|
| - 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
___________________________________________________________________
(page generated 2020-05-25 23:00 UTC)