If SQL is to databases as Javascript is to browsers — ubiquitous and largely standard across the market — are there any languages that transpile to SQL?
People in this thread have commented that SQL is clunky, and JS definitely fit that description for a while. So I'm wondering if there are any alternatives that would prove SQL's clunkiness to me.
Just like JavaScript is not perfect, SQL is not perfect. I would rather have a general purpose programming language like Scheme to run queries against my database.
I'm an engineer (the non software kind) that works in an industrial plant. I often have to do ad-hoc querying manipulation etc of data from a lot of diverse sources. Usually to do fault finding and analytics - sometimes also for reporting.
I don't know about transpiling but I use SAS to work around SQL's clunkiness. I can embed SQL seamlessly into my SAS code so it works well. I use SQL for things that are simpler in SQL (selecting, filtering, aggregating data and "case when" statements etc) and I use SAS's data step language for when I have to do more complicated stuff with the data like Arrays, loops, macros, weighted averaging, time series based stuff and regressions.
The thing I love about SQL is it is the lowest common denominator for all of my organisations data it doesn't mater if data is stored in an Oracle table, a Microsoft SQL Server table, or even Access DB or spreadsheet. The language to extract/merge/join all the data is the same and standardized.
We also have things called "Data historians" which are basically time-series datastores (usually they are linked to PLC vendors) and nothing frustrates me more when they use some proprietary "sql like" language which seems similar to ANSI SQL except things like aggregations (SUM, AVG, MAX etc) don't work or they do weird things with data types (especially dates) or similar everyone of these seem to be subtly different - annoying.
ActiveRecord transpiles from Ruby to SQL. Django ORM and SQLAlchemy are popular ones to write SQL in Python. Ecto is from Elixir. Every language has its own transpilers. Not all of them are easy to use and they are much harder to use than SQL for complex queries, maybe even impossible to build them.
Frankly I'm a little fed up to have to learn a new way of generating SQL for every language and framework. I'm working on or maintaining projects in Rails, Django, Web2py and Elixir for my customers and I'm coming to wish we were using plain SQL. After all I knew SQL long before any of those other technologies existed, I know how to create prepared statements and how to use positional parameters to avoid SQL injections. Then there is how to efficiently extract the data in the resultsets, create data structures in the original language and possibly cache queries. They don't look so hard (maybe handling joins gracefully.)
I've heard good things about Ecto's approach (Elixir). Would you say it's at least better than many other ORM's, or is it similar enough in regards to drawbacks/advantages compared SQL that this 'better' doesn't matter much?
Ecto is well engineered but it's an overkill for simple queries and it makes them harder than it should. It's ok for average sized queries (maybe composed over a pipeline). It's useless for very complex queries, but every ORM can't do them. Think UNION, HAVING, etc. I write them in SQL and then handle the resultset, which is not in the transpiler. The transpiler is a subset of the functionality of an ORM.
People in this thread have commented that SQL is clunky, and JS definitely fit that description for a while. So I'm wondering if there are any alternatives that would prove SQL's clunkiness to me.