Live data from Hacker News

Things I wished more developers knew about databases

medium.com

151–160 of 464 posts

Re: Things I wished more developers knew about databases

#151
post #70
post #20

Learn about modelling. Database is more than just storing data. Drink less koolaid of NoSQL, any NoSQL. It is trading initial result with future development time. SQL has been battlefield tested. No amount of "convenience" is more convenient than learning the fundamentals.

Any tips where to look for db modelling learning resources?

I actually don't know. There was this previous discussion https://news.ycombinator.com/item?id=22324691. Might be some gems there.

For me, it is the 3rd normal form and the adage of "Normalize till it hurts, denormalize till it works".

Re: Things I wished more developers knew about databases

#152
post #48

Earlier quoted context omitted.

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.

Which ORMs are bad for queries? Most popular ORMs these days expose most of sql in a language-specific DSL (and some allow you to splice in bits of raw sql in a semi-structured way as an escape hatch). Sure, you still need to learn to "think in sql", to use these effectively, but the ORM has not been the problem for a long time in most languages.

ActiveRecord, Rails ORM was the poster child of ORMs for a while, and is completely abysmal for non-trivial left joins, non-trivial aggregations and some subqueries.

Of course using raw SQL is possible but might force you into converting other parts of the code into raw SQL. There is Arel, but it's just a more verbose SQL in quasi-AST form.

On the other hand, I never had much problems with LINQ or Hibernate.

Re: Things I wished more developers knew about databases

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

Too bad there isn't an equivalent on-call punishment for management and others around the dev process who also make mistakes and bad decisions.

As far as I can tell, they get paid more for doing less.

Re: Things I wished more developers knew about databases

#154
post #134
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…

SQL definitely solves a lot of performance issues for typical users who think they can do better but throwing strings over the wire, trying to get as much as possible without crossing cardinalities and ignoring the n query problem makes SQL at scale disastrous Devs always get one of these things wrong and not being able to use my programming language in the query makes some things hard to express Datalog and Datomic…

could you clarify what throwing strings over the wire and crossing cardinalities means in this context?

Re: Things I wished more developers knew about databases

#155
post #103

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…

There is an entire world between ORM and PL/SQL. Programmatically constructing SQL statements is also a thing. Just because someone writes SQL does not mean SQL needs to be spread through out code or that we need to have lot of logic in PL/SQL. Of course, there will be cases where a store procedure is desired (any kind of validation that cannot be expressed as fkeys, canonicalization of some core data components etc)…

How did you minimize roundtrips between server and DB, or did you find that they were not a big concern?

I'm working on a project with a Postgres database, and as it gets more complex I'm moving more stuff into stored procedures, pretty much wherever a single action requires multiple statements in series (e.g. check if this thing exists, check a value, get the id of some other thing, on success update another table).

Of course I would prefer to leave as much of it in the middle tier as possible because the ergonomics are better there but I don't want to sacrifice performance.

Re: Things I wished more developers knew about databases

#156

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)?

You could do something like this.

Create a table with two columns:

1. auto-incrementing primary key

2. integer for the actual number you're generating, let's call it 'IDValue'

Seed the new table with a single row with IDValue set to one less than your minimum value (say 0), then use the following process to generate a new number:

1. Insert a new row into the table, with a known invalid value (e.g. -1) for IDValue (note this must not be the same as the IDValue from your initial row)

2. Get the primary key of the newly inserted row

3. Get all the rows from the table (in primary key order) with primary key Key / IDValue

61 / 1119

62 / 1120

64 / 1121

65 / -1

67 / 1123

70 / -1

71 / -1

(your row is the next one after this)

4. Your new IDValue == the last valid IDValue in that set of rows + the number of rows between that and your new row + 1 - update your row with this new value - in the above example, 1123 + 2 + 1 - i.e. 1126

5. Delete the first unbroken sequence of valid rows except for the latest one, to keep the table small but leave at least one valid IDValue (IDValues 1119 and 1120 in the above example) - just something like DELETE FROM table WHERE Id The database takes care of atomically creating rows which is the tricky bit, and then you can generate your own number at your leisure regardless of gaps in the Key numbering sequence.

Re: Things I wished more developers knew about databases

#158
post #89

Earlier quoted context omitted.

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.

And with CSS, basically all you do is tell the browser how things look.

Conceptually yes. The devil is in the details: tables, flexbox, grid, div-soup, inconsistent naming, etc.

Re: Things I wished more developers knew about databases

#159
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…

Swapping out database platforms is pretty deep into YAGNI territory for most. Unless you know you are selling an on-prem software product to some customers who will demand MSSQL and others who will demand Oracle, or whatever, this "swap databases" justification for scrupulously using an ORM is not well grounded in reality.

Re: Things I wished more developers knew about databases

#160
post #89

Earlier quoted context omitted.

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…

I will be very glad if you actually answer these questions, in a separate comment. I'm driven to write this by nothing but the desire to know.
Post reply on HN