Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Things I wished more developers knew about databases

medium.com

Post reply on HN
Print view View original

111–120 of 464 posts

Re: Things I wished more developers knew about databases

#111

Post by lovegoblin »

petepete wrote: post #32
rietta wrote: post #21

Earlier quoted context omitted.

I sometimes feel in the minority. I love databases. When I work in the Ruby on Rails ORM ActiveRecord I can actually visualize the SQL it is generating in my head and also do all sorts of tricks when needed.

That's the key. ORMs get a bad name but most of the time you just want to display a list of things, or one thing in more depth or maybe create a new thing. ORMs unfortunately, have a habit of getting in the way when you want to do something they don't natively support. When they just ignore things the database provides people just end up reinventing the wheel. Rails' implementation of enums is a good example of this.

> Rails' implementation of enums is a good example of this.

The advantage of ActiveRecord enums (vs db-native) is that you can change the list of valid values without having to run a whole ALTER TYPE - and all the overhead that would entail in doing without downtime in a production db.

Re: Things I wished more developers knew about databases

#112

Post by btilly »

jakearmitage wrote: post #61
btilly wrote: post #49

Earlier quoted context omitted.

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 = b…

Curious, what is the difference here?

The difference is whether the condition on bar is a filter is on the join from bar or baz.

Re: Things I wished more developers knew about databases

#113

Post by ffdjjjffjj »

falcolas wrote: post #7

(The 80/20 rule applies below, some developers do care) Developers... just don't care. They want to spin up an ORM, point it at a URI, and forget about it. I've fought this for over a decade now as a DBA, SRE, DevOps, and architect. Most of the developers don't want to deal with anything infrastructure-wise; they want to spend all the time they can just focusing on the problem they're writing software to solve. Obser…

You’d be surprised how much rank and file developers can care about observability and reliability. The key to unlocking this is making them responsible for how their code runs in production by adding them to the on call schedule.

Re: Things I wished more developers knew about databases

#114

Post by crazygringo »

taffer wrote: post #89
crazygringo wrote: post #52

Earlier quoted context omitted.

SQL is very much like CSS to me. It's declarative, the primitives seem entirely non-intuitive, it often takes a lot of fiddling to get what you want, the behind-the-scenes execution is mostly a black box, and while it's supposed to work the same on different implementations (of browsers/databases), there are tons of little gotcha quirks. All in all, they're both entirely different skill sets from traditional programm…

When you look at SQL from a logical/set-based perspective, it is by no means unintuitive. Basically, all you do is join all the tables you need and then filter out everything you don't need and maybe do an aggregation here and there.

How about the following:

- When to use JOIN vs a subquery?

- When is a subquery actually a correlated subquery? Will this destroy your performance? Or is it a critical feature?

- Should you put constraints in the JOIN or in the WHERE? Will the distinction drastically affect performance?

- When do you use WHERE vs HAVING?

- Is the NULL from the join because no joined row was found, or because the joined row had a NULL value itself?

Etc. etc. The basic concepts are simple, but the implementation details quickly become very complex, particularly when you're ensuring high performance with indices, and making sure the query uses the indices.

And all of the questions I pose above have clear answers... but the answers certainly aren't obvious from SQL basic concepts.

And also, while JOIN seems like it ought to be intuitive, in real life it seems like it's like pointers in C -- some people get it pretty quickly, other people struggle forever.

Re: Things I wished more developers knew about databases

#115

Post by btilly »

gtsteve wrote: post #108
jakearmitage wrote: post #61

Earlier quoted context omitted.

Curious, what is the difference here?

I believe they may have intended to write this query with INNER JOINs instead of LEFT JOINs. If so, the result of the query would be identical but the second one would likely have performance problems given the category_id filter is not being applied at the point where bar is joined. A clever database engine might find an optimisation but I wouldn't count on it. This illustrates the subtle problem that ORMs can intro…

No, left joins were intended here. They mean different things.

With inner joins the semantics are the same. If the performance is different, the database query optimizer is broken.

Re: Things I wished more developers knew about databases

#116

Post by eternauta3k »

karatestomp wrote: post #97
TremendousJudge wrote: post #82

Earlier quoted context omitted.

> The application language was a pass through later between the client and the database. This style of doing things resulted in spaghetti style unmanageable databases, filled with an unknowable number of triggers and procedures, all written in PL/SQL (which is much, much worse than either Java or PHP). The reason why ORMs started to become popular is that you can write your application without filling your DB with ar…

But going too far the other way is how you end up with performance 100-1000x worse than it should be. Which is an actual thing that happens quite often in the wild. Also often ends up heavily dependent on some ORM or framework, making rewrites or multi-client DB access dangerous and painful.

You can create pretty complex SQL queries with SqlAlchemy's ORM, without going back and forth to the DB.

Re: Things I wished more developers knew about databases

#117

Post by takeda »

frosted-flakes wrote: post #92
treis wrote: post #58

Earlier quoted context omitted.

>This kind of "magic" isn't always clear when programmers are mostly used to working with data structures and procedural code. My problem is that it is like some sort of black magic to me. If I write a complex query I have no idea if what is spit back to me is actually what I want. The only way is seeding lots of records and then manually checking that each filter and calculation is doing what I want. In code complex…

Yes, SQL's biggest fault is that it's not very composable. Complex queries end up being long and repetitive, and the order of the parts of a query is totally unintuitive (it should go something like: FROM, GROUP BY, SELECT, ORDER BY rather than SELECT, FROM, GROUP BY, ORDER BY, which makes autocompletion hard).

QUEL[1] was like that, but thanks to Oracle SQL won.

[1] https://en.wikipedia.org/wiki/QUEL_query_languages

Re: Things I wished more developers knew about databases

#118

Post by seppel »

balls187 wrote: post #79
LoSboccacc wrote: post #37

Earlier quoted context omitted.

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

If it was a business requirement that you have perfectly sequential invoice numbers with no gaps, do it at the application level, not at the storage level. Let the database do what it's great at doing: efficiently store and retrieve data.

A database is also exceptionally good at doing transactional stuff like atomically incrementing something. I'd even say: This is something that belongs in the database and not in some brittle application logic.

Re: Things I wished more developers knew about databases

#119

Post by lowercased »

yjftsjthsd-h wrote: post #91
lowercased wrote: post #87

Earlier quoted context omitted.

So there are tools that autoformat as you type? let longVariableName = "hello " + this.name; magically becomes const longVariableName = `hello ${this.name}`; without visually flagging it, just autocorrects as I type? And... I can just quickly swap settings for different clients? Because one has eslint block any PRs that don't have "prefer template" rules followed but another client doesn't like that style, and don't…

I don't think that counts as a formatting issue. Yes, if your clients have hard rules about different coding styles at that level then it's not a technology problem (nor is it likely solvable with technology). I assumed we were talking about formatting issues like tabs-vs-spaces, in which case yes every single project could be different but auto-fixed.

thx. sorry, i sounded a bit snarky before and wasn't meaning to be. it's just easier for people to focus on visual issues vs operational/functionality. and switching between multiple projects/clients/standards illustrates to me how relatively unimportant some of these things are (but of course just imo).
Post reply on HN