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

Re: Things I wished more developers knew about databases

#271

Post by wenc » Wed, Apr 22, 2020, 10:10 PM UTC

cryptonector wrote: post #263

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…

Not only that, but VIEWs can have INSTEAD OF triggers, which then lets you build powerful abstractions in SQL.

Amazing. I didn't know it was possible to write to a VIEW.

Re: Things I wished more developers knew about databases

#272

Post by marcosdumay » Wed, Apr 22, 2020, 10:10 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)?

Usually, you don't. What kind of messed up requirement is that? Do you want the sky to be green at sunrise tomorrow too?

On the exceptional case where the user is on the clear and there is an unavoidable reason to create this beast, you create a separate numbering application, that uses database transactions that are independent from the ones of the main application and only cares about numbering. It will still have holes, but few enough that you can manually inspect every so often and explain why they happened.

Re: Things I wished more developers knew about databases

#273

Post by bdcravens » Wed, Apr 22, 2020, 10:15 PM UTC

chaps wrote: post #257

nuggien wrote: post #255

Earlier quoted context omitted.

Nothing about go is magical, and you probably solved the problem blindly.

Wow, okay. You alright over there? Edit, because meh: I'm making no claims about go itself. No idea what makes you think that's what I'm saying, since I'm clearly talking about a library, and not even any stdlibs. "Magic" is just a term useful for describing systems that sweep much of their abstractions under the carpet in a way that probably has gotchas. Granted, the term itself is magical. In terms of fixing the pr…

I presume they took it as an attack on Go. Truth is, it's an attack on the library developer who themselves may have found their keep-alive solution by stumbling blindly on it.

Re: Things I wished more developers knew about databases

#274

Post by codegladiator » Wed, Apr 22, 2020, 10:30 PM UTC

brabel wrote: post #261

osrec wrote: post #228

Earlier quoted context omitted.

I've seen older developers that call themselves senior, but lack basic knowledge. I've seen younger developers, wise well beyond their years. Age simply isn't a big factor in how you judge a developer.

Just because you saw a few exception does not mean the rule does not hold in general. Or are you saying most people don't learn with time (a corollary of your theory that age is not a big factor)?

> most people don't learn with time

I think this is correct. The curve flattens with age (in my opinion).

> you saw a few exception

I think the seniors you met (the knowledgeable ones) /were/ the exceptions. Most seniors I have seen have convinced themselves that they know all there is to know.

Re: Things I wished more developers knew about databases

#275

Post by dirtydroog » Wed, Apr 22, 2020, 10:30 PM UTC

I find SQL is quite like Python. You know what you want to do, you can take a stab at attempting a couple of times but eventually you'll consult stackoverflow and kick yourself for not figuring it out. Repeat the above in two weeks when you've forgotten it.

Re: Things I wished more developers knew about databases

#276

Post by kerng » Wed, Apr 22, 2020, 10:33 PM UTC

chaps wrote: post #149

Here's a fun bug I had a few years ago - Had a postgres database which was using pgbouncer for connection pooling. The most senior developer (24yo or so) we had on the project was using Go to connect to the database to write some simple reports, but each report took hours to run, and often had to sleep for 30+ minutes. So, after a while, pgbouncer would kill their connection, and their report would die. No other appl…

Go code often reinvents/reimplements a lot of things from scratch, reintroducing problems that have been addressed long ago in other systems.

It's like this new trend, let's rewrite everything in Go to be cool. Financially makes little to no sense.

Re: Things I wished more developers knew about databases

#277

Post by deckard1 » Wed, Apr 22, 2020, 10:40 PM UTC

wenc wrote: post #198

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…

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…

> highly performant API that outperformed REST at scale -- it's called SQL

You are conflating many disparate things here.

SQL is a language (DSL) for accessing data. REST is a protocol and a data transport method (one could surmise a way to do REST without HTTP, but when reasonable people refer to REST they mean HTTP (over TCP (over IP (etc.)))).

Even REST is not an API. You can't do anything with a GET or a POST without other abstractions built on top of that. So I don't understand how anyone could make performance claims beyond something like "HTTP is slow" and "binary transport is faster", with regards to SQL vs. REST/HTTP.

SQL does not define how you receive your data. Databases have different methods of sending SQL and responding to SQL. Oracle, MSSQL, MySQL, etc.

> This centralization of the core logic becomes especially powerful if the database is accessed from multiple consumers

That's the entire point of an API. Any API. REST APIs, even.

Re: Things I wished more developers knew about databases

#278

Post by nuggien » Wed, Apr 22, 2020, 10:42 PM UTC

chaps wrote: post #257

nuggien wrote: post #255

Earlier quoted context omitted.

Nothing about go is magical, and you probably solved the problem blindly.

Wow, okay. You alright over there? Edit, because meh: I'm making no claims about go itself. No idea what makes you think that's what I'm saying, since I'm clearly talking about a library, and not even any stdlibs. "Magic" is just a term useful for describing systems that sweep much of their abstractions under the carpet in a way that probably has gotchas. Granted, the term itself is magical. In terms of fixing the pr…

I have no affection towards go or any language. They're just tools. You sounded elitist calling something magic and pointing out someone's age as part of your point. And your passive aggressiveness to my response is proof of it. I "genuinely" hope you're doing alright too.

Re: Things I wished more developers knew about databases

#279

Post by chaps » Wed, Apr 22, 2020, 10:45 PM UTC

nuggien wrote: post #278

chaps wrote: post #257

Earlier quoted context omitted.

Wow, okay. You alright over there? Edit, because meh: I'm making no claims about go itself. No idea what makes you think that's what I'm saying, since I'm clearly talking about a library, and not even any stdlibs. "Magic" is just a term useful for describing systems that sweep much of their abstractions under the carpet in a way that probably has gotchas. Granted, the term itself is magical. In terms of fixing the pr…

I have no affection towards go or any language. They're just tools. You sounded elitist calling something magic and pointing out someone's age as part of your point. And your passive aggressiveness to my response is proof of it. I "genuinely" hope you're doing alright too.

Heh, I wasn't being passive aggressive, I was being serious. Take a breath, man.

Also "magic" is not a new term: https://en.wikipedia.org/wiki/Magic_(programming)

Re: Things I wished more developers knew about databases

#280

Post by osrec » Wed, Apr 22, 2020, 10:52 PM UTC

brabel wrote: post #261

osrec wrote: post #228

Earlier quoted context omitted.

I've seen older developers that call themselves senior, but lack basic knowledge. I've seen younger developers, wise well beyond their years. Age simply isn't a big factor in how you judge a developer.

Just because you saw a few exception does not mean the rule does not hold in general. Or are you saying most people don't learn with time (a corollary of your theory that age is not a big factor)?

I would say that I have seen no discernible pattern, so I have learnt that it is imprudent to judge a developer by their age. People do learn over time, but some people learn more "cogently" than others, i.e. some get more out of one year's worth of learning than others get in 10.