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

61–70 of 464 posts

Re: Things I wished more developers knew about databases

#61

Post by jakearmitage »

btilly wrote: post #49
fimdomeio wrote: post #27

Earlier quoted context omitted.

I struggle with lack of experience with SQL by always being told that I should always use an ORM or I would regret it in the future when I would change database technology. I'm in the future now and spend a lot of time debbuging the ORM and the sql statements it produces, when I could split that work in half by not using the orm at all. Would also have a lot more experience with sql so there would probably be less bu…

If you're writing a CRUD application, an ORM saves a lot of headaches. If you're doing complex reporting queries, an ORM is strictly worse. And yes, I've seen developers, architects, and authors of ORMs that believed otherwise. They are wrong. As an example, very, very few ORMs can make the distinction between SELECT ... FROM foo LEFT JOIN bar ON foo.id = bar.foo_id AND bar.category_id = 5 LEFT JOIN baz ON bar.id = b…

Curious, what is the difference here?

Re: Things I wished more developers knew about databases

#62

Post by EvanAnderson »

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…

Your example is a fun one and not too hard to do.

Thinking about the intersection of sets is important for grokking what you're trying to do in that example (and with SQL in general). For me, at least, SQL suddenly made a lot of sense when the idea that I was working with intersections and subsets of sets.

In your example you have (1) a set of all dates in a range, and (2) a set of sales totals for days that had sales. Set 1 could be a "numbers table" or generated with something like "generate_series()" in PostgreSQL. Set 2 is made by summarizing the data in a sales table by date using "GROUP BY" and "SUM".

Then you're just looking at JOIN'ing those sets and COAELSCE'ing the NULL returned from days when there are no sales into 0.

Re: Things I wished more developers knew about databases

#63

Post by davedx »

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…

As a developer: I do care, but it's hard for me to focus on building software if I also need to think about DBA tasks, DevOps tasks, and so on. These things all take time, patience, and energy. I've just spent most of today running and re-running a CloudFormation template to create a SQL database. Most. Of. A. Day. It's partly because I'm not a DevOps expert and even if I wanted to be one, that would also take time, patience and energy.

There's a reason people have specializations...

Re: Things I wished more developers knew about databases

#64

Post by falcolas »

arvinsim wrote: post #10
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…

Perhaps development work has reached complexity where it demands full-time attention? Perhaps there is a reason why DBA, SRE, Devops and Architects are separate roles?

A DBA can't fix a developer's code. A good 3/4 of the list linked in TFA are things a DBA or Architect can't affect.

At some point, a developer needs to be responsible for how they use a tool that's been provided for them. Abstractions break.

Re: Things I wished more developers knew about databases

#65

Post by dominotw »

brightball wrote: post #56
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…

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

i did tons of sql couple of years ago on a reporting team. Now I do android dev fulltime and don't remember any SQL beyond basics, highly doubt it will all come back to me if i tried.

Re: Things I wished more developers knew about databases

#66

Post by pbowyer »

EvanAnderson wrote: post #62
pbowyer wrote: post #45

Earlier quoted context omitted.

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…

Your example is a fun one and not too hard to do. Thinking about the intersection of sets is important for grokking what you're trying to do in that example (and with SQL in general). For me, at least, SQL suddenly made a lot of sense when the idea that I was working with intersections and subsets of sets. In your example you have (1) a set of all dates in a range, and (2) a set of sales totals for days that had sale…

> Set 1 could be a "numbers table" or generated with something like "generate_series()"

I've done it this way when doing it per-minute or per-hour - i.e. for 60 minutes or 24 hours; fixed, constant ranges. I picked this example because (to me at least) it's more difficult :)

Calendar months have different lengths, so something would have to tell it which month to use (and account for leap years). But say you wanted the last 60, 90 or 120 days? I suppose you could first create a temporary table to be the "numbers table", custom made for the range you want to fetch - is there an alternative?

Re: Things I wished more developers knew about databases

#67

Post by MaxBarraclough »

andybak wrote: post #51
MaxBarraclough wrote: post #22

Earlier quoted context omitted.

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

They're not identical concepts, but both relational schemas and statically typed programming languages provide assurances about basic structure. Mongo, and Python, offer no such assurances, and leave it to the developer to get it right.

Agree that SQL's syntax is rather bad.

Re: Things I wished more developers knew about databases

#68

Post by pbowyer »

JamesSwift wrote: post #59
pbowyer wrote: post #45

Earlier quoted context omitted.

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…

Yes, agreed. It was designed for OLTP not OLAP, and I have to get my mindset into that.

For days as my example has, would the Time table be generated for say 1970-2050? Given months are of different lengths and there are leap years, I'm assuming this is needed, rather than storing a single year.

Re: Things I wished more developers knew about databases

#69

Post by pbowyer »

sk5t wrote: post #55
pbowyer wrote: post #45

Earlier quoted context omitted.

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 writ…

I find thinking about analytic queries like these as map/filter/reduce easier than thinking in terms of the SQL!

Re: Things I wished more developers knew about databases

#70

Post by tingol »

a_c wrote: post #20

Learn about modelling. Database is more than just storing data. Drink less koolaid of NoSQL, any NoSQL. It is trading initial result with future development time. SQL has been battlefield tested. No amount of "convenience" is more convenient than learning the fundamentals.

Any tips where to look for db modelling learning resources?
Post reply on HN