Things I wished more developers knew about databases • Hacker News Board

Re: Things I wished more developers knew about databases

#41

Post by vnorilo » Wed, Apr 22, 2020, 3:06 PM UTC

wenc wrote: post #16

I never realized this before but many excellent developers struggle with SQL beyond simple SELECT statements. I have a colleague who is by all accounts a deeply technical person but one day he confessed to me that he didn't really grok SQL and that he'd rather work with a "real" procedural programming language to just store and retrieve data. Part of it may be due to the fact SQL isn't really a programming language b…

I agree that this is in part why nosql became popular. I'm quite experienced with systems programming but databases were never central to my interests.

So when I needed one for my personal stuff, I went with CouchDB, because it's just a very nice RESTful API around a data structure I understand well.

Re: Things I wished more developers knew about databases

#42

Post by corey_moncure » Wed, Apr 22, 2020, 3:07 PM UTC

wenc wrote: post #16

I never realized this before but many excellent developers struggle with SQL beyond simple SELECT statements. I have a colleague who is by all accounts a deeply technical person but one day he confessed to me that he didn't really grok SQL and that he'd rather work with a "real" procedural programming language to just store and retrieve data. Part of it may be due to the fact SQL isn't really a programming language b…

I'm an SQL hater in remediation.

In a given week I might work with all of the following: SQL, C#, Python, JS (Kendo, Vue, React), XSL, bash, and more. I'm a quick learner and I pick things up fast, always have, I've got a deadline and I don't have the time or capacity to fully internalize the minutiae of all the technologies I have to work with. In other words I depend on the tools to show me the options at my disposal and construct syntactically correct expressions.

What I really dislike about SQL is common to most systems I dislike, where the tools aren't discoverable to me. In SQL it boils down to the fundamental syntactical requirement to put the SELECT clause before the FROM clause. So I have to build up my statement in this weird spiral pattern where I change something deeper in before I know what I can SELECT in the first place. The ability to give tables shorthand names with MS-SQL, i.e.

`left join [dbo].[sometable] st`

...is very helpful but I gather this isn't common to all dialects. Working with XML in SQL is a nightmare as the tool cannot tell you whether you can do obvious things like pass an sql:variable into nodes() until you actually query the server. (spoiler: you can't.)

I vastly prefer the functional programming approach to working with data e.g. C#'s lambda style linq. The tool shows me all the pieces I have to work with, and all I have to do is piece them together the right way.

Re: Things I wished more developers knew about databases

#43

Post by lowercased » Wed, Apr 22, 2020, 3:09 PM UTC

bdamm wrote: post #34

mgkimsal wrote: post #25

Earlier quoted context omitted.

> 2 years working on a non-trivial backend should expose one to these problems. You can be exposed to them, but without understanding them, and experiencing both good, bad, and really bad 'solutions' to them, and understand the impact (on the business, on the code, on security, on maintainability, etc)... you just can't really get all that in 2 years. I know plenty of people who've been 'exposed' to certain type of t…

I wish I could slap anyone who gives a hoot about tabs vs spaces. Fortunately modern languages like go are removing the version control problem that not caring about style and using auto-formatting IDEs produces.

I'm working in a couple of projects where there's a bunch of linter-checker things that prevent any PR merges (another... imo somewhat over-used tool) and... I split my time between Java, PHP, various SQL engines and various JS frameworks (react, extjs, vue, etc) and I'm constantly battling different mental models with various IDEs always showing different colored squiggles and highlights telling me all the ways I'm "wrong" about the code I've just typed.

Can't use double quotes! Always to use double quotes! "Prefer string/template interpolation" in JS Always do string concatenation per another project's standards.

Lots of different frameworks, languages, projects and companies all force different types of ceremony on formatting. "Hey, just let the tooling tell you!" turns in to constant UI distractions telling you that you're 'wrong' degrades (my?) performance. And... the value of most of these formatting things is pretty low, long term. I know that's heresy to some folks, and everyone I talk to sort of agrees, then says "yeah, but I really think standard XYZ is a good thing", but... it's nearly all preference, just like tabs/spaces.

Re: Things I wished more developers knew about databases

#44

Post by hderms » Wed, Apr 22, 2020, 3:13 PM UTC

LoSboccacc wrote: post #37

stronglikedan wrote: post #19

Earlier quoted context omitted.

I'd posit that autoincrements are fine for primary keys, but primary keys aren't fine for auditing.

how would you generate hole-free sequences for use cases like that of parents (invoicing)?

Not the OP but perhaps if only accounting cared about it you could assign these IDs separately as some asynchronous single threaded process. This would only work if insert rates were low enough or the assignment of the gapless sequence only needed to be eventually concistent over some window of time (i.e. before next payroll we must have assigned gapless IDs to all the new user accounts)

Re: Things I wished more developers knew about databases

#45

Post by pbowyer » Wed, Apr 22, 2020, 3:14 PM UTC

wenc wrote: post #16

I never realized this before but many excellent developers struggle with SQL beyond simple SELECT statements. I have a colleague who is by all accounts a deeply technical person but one day he confessed to me that he didn't really grok SQL and that he'd rather work with a "real" procedural programming language to just store and retrieve data. Part of it may be due to the fact SQL isn't really a programming language b…

I like SQL when I'm not writing reporting queries. GROUP BYs bite me (With MySQL 8 I end up reaching for the ANY_VALUE() function), and I end up with more subqueries than I feel I should need.

When working with time-indexed data I feel I'm forcing the database to do something it doesn't want to. E.g. if I want to answer the query "How many sales are there per day this month?" and I want an entry for every day in the month, even when there were zero sales. Or another query asking "Which days didn't have sales?" and listing the days. I haven't found a way to do this in-database. I end up answering in code based on the data I fetch.

Definitely feel it's me not SQL, but not found the answer.

Re: Things I wished more developers knew about databases

#46

Post by vsareto » Wed, Apr 22, 2020, 3:17 PM UTC

harikb wrote: post #23

vsareto wrote: post #12

Earlier quoted context omitted.

Many interests are pulling developers' attention in several different areas all the time. Database, security, accessibility, performance, infrastructure, tooling and productivity, business concerns, workflow processes (agile), language concerns, new things All of these like to say "if only the developer could do $MY_AREA better, they'd be better developers and we'd have better software". Each of them wants to pile on…

Not really. That is exactly the point the article is trying to make. Developers need to care about these things - _enough to know who to go get help from_. That is the minimum. Also 10 years is an exaggeration. 2 years working on a non-trivial backend should expose one to these problems. From what I have seen, products built without caring about these will usually get rebuilt a year from the original release - either…

>Not really. That is exactly the point the article is trying to make. Developers need to care about these things - _enough to know who to go get help from_.

That isn't it. They aren't telling you to learn about database details for the sole purpose of "DBA handles databases. Go ask the DBA database questions".

It's more than that. They're offloading specific knowledge onto the dev and then making them accountable for it. It's a reaction to common questions and an attempt to answer them all at once by teaching devs answers to common questions. This is a noble goal, but it's problematic in aggregate from multiple perspectives.

The aggregate goal of all of these areas trying to teach developers their own specialties is to make developers the masters of low-hanging fruit.

Re: Things I wished more developers knew about databases

#47

Post by gwbas1c » Wed, Apr 22, 2020, 3:17 PM UTC

wenc wrote: post #16

I never realized this before but many excellent developers struggle with SQL beyond simple SELECT statements. I have a colleague who is by all accounts a deeply technical person but one day he confessed to me that he didn't really grok SQL and that he'd rather work with a "real" procedural programming language to just store and retrieve data. Part of it may be due to the fact SQL isn't really a programming language b…

Part of the issue is that a complicated database can handle the same SQL query many different ways based on indexes and other configurations.

This kind of "magic" isn't always clear when programmers are mostly used to working with data structures and procedural code.

The other problem, IMO, is that programming languages are very poor at bridging the difference between the SQL domain and the language domain. We really need plugins for compilers because ORM libraries often are harder to learn than the database itself.

Re: Things I wished more developers knew about databases

#48

Post by pjc50 » Wed, Apr 22, 2020, 3:20 PM UTC

wenc wrote: post #16

I never realized this before but many excellent developers struggle with SQL beyond simple SELECT statements. I have a colleague who is by all accounts a deeply technical person but one day he confessed to me that he didn't really grok SQL and that he'd rather work with a "real" procedural programming language to just store and retrieve data. Part of it may be due to the fact SQL isn't really a programming language b…

This is partly why I love LINQ: it's a more C# flavoured way of expressing queries. And it works on objects as well as databases.

Most ORMs are bad for queries (pull over all the objects and look at their properties!), but LINQ will actually turn your code into SQL under the hood with some remarkable machinery.

Re: Things I wished more developers knew about databases

#49

Post by btilly » Wed, Apr 22, 2020, 3:22 PM UTC

fimdomeio wrote: post #27

wenc wrote: post #16

I never realized this before but many excellent developers struggle with SQL beyond simple SELECT statements. I have a colleague who is by all accounts a deeply technical person but one day he confessed to me that he didn't really grok SQL and that he'd rather work with a "real" procedural programming language to just store and retrieve data. Part of it may be due to the fact SQL isn't really a programming language b…

I struggle with lack of experience with SQL by always being told that I should always use an ORM or I would regret it in the future when I would change database technology. I'm in the future now and spend a lot of time debbuging the ORM and the sql statements it produces, when I could split that work in half by not using the orm at all. Would also have a lot more experience with sql so there would probably be less bu…

If you're writing a CRUD application, an ORM saves a lot of headaches.

If you're doing complex reporting queries, an ORM is strictly worse. And yes, I've seen developers, architects, and authors of ORMs that believed otherwise. They are wrong.

As an example, very, very few ORMs can make the distinction between

    SELECT ...
    FROM foo
      LEFT JOIN bar
        ON foo.id = bar.foo_id
          AND bar.category_id = 5
      LEFT JOIN baz
        ON bar.id = baz.bar_id
    ...

and

    SELECT ...
    FROM foo
      LEFT JOIN bar
        ON foo.id = bar.foo_id
      LEFT JOIN baz
        ON bar.id = baz.bar_id
          AND bar.category_id = 5
    ...

(And if you get into things like analytic queries, just forget about it.)

Re: Things I wished more developers knew about databases

#50

Post by andybak » Wed, Apr 22, 2020, 3:23 PM UTC

pjc50 wrote: post #48

wenc wrote: post #16

I never realized this before but many excellent developers struggle with SQL beyond simple SELECT statements. I have a colleague who is by all accounts a deeply technical person but one day he confessed to me that he didn't really grok SQL and that he'd rather work with a "real" procedural programming language to just store and retrieve data. Part of it may be due to the fact SQL isn't really a programming language b…

This is partly why I love LINQ: it's a more C# flavoured way of expressing queries. And it works on objects as well as databases. Most ORMs are bad for queries (pull over all the objects and look at their properties!), but LINQ will actually turn your code into SQL under the hood with some remarkable machinery.

Not doubting there are bad ORMs but none of the ones I've dived into is quite that bad (SQL Alchemy and Django's ORM).

I sometimes wonder if the debate about ORM is mainly driven by some traumatic horror witnessed at some point in every developer's career. If we judged programming languages by the same metric... Oh... Actually we kind often do that too!