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

231–240 of 464 posts

Re: Things I wished more developers knew about databases

#231

Post by homami »

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 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,
       ...

Re: Things I wished more developers knew about databases

#232

Post by infogulch »

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

Earlier quoted context omitted.

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

I would encourage you should check out editorconfig more closely. The whole idea is that each project has a file that defines the simple formatting rules for various file types in the project/directory tree and your editor will automatically follow them on a per-project basis. It's surprisingly well supported across editors.

Re: Things I wished more developers knew about databases

#233

Post by Macha »

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.

This is why I'm skeptical of the suggestion to prefer a natural primary key like a username. It works fine... until they day the business asks for changeable usernames because BigClient is now LargeClient and can't stand anything to still have their old brand identity.

Re: Things I wished more developers knew about databases

#235

Post by Scarbutt »

oftenwrong wrote: post #168
goostavos wrote: post #86

Earlier quoted context omitted.

>programming languages are very poor at bridging the difference between the SQL domain and the language domain Depends a lot on the language. I've lost countless hours to things like JOOQ trying to figure out how to get it to do what I want, or express the query in its quirky not-quite-right DSL, plus dealing with mappings, pojos, auto-generation, and so on. However, on the other hand, in a dynamic language with just…

jOOQ can be used in a less-type-safe way. For example, `fetchMaps` [1] does more-or-less what you describe. However, I have found it worthwhile to learn to use the more advanced features you mention. Extending type safety to queries is incredibly useful. Consider cases when developers are making code and schema changes concurrently that overlap. [1] https://www.jooq.org/javadoc/latest/org.jooq/org/jooq/Result...

SQL (at least in Postgres) is already type safe.

Re: Things I wished more developers knew about databases

#236

Post by fools »

acdha wrote: post #172
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…

> 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. My reaction to that is that it's similar in a different way: everyone needs to use it but many developers don't…

If I write a C program, especially with the appropriate compiler warnings enabled, or some filesystem code, there is a high chance that it will work across platforms with no further coding necessary. Anecdotally, the same cannot be said for CSS or SQL.

It doesn't matter much for SQL, because I always know what database I'm using, but for CSS, it's a massive pain.

Re: Things I wished more developers knew about databases

#238

Post by PZ81JUXJE7uJ »

homami wrote: post #231
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 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, ...

MySQL supports a insert/set statement:

    INSERT INTO tbl SET a = 1, b = 2;

Re: Things I wished more developers knew about databases

#239

Post by Scarbutt »

ComodoHacker wrote: post #219
Dowwie wrote: post #176

"The fastest way to access to a row in a database is by its primary key. If you have better ways to identify records, sequential IDs may make the most significant column in tables a meaningless value. Please pick a globally unique natural primary key (e.g. a username) where possible." Has anyone had a problem due to surrogate keys?

I can agree with everything in the article except this one. >Has anyone had a problem due to surrogate keys? There's one problem with surrogate keys: they are not convenient to users (too long and not meaningful). There are two problems with natural primary keys, and you are guaranteed to hit one of them at some point. 1. It turns out your key isn't actually unique. To resolve the collision you have to replace natura…

There's one problem with surrogate keys: they are not convenient to users (too long and not meaningful).

You shouldn't expose those keys to users though.

Re: Things I wished more developers knew about databases

#240

Post by lanius »

eeZah7Ux wrote: post #215
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…

> The most senior developer (24yo or so) I see the problem there.

Age is a just a number. John Carmack created Doom in his early 20s.
Post reply on HN