6 ms·

> All they had to do was implement a nested set pattern for their groups I've been told a million times that SQL is declarative, meaning you say what you want,

by ken 7y ago

> All they had to do was implement a nested set pattern for their groups I've been told a million times that SQL is declarative, meaning you say what you want, not how to get it. I've also been told a million times that I need to write my queries in a specific way, or use a specific data structure, or add hints for the query optimizer. Otherwise it'll pick the wrong "how" for your "what". Is there any practical difference between a declarative language with a Sufficiently Dumb Compiler, and an imperative language with weak features and an awkward syntax? However I'm forced to write it, we all know I'm really trying to do is get it to LOOP first over these items here, and not those there. What's the point of being declarative if common tasks require us to bend over backwards to design our schema/queries/indices/hints in exactly the right way, in order for it to be performant on two popular SQL databases (when that's even possible)? Even if the vaguely-English-like syntax that's completely unlike any other computer language weren't problematic for those reasons, it seems that the lack of abstraction is a complete buzzkill. These two databases require different implementations for "nested groups", but there's no (remotely portable) way to define a CREATED NESTEDGROUP to allow for a similar interface. Of all the languages I have to use, SQL are the ones I hate most. From decades of writing SQL, I can say GitLab got one thing absolutely right: it's easiest to just treat it as a family of incompatible proprietary languages. It's easier for me to target JS and the JVM from the same program than two different SQL databases from the same queries.

jayd16 7y ago
You're conflating the nitty gritty of schema design (which I agree with you is not that declarative) with the usual case of "I know what I mean why doesn't the machine know what I mean" which is different. You're still expected ask for what you want specifically.
- ses1984 7y ago
  > It's easier for me to target JS and the JVM from the same program than two different SQL databases from the same queries. "I know how to express what I mean in such a way that I can target two very different platforms like JS and the JVM, why isn't there something as powerful for SQL?"
  - michaelper22 7y ago
    "One does not simply use an ORM against multiple database types."
testvox 7y ago
Yeah, SQL in applications that need to be performant and responsive is a huge pain and I have found it to not be worth the effort. If you don't need performance or responsiveness though you can save a lot of effort except in very simple cases. I've seen it shine in quickly built proof of concept applications and in ad-hoc analytics queries.
- tracker1 7y ago
  I tend to prefer to treat SQL databases as dumb and generic as possible... you can go a LONG way supporting multiple databases until you need something more complex. For most organizations, supporting more than a single SQL variant is just painful. The only time I ever saw, for example, Microsoft Data Application Blocks in a useful scenario was an enterprise app that had to support MS-SQL and Oracle. Most of the variance was custom stored procedures for each named the same with the same signatures... It actually really sucked. I don't blame them, and despite supporters, mySQL is constantly the most inconsistent non-standard SQL database of them all. I wished they'd just do a major version shift, dump the historical defaults, and be strongly consistent and compliant by default. Much like Oracle, I have no intention of ever using mySQL unless managing the thing is something I never have to do. I'd rather use MS-SQL or PostgreSQL than mySQL.
  - evanelias 7y ago
    > I wished they'd just do a major version shift, dump the historical defaults, and be strongly consistent and compliant by default. Regarding the historical defaults and consistency, they fixed that 4 years ago in MySQL 5.7. And last year's release of MySQL 8.0 added more modern SQL features (CTEs, window functions, etc). There's still some non-standard idiosyncrasies, but that's true of every relational DBMS :) And while it's certainly possible that MySQL is arguably still more idiosyncratic than MS-SQL or Postgres, it's not like an order-of-magnitude difference anymore.
    - tracker1 7y ago
      While I am glad to hear it... I've been very happy with mssql and postgres, with little chance of giving mysql/mariadb much in the way of future opportunity. I've just been burned too many times with other open and commercial options that give me what I expect.
taffer 7y ago
> What's the point of being declarative if common tasks require us to bend over backwards to design our schema/queries/indices/hints in exactly the right way, Because 99% of the time you don't notice that it just works. 99% of the time you don't have to bend your query or schema to make it work.
- kjeetgill 7y ago
  99% is a strong claim. Maybe 70%? If you have a query that needs to look at over about 5K rows and you need responses under 100ms you need to know what its doing.
  - taffer 7y ago
    For each query I had to optimize, I wrote dozens, if not hundreds, that worked without any intervention. 99% is just a rough guess. > If you have a query that needs to look at over about 5K rows and you need responses under 100ms you need to know what its doing. Thats very unspecific, but getting 5000 rows from a table should take a few milliseconds on modern hardware.
manigandham 7y ago
This is why decoupling RDBMS from the application is rarely ever useful, and why no app really changes databases anyway unless there's a massive cost or technical problem.
- rch 7y ago
  As a long time PostgreSQL user, I'd say that decoupling has been very useful for most of the last two decades, but it's quickly becoming irrelevant for reasons that should be obvious here.
- wyoung2 7y ago
  The app I’ve been working on for the past 24 years is currently on its third DBMS, and I would be unwilling to bet on whether it’s on its last DBMS. There is zero SQL code in our app above a very low level. All high-level code calls through a DB API, and all SQL code is behind that abstraction layer. That said, each one of these transitions was a “pull up stakes and move” kind of thing. We never tried to support multiple back-ends at once. So on those grounds, I think this is probably a good move for GitLab.
  - manigandham 7y ago
    If you're using basic ANSI/92 SQL syntax then it'll work pretty much anywhere, especially with a decent ORM, but as soon as you start using any of the interesting features of a particular database, then you're locked in. Supporting multiple databases at this point is usually not economically feasible.
    - wyoung2 7y ago
      That's the thing: it's super tempting to use nonstandard features. On our last SQL transition, we were most bitten by use of data types the new DBMS didn't support and by changes in the way "standard" text data types behaved between the two. (Just as GitLab was, on that last one.) That's why I don't regret putting all of our SQL behind that DB layer: to port to the new DBMS, we just had to rewrite parts of that layer, rather than go on a hunt through the whole application for one-off SQL calls, as would probably occur if we didn't enforce this discipline.
jeffdavis 7y ago
A major benefit is declarative constraints. If you declare something UNIQUE, it's going to be unique no matter what. No race conditions, no "whoops, I guess there was a bug in version 4.7.3 of the application that didn't properly check for an existing entry, and it was silently fixed later in some refactoring, but we still have duplicate entries floating around". Same for foreign keys and check constraints. Queries are pretty intelligent, also, and mostly just work efficiently. You are right that some queries don't, and you have to know about the internals to make it do what you want. But here's what you are missing in that story: your data lives for a long time across many different applications. If you program everything yourself imperatively, you are bound to either over-engineer or under-engineer a lot of queries. The over-engineered pieces will look like premature optimization, clutter your code and tests, and be a time sink. The under-engineered pieces will come back to haunt you when your data grows and changes. Declarative relational systems actually do a pretty darn good job of handling this problem with a cost-based optimizer. Is it perfect? No, but it's an improvement in an awful lot of cases. Also, you are ignoring places where databases do a good job with queries, like parallelization. It's easy to say that you'll just write all the queries imperatively yourself, but a lot of people don't want to spend the time to do the harder stuff even if it really matters for performance.
pmontra 7y ago
> vaguely-English-like syntax that's completely unlike any other computer language COBOL is also English like. I'm not negative about SQL. I'd rather write queries in SQL than having to learn for the nth time how to write SQL in language X with ORM/module Y. I'm doing it with Django, Rails and Phoenix in the same week since a long time now and it feels a bit pointless. But then there is the deserialization in the data structures of the language. I just wish those libraries would be smarter and know how to handle some hypothetical code like sql("select * from users").each |user| do something(user) end And be able to handle joins.
- piaste 7y ago
  The best experience I know of in this regard is from some packages in ML languages, where the string literal query you write gets sent _at compile time_ to a database engine for parsing, using the database's API to determine the query's expected return type (column names and types). They're most used in F#, which calls them type providers: https://github.com/fsprojects/FSharp.Data.SqlClient https://github.com/fsprojects/FSharp.Data.SqlClient https://hackage.haskell.org/package/postgresql-typed https://hackage.haskell.org/package/postgresql-typed

by ken 7y ago

jayd16 7y ago

You're conflating the nitty gritty of schema design (which I agree with you is not that declarative) with the usual case of "I know what I mean why doesn't the machine know what I mean" which is different. You're still expected ask for what you want specifically.

ses1984 7y ago
> It's easier for me to target JS and the JVM from the same program than two different SQL databases from the same queries. "I know how to express what I mean in such a way that I can target two very different platforms like JS and the JVM, why isn't there something as powerful for SQL?"
- michaelper22 7y ago
  "One does not simply use an ORM against multiple database types."

testvox 7y ago

Yeah, SQL in applications that need to be performant and responsive is a huge pain and I have found it to not be worth the effort. If you don't need performance or responsiveness though you can save a lot of effort except in very simple cases. I've seen it shine in quickly built proof of concept applications and in ad-hoc analytics queries.

tracker1 7y ago
I tend to prefer to treat SQL databases as dumb and generic as possible... you can go a LONG way supporting multiple databases until you need something more complex. For most organizations, supporting more than a single SQL variant is just painful. The only time I ever saw, for example, Microsoft Data Application Blocks in a useful scenario was an enterprise app that had to support MS-SQL and Oracle. Most of the variance was custom stored procedures for each named the same with the same signatures... It actually really sucked. I don't blame them, and despite supporters, mySQL is constantly the most inconsistent non-standard SQL database of them all. I wished they'd just do a major version shift, dump the historical defaults, and be strongly consistent and compliant by default. Much like Oracle, I have no intention of ever using mySQL unless managing the thing is something I never have to do. I'd rather use MS-SQL or PostgreSQL than mySQL.
- evanelias 7y ago
  > I wished they'd just do a major version shift, dump the historical defaults, and be strongly consistent and compliant by default. Regarding the historical defaults and consistency, they fixed that 4 years ago in MySQL 5.7. And last year's release of MySQL 8.0 added more modern SQL features (CTEs, window functions, etc). There's still some non-standard idiosyncrasies, but that's true of every relational DBMS :) And while it's certainly possible that MySQL is arguably still more idiosyncratic than MS-SQL or Postgres, it's not like an order-of-magnitude difference anymore.
  - tracker1 7y ago
    While I am glad to hear it... I've been very happy with mssql and postgres, with little chance of giving mysql/mariadb much in the way of future opportunity. I've just been burned too many times with other open and commercial options that give me what I expect.

taffer 7y ago

> What's the point of being declarative if common tasks require us to bend over backwards to design our schema/queries/indices/hints in exactly the right way, Because 99% of the time you don't notice that it just works. 99% of the time you don't have to bend your query or schema to make it work.

kjeetgill 7y ago
99% is a strong claim. Maybe 70%? If you have a query that needs to look at over about 5K rows and you need responses under 100ms you need to know what its doing.
- taffer 7y ago
  For each query I had to optimize, I wrote dozens, if not hundreds, that worked without any intervention. 99% is just a rough guess. > If you have a query that needs to look at over about 5K rows and you need responses under 100ms you need to know what its doing. Thats very unspecific, but getting 5000 rows from a table should take a few milliseconds on modern hardware.

manigandham 7y ago

This is why decoupling RDBMS from the application is rarely ever useful, and why no app really changes databases anyway unless there's a massive cost or technical problem.

rch 7y ago
As a long time PostgreSQL user, I'd say that decoupling has been very useful for most of the last two decades, but it's quickly becoming irrelevant for reasons that should be obvious here.
wyoung2 7y ago
The app I’ve been working on for the past 24 years is currently on its third DBMS, and I would be unwilling to bet on whether it’s on its last DBMS. There is zero SQL code in our app above a very low level. All high-level code calls through a DB API, and all SQL code is behind that abstraction layer. That said, each one of these transitions was a “pull up stakes and move” kind of thing. We never tried to support multiple back-ends at once. So on those grounds, I think this is probably a good move for GitLab.
- manigandham 7y ago
  If you're using basic ANSI/92 SQL syntax then it'll work pretty much anywhere, especially with a decent ORM, but as soon as you start using any of the interesting features of a particular database, then you're locked in. Supporting multiple databases at this point is usually not economically feasible.
  - wyoung2 7y ago
    That's the thing: it's super tempting to use nonstandard features. On our last SQL transition, we were most bitten by use of data types the new DBMS didn't support and by changes in the way "standard" text data types behaved between the two. (Just as GitLab was, on that last one.) That's why I don't regret putting all of our SQL behind that DB layer: to port to the new DBMS, we just had to rewrite parts of that layer, rather than go on a hunt through the whole application for one-off SQL calls, as would probably occur if we didn't enforce this discipline.

jeffdavis 7y ago

A major benefit is declarative constraints. If you declare something UNIQUE, it's going to be unique no matter what. No race conditions, no "whoops, I guess there was a bug in version 4.7.3 of the application that didn't properly check for an existing entry, and it was silently fixed later in some refactoring, but we still have duplicate entries floating around". Same for foreign keys and check constraints. Queries are pretty intelligent, also, and mostly just work efficiently. You are right that some queries don't, and you have to know about the internals to make it do what you want. But here's what you are missing in that story: your data lives for a long time across many different applications. If you program everything yourself imperatively, you are bound to either over-engineer or under-engineer a lot of queries. The over-engineered pieces will look like premature optimization, clutter your code and tests, and be a time sink. The under-engineered pieces will come back to haunt you when your data grows and changes. Declarative relational systems actually do a pretty darn good job of handling this problem with a cost-based optimizer. Is it perfect? No, but it's an improvement in an awful lot of cases. Also, you are ignoring places where databases do a good job with queries, like parallelization. It's easy to say that you'll just write all the queries imperatively yourself, but a lot of people don't want to spend the time to do the harder stuff even if it really matters for performance.

pmontra 7y ago

> vaguely-English-like syntax that's completely unlike any other computer language COBOL is also English like. I'm not negative about SQL. I'd rather write queries in SQL than having to learn for the nth time how to write SQL in language X with ORM/module Y. I'm doing it with Django, Rails and Phoenix in the same week since a long time now and it feels a bit pointless. But then there is the deserialization in the data structures of the language. I just wish those libraries would be smarter and know how to handle some hypothetical code like sql("select * from users").each |user| do something(user) end And be able to handle joins.

piaste 7y ago
The best experience I know of in this regard is from some packages in ML languages, where the string literal query you write gets sent _at compile time_ to a database engine for parsing, using the database's API to determine the query's expected return type (column names and types). They're most used in F#, which calls them type providers: https://github.com/fsprojects/FSharp.Data.SqlClient https://github.com/fsprojects/FSharp.Data.SqlClient https://hackage.haskell.org/package/postgresql-typed https://hackage.haskell.org/package/postgresql-typed