undefined | Better HN

0 pointsstephen1y ago0 comments

I can assure you that brandur knows SQL. :-)

(I don't know him personally, but have been following his blog for years.)

What these "just write SQL" rants are missing is encapsulation--let's say you've got a business logic, like "if the account is disabled, render the account name as 'Foo (disabled)'".

You want to write this logic in your preferred backend language, Go/TS/C/etc.

This works fine, in the /account/X endpoint (just load the account, and apply the logic).

But now what about the /accounts/client:Y endpoint (load all accounts, all the logic for all of their accounts)

As time goes by, you end up having 10-20 endpoints that all "return some part of account" as their payload, and you want the same "Foo (disabled)" business logic.

Your options are:

1. Build a single, giant SQL statement that strings together every snippet of business logic applicable to this endpoint (bulk friendly b/c the db is doing all the cross-entity work w/joins)

2. Push the business logic down into the db layer (simple for this, just string concate with an if, but what about anything that is a loop? doable in SQL but tedious)

3. Use your language's abstractions, like functions, to organize the business logic (what Brandur is attempting to do).

Nearly everyone wants to do 3, because 1 doesn't scale as your business logic becomes more & more sophisticated (copy/pasting it around every endpoint's single-giant SQL statements, or pushing it down into the db as views/stored procedures).

0 comments

sgarland1y ago

> because 1 doesn't scale as your business logic becomes more & more sophisticated (copy/pasting it around every endpoint's single-giant SQL statements, or pushing it down into the db as views/stored procedures).

I think the idea that doing business logic inside the DB is an anti-pattern is cargo culting. You can quite easily store schema definitions, stored procedures, etc. inside version control. You can document it just as you would any other code. You don’t have to see what precisely the call is doing at all times, provided you understand its signature.

Letting the DB do more than just be a dumb data store is a great idea, IMO, and one that is too often eschewed in favor of overly-complicated schemes.

bbkane1y ago

I know putting business logic in the DB has been used very successfully, but it also has some large downsides.

It's harder to express some things as SQL and your team will be less familiar with SQL than your preferred language. SQL language tooling (code autocomplete, testing libs, etc) is also far less mature than tooling for most languages.

It's harder to debug when things go wrong. Where do you put a breakpoint?

It's likely your team ends up splitting your business logic between your app and your DB, and then you have to figure out which part is writing incorrect data when there's a problem.

For my apps, I'm trying to set up the database to reject incorrect information (with types and constraints and sometimes triggers), but have the app code do all the business logic and writing data.

Honestly, it's too soon to tell whether my apps will be successful with this approach. They haven't yet gone through years of real world usage and requirements changing. But so far it's gone well!

sgarland1y ago

> It's harder to express some things as SQL

This is true, but then, it's also harder to write Rust than Python. There are tradeoffs which are made for every choice.

> and your team will be less familiar with SQL than your preferred language.

Also true, but given how SQL is practically everywhere and is not likely to go away any time soon, I strongly feel that nearly every dev could benefit from learning it.

> SQL language tooling (code autocomplete, testing libs, etc) is also far less mature than tooling for most languages.

Again, true. Personally I dislike autocomplete functions (they break my flow, needing to parse the suggestion and accepting it, rather than just typing from muscle memory), but I get that others like them. Re: testing, I have an unpopular opinion: this matters very little compared to most other languages. Declarative languages like SQL or Terraform generally do not produce surprises. If you get an unexpected output, it's probably because you have incorrect logic. There are some testing frameworks that exist for various flavors of SQL should you wish, but IMO as long as you have some rigorous E2E tests, and your dev/staging environments are accurate and representative of prod, you'll be fine.

> For my apps, I'm trying to set up the database to reject incorrect information (with types and constraints and sometimes triggers), but have the app code do all the business logic and writing data.

Hey, you're doing better than most! I've never understood people's unwillingness to use things like length constraints. Sure, your app should ideally catch basic issues so they never have to make it to the DB, but I'd rather have the guarantee. The last thing you want is for someone to find a way to inject the entirety of Moby Dick into a `name` field.

d01001y ago

Since I mainly use Postgresql, I mix 1, 2 and 3: I create CTE's in code which are then reused in queries

    sqrl.Select("id", "cte1.*", "cte2.*").From("mytable").Join(CTE1{}).Join(CTE2{}).Where(etc)

xarope1y ago

for 3, you could write a stored proc to handle the various situations, and call that stored proc appropriately, letting the DB engine optimize appending "(disabled)".

however, I do wish Go had a map function ala python map() as opposed to just verbosely writing more for loops or a complicated thread-safe goroutine. Seems almost strange that Google, who popularized the mapreduce model, doesn't want it in Go.

sgarland1y ago

Stored procedures are also screeched at as being anti-patterns, somehow. I don’t get it; you can store them in version control just like anything else, and add comments where necessary in the code base indicating what the DB is doing.

jimbokun1y ago

Now that Go has generics, you can write “map_slice” in a few lines then use it everywhere. I’ve done it myself.

Not sure why they haven’t added it to the standard library.

j / k navigate · click thread line to collapse

0 pointsstephen1y ago0 comments

I can assure you that brandur knows SQL. :-)

(I don't know him personally, but have been following his blog for years.)

What these "just write SQL" rants are missing is encapsulation--let's say you've got a business logic, like "if the account is disabled, render the account name as 'Foo (disabled)'".

You want to write this logic in your preferred backend language, Go/TS/C/etc.

This works fine, in the /account/X endpoint (just load the account, and apply the logic).

But now what about the /accounts/client:Y endpoint (load all accounts, all the logic for all of their accounts)

As time goes by, you end up having 10-20 endpoints that all "return some part of account" as their payload, and you want the same "Foo (disabled)" business logic.

Your options are:

1. Build a single, giant SQL statement that strings together every snippet of business logic applicable to this endpoint (bulk friendly b/c the db is doing all the cross-entity work w/joins)

2. Push the business logic down into the db layer (simple for this, just string concate with an if, but what about anything that is a loop? doable in SQL but tedious)

3. Use your language's abstractions, like functions, to organize the business logic (what Brandur is attempting to do).

0 comments

sgarland1y ago

Letting the DB do more than just be a dumb data store is a great idea, IMO, and one that is too often eschewed in favor of overly-complicated schemes.

bbkane1y ago

I know putting business logic in the DB has been used very successfully, but it also has some large downsides.

It's harder to debug when things go wrong. Where do you put a breakpoint?

It's likely your team ends up splitting your business logic between your app and your DB, and then you have to figure out which part is writing incorrect data when there's a problem.

For my apps, I'm trying to set up the database to reject incorrect information (with types and constraints and sometimes triggers), but have the app code do all the business logic and writing data.

Honestly, it's too soon to tell whether my apps will be successful with this approach. They haven't yet gone through years of real world usage and requirements changing. But so far it's gone well!

sgarland1y ago

> It's harder to express some things as SQL

This is true, but then, it's also harder to write Rust than Python. There are tradeoffs which are made for every choice.

> and your team will be less familiar with SQL than your preferred language.

Also true, but given how SQL is practically everywhere and is not likely to go away any time soon, I strongly feel that nearly every dev could benefit from learning it.

> SQL language tooling (code autocomplete, testing libs, etc) is also far less mature than tooling for most languages.

> For my apps, I'm trying to set up the database to reject incorrect information (with types and constraints and sometimes triggers), but have the app code do all the business logic and writing data.

d01001y ago

Since I mainly use Postgresql, I mix 1, 2 and 3: I create CTE's in code which are then reused in queries

    sqrl.Select("id", "cte1.*", "cte2.*").From("mytable").Join(CTE1{}).Join(CTE2{}).Where(etc)

xarope1y ago

for 3, you could write a stored proc to handle the various situations, and call that stored proc appropriately, letting the DB engine optimize appending "(disabled)".

sgarland1y ago

jimbokun1y ago

Now that Go has generics, you can write “map_slice” in a few lines then use it everywhere. I’ve done it myself.

Not sure why they haven’t added it to the standard library.

j / k navigate · click thread line to collapse