Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

I'm all-in on server-side SQLite (2022)

fly.io

Post reply on HN
Print view View original

121–130 of 167 posts

Re: I'm all-in on server-side SQLite (2022)

#121

Post by marcosdumay »

vsareto wrote: post #107
JohnBooty wrote: post #83

Earlier quoted context omitted.

As if postgres and others don’t have a way to run application logic at the database. I mean... This is probably the least popular possible thing you can possibly suggest as an engineer in 2023. Me? I actually think pushing app logic to the DB is a solid, underrated, and possibly even optimal solution for a lot of scenarios. But don't tell anybody I said that. I might get beaten up. That's probably why fly.io sort of…

>Me? I actually think pushing app logic to the DB is a solid, underrated, and possibly even optimal solution for a lot of scenarios. The languages for writing it are not as comfy as traditional programming languages, which affects how expressive and maintainable your code will be. The tools for debugging a regular language might also be better than debugging application logic in SQL. Having done some of this in T-SQL…

> Having done some of this in T-SQL

Don't expect your experience with any other DBMS to give you an idea about how nice it is to program in Postgres.

It's still not as nice as creating some independent code. But Postgres is quite nice to program in.

Re: I'm all-in on server-side SQLite (2022)

#122

Post by simonw »

endisneigh wrote: post #13

Use Postgres. Or if you insist on this type of architecture use CouchDB. I shudder thinking about a SQLite schema migration across clients with potentially unknown versions. Seems like a disaster waiting to happen unless you have a bunch of logic centralized somewhere to keep track of last know schemas per user client database. And if you’re going to do all that, unless you desperately need low latency (in which case…

"unless you have a bunch of logic centralized somewhere to keep track of last know schemas per user client database"

I've been building exactly that here: https://github.com/simonw/sqlite-migrate

Re: I'm all-in on server-side SQLite (2022)

#123

Post by simonw »

imhoguy wrote: post #78

Did anybody try something like that: read/write to SQLite database file on backend, but also allow the database file to be downloaded at any time by rich JS frontend for read-only querying. I just wonder if the file is going to be (eventually-) consistent and not corrupted.

My hunch is that if you want to do that the safe way would be to have a mechanism that creates a snapshot of the SQLite database for the client to download when they request it.

One way to do that is with VACUUM INTO, e.g. how I use it in this TIL: https://til.simonwillison.net/sqlite/python-sqlite-memory-to...

If your database is less than 100MB or so I imagine this would easily be fast enough that the performance overhead wouldn't be worth worrying about.

Re: I'm all-in on server-side SQLite (2022)

#125

Post by sodapopcan »

JohnBooty wrote: post #83
jmull wrote: post #29

I’m bullish on SQLite, and this is mostly a great article, but this kind of stuff is flat-out misleading: > When you put your data right next to your application, you can see per-query latency drop to 10-20 microseconds. As if postgres and others don’t have a way to run application logic at the database. I like the SQLite way of doing it — you pretty much freely choose your own host language — anything with a decent…

As if postgres and others don’t have a way to run application logic at the database. I mean... This is probably the least popular possible thing you can possibly suggest as an engineer in 2023. Me? I actually think pushing app logic to the DB is a solid, underrated, and possibly even optimal solution for a lot of scenarios. But don't tell anybody I said that. I might get beaten up. That's probably why fly.io sort of…

I run a bunch of business logic in the database but have no interest in writing my whole application as triggers or whathaveyou. It's a bit of a middle ground, perhaps?

Re: I'm all-in on server-side SQLite (2022)

#126

Post by marcosdumay »

Micoloth wrote: post #92
JohnBooty wrote: post #83

Earlier quoted context omitted.

As if postgres and others don’t have a way to run application logic at the database. I mean... This is probably the least popular possible thing you can possibly suggest as an engineer in 2023. Me? I actually think pushing app logic to the DB is a solid, underrated, and possibly even optimal solution for a lot of scenarios. But don't tell anybody I said that. I might get beaten up. That's probably why fly.io sort of…

Honest question.. Why? I’m always thinking that a db that can also run business logic would be the ultimate backend solution for crud apps. I know there are several options to do it, but I always assumed Postgres did not support it. What do people have against it?

Postgres has plugins for running the entire backend.

People don't like it for a lot of reasons. Making privilege escalation harder is a big one, but also, all the CPU (and memory) load on serializing that data is CPU that could be used managing the distributed processes problems that only the DBMS can solve.

Personally, I think access management on those tools needs to improve a lot before they get usable. But also, the data-oriented languages have some issues, and the non data-oriented ones don't gain much by running inside the database.

IMO, we are missing a really good data-oriented language. But I don't see any gain from running it inside the database.

Re: I'm all-in on server-side SQLite (2022)

#127

Post by marcosdumay »

benbjohnson wrote: post #88
kijin wrote: post #12

> When you put your data right next to your application, you can see per-query latency drop to 10-20 microseconds. That’s micro, with a μ. A 50-100x improvement over an intra-region Postgres query. Why compare the latency of a remote Postgres database with a local SQLite database? If your app is so simple and self-contained that it runs on a single EC2 instance using local files, nothing prevents you from installing…

Author here. The comparison was meant to be about how Postgres (or any client/server RDBMS) is typically deployed. Yes, you can deploy Postgres on the same machine but I wouldn't say it's common. Maybe I could have expanded more on that point or simply referenced client/server architecture rather than Postgres so it didn't seem like a straw man argument.

If I had to guess, I'd say that single-machine (with cold backups) is the most common way to use Postgres with a web server.

Re: I'm all-in on server-side SQLite (2022)

#128

Post by JohnBooty »

Micoloth wrote: post #92
JohnBooty wrote: post #83

Earlier quoted context omitted.

As if postgres and others don’t have a way to run application logic at the database. I mean... This is probably the least popular possible thing you can possibly suggest as an engineer in 2023. Me? I actually think pushing app logic to the DB is a solid, underrated, and possibly even optimal solution for a lot of scenarios. But don't tell anybody I said that. I might get beaten up. That's probably why fly.io sort of…

Honest question.. Why? I’m always thinking that a db that can also run business logic would be the ultimate backend solution for crud apps. I know there are several options to do it, but I always assumed Postgres did not support it. What do people have against it?

After several decades in the business, I have learned not to underestimate the massive power of trendiness.

A lot of the argument against it is: SQL is stodgy and uncool, and I want to use Cool Language XYZ. And honestly, I don't entirely blame people for that. If you want to work in this industry you need to do things the "cool" way or you'll never even be considered for roles. Gotta keep that resume looking good.

People also don't appreciate how performant it can be. Depending on what you're doing it can be orders of magnitude more performant to do things in-database versus shuffling things back and forth. (It can also be less performant...)

There are definitely cons to that approach.

One is scalability. It's easier to scale your app layer horizontally than it is to scale your database server vertically. This isn't necessarily an issue: a modern beefy server CPU with 64-128 cores and a TB or two of RAM is more than 99.9% of companies need, is really not that expensive, and is probably a lot cheaper than more complicated setups and extra devops headcount. But that's not cool either.

Two is the language/skills mismatch. You've got an app layer in one language, a frontend in another, and now potentially a data storage layer written in a third. This is a valid concern, but also nobody seems to use it as an argument against Javascript frontends, so apparently sometimes it's cool and sometimes it isn't.

Debugging stored procedures sort of sucks. That's fair. (But also, nobody is saying to rewrite your entire app, or even most of it, in the DB layer)

Common migration tools often don't really have explicit support for stored procs and things like that, but AFAIK they do let you run arbitrary SQL DDL stuff, so I don't think this is a hard barrier.

Re: I'm all-in on server-side SQLite (2022)

#129

Post by [deleted] »

simonw wrote: post #119
Micoloth wrote: post #92

Earlier quoted context omitted.

Honest question.. Why? I’m always thinking that a db that can also run business logic would be the ultimate backend solution for crud apps. I know there are several options to do it, but I always assumed Postgres did not support it. What do people have against it?

I think the main thing is that most people still aren't working with a good migrations system to manage changes to their schema... which means logic held in database triggers and stored procedures quickly becomes a non-version-controlled not-properly-tested mess. Good migration systems exist, and people should use them! I held off on doing interesting things with triggers for more than a decade. In the past year I've…

[deleted]

Re: I'm all-in on server-side SQLite (2022)

#130

Post by arcatech »

andrewstuart wrote: post #42
dimgl wrote: post #31

Earlier quoted context omitted.

> any valid reason Well, cost, right? Cost is a reason why someone may not want to use a traditional RDBMS. AWS RDS and GCP Cloud SQL aren't exactly the cheapest solutions out there.

Postgres is free. Put it on a computer. Cost is certainly not an argument against Postgres cause it can run on any back end that runs Linux.

Who pays for the computer and bandwidth?
Post reply on HN