6 ms·

It's time for a new relational query language(s) to compete with SQL. We need something that's more API-based rather than using custom key-words in a COBOL-esqu

by tabtab 9y ago

It's time for a new relational query language(s) to compete with SQL. We need something that's more API-based rather than using custom key-words in a COBOL-esque way. With the API-ish approach, Vendors (and DBA's) may still add proprietary functions/methods, but the syntax itself could be consistent across vendors. It's roughly comparable to using XML to define a domain-specific sub-language: a standard XML parser can be used to parse it even though specific tags or attributes are added by the vendor/domain. The syntax charts for SQL are pasta-city. My favorite SQL-replacement candidate is SMEQL (formerly TQL). One of its features is using tables, real or virtual, to describe the attributes of schemas and RDBMS settings rather than language text. This also makes it easier to "query" commands, giving one meta abilities/abstraction.

manigandham 9y ago
SQL is fine, most of the quirky way of stating things is because it's declarative, which actually makes it very powerful. The biggest problem is that the standards move so slowly that many of the database implementers came up with their own dialects. The latest official SQL:2016 is actually pretty great and it would be better if all the various databases converged back to that, however it's probably too late for that now, let alone a whole new language...
- tabtab 9y ago
  The API/functional-versus-keyword issue is not about declarative versus imperative. Those are mostly independent issues. Therefore, I'm not following you. Perhaps there's a miscommunication.
  - manigandham 9y ago
    SQL already has UDFs and even custom types. Those can be namespaced and customized with built-in implementations by all databases, which they all already do to some extent (eg: system data queries) so it seems the only real issue is the fact that the SQL dialects are different. If the SQL dialect was the same across all databases, then what would be the advantage of an "API" approach as you say?
    - hderms 9y ago
      I personally feel like there's still room to build something that's more terse, yet equally as powerful as SQL. get name, age -> (foos.id X bars.foo_id) |> get first(name) |> head as opposed to select name from (select foos.name from foos inner join bars on foos.id = bars.foo_id) A order by created_at ASC limit 1; more regular syntax that looks closer to conventional programming would be nice. At the very least it would probably be easier to write ORM style libraries for as the grammar would be closer together and more regular
      - manigandham 9y ago
        That looks like going back to imperative/functional instead of the declarative syntax of SQL... Have you used C# before? I think the LINQ query syntax could be a good evolution for some uses cases, but it again goes back to decades of dialects already being defined. Seems anything new would have to be implemented as some kind of proxy that then maps back to SQL or uses low level db access, but then why not just use an ORM or query builder in the first place and query from inside a language?
      - fusiongyro 9y ago
        Part of the point of SQL is that the database may choose to satisfy your query in a very different manner than you might expect given the procedural reading. Your example query is a nice illustration; a modern relational database may move your project/select steps earlier in the processing as long as it doesn't change the declarative meaning. Doing this kind of rearranging with a pipeline-looking query language is going to surprise people because you're giving the majority of people who think procedurally a false affordance. This is the fundamental "impedance mismatch": the ORM is trying to put a low-level wrapper around a higher-level idea. OO languages are still row-by-row procedural systems where relational databases are set-oriented. If the target language is Haskell or Ruby or another "sufficiently DSL-capable language" it will be possible to make an internal DSL that encapsulates your query. However, in that case I think you'll surprise your users when you either have non-obvious limits to how you can intermix query with in-language code, or you'll surprise them with how badly your database performs compared to Postgres. You can see a little of both in the story of RethinkDB. If you are not using an internal DSL, you'll be stuck in the same situation as SQL where you are embedding your query language in the code somehow. Relational databases are not just storage technology with a complicated query language. They are also integration technology. SQL's surface syntax may be unfortunate, but I'm increasingly doubtful that there is going to be a serious general-purpose database competitor that manages to hit the same notes only better. The main contender from the 90s was OODBs; they managed to have both shitty performance and lacked the ecosystem that makes SQL so useful as an integration technology: your application may be the main actor, but there are usually a large number of supporting cast members like cron jobs, reports, little scripts, ad-hoc queries, backup and restore etc, and having to do all of that with an OODB is very painful. There are now and will continue to be niches where relational databases suffer, the prominent one today being distributed data storage. But the problem you think is a problem really isn't a problem. Industrially speaking, "I don't like SQL" is a non-problem. For people who hate it, there are ORMs that make it possible to ignore it most of the time for better or worse (mostly worse, but whatever). Syntactically, the main problem with it is, as another commenter stated, different dialects that behave slightly differently. This turns into a major engineering annoyance, but one that is mostly swallowed by your library dependencies, who have by now already basically taken care of it. The benefit of using a modern relational database (I'm talking about Postgres but it applies to SQL Server and probably others as well) is they already have hundreds or thousands of man-years of effort put into optimizing them. I really thought RethinkDB had a brilliant design and it was going to be the next generation database. But it performed worse than Postgres and that makes it basically a non-starter. This is part of why niche databases are not based on SQL: if you handle a niche well, I will tolerate your oddities, but if you want to build a new general-purpose database today, in 2018, you can't just have a better design. Your better design has to come with better performance and the ecosystem, the tool support, maintainability, etc., or it's a non-starter. Databases are one of the hardest technical markets to break into. For most companies, the data is actually the most important thing.
- willtim 9y ago
  > SQL is fine, most of the quirky way of stating things is because it's declarative No, it's quirky because of its semantics. To quote Erik Meijer "SQL is rife with noncompositional features. For example, the semantics of NULL is a big mess: why does adding the number 13 to a NULL value, 13+NULL, return NULL, but summing the same two values, SUM(13, NULL), returns 13?" SQL also has no means of abstraction, i.e. defining parameterizable queries. This is one reason that LINQ exists.
  - noway421 9y ago
    Coming from javascript, that doesn't sound all too bad! > 13+null 13 > 13+undefined NaN
  - manigandham 9y ago
    Sure SQL can be awkward, but in your example the + operator is not the same as the SUM() function, so it's not surprising that they wouldn't work the same either. SUM() will probably cast the null into a 0 first in most databases while the + operator has no proper guidance on what to do with a null value. Nullability is required to represent empty values and every database has ISNULL() and IFNULL/COALESCE() and both of those read fine to me but I can see why they're strange. I'm not sure what you mean by abstraction though since CTEs, joins, window functions, table values, UDFs, views, and more can all be used to compose logic and the language does support parameters, cursors, if/else/case statements, ... what's missing here?
    - willtim 9y ago
      > The + operator is not the same as the SUM() function, so it's not surprising that they wouldn't work the same No, it's very surprising that they are different. In mathematics and most programming languages, summation is defined in terms of the (+) operator. > Nullability is required to represent empty values I have no problem with (optional) nullability. > I'm not sure what you mean by abstraction There is no way to abstract queries over tables, joins, etc. Views allow me to re-use a particular concrete query, but I cannot parameterize a query.
dragonwriter 9y ago
> It's time for a new relational query language(s) to compete with SQL. There are several, some of which are arguably abstractly better than SQL. But given how well established SQL is and the strength of the ecosystem around it, you need a big advantage to make it worth changing.
- tabtab 9y ago
  Agreed. But we need competitors to seed the next generation. I agree any decent SQL competitor is not going to unseat SQL in the short-term in the general industry, but experimental and specialized projects would use them and gradually improve them. This approach has worked in the empirical language space, which had/has hundreds of active competitors, but RDBMS-land is sparse. Somebody pointed out that one can create user-defined functions in most SQL dialects, but that's generally for the value level, not the table label. It's still too dependent on per-case key-words for table-level features. I don't believe this is an inherent limit of declarative languages, as the SMEQL drafts suggest.

by tabtab 9y ago

manigandham 9y ago

SQL is fine, most of the quirky way of stating things is because it's declarative, which actually makes it very powerful. The biggest problem is that the standards move so slowly that many of the database implementers came up with their own dialects. The latest official SQL:2016 is actually pretty great and it would be better if all the various databases converged back to that, however it's probably too late for that now, let alone a whole new language...

tabtab 9y ago
The API/functional-versus-keyword issue is not about declarative versus imperative. Those are mostly independent issues. Therefore, I'm not following you. Perhaps there's a miscommunication.
- manigandham 9y ago
  SQL already has UDFs and even custom types. Those can be namespaced and customized with built-in implementations by all databases, which they all already do to some extent (eg: system data queries) so it seems the only real issue is the fact that the SQL dialects are different. If the SQL dialect was the same across all databases, then what would be the advantage of an "API" approach as you say?
  - hderms 9y ago
    I personally feel like there's still room to build something that's more terse, yet equally as powerful as SQL. get name, age -> (foos.id X bars.foo_id) |> get first(name) |> head as opposed to select name from (select foos.name from foos inner join bars on foos.id = bars.foo_id) A order by created_at ASC limit 1; more regular syntax that looks closer to conventional programming would be nice. At the very least it would probably be easier to write ORM style libraries for as the grammar would be closer together and more regular
    - manigandham 9y ago
      That looks like going back to imperative/functional instead of the declarative syntax of SQL... Have you used C# before? I think the LINQ query syntax could be a good evolution for some uses cases, but it again goes back to decades of dialects already being defined. Seems anything new would have to be implemented as some kind of proxy that then maps back to SQL or uses low level db access, but then why not just use an ORM or query builder in the first place and query from inside a language?
    - fusiongyro 9y ago
      Part of the point of SQL is that the database may choose to satisfy your query in a very different manner than you might expect given the procedural reading. Your example query is a nice illustration; a modern relational database may move your project/select steps earlier in the processing as long as it doesn't change the declarative meaning. Doing this kind of rearranging with a pipeline-looking query language is going to surprise people because you're giving the majority of people who think procedurally a false affordance. This is the fundamental "impedance mismatch": the ORM is trying to put a low-level wrapper around a higher-level idea. OO languages are still row-by-row procedural systems where relational databases are set-oriented. If the target language is Haskell or Ruby or another "sufficiently DSL-capable language" it will be possible to make an internal DSL that encapsulates your query. However, in that case I think you'll surprise your users when you either have non-obvious limits to how you can intermix query with in-language code, or you'll surprise them with how badly your database performs compared to Postgres. You can see a little of both in the story of RethinkDB. If you are not using an internal DSL, you'll be stuck in the same situation as SQL where you are embedding your query language in the code somehow. Relational databases are not just storage technology with a complicated query language. They are also integration technology. SQL's surface syntax may be unfortunate, but I'm increasingly doubtful that there is going to be a serious general-purpose database competitor that manages to hit the same notes only better. The main contender from the 90s was OODBs; they managed to have both shitty performance and lacked the ecosystem that makes SQL so useful as an integration technology: your application may be the main actor, but there are usually a large number of supporting cast members like cron jobs, reports, little scripts, ad-hoc queries, backup and restore etc, and having to do all of that with an OODB is very painful. There are now and will continue to be niches where relational databases suffer, the prominent one today being distributed data storage. But the problem you think is a problem really isn't a problem. Industrially speaking, "I don't like SQL" is a non-problem. For people who hate it, there are ORMs that make it possible to ignore it most of the time for better or worse (mostly worse, but whatever). Syntactically, the main problem with it is, as another commenter stated, different dialects that behave slightly differently. This turns into a major engineering annoyance, but one that is mostly swallowed by your library dependencies, who have by now already basically taken care of it. The benefit of using a modern relational database (I'm talking about Postgres but it applies to SQL Server and probably others as well) is they already have hundreds or thousands of man-years of effort put into optimizing them. I really thought RethinkDB had a brilliant design and it was going to be the next generation database. But it performed worse than Postgres and that makes it basically a non-starter. This is part of why niche databases are not based on SQL: if you handle a niche well, I will tolerate your oddities, but if you want to build a new general-purpose database today, in 2018, you can't just have a better design. Your better design has to come with better performance and the ecosystem, the tool support, maintainability, etc., or it's a non-starter. Databases are one of the hardest technical markets to break into. For most companies, the data is actually the most important thing.
willtim 9y ago
> SQL is fine, most of the quirky way of stating things is because it's declarative No, it's quirky because of its semantics. To quote Erik Meijer "SQL is rife with noncompositional features. For example, the semantics of NULL is a big mess: why does adding the number 13 to a NULL value, 13+NULL, return NULL, but summing the same two values, SUM(13, NULL), returns 13?" SQL also has no means of abstraction, i.e. defining parameterizable queries. This is one reason that LINQ exists.
- noway421 9y ago
  Coming from javascript, that doesn't sound all too bad! > 13+null 13 > 13+undefined NaN
- manigandham 9y ago
  Sure SQL can be awkward, but in your example the + operator is not the same as the SUM() function, so it's not surprising that they wouldn't work the same either. SUM() will probably cast the null into a 0 first in most databases while the + operator has no proper guidance on what to do with a null value. Nullability is required to represent empty values and every database has ISNULL() and IFNULL/COALESCE() and both of those read fine to me but I can see why they're strange. I'm not sure what you mean by abstraction though since CTEs, joins, window functions, table values, UDFs, views, and more can all be used to compose logic and the language does support parameters, cursors, if/else/case statements, ... what's missing here?
  - willtim 9y ago
    > The + operator is not the same as the SUM() function, so it's not surprising that they wouldn't work the same No, it's very surprising that they are different. In mathematics and most programming languages, summation is defined in terms of the (+) operator. > Nullability is required to represent empty values I have no problem with (optional) nullability. > I'm not sure what you mean by abstraction There is no way to abstract queries over tables, joins, etc. Views allow me to re-use a particular concrete query, but I cannot parameterize a query.

dragonwriter 9y ago

> It's time for a new relational query language(s) to compete with SQL. There are several, some of which are arguably abstractly better than SQL. But given how well established SQL is and the strength of the ecosystem around it, you need a big advantage to make it worth changing.

tabtab 9y ago
Agreed. But we need competitors to seed the next generation. I agree any decent SQL competitor is not going to unseat SQL in the short-term in the general industry, but experimental and specialized projects would use them and gradually improve them. This approach has worked in the empirical language space, which had/has hundreds of active competitors, but RDBMS-land is sparse. Somebody pointed out that one can create user-defined functions in most SQL dialects, but that's generally for the value level, not the table label. It's still too dependent on per-case key-words for table-level features. I don't believe this is an inherent limit of declarative languages, as the SMEQL drafts suggest.