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

Re: Things I wished more developers knew about databases

#411

Post by kerkeslager » Thu, Apr 23, 2020, 5:29 PM UTC

gfxgirl wrote: post #334

rimliu wrote: post #329

Earlier quoted context omitted.

Another option is to learn from the mistakes of others.

The problem is you don't even know where to learn about the mistakes of others until you make the mistake yourself and in making the mistake you get some clue was to what to search for that then uncovers the mistakes of others.

I've found that when I ask others what mistakes I should avoid, they tend to answer the question.

Another way to discover any mistakes is to ask people why they didn't do certain things which you think are good ideas. Often that reveals that they did do that, and it turned out poorly.

Re: Things I wished more developers knew about databases

#412

Post by eeZah7Ux » Thu, Apr 23, 2020, 5:39 PM UTC

101404 wrote: post #353

dvtrn wrote: post #318

Earlier quoted context omitted.

Because then it’s harder to deal with our own imposter syndromes if we can’t blame it on the youth and hold their heads in the toilet while giving them the professional-development equivalent of a wedgie. This was discussed at length in last week’s “Grey Beard Weekly” newsletter.

So the "old people" are the bad guys for you, hmm. Then again, it's mostly "old people" who are discriminated against when it comes to hiring.

It seems so, and the fact that people are accusing me of ageism as I pointed out that a company hires only people below 24 years of age is very telling...

Re: Things I wished more developers knew about databases

#413

Post by wenc » Thu, Apr 23, 2020, 5:46 PM UTC

MaxBarraclough wrote: post #407

wenc wrote: post #198

Earlier quoted context omitted.

There's a middle way which is very powerful: SQL views (just SQL queries; no triggers or procedures) Here's a powerful mindset trick: think of SQL views as an sort of a REST API , but whose access language is SQL and not HTTP, and that returns data in a table rather than JSON (hierarchical). I once tried to build a REST API to a database, and someone told me I already had a battle-tested and highly performant API tha…

What do you make of tools like LoopBack which automatically map REST to SQL (without you having to write code for each mapping)? https://loopback.io/doc/en/lb4/Database-connectors.html

Postgres has something similar called pgREST too. I think I would only adopt these kinds of interfaces if the consumer insists on accessing the data through a REST interface.

If you are building something from scratch, or your consumers don't have a hard requirement for going through REST, I would go directly to the database view.

Re: Things I wished more developers knew about databases

#414

Post by UncleSam » Thu, Apr 23, 2020, 5:52 PM UTC

baud147258 wrote: post #360

shifto wrote: post #352

Earlier quoted context omitted.

Wouldn't be the first developer to just SELECT * with a thousand joins and then sort data in app(!).

Or even worst/better, thousand of SELECT with joins returning one result and then sort in app.

This sounds like the Hibernate Execution plan I found one time when it wasn't configured properly.

Re: Things I wished more developers knew about databases

#415

Post by rspeele » Thu, Apr 23, 2020, 5:56 PM UTC

castorp wrote: post #258

homami wrote: post #231

Earlier quoted context omitted.

I find SQL INSERT statement not intuitive. I can understand why SQL requires me to declare the field names and then the values of a new row that I am inserting; but it would've been a huge time saver if SQL had a key-value dictionary-like syntax: INSERT INTO "my_table" "col1": value1, "col2": value2, ...

And when you need to insert more than one row, you repeat the column name over and over again for each row - not a good idea either.

Single-row inserts are super common in both application code and in interactive usage of SQL, so I think that it is worth it to have a syntax for them that reduces this common error. Especially when a table has many columns of the same type (like booleans). E.g.

insert into Permissions(UserId, Create, Read, Update, Delete, Share, ForceUnlock, LaunchNukes) values (12345, 1, 1, 0, 1, 0, 0, 1);

When I wrote my (now unmaintained) statically typed SQL dialect for F#, which compiles to underlying SQLite/Postgres/MSSQL, I added a single-row insert with Field=Value because it's nice to have and took no more than 30 minutes to do. It's only a tweak to the parser after all -- you just parse it to the same AST used to represent the `INSERT ... VALUES` clause and all later stages of the compilation do not need to know about it.

https://rspeele.gitbooks.io/rezoom-sql/doc/Language/InsertSt...

Re: Things I wished more developers knew about databases

#416

Post by inopinatus » Thu, Apr 23, 2020, 5:56 PM UTC

kerkeslager wrote: post #410

inopinatus wrote: post #408

Earlier quoted context omitted.

The question was and is rhetorical. People don’t inwardly digest the mistakes of others. And anthropomorphising a language? Most peculiar. None of this makes NIH a less than widespread phenomenon. The readers of this forum often do know their type theory. Gatekeeping otherwise won’t go over well, it just reads like an arrogant insult from someone utterly lacking in self-awareness and accustomed to presuming themselve…

> The question was and is rhetorical. The fact that you ask a question not expecting a direct answer is not proof that a direct answer does not exist. > People don’t inwardly digest the mistakes of others. In my spare time, I'm a rock climber, and mistakes in my rope systems can kill me. The same is true in mycology, firearms, airplane piloting, civil engineering. If you really feel that you can only learn from your…

No, but it’s a trap. If someone answers an obviously rhetorical question, they’re inadvertently demonstrating a predilection for engaging the construction, not the substance, of a statement, and almost certainly missing the ironic subtext.

I’d be happy to repeat my assertion though. People don’t inwardly digest the mistakes of others, which is why educators on safety-critical topics such as those mentioned must go to extraordinary lengths to extract and convey the salient teachings, translated into better practices, drills, equipment etc.

Reading the archives of the Dropzone fatalities database, for example, won’t make me a better skydiver.

Conversely, the best structured educational processes I’ve experienced are essentially offering the student the opportunity to make their own mistakes, but under circumstances that don’t have consequences (other than pedagogical or scholastic)

Re: Things I wished more developers knew about databases

#417

Post by devonkim » Thu, Apr 23, 2020, 5:59 PM UTC

vb6sp6 wrote: post #401

devonkim wrote: post #391

Earlier quoted context omitted.

Because we got rid of DBAs in favor of “big data” developers that never learned much about SQL in the first place.

Because 64gb of ram is really cheap these days. It no longer makes sense to tune your queries, or to wait weeks\months for the vendor to tune their queries, when you can just slap a few sticks in and call it a day.

If you're like a company I was at before, you'd pay $10k+ for DB consultants to tune some queries and your prod database, and when migrating your DB to new hardware forget to re-tune it and waste the extra 64GB and even SSDs installed. There should still be a bare minimum floor of competence for actually developing against and maintaining databases organizationally whether it's a DBA, better engineers, etc. Throwing hardware at a problem is fine when you're sure that you are actually throwing it in the first place which I have seen surprisingly few places do well.

Re: Things I wished more developers knew about databases

#418

Post by rietta » Thu, Apr 23, 2020, 6:09 PM UTC

evsasse wrote: post #386

petepete wrote: post #380

Earlier quoted context omitted.

And the disadvantage is that you need to look at the code to understand the data. Some things change infrequently enough for DB native to be the better solution.

A nice "trick" is to declare your enum column on the database as a string, and your enum in the code as a hash with string values. So you can have self-explained data saved onto the database, and all the niceties Rails gives you from the enum. enum object_type: { review: "review", purchase: "purchase", offer: "offer", reward: "reward" } # OR enum action: TYPES.map { |type| [type.to_sym, type.to_s] }.to_h

Nice! I've done #1, but #2 is a neat trick.

Re: Things I wished more developers knew about databases

#419

Post by rietta » Thu, Apr 23, 2020, 6:12 PM UTC

flukus wrote: post #316

petepete wrote: post #32

Earlier quoted context omitted.

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.

> 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. If you just need to display a list of things then there's nothing much simpler than: var result = exec_query("select * from things") foreach (var row in result) { //output html or something here } The problem is we decided this was bad and had to add more layers…

In Ruby on Rails, ActiveRecord has a method called pluck that returns the result set as an array of strings or as an array of array of strings. I've totally done this in areas that need performance. In my experience, if you are concerned with just showing some data you should specify the columns instead of doing a *. ActiveRecord also supports that.

So, for the bulk case. ORM is safer than straight SQL and allows for more easily testable business logic. It does not block doing the things you suggest even if it is not the most common approach.

Re: Things I wished more developers knew about databases

#420

Post by breischl » Thu, Apr 23, 2020, 6:33 PM UTC

LouisSayers wrote: post #297

breischl wrote: post #163

Earlier quoted context omitted.

But now you're filling your application with arcane and inscrutable logic, with an extra layer of abstraction via the ORM to make it even less scrutable. I think one should view a SQL DB like a microservice. Instead of REST endpoints (or gRPC or whatever), create stored procedures. These define a strong contract with your DB, the capabilities that it provides to your app(s). Now you know what the query and insert pat…

I remember people I used to work with arguing against stored procedures for two main reasons. 1) Version Control - I guess a lot of the stored procedures were being put straight into the DB without recording a history of the changes. These days you could easily do this using DB migrations I guess. 2) Testing - is unit testing a thing for Stored Procedures? I guess again, you might be able to do this from code as well…

1) I find this kinda funny. Why are you worried about this in SQL but not for other code? It's not like it's hard to chuck Python, JS, Ruby, ASP, etc code straight into prod, you just don't do that because it's stupid. Don't do it for SQL either. If you really want to, build out user permissions that only allow your CICD system to change them.

2) TBH we never built anything complex enough to need this, and I would tend to think that if you do need this you're probably overcomplicating your DB. But you could probably do something that creates a temp DB, populates example data, and then runs tests.