2018-03-10 SQL Statements

There was an interesting discussion on Mastodon these days where we talked about databases and SQL. It all started with Nate Cull talking about design (and user freedom) but soon the conversation drifted to command line interfaces, graphical user interfaces, and finally SQL.

To me, many of the problems of SQL arise because of how we use it:

  1. We are very restrictive with permissions. How much easier to solve problems if you can creat a ton of temporary tables. How much easier the queries. But if we restrict users by default and don’t let them access the data, don’t let them work with the data, then of course they don’t like it. Imagine getting all the data from a big spreadsheet and being told that you could only use Notepad to work with it.
  2. Embedding it in apps. That can be dangerous! Embedding the magic strings is tricky because of escaping. If your host language is statically typed, then casting the result back into values your language understands might lead to type cast errors at run time. Concatenating user input with SQL statements in order to dynamically generate new statements is dangerous. User input has to be sanitised extra carefully. (But shouldn’t you do this always?)

Let me be clear: I don’t mind embedding SQL in apps given that I don’t want two different ways to access data in a database. Being able to copy and paste SQL statements from source code and trying them in a general REPL is great for development and for debugging. But for people who come from a strict programming background (Java only for example) the embedded SQL (strings, no type safety, no compilation) is abhorrent. That’s why we have Java based QL now and it makes debugging hard. 😒

At the office they are all gung-ho about getting rid of SQL statements and replacing them with a QL that generates the statements and thus guarantees type safety (database tables generated from interfaces and bind variables generated from the same interfaces). I tell them that this catches the kinds of errors I don’t make and makes my life more difficult regarding the errors I do make, but it’s not helping.

Tags:

Comments

Relevant, I think, and possibly of interest: http://blogs.tedneward.com/post/the-vietnam-of-computer-science/

Aaron 2018-03-10 18:58 UTC


That’s a lot of text... about Vietnam! And ORM! And I must confess I started skimming it after about a third but the things I read did seem eerily familiar. So yes, totally of interest! I posted it at the office, perhaps it will lead to some interesting discussions. Thanks!

– Alex Schroeder 2018-03-11 21:16 UTC


One coworker commented: “I love the smell of OR-Mapping in the morning.”

Heh!

– Alex Schroeder 2018-03-12 14:00 UTC


Please make sure you contribute only your own work, or work licensed under the GNU Free Documentation License. Note: in order to facilitate peer review and fight vandalism, we will store your IP number for a number of days. See Privacy Policy for more information. See Info for text formatting rules. You can edit the comment page if you need to fix typos. You can subscribe to new comments by email without leaving a comment.

To save this page you must answer this question:

Please say HELLO.