8 ms·
> I just want my SQL back. It's a language everyone understands, it's been around since the seventies, and it's reasonably standardized. It's easy to read, and
by dgellow 6y ago
> I just want my SQL back. It's a language everyone understands, it's been around since the seventies, and it's reasonably standardized. It's easy to read, and can be used by anyone, from business people to engineers.
I rely a lot on SQL and in general advocate for it, but that’s too simplistic of a view IMHO. SQL makes it easy to write and read simple queries, and ridiculously complicated and arcane to write slightly more complex logic.
More than once I’ve been asked to help fix giant SQL queries written by a business or analytics team and that’s a terrible experience.
Also, the tooling around SQL is often terrible and almost didn’t evolve during the past 15+ years. SQL queries from another programming language without an ORM means that you do everything by manipulating strings by hand, with zero type safety.
Edit: My favorite library in Go is reform, a generator that generates types and implement the Scanner/Valuer interfaces. You annotate your structs, generate the types, add the generates files to git, done. That way you have very limited ORM magic but gain some type safety. And you still have complete control over your SQL queries (which is often frustrating to do with classic ORMs).
https://github.com/go-reform/reform https://github.com/go-reform/reform
Edit 2: the most promising ORM I’ve seen is Prisma, https://www.prisma.io/ https://www.prisma.io/. I haven’t tried yet, I’m still quite attached to writing most of my SQL by hand because that’s what I know well, but their pitch seems good to me.
- slcjordan 6y agoInteresting. I've been looking at https://github.com/bokwoon95/go-structured-query https://github.com/bokwoon95/go-structured-query but I'll have to look at reform too, now. Thanks.
- dgellow 6y agoReform documentation isn't always that good but the implementation and generated code is not too difficult to read when trying to understand how everything works. Thanks for sharing go-structured-query, I didn't know about it.
- fouric 6y ago> SQL makes it easy to write and read simple queries, and ridiculously complicated and arcane to write slightly more complex logic. This is consistent with my experience, and exactly backward of what you want a language to be - which is good scaling of solution complexity as a function of problem complexity, even (although ideally not) at the cost of upfront effort required to learn.
- dgellow 6y agoJust to add a thought: really what I would like to have is a language that has native support for SQL. (I mean, I also want a better alternative to SQL, but the post author is right that it is the standard we currently have and it will not disappear or be replaced soon) That sounds absurd, but my dream would be to write Go (just my personal favorite), then whenever I want to interact with the DB I can directly write actual SQL, not as a string, but as an actual valid expression. I have zero idea how that would work in practice but that’s the dev experience I would love to have. SQL as a DSL, written in my go file, without the need to change context, with syntax highlighting, type check, linting, etc. Instead of having SQL as a second-class language it could be first class and that would be fantastic. Not that something like this will ever happen, I’m well aware that standard SQL isn’t an actual thing in the real world and all the other issues around that idea, but I would LOVE this!
- et1337 6y agoLINQ from .NET was pretty close to this. You could write a variant of SQL directly inline with your C#. I think technically there was also an ORM layer (Entity Framework) but for the most part it was just a query builder.
- teh_klev 6y agoLINQ is still very much alive and well. I think you're thinking of LINQ to SQL which was an ORM in its own right. Then along came entity framework which also supports LINQ querying syntax (or you can use expressions).
- davidspiess 6y agoSQLC could be a good fit. It generates type-safe Go code from SQL. https://github.com/kyleconroy/sqlc https://github.com/kyleconroy/sqlc
- dgellow 6y agoWow, that's interesting. Thanks for sharing, I will look into it!
- pwinnski 6y ago> SQL makes it easy to write and read simple queries, and ridiculously complicated and arcane to write slightly more complex logic. This is the thing that makes ORMs sound appealing: let's make the complicated things simple! In my experience, the complicated things are truly-complicated, not just because SQL makes them seem to be. So by the time you make Use Case A "simple," you've run into Use Case B and Use Case C, and by the time your ORM handles all of those, well, there's a good chance it still doesn't handle all of them, and however many it does handle, the syntax ends up being... complicated. There is so much to be said for a known and well-supported standard, and we are so quick to assume we can do better. Most of them, we really can't. But hey, keep trying! Coming up on 50 years, we should manage to surpass SQL at some point, right? And in the meantime, we'll have our five-thousandth ORM or DSL to reach the limits of and have to revert to SQL anyway for just those last two or three queries.
- workingworkguy 6y agoI'm one of the analysts causing issues for the OP, but a common example to me of something that's cake in a "real" language and frustrating in sql (ms in my case) is expanding a function to take a dynamic amount of params. myFun param = for each param do... Something like that has given me WAY more headaches than i ever expected, and that's before i saw vendor code (for systems that are used by millions) handling business logic in the stored proc with a 70 case switch statement...
- megagator 6y agoDatabases are generally better at storing data, not logic.
- hobs 6y agoThat's what adhoc/dynamic SQL is for if you must, and power it with another language - you are trying to loops in a set based language, its the opposite of what its good at.
- pwinnski 6y agoThat sounds like switch abuse, but a better-written query builder seems like a good solution here. Use `for each param` to build a SQL query, and you're golden.
- stickfigure 6y ago> SQL queries from another programming language without an ORM means that you do everything by manipulating strings by hand, with zero type safety. This is not true at all. If you connect IntelliJ to your database (which you should - the query tools are fantastic), it becomes incredibly good at validating SQL strings in code. Even when doing crazy conditional construction. I like ORMs. I use Hibernate a lot. But I write all my queries in native SQL.
- dgellow 6y agoDo you have a link to Intellij documentation for this feature? That sounds interesting.
- andylynch 6y agoHere you go: https://blog.jetbrains.com/idea/2020/06/language-injections-in-intellij-idea/ https://blog.jetbrains.com/idea/2020/06/language-injections-... (Overview with link to the full docs) It’s been a while since I’ve used it but it works great (and not just for SQL but pretty much anything IntelliJ knows about).
- dgellow 6y agoThank you!
- ragnese 6y ago> I rely a lot on SQL and in general advocate for it, but that’s too simplistic of a view IMHO. SQL makes it easy to write and read simple queries, and ridiculously complicated and arcane to write slightly more complex logic. > > More than once I’ve been asked to help fix giant SQL queries written by a business or analytics team and that’s a terrible experience. > > Also, the tooling around SQL is often terrible and almost didn’t evolve during the past 15+ years. SQL queries from another programming language without an ORM means that you do everything by manipulating strings by hand, with zero type safety. But this is all beside the point. Yes, SQL kind of sucks. But what sucks more is having a leaky query language on top of SQL such that you have to learn the query language AND SQL. Because- let's face it- you almost always end up printing out the SQL that the damn thing generated to figure out why it's doing something weird. You almost always end up needing to drop to SQL anyway. And next year there will be a new awesome ORM/query-language that you're expected to learn all the pitfalls of.
- dgellow 6y agoIt's not beside the point, it's the other face of the discussion. Building and calling SQL queries from a programming language without an ORM often sucks. SQL is not the simple language the author seems to talk about. ORMs have leaky abstractions. All of this is true.
- ragnese 6y agoBut my view at this point in my life is that SQL is SO not a simple language, that any and all ORMs are going to be way too restrictive and leaky. There's all kinds of weird crap that SQL does when it comes to nulls, empty strings, falsey/truthy things, dates as numbers or strings, etc. And that's not even thinking about unions, stored procedures, weird types of joins, etc. My current comfort zone is if I can find a query builder that has enough static typing that it has all of the keywords of my preferred SQL flavor, has prepared statements with placeholders- mainly for safety/security, and basically returns a string when you're done. At least then I'm just dealing with SQL instead of figuring out Hibernate's arcane caching feature or why in the FUCKING FUCK JDBC will return a `0` if a column was an int value that was `null` in the result set. IT WAS NULL- GIVE ME NULL.
- sa46 6y agoYou might like the approach I took with pggen[1] which was inspired by sqlc[2]. You write a SQL query in regular SQL and the tool generates a type-safe Go querier struct with a method for each query. The primary benefit of pggen and sqlc is that you don't need a different query model; it's just SQL and the tools automate the mapping between database rows and Go structs. [1]: https://github.com/jschaf/pggen https://github.com/jschaf/pggen [2]: https://github.com/kyleconroy/sqlc https://github.com/kyleconroy/sqlc
- dgellow 6y agoThanks for sharing!
- incog_nit0 6y agoAnecdata but our design agency decided to branch out beyond fully local Wordpress sites to something more cutting edge using Prisma + React. It had a great developer experience but was slow and had poor pagespeed and usability scores compared to the standard Wordpress sites they were providing clients. Their next site was a bog standard all local Wordpress job although they've since moved onto React/Gatsby.
- namdnay 6y agoWhat types of sites are these? I’d say React and co should be kept for webapps, not websites. If it’s just static content or a simple shopping site, Wordpress is going to beat any client-side system hands down
- grumblestumble 6y agoThere's a whole ecosystem around using React for static site development now, this is exactly what Gatsby is if I'm not mistaken. Use React as a more powerful templating system for authoring content, but generate most of it out as static HTML/CSS, while still having the capability for individual components to have interactivity / data fetching post load. Also, I think this is what's going on with NextJS and React's server-side-components stuff. Having solved client-side webapps, the world has now turned to reinventing Cold Fusion...
- namdnay 6y agoOh god we’re back there again are we?
- namdnay 6y agoBack in my Oracle days, I remember wishing there was a way to directly code “execution plans”, instead of faffing around with SQL, which was just (from my perspective at least) a complicated abstraction on top of them ...
- Scarbutt 6y agoI really don't see what's promising about prisma. Lots of condescending stuff in their docs making SQL sound as something only a few can learn. It's extremely over-hyped, immature and buggy. But they are a startup so...
- keithnz 6y agoin C# (using Rider) I do all my queries in .sql files, fully connected to a real database with full auto complete (including auto writing your join), there's a section to define all the variables the query is expecting, and then, then I have a wee open source lib for embedding all your queries into your program and an easy way to get them out then use things like Dapper or RepoDB to ruin the queries with parameters. No string based sql at all. works well. https://github.com/keithn/katoa.queries https://github.com/keithn/katoa.queries
- magicpointer 6y agoA JVM library in this space I recently started using seriously and fell in love with: jOOQ. It's not an ORM, rather a query builder but an extremely smart one. In the codegen mode, it scans your DB schema and generates record classes + a lot of utilities. If the DB is well done (and it should be), it interprets many constructs, including relationships, domain types and various constraints. It can also generate activerecord-like classes if needed. It allows far better safety and composability than raw strings and a lot of control on the query. Most DSL functions are called the same as in standard SQL, and the docs always shows the DSL next to the SQL version. Everyone in the Java world seems to reach for JPA directly, but for me working with something closer to the DB is really a breath of fresh air. The DB-firat approach really works wonderfully.
- gregopet 6y agoAnother jOOQ fanboy here - it's not just the manual that shows DSL->SQL, you can just .toString() most of jOOQ constructs in your code and get the raw SQL out. In fact, I believe you can just take that .toString() and put it straight into your prepared statement if you don't want jOOQ to run your queries and just use it for query composition.
- lukaseder 6y agoLike any toString() implementation, it is meant for debugging. If the object you're calling toString() on is "attachable" (e.g. a Query), then you get the vendor specific string for additional convenience. If you just call substring(a, b, c).toString(), you'll get a generic rendering. If you always want the vendor specific SQL string, use DSLContext.render(QueryPart)