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

Re: Things I wished more developers knew about databases

#51

Post by andybak » Wed, Apr 22, 2020, 3:25 PM UTC

MaxBarraclough wrote: post #22

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 suspect this was what attracted developers to noSQL databases like Mongo in the first place -- it's more attuned to a programmatic mindset. Well, it's more attuned to the dynamically typed mindset, sure. Programmers who understand the value of static type systems should understand the value of relational schemas.

I disagree. There's more to relationships that typing. I'm very pro-dynamic languages and still chafe at static typing but the wonder of the relational model fits nicely with my liking for declarative and functional approaches.

(EDIT - and as another data point I dislike SQL's syntax. The semantics are bearable but the syntax just makes my brain melt)

Re: Things I wished more developers knew about databases

#52

Post by crazygringo » Wed, Apr 22, 2020, 3:26 PM UTC

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…

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 programming, and also where experience counts for a ton more than just normal logical thinking.

Re: Things I wished more developers knew about databases

#53

Post by smilliken » Wed, Apr 22, 2020, 3:26 PM UTC

hobs wrote: post #36

earhart wrote: post #26

Earlier quoted context omitted.

That gets tricky if one of the writes fails.

Generally you get two phase commit involved here, or an asynch mirror that you can restore over if there's failure.

Two phase commit makes a promise it can't keep. CAP theorem is not amused.

It's slow, complex, a leaky abstraction, and ahould be avoided for better consistency models.

Re: Things I wished more developers knew about databases

#54

Post by Ididntdothis » Wed, Apr 22, 2020, 3:26 PM UTC

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…

SQL feels to me like bash or regular expressions. You can do amazing things if you do it full time. But if you do it only a few times per month or year you quickly forget all the subtleties and it gets hard to understand even the stuff you wrote half a year ago. I guess in the end things have become so complex that as a dev you can’t be good at everything. I often wish there were dedicated database guys but if you have one usually that person is sysadmin and won’t help much with coding against the database.

Re: Things I wished more developers knew about databases

#55

Post by sk5t » Wed, Apr 22, 2020, 3:26 PM UTC

pbowyer wrote: post #45

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 like SQL when I'm not writing reporting queries. GROUP BYs bite me (With MySQL 8 I end up reaching for the ANY_VALUE() function), and I end up with more subqueries than I feel I should need. When working with time-indexed data I feel I'm forcing the database to do something it doesn't want to. E.g. if I want to answer the query "How many sales are there per day this month?" and I want an entry for every day in the…

A more functional mindset can definitely help here. Think of your "sales per day" model in terms of starting with a sequence of days--startingDay up to startingDay + n--as the input to a function that maps to an aggregate of that day's activity.

Aggregate functions in SQL are IMHO quite awesome once you develop a comfort level to stop worrying about them per se. I wouldn't like to try to get Excel to tell me--or write the code to do manually--something like "show me the standard deviation in units sold by day of week over the last ten summers."

Re: Things I wished more developers knew about databases

#56

Post by brightball » Wed, Apr 22, 2020, 3:29 PM UTC

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 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 query and then to simply return the results of that query to the interface. That meant formatting numbers or currency in your SQL. Optimizing inserts or updates to be handled in a single query. Grouping, counting, left/inner joins, having statements to filter on aggregate results. More than 1 or 2 queries for the primary area of the screen was both a rare and foreign experience.

And then ORMs started to slowly integrate themselves into the flow of various frameworks to automate the repetitive things around CRUD tasks. Then to address scaling & bloat problems we saw an uptick in REST APIs, microservices that further made those ORMs the norm...and then many developers started actively trying to stay within those API constraints to an almost religious degree which led to a nested payload becoming acceptable fueling the whole "NoSQL" situation, along with the idea that it was somehow better to repeat the same data thousands of times over.

A whole lot of people pushed back against this and eventually, it mostly ran its course. I've often seen resistance to SQL to be driven by fear of SQL more so than anything else. As soon as people get a basic comfort level with SQL, it become almost automatic.

Re: Things I wished more developers knew about databases

#57

Post by hobs » Wed, Apr 22, 2020, 3:31 PM UTC

smilliken wrote: post #53

hobs wrote: post #36

Earlier quoted context omitted.

Generally you get two phase commit involved here, or an asynch mirror that you can restore over if there's failure.

Two phase commit makes a promise it can't keep. CAP theorem is not amused. It's slow, complex, a leaky abstraction, and ahould be avoided for better consistency models.

I generally couldn't agree more, but yet its the first tool in the toolbox for a lot of databases.

Re: Things I wished more developers knew about databases

#58

Post by treis » Wed, Apr 22, 2020, 3:31 PM UTC

gwbas1c wrote: post #47

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…

Part of the issue is that a complicated database can handle the same SQL query many different ways based on indexes and other configurations. This kind of "magic" isn't always clear when programmers are mostly used to working with data structures and procedural code. The other problem, IMO, is that programming languages are very poor at bridging the difference between the SQL domain and the language domain. We really…

>This kind of "magic" isn't always clear when programmers are mostly used to working with data structures and procedural code.

My problem is that it is like some sort of black magic to me. If I write a complex query I have no idea if what is spit back to me is actually what I want. The only way is seeding lots of records and then manually checking that each filter and calculation is doing what I want.

In code complex things can be broken down into more simple items. Then I can reason about and test those building blocks into something I understand and am confident that is working as intended.

Re: Things I wished more developers knew about databases

#59

Post by JamesSwift » Wed, Apr 22, 2020, 3:31 PM UTC

pbowyer wrote: post #45

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 like SQL when I'm not writing reporting queries. GROUP BYs bite me (With MySQL 8 I end up reaching for the ANY_VALUE() function), and I end up with more subqueries than I feel I should need. When working with time-indexed data I feel I'm forcing the database to do something it doesn't want to. E.g. if I want to answer the query "How many sales are there per day this month?" and I want an entry for every day in the…

The answer is likely that your storage schema is incorrect. You have things stored in OLTP (i.e. app database) but trying to read it as OLAP (i.e. reporting database). Once you reimagine the data in the OLAP style then these kinds of queries are simplistic.

EDIT: specifically for your example, in an OLAP style you would generate a Times table and then foreign key the sales table to it based on the date. Then you can easily query against that Times table as the filter/bucket for your queries.

Re: Things I wished more developers knew about databases

#60

Post by falcolas » Wed, Apr 22, 2020, 3:31 PM UTC

hobs wrote: post #17

throwaway13337 wrote: post #14

Earlier quoted context omitted.

All of those things are unproductive until after your project is successful which is usually not a guarantee when building the software. You're not gonna need it.

Observeability, reliability, scalability - really easy to bolt on once you are successful. There's a middle ground, and a DBA turned SRE turned Architect is probably ok with compromises, those are all roles where "it depends" is a bylaw.

So, they really aren't that easy to bolt on, if not considered from the beginning. Monoliths, for example, are a real PITA to make reliable and scalable.

Worse, once your company is successful, there will be an endless list of features to add to your product, meaning nobody has the time to "bolt those features on". How the product begins, is how the product often lives on well past it's expected lifespan.