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

191–200 of 464 posts

Re: Things I wished more developers knew about databases

#191

Post by beckingz »

lhorie wrote: post #137
brightball wrote: post #56

Earlier quoted context omitted.

I firmly believe that every developer should spend 2-3 weeks early in their career working with nothing but SQL. It will pay huge dividends for the rest of it. IMO a lot of the issue is that developers for many years using Java or PHP, were using SQL to handle everything. The application language was a pass through later between the client and the database. Your goal was to accomplish as much as possible in a single…

With the whole shelter-at-home thing, I had a chance to work on a simple app for my kids. One of the corollaries of "simple" was avoiding an ORM. Implementing logic is single SQL queries can definitely be a bit challenging, but I thought it was also quite rewarding and liberating - Raw SQL is incredibly powerful!

At first I missed for loops.

Then I was empowered and did not miss for loops.

Now I still miss for loops.

Re: Things I wished more developers knew about databases

#192

Post by StandardFuture »

AndrewKemendo wrote: post #179
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…

10, 11 year timeline to what? You should be constantly learning everything about the whole stack so that you can actually build functional, reliable, manageable and maintainable systems. I expect a competent developer to be able to build a modern multi-page web application, with a HTML/JS front end, relational database back end, appropriately configured certificates and DNS/CNAME/URL, build basic uptime and applicati…

You seem to be contradicting yourself without realizing it.

> You should be constantly learning everything

This is an in-progress action i.e. the developer is still learning.

> I expect a competent developer to be able to build

This is now considering a "learned" developer.

I am not sure you are making the point you think you are making. The point I think you are trying to make is your expectations of what an experience developer should know. But, you seem to be expressing it as what a new developer should be doing.

While the core discussion of the article might be in regards to what developers do and do not know, I can't help but notice that a developer knowing about something does not necessarily allow them to be productive in that area of their knowledge (especially relative to another co-worker with both the knowledge and the dedicated focus in that area).

Also very important to note: the comment you are addressing seems to be referring to knowledge that is local (particular customer necessity/problems, particular architecture choices for infrastructure, particular product design decisions, particular ways to answer the quirky CEO/CTO in a way that they understand, etc.). There is a lot of locale-based knowledge that a developer must learn at a company/job/project and can even change over time (temporal-locales).

Globally-applicable knowledge like frontend, backend, and general CS concepts are for sure a reasonable expectation of an experienced developer. But, there is a delicate balance a developer must take in the real working world that is subject to not attempting to master every aspect of the product/business (especially if it overlaps with someone else's job/focus) just because you have a high-level understanding of the global concepts. In other words, it is not necessarily efficient for a developer to know every aspect of every language and every database in the company unless that actually buys the company more customers and money.

I would expect any decent manager to understand this very basic principle. Everyone in the company trying to be a master at everyone else's job does not help the company make more money. Being reasonable about expectations in the moment is also a critical asset of working together to make money. :)

Re: Things I wished more developers knew about databases

#193

Post by beckingz »

411111111111111 wrote: post #100
taffer wrote: 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.

SQL can do a lot more though. Triggers, functions, procedures, access control.....

Turing complete!

Re: Things I wished more developers knew about databases

#194

Post by throwaheyy »

vsareto wrote: post #12
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…

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…

Exactly. Without abstractions, developers would never be able to get anything done. Every task would be dozens of new rabbit holes.

Re: Things I wished more developers knew about databases

#195

Post by atian »

cpufry wrote: post #181
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…

which library?

Sounds like pgx.

Spent forever debugging this myself.

https://github.com/jackc/pgx/issues/494

Re: Things I wished more developers knew about databases

#196

Post by Terr_ »

hobs wrote: post #33
mattferderer wrote: post #8

> AUTOINCREMENT’ing can be harmful I'll add that they should never be trusted to not jump around either! I imagine everyone makes this mistake at least once in their life. There is a very high chance that the database will skip a few numbers from time to time. You will then have someone from an accounting department asking where Record #XX is.

There's a simple fix - Don't you ever expose primary keys to the users. Ever. Seriously, ever. A primary key is not an order id, it isnt a person identifier, it isnt a paycheck - its a thing the database should be using behind the scenes. All of the things I just mentioned change - besides the primary key. You'll never have this problem if you separate your business logic from your keys.

Quite. Not only does it protect you from architecture pain later, but it also allows you to create business-keys that humans have an easier time reading, comparing, and typing.

The implicit requirement of a global always-online single master counter is great until it isn't. Perhaps one day it becomes a performance bottleneck, or you have to support systems that create provisional items offline, or some law or client-contract stipulates everything for them must live in a daughter-system hosted inside/not-inside a particular country...

Re: Things I wished more developers knew about databases

#197

Post by bpyne »

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 think you're onto something with the observation about what type of developers take to SQL. We have a spectrum in which people think more like how the processor operates to people who think more in classes of problems and their solutions.

We run into problems when someone with a proclivity to one side meets a problem best solved by thinking on the other side of the spectrum. An example is a young dev in my organization who was given requirements for a new app that required a data store. Not being comfortable with SQL or relational databases, he chose a document store. It wasn't a good fit. Very quickly requirements expanded and caused his code to balloon into a mess of nested loops with lots of if-checks. Performance has progressively decayed as well. A simple multi-table join with filtering would have knocked off 2/3 of the code.

Re: Things I wished more developers knew about databases

#198

Post by wenc »

TremendousJudge wrote: post #82
brightball wrote: post #56

Earlier quoted context omitted.

I firmly believe that every developer should spend 2-3 weeks early in their career working with nothing but SQL. It will pay huge dividends for the rest of it. IMO a lot of the issue is that developers for many years using Java or PHP, were using SQL to handle everything. The application language was a pass through later between the client and the database. Your goal was to accomplish as much as possible in a single…

> 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 that outperformed REST at scale -- it's called SQL. A SQL view is a dynamic lens into the underlying tables, so even if the underlying tables/schemas were to change, your consumers don't care as long as they can access the SQL View.

SQL views are also composable: you can build SQL views on top of other SQL views, and any changes made in the base views are propagated throughout. Need to add/transform a field? Do it in the view. Need pull in auxiliary data? Bring it in through a JOIN in the view. I've built many systems by composing SQL views and they're very maintainable and very flexible. They're kind of like function compositions but on tabular data.

The rule of thumb is: always access a database through a view, never the underlying raw tables. In computer science, a great many maintainability issues are alleviated through a layer of abstraction/indirection, and SQL views provide exactly that.

This centralization of the core logic becomes especially powerful if the database is accessed from multiple consumers (webapps, analytics backends, Tableau, ML tools, etc.) The "API" remains consistent throughout.

Re: Things I wished more developers knew about databases

#199

Post by josephorjoe »

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…

When I first encountered SQL, my immediate reaction was to start writing my own lightweight ORM (two days later I learned ORMs were a thing that already existed) because I took one look at that syntax and decided it was insane to work with directly.

I definitely don't think in SQL.

That said, I like working w SQL databases -- just please give me a battle tested ORM...

Re: Things I wished more developers knew about databases

#200

Post by harikb »

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 sql/database uses its own connection pool. But still that shouldn't create any problems. I have seen the reverse where apps that assumed temporary tables stick around from statement to statement without an explict `txn` (which regular postgres connections don't need) clearly failed. But I have not seen the issue you talk about.

My wild guess would be that the Go code never closed the result/rows which caused either a pool exhaustion / left the connections hanging and eventually got a timeout. Consider this similar to how http client's need to close the response.Body or else connections can't be reused.

Post reply on HN