Comment by sgarland
4 days ago
Every time I see stuff like this (Google’s new SQL-ish language with pipes comes to mind), I am baffled. SQL to me is eminently readable, and flows beautifully.
For reference, I think the same is true of Python, so it’s not like I’m a Perl wizard or something.
Oh I agree. The problem is that they are two different languages. Inside a Python file, SQL is just a string. No syntax highlighting, no compile time checking, etc. A Kwisatz Haderach of languages that incorporates both its own language and SQL as first class concepts would be very nice but the problem is that SQL is just too different.
For one thing, SQL is not really meant to be dynamically constructed in SQL. But we often need to dynamically construct a query (for example customer applied several filters to the product listing). The SQL way to handle that would be to have a general purpose query with a thousand if/elses or stored procedures which I think takes it from “flows beautifully” to “oh god who wrote this?” Or you could just do string concatenation in a language that handles that well, like Python. Then wrap the whole thing in functions and objects and you get an ORM.
I still have not seen a language that incorporates anything like SQL into it that would allow for even basic ORM-like functionality.
Are you thinking of query generators like Ecto in Elixir?
I am not familiar with them but when I think of query generators I think of the lower level API for SQLAlchemy which is fine but still kludgy as it tries to translate SQL into a new “language” that is less known and less intuitive and still requires you to think in terms of is the data you are working with local or remote.
This is why key-value stores are so popular, I think. They make you do more but with all local data (that is data in your memory not in the database server). SQL can do a lot but because we almost never represent a user object as just a tuple there is a fundamental impedance mismatch between an environment that only deals with tuples in tables and an environment that deals with objects of some kind. Something that can do both at once would be the ultimate. Maybe the way to look at it isn’t to bring the database into your application but to run the entirety of the application inside a database. Imagine if all your business logic could easily be encoded into stored procedures and all you had to do was expose endpoints to draw a UI for it. That might actually work (and I know there are some systems that try this but none are mainstream enough).