7 ms·

I tend to follow the Postgres releases and I am always impressed by the cool things SQL databases can do. But for programmers like me who are used to code in C+

I tend to follow the Postgres releases and I am always impressed by the cool things SQL databases can do. But for programmers like me who are used to code in C++/C# I always find the transition from these languages to SQL too harsh. Especially if you don’t have to do SQL daily it’s really hard to remember the syntax and read complex SQL code. Also the transition from SQL results to typed languages is tedious. ORMs help a little but they have their own set of problems. Not sure what I am trying to say other than that I wish it was easier for regular programmers to use advanced SQL features...

magicalhippo 7y ago
SQL is a bit verbose at times, but it's not much different from going to other programming languages. In fact I'd say it's easier given that it's so verbose. I have no idea what all those modifier symbols do in Rust, say, but I find "case when InvoiceNo is null then" or "select Name, list(distinct Title) as Titles from" pretty transparent in comparison. The biggest mental difference I think is that you're dealing with a different data model. You don't have a bunch of individual entities floating around. You got rows in tables, which you mostly process in bulk. As such it's more like an Excel spreadsheet.
- pizza234 7y ago
  What specifically do you find verbose? The only thing I find redundant (based on the queries I handle) is that theoretically join arguments could be deduced from foreign keys (maybe extending this behavior, having joins automatically deduced). I also wonder if the perceived verbosity is caused by some programmers cramming logic into the queries, rather than keeping them simple and processing the results in the client language.
  - magicalhippo 7y ago
    It's verbose compared to a lot of other languages simply by using words rather than symbols or abbreviations for most things. Though I tried to convey that I did not think this was a bad thing. The only case where things get really verbose for me is when I need to do subqueries for columns. Often I might need to run basically the same subquery but return aggregates over different fields, ie sum of net weight, sum of gross weight, sum of value. For performance reasons I can't write that as a join, at least on our SQL server. I agree there should be a shortcut syntax for joining using foreign keys ("join X on foreign key"?) though.
    - pizza234 7y ago
      Agreed, I didn't want to convey that it's not verbose, either :-)
SigmundA 7y ago
Having done both SQL and C# for a very long time I prefer C# with Linq. Linq gives you nearly all the declarative power of SQL with all the procedural goodness of C# to get things done. Whats needed is a really good object database for C# to eliminate the impedance mismatch. Don't get me wrong SQL is great, but to be useful it needs to be mixed with a procedural language (pl/SQL, T-SQL, etc) I would just prefer using a more capable typed procedural language instead.
- philliphaydon 7y ago
  MartenDB. :)
  - SigmundA 7y ago
    Right or Entity Framework, but they are just ORM's which is just hiding the impedance mismatch. I am thinking more along the lines of an embedded db like SQLite but does native serialization, indexing and query optimization against .Net objects and types. Then to make a "database server" is really just an app server running your c#. If you really wanted to make it like pg then you would have an app server that accepts code snippets, compiles runs and returns results. Of course you would need proper sandboxing like .net fiddle. You could also do something in between like serializing Linq expression trees to send them to server for processing. In an ideal world where your code runs (client, app server, db server) is a choice based on locality needs not forced by runtimes (javascript in the browser, .Net on the app server, pg/Sql on the db server). You can pretty much get close to this now with javascript/node/plV8, but again I prefer C#/.Net.
    - philliphaydon 7y ago
      So... Marten DB...
      - SigmundA 7y ago
        As far I know Marten doesn't run .Net in the PG process and just converts .Net objects to JSON and Linq statements into SQL. Am I missing something?
raphaelj 7y ago
Indeed. SQL concepts are insanely powerful, but the language itself feels a little bit old.
- comboy 7y ago
  I think it aged insanely well. DBs are often a bottleneck and need to be heavily optimized so you need to spend some time explaining to your database what it is that you really need, but in general, SQL is all about telling what do you want and not worrying about how it's going to get done. That's how programming languages should look like. We have so many ORMs and most often they look more ugly than pure SQL to me, especially when it comes to more complex queries. You could write a translator (something like coffeescript -> JS but X -> SQL), but can you really come up with a syntax that's enough of an improvement that it would be worth using it?
  - AmericanChopper 7y ago
    I would agree. SQL is absolutely beautiful in its simplicity imo. I learned SQL along side my first imperative programming language, so I guess I’m significantly biased, but I think it’s the easiest language I’ve ever learned. The best part is, if you spend enough time writing SQL, you end up learning quite a bit about how databases, data structures, and the time complexity of query execution all works. Which is all incredibly valuable for just about any programmer. I also love ORMs. But perhaps my knowledge of SQL has enhanced my ability to use them well. Without them you end up with numerous other problems which I’d consider worse than the occasional compromises ORMs require. Ideally you want all your business logic in one place, or at least in discrete units, so the fragmentation of stored procedures is a major downside. Embedding SQL directly into other business logic is like and uglier version of an FFI, and serializing SQL statements is even more of a security concern than marshalling FFIs (something that can already be a bit of a footgun). I’d recommend any engineer to learn SQL, but for people who use ORMs a lot already, I’d suggest to start with understanding explain plans first.
    - Kihashi 7y ago
      IME knowing SQL is a prerequisite to using an ORM efficiently.
dewey 7y ago
I found this book to be very helpful, I'm not done yet but so far I'm really enjoying it. "Perfectly intelligent programmers often struggle when forced to work with SQL. Why? Joe Celko believes the problem lies with their procedural programming mindset, which keeps them from taking full advantage of the power of declarative languages. The result is overly complex and inefficient code, not to mention lost productivity." https://www.amazon.com/Joe-Celkos-Thinking-Sets-Management/dp/0123741378 https://www.amazon.com/Joe-Celkos-Thinking-Sets-Management/d...
- Ididntdothis 7y ago
  I think it’s not only the procedural vs declarative difference but also just the plain syntax. To me SQL is just hard on the eyes. It feels a little like FORTRAN in the good old days.
  - misterdoubt 7y ago
    Well it doesn't help that people continue using ALL CAPS for SQL keywords as a preferred style. In this century.
    - zo1 7y ago
      I find that it helps differentiate the different components of the query. Same way we use all-caps for constants, etc. If it's lower-case, or camel-case, then it just sort of "melts" into the rest of the query.
      - pletnes 7y ago
        Not with syntax highlighting.
        deleted 7y ago
        [deleted]
        preommr 7y ago
throwaway_bad 7y ago
There's definitely a language/paradigm switching cost if you jump back and forth too much. What I found helpful was to prototype all the migrations and queries (of whatever feature or user flow you're building) in a .sql file first with mocked values. You'll be surprised by how much you can do if you're simply forced to write pure sql. Once you've reached the limit of what you can do you can move the queries back into your app to wire the rest up. This is usually not much work if you use raw queries instead of ORMs. You usually end up writing a lot more efficient apps this way!
BjoernKW 7y ago
You’re basically experiencing impedance mismatch first hand ;-) The term is usually applied to mapping from object-oriented to relational systems and vice versa but it’s perhaps more of or also a mismatch between imperative and declarative programming styles. I think we just have to accept that these two are different and that each is great at what it does instead of trying to shoehorn a relational approach into object-oriented and imperative ones.
deleted 7y ago
[deleted]
shrumm 7y ago
I think you need a visual model that works for you to approach it since they're entirely different beasts. You'll hit a wall very soon if you approach learning SQL in terms of C++/C#. CSS is a similar phenomenon, you can't approach it as code. The box model is a nice way to get the basics and manipulate the DOM. Similarly, I think in terms of tables (rows and columns) to visualize SQL operations that I need to do. Every operation is like a matrix operation, so you need to stop thinking in terms of for/loops iterators and think of matrix/table wide operators. Once you've got the basics, then you can look at stuff like window functions to do more advanced operations. Takes a bit of working with to get used to. That's just how I think about it - you'll need to find a metaphor that works for you!
- Deimorz 7y ago
  > CSS is a similar phenomenon, you can't approach it as code. Mozilla put out a good video about this today: https://www.youtube.com/watch?v=aHUtMbJw8iA https://www.youtube.com/watch?v=aHUtMbJw8iA
- adamcharnock 7y ago
  I sometimes mention declarative vs imperative when talking about software development, especially to people who are coming from a HTML/CSS background. I'll give some details below in case it is useful. Declarative languages encompass things like HTML, CSS, and SQL. Here we say what should happen, but not specifically how it should happen. Imperative languages are C, Python, Java, JS etc. Here we give the precise steps which describe how something should work, and there is an absence of describing what the problem actually is. Declarative languages give us a lot of power to solve a very narrow range of tasks. Imperative languages give us comparatively less power, but allow us to tackle a very wide range of tasks. For example: We could certainly write a Python program to sort, filter, group, and join two lists of data. However, the equivalent SQL code would be much shorter and – all things being equal – much more readable. Conversely, SQL would be a non-starter for writing a web service or an image processing library. SQL does one thing really well, and that thing is data wrangling.
tabtab 7y ago
ORM's tend to lock one into a particular architecture. SQL is more standardized across platforms. It's my opinion that ORM's should assist with SQL generation or usage rather than outright hide it. ORM's job should be to reduce the grunt-work of preparing typical SQL, not wrapping it fully so you can ignore it. You can take your SQL knowledge to another platform, but less so with say Entity Framework or LINQ. Don't get locked in. Plus, if the ORM doesn't do your query correctly or as intended, someone will need to understand SQL anyhow to debug it.
modernista 7y ago
I wish it was easier for regular programmers to use advanced SQL features... It's a fundamentally different way of thinking. With imperative programming, the mindset is: "I have this certain outcome in mind. What is the sequence of steps I need to take to make it happen?" In a relational database, the mindset you need to be in is: "How can I define the relational structure of my world (i.e. my data) so that I can get the outcomes I want - and end up saving a great deal of imperative programming effort in the longer run?" Quite different. And yes, it takes a while to get used to.

magicalhippo 7y ago

SQL is a bit verbose at times, but it's not much different from going to other programming languages. In fact I'd say it's easier given that it's so verbose. I have no idea what all those modifier symbols do in Rust, say, but I find "case when InvoiceNo is null then" or "select Name, list(distinct Title) as Titles from" pretty transparent in comparison. The biggest mental difference I think is that you're dealing with a different data model. You don't have a bunch of individual entities floating around. You got rows in tables, which you mostly process in bulk. As such it's more like an Excel spreadsheet.

pizza234 7y ago
What specifically do you find verbose? The only thing I find redundant (based on the queries I handle) is that theoretically join arguments could be deduced from foreign keys (maybe extending this behavior, having joins automatically deduced). I also wonder if the perceived verbosity is caused by some programmers cramming logic into the queries, rather than keeping them simple and processing the results in the client language.
- magicalhippo 7y ago
  It's verbose compared to a lot of other languages simply by using words rather than symbols or abbreviations for most things. Though I tried to convey that I did not think this was a bad thing. The only case where things get really verbose for me is when I need to do subqueries for columns. Often I might need to run basically the same subquery but return aggregates over different fields, ie sum of net weight, sum of gross weight, sum of value. For performance reasons I can't write that as a join, at least on our SQL server. I agree there should be a shortcut syntax for joining using foreign keys ("join X on foreign key"?) though.
  - pizza234 7y ago
    Agreed, I didn't want to convey that it's not verbose, either :-)

SigmundA 7y ago

Having done both SQL and C# for a very long time I prefer C# with Linq. Linq gives you nearly all the declarative power of SQL with all the procedural goodness of C# to get things done. Whats needed is a really good object database for C# to eliminate the impedance mismatch. Don't get me wrong SQL is great, but to be useful it needs to be mixed with a procedural language (pl/SQL, T-SQL, etc) I would just prefer using a more capable typed procedural language instead.

philliphaydon 7y ago
MartenDB. :)
- SigmundA 7y ago
  Right or Entity Framework, but they are just ORM's which is just hiding the impedance mismatch. I am thinking more along the lines of an embedded db like SQLite but does native serialization, indexing and query optimization against .Net objects and types. Then to make a "database server" is really just an app server running your c#. If you really wanted to make it like pg then you would have an app server that accepts code snippets, compiles runs and returns results. Of course you would need proper sandboxing like .net fiddle. You could also do something in between like serializing Linq expression trees to send them to server for processing. In an ideal world where your code runs (client, app server, db server) is a choice based on locality needs not forced by runtimes (javascript in the browser, .Net on the app server, pg/Sql on the db server). You can pretty much get close to this now with javascript/node/plV8, but again I prefer C#/.Net.
  - philliphaydon 7y ago
    So... Marten DB...
    - SigmundA 7y ago
      As far I know Marten doesn't run .Net in the PG process and just converts .Net objects to JSON and Linq statements into SQL. Am I missing something?

raphaelj 7y ago

Indeed. SQL concepts are insanely powerful, but the language itself feels a little bit old.

comboy 7y ago
I think it aged insanely well. DBs are often a bottleneck and need to be heavily optimized so you need to spend some time explaining to your database what it is that you really need, but in general, SQL is all about telling what do you want and not worrying about how it's going to get done. That's how programming languages should look like. We have so many ORMs and most often they look more ugly than pure SQL to me, especially when it comes to more complex queries. You could write a translator (something like coffeescript -> JS but X -> SQL), but can you really come up with a syntax that's enough of an improvement that it would be worth using it?
- AmericanChopper 7y ago
  I would agree. SQL is absolutely beautiful in its simplicity imo. I learned SQL along side my first imperative programming language, so I guess I’m significantly biased, but I think it’s the easiest language I’ve ever learned. The best part is, if you spend enough time writing SQL, you end up learning quite a bit about how databases, data structures, and the time complexity of query execution all works. Which is all incredibly valuable for just about any programmer. I also love ORMs. But perhaps my knowledge of SQL has enhanced my ability to use them well. Without them you end up with numerous other problems which I’d consider worse than the occasional compromises ORMs require. Ideally you want all your business logic in one place, or at least in discrete units, so the fragmentation of stored procedures is a major downside. Embedding SQL directly into other business logic is like and uglier version of an FFI, and serializing SQL statements is even more of a security concern than marshalling FFIs (something that can already be a bit of a footgun). I’d recommend any engineer to learn SQL, but for people who use ORMs a lot already, I’d suggest to start with understanding explain plans first.
  - Kihashi 7y ago
    IME knowing SQL is a prerequisite to using an ORM efficiently.

dewey 7y ago

I found this book to be very helpful, I'm not done yet but so far I'm really enjoying it. "Perfectly intelligent programmers often struggle when forced to work with SQL. Why? Joe Celko believes the problem lies with their procedural programming mindset, which keeps them from taking full advantage of the power of declarative languages. The result is overly complex and inefficient code, not to mention lost productivity." https://www.amazon.com/Joe-Celkos-Thinking-Sets-Management/dp/0123741378 https://www.amazon.com/Joe-Celkos-Thinking-Sets-Management/d...

Ididntdothis 7y ago
I think it’s not only the procedural vs declarative difference but also just the plain syntax. To me SQL is just hard on the eyes. It feels a little like FORTRAN in the good old days.
- misterdoubt 7y ago
  Well it doesn't help that people continue using ALL CAPS for SQL keywords as a preferred style. In this century.
  - zo1 7y ago
    I find that it helps differentiate the different components of the query. Same way we use all-caps for constants, etc. If it's lower-case, or camel-case, then it just sort of "melts" into the rest of the query.
    - pletnes 7y ago
      Not with syntax highlighting.
      - deleted 7y ago
        [deleted]
      - preommr 7y ago

throwaway_bad 7y ago

There's definitely a language/paradigm switching cost if you jump back and forth too much. What I found helpful was to prototype all the migrations and queries (of whatever feature or user flow you're building) in a .sql file first with mocked values. You'll be surprised by how much you can do if you're simply forced to write pure sql. Once you've reached the limit of what you can do you can move the queries back into your app to wire the rest up. This is usually not much work if you use raw queries instead of ORMs. You usually end up writing a lot more efficient apps this way!

BjoernKW 7y ago

You’re basically experiencing impedance mismatch first hand ;-) The term is usually applied to mapping from object-oriented to relational systems and vice versa but it’s perhaps more of or also a mismatch between imperative and declarative programming styles. I think we just have to accept that these two are different and that each is great at what it does instead of trying to shoehorn a relational approach into object-oriented and imperative ones.

deleted 7y ago

[deleted]

shrumm 7y ago

I think you need a visual model that works for you to approach it since they're entirely different beasts. You'll hit a wall very soon if you approach learning SQL in terms of C++/C#. CSS is a similar phenomenon, you can't approach it as code. The box model is a nice way to get the basics and manipulate the DOM. Similarly, I think in terms of tables (rows and columns) to visualize SQL operations that I need to do. Every operation is like a matrix operation, so you need to stop thinking in terms of for/loops iterators and think of matrix/table wide operators. Once you've got the basics, then you can look at stuff like window functions to do more advanced operations. Takes a bit of working with to get used to. That's just how I think about it - you'll need to find a metaphor that works for you!

Deimorz 7y ago
> CSS is a similar phenomenon, you can't approach it as code. Mozilla put out a good video about this today: https://www.youtube.com/watch?v=aHUtMbJw8iA https://www.youtube.com/watch?v=aHUtMbJw8iA
adamcharnock 7y ago
I sometimes mention declarative vs imperative when talking about software development, especially to people who are coming from a HTML/CSS background. I'll give some details below in case it is useful. Declarative languages encompass things like HTML, CSS, and SQL. Here we say what should happen, but not specifically how it should happen. Imperative languages are C, Python, Java, JS etc. Here we give the precise steps which describe how something should work, and there is an absence of describing what the problem actually is. Declarative languages give us a lot of power to solve a very narrow range of tasks. Imperative languages give us comparatively less power, but allow us to tackle a very wide range of tasks. For example: We could certainly write a Python program to sort, filter, group, and join two lists of data. However, the equivalent SQL code would be much shorter and – all things being equal – much more readable. Conversely, SQL would be a non-starter for writing a web service or an image processing library. SQL does one thing really well, and that thing is data wrangling.

tabtab 7y ago

ORM's tend to lock one into a particular architecture. SQL is more standardized across platforms. It's my opinion that ORM's should assist with SQL generation or usage rather than outright hide it. ORM's job should be to reduce the grunt-work of preparing typical SQL, not wrapping it fully so you can ignore it. You can take your SQL knowledge to another platform, but less so with say Entity Framework or LINQ. Don't get locked in. Plus, if the ORM doesn't do your query correctly or as intended, someone will need to understand SQL anyhow to debug it.

modernista 7y ago

I wish it was easier for regular programmers to use advanced SQL features... It's a fundamentally different way of thinking. With imperative programming, the mindset is: "I have this certain outcome in mind. What is the sequence of steps I need to take to make it happen?" In a relational database, the mindset you need to be in is: "How can I define the relational structure of my world (i.e. my data) so that I can get the outcomes I want - and end up saving a great deal of imperative programming effort in the longer run?" Quite different. And yes, it takes a while to get used to.