6 ms·

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

by 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.
- garyclarke27 9y ago
  Nicely Put. I fully agree the power of SQL with Postgres is phenomenal. I would add that Postgres table returning functions are incredibly useful for getting around SQL annoyances such as verbosity and composability. Because they automatically lateral join, they almost feel magical, enbling complex multiple combinations, without confusing deep nesting of sub queries or easier to follow but long winded and slower CTEs.

by hderms 9y ago

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.

garyclarke27 9y ago
Nicely Put. I fully agree the power of SQL with Postgres is phenomenal. I would add that Postgres table returning functions are incredibly useful for getting around SQL annoyances such as verbosity and composability. Because they automatically lateral join, they almost feel magical, enbling complex multiple combinations, without confusing deep nesting of sub queries or easier to follow but long winded and slower CTEs.