Hacker News Reader

Comment by Ididntdothis | Hacker News Reader

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...

View on HN

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!

> 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

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.

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/d...

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.

Well it doesn't help that people continue using ALL CAPS for SQL keywords as a preferred style. In this century.

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.

Not with syntax highlighting.

The tooling around sql is pretty awful. I was looking for a vscode plugin to get some syntax highlighting and linting for postgres yesterday, and just gave up. There are plenty of administrative tools that help with connecting to dbs and showing the results in a nice table, but nothing that really helps with writing it (or at least nothing of high quality that I could find).

https://www.jetbrains.com/datagrip/ and Postico are pretty great for PG I think. I'm using them every day and having the option to attach DataGrip to a directory of queries in your code and directly executing them from there with set variables is pretty sweet.

It's helping you write queries by auto formatting and good auto completion.

Also nice for differentiating it from the rest of non-SQL code.

Over the years, I must have annoyed various DBAs by saying "Is that a query, or a ransom note?"

I so very wish this convention would die.

Also, leading a new line with the comma.

I actually like them both a lot for someone who is a developer who has spend hours fixing/optimizing large queries. As someone mentioned in another comment good syntax highlighting is often not available for sql (if any) so having the keywords separated is very helpful. When fixing and improving things I mostly care about 10% of the query, the JOINS and WHERE clause and CAPS keyword make those much easier to grasp. And the comma at the line start is very helpful (even though it looks odd for me) when you are adding/removing columns and sections on the fly. If the comma is at the start of the line you can simply comment, if it's at line end you need to edit two lines when removing a particular line from the query.

Book looks interesting but the reviews are a bit of a mixed bag mostly about copy editing, has that been your experience?

So far I didn't notice anything.

I've read one of Celko's books (not that one), and I confess I didn't find it very helpful. And hearing "think in sets" is the worst.

In all the algebra classes I've taken, one nice thing about sets is you can arrange them in any order you like, and you'll get the same answer any way you slice it. There's no inherently correct orientation of mathematics. That's kind of the point. It's fully generic, by default. "x" can mean anything (at least, in the algebraic structure we've assumed).

With SQL, the experts drone "think in sets!" as a way to mean "you happened to pick a different order than me, so your query will run 1000 times slower". Well, mine is still sets. I'm not writing a for-loop here (and I'm pretty sure my SQL dialect has those by now).

I've actually had the most luck with SQL by thinking about it in terms of looping first. Figure out the most efficient loop over your biggest table, and then write a query in a way that makes it easy for the optimizer to loop over that.

Then again, nobody ever accused me of being "perfectly intelligent".

For me, it's because I've never studied my database the way I do my programming language. I'll read a thousand pages to master a new language, and I'll take it all to heart. Meanwhile, I begrudgingly read a few pages about PostgreSQL (let's say) and move on to the next task ASAP.

PostgreSQL has great documentation, and I will give it a serious read one day.

Spending a few professional years using/learning PostgreSQL hard have paid off like crazy for me. It's a great server, doing my own devops for it is not much burden, and it doesn't hurt to have it splashed all over my resume.

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.

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.

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.

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.

Agreed, I didn't want to convey that it's not verbose, either :-)

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.

MartenDB. :)

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.

So... Marten DB...

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?

Well you don’t need to worry about creation, and migrations. You only need to tell the store which objects it needs to know about. You declare everything in c#.

It’s stores it as jsonb. So you don’t need to worry about weird relationships you only worry about your root aggregate. You can index it. Apply full text search. And persisted columns in the map if for convince or speed.

So Yeah it maps the linq to the equiv sql for you. But allows you to work with your object and worry less about persistence. Cos you can add and remove properties. It’s the closest thing I’ve come to, to being able to forget about the database and focus on code.

I Guess unless you write your own database from the ground up so it supports the language as a feature without dropping back to something else. It won’t happen.

Again that's great, its an ORM, that's what they do, try to abstract the DB from your language, and do their best to hide the impedance mismatch.

However the mismatch is always there, the overhead of mapping types, inefficient serialization (key names repeated over and over in json vs a real schema), certain statements cannot be transpiled to sql so you have pathologic cases at unexpected times, in ability to run any statement you want in the db tier to take advantage of locality and so on.

Most everyone wants a object database since most people are working with objects which is why ORM are so popular.

Closet thing I ever saw was db4o.

It’s jsonb so you can’t repeat key names. Serialization is what ever you want to use for json serialization. Other than some report type queries I wanted to write I haven’t found anything that I couldn’t do in linq.

jsonb doesn't do key interning as far as I know. Every field name will be repeated in every record vs a normal column which will only have the column name once in the db.

This really adds up on a large database. Mongo used to recommend short key names and I even think some client would translate them based on some definition to try and save disk/memory/io

https://github.com/postgrespro/zson tries to do key compression.

Also its not that Linq can't do what you want, its that if you do certain things that can't be translated to SQL the whole intermediate result will be retrieved from the db, deserialized then the linq statement will be run in memory on the client. Its like putting ToList in the middle of your linq statement where a maybe a few million rows might need to be processed to give the very small result.

If the linq statement was being run in process on the db server at least it would happen local to the data and would be more akin to a table scan vs index, still not great but much better then sending all the results to the client for processing.

I'm with you on this one. I was thinking it would be cool to "bake in" the DB layer of an application and have it run in-process. Then you'd just provide it with block storage and it would handle the rest without having to have a separate server. You could even do compile-time migration and index generation based on your queries. With a distributed actor framework like Akka.NET or Orleans, it could probably be made to scale too.

The only downside is that the only interface to your data is through the application, but there's certainly a use-case for something like that.

This is basically what most rdbmses have been offering for the last 20 years. Oracle rdbms is in fact an application server, and best practice is to have an app layer built using plsql package through which you access all the data.

More than 20 years ago I actually used it as a true application server, where the database application layer would even generate all the HTML code required to displayed your app ui.

And with database schemas you have proper sandboxing.

Postgres is object-relational database, so it can certainly be used in such a way (and I use it like that most of the time).

But this is quite a niche usage of PG and there are little to zero tools supporting it.

There's a huge list of caveats in Postgres's inheritance documentation [1], and even the obvious issues haven't been touched in decades. I've never heard of anyone using this feature.

At this point, I would say Postgres itself is a tool that doesn't support Postgres inheritance.

[1]: https://www.postgresql.org/docs/current/ddl-inherit.html

Yeah but it doesn't support .Net types directly everything must be translated between the two type systems. Also doesn't support executing .Net code in the PG process. These are the main mismatches that ORM's try to hide with significant overhead.

The impedance mismatch is not about having to translate, but being unable to translate. An yes, that problem exist with relational-only database, but not with PG where you can create composite types, can have arrays of composite types as a column and all other goodies which come with that.

I personally do not find useful to execute .NET code in PG, but technically you could do that.

As long as you can translate LINQ expressions to PG as you can most of them with Revenj you can avoid the pitfalls of mainstream ORMs.

Not sure I have seen a type or construct that could not be translated one way or another. The mismatch is the need for translation. Table per hierarchy, table per subclass etc.. References vs foreign keys. Instance identity vs primary key.

The ability to run procedural code on the db server allow for much better performance than sending data across the network to the app server for processing. Being able to share procedural code between app server and db allows for the choice to be made easily based on the best place to run it rather than whether it might have to be rewritten to move from one to the other. I suppose a plugin could be made for .Net in PG similar to plV8, SQL server has had .Net stored procedures some time with many issue and caveats so it doesn't get much use.

The problem with Linq and what makes it so nice to use is the ability to mix procedural code in the Linq statement ex: list.Where(x=>x.SomeMethod()) Very easy to make something that can't be translated and then starts streaming the entire intermediate result to the app server for processing SomeMethod that would be much more performant in the db tier local to the data.

You might want to take a look at starcounter. https://starcounter.io/ I haven't used it myself, but saw a demo in their office a couple of years 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!

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.

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

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?

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.

IME knowing SQL is a prerequisite to using an ORM efficiently.

IMO, a disadvantage of SQL is that it was designed for batch processing, not for modern editors. If you type

  select foo, bar from baz

auto-complete cannot help you type the field names because it doesn’t know what table(s) you’ll be querying. On the other hand, LinQ’s

  from baz select foo, bar

allows your editor to auto-complete the field names for you.

I much prefer it to arcane JS(ON) query formats someone dreamed up in a hurry, only to avoid SQL. Only exception I can think of was RethinkDB

ReQL was really nice. I miss that DB (Yes, i know it still exists - but is it still being worked on?)

> is it still being worked on?

Maybe? Seems like ReQL itself will live on, even if not the whole RethinkDB: https://github.com/rethinkdb/rethinkdb/issues/6747

Or is it the other way around? C++/C# concepts are insanely powerful, but they feel to me like we're trying really hard to drag the Simula data model into the 21st century.

Today if you asked me to design an object model for software to model objects, I wouldn't end up with anything remotely like C++/C# has.

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.

Reply on news.ycombinator.com