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

SQLite the only database you will ever need in most cases (2021)

unixsheikh.com

Post reply on HN
Print view View original

161–170 of 378 posts

Re: SQLite the only database you will ever need in most cases (2021)

#161

Post by Apocryphon »

joshspankit wrote: post #156

I’ve argued this before and I’ll argue it here now: Modern computers are fast enough that in many cases “the only database you will ever need” can be files on the filesystem. For example “1 row = 1 file”. It brings additional benefits as well: for low-write applications you can use git to get a history (+transactions if you store them in the log), backups are super easy, replication is trivial. For higher-write appli…

http://howfuckedismydatabase.com/letters/

> Name: Edward I'm using grep and find and the Unix file system

> Name: Toni Why do you need a database? I'm using CSV files!

> Name: Carlos I came after a long journey to your website to seek enlightenment : am I fucked? And it didn't answer that question clearly. Let me re-phrase it: I first search an LDAP directory, then I remotely execute a quota status, after that I query a PostgreSQL database, and then I generate a .txt file with the timestamp as its name or a .csv file with an hash as its name, and then I look up the files from a web page, load it all to a multi-dimensional array, and generate a nice report, re-loading the entire file every time the user wants to, say, sort by another field. Something this complex can't ever possibly fuck up, can it?

Re: SQLite the only database you will ever need in most cases (2021)

#162

Post by drexlspivey »

joshspankit wrote: post #156

I’ve argued this before and I’ll argue it here now: Modern computers are fast enough that in many cases “the only database you will ever need” can be files on the filesystem. For example “1 row = 1 file”. It brings additional benefits as well: for low-write applications you can use git to get a history (+transactions if you store them in the log), backups are super easy, replication is trivial. For higher-write appli…

how do you do joins?

Re: SQLite the only database you will ever need in most cases (2021)

#163

Post by sanderjd »

chillfox wrote: post #141
sanderjd wrote: post #102

Earlier quoted context omitted.

> It's not a good option for what you appear to want. Ok but the article you're replying in the comments to says "SQLite is all you need for nearly everything", and what the comment you're replying to is describing is, to use their very apt word choice, entirely ordinary . So how do we square this circle of somebody being told both "SQLite is all you need for everything" and "it is not a good option for your totally…

Most things don't need zero downtime deployment. Now it's nice to have and might be an interesting technical challenge to solve, but it's usually not strictly needed. edit: when I worked as a sysadmin we would have to schedule outages for updating apps that were designed for zero downtime anyways because those were the processes of the organisation.

Yeah I've realized this is just a disagreement over the word "most" which won't really be resolvable empirically. It doesn't fit my experience that most applications are single node with lax uptime and failover requirements.

Re: SQLite the only database you will ever need in most cases (2021)

#164

Post by irskep »

berdon wrote: post #151
JohnBooty wrote: post #134

Earlier quoted context omitted.

You don't need to be a full-blown sysadmin to know how to do basic deployments, and learning these things will make you a better developer. Yes. True. However, it is also true that we have a limited amount of time per week and a limited number of weeks on Earth. Time spent learning sysadmin-y stuff is less time spent mastering developer-y stuff. Think about what it means to be a "full stack" engineer in 2023: - Unix…

Fwiw, I’ve used all of those in my career, extensively here and there. I’ve written many thousands of lines of bash scripts. Even written my own php-for-bash-script style code tags that support arbitrary shells. I’ve written my own log based distributed kv store, gone down the YouTube trail of writing my own db, gone through the angular and react iterations, deep dove in docker, docker compose, k8s, crds and custom h…

Sure, you can do all that, but have you also written video games, UI frameworks, compilers, Twitter bots, localization tools, and web-based whiteboards? Have you ever made a music video or gotten a novel published? If not, why not? I'd say it's because you had different interests and spent your time elsewhere, which is completely fine and not a problem at all. People are allowed to have their own interests and explore the amazing world of technology in their own ways.

It's weird to say that people aren't learning these things because they're "stopping themselves." They're just going in other directions. No one in my life has ever accused me of learning too few things and failing to learn by building things.

Edit: I think I misunderstood the parent comment, sorry for the defensiveness here

Re: SQLite the only database you will ever need in most cases (2021)

#165

Post by kragen »

erik_seaberg wrote: post #125
kragen wrote: post #17

Earlier quoted context omitted.

people have been providing acid transaction semantics on single machines for 50 years do you think ims/db ran on a cluster the d in acid stands for durability you're talking about pitr, which is what mysql semi-sync provides (and afaik you are correct that sqlite doesn't offer pitr)

Sounds like IMS runs on Z system mainframes with redundant hot-swappable CPUs and memory. They pay IBM a lot of money for the illusion of a single reliable machine, when a different OS would manage it as a small cluster. We economize by using racks of cheap, flaky commodity hardware, but we have to be ready for one computer to die by failing each application over to another.

in 01973 the s/360 did not have hot-swappable cpus or memory

even in the 01990s i don't think ibm had such an offering, though tandem did (but it couldn't run ims)

Re: SQLite the only database you will ever need in most cases (2021)

#166

Post by onlypositive »

Nican wrote: post #31
paulryanrogers wrote: post #6

Earlier quoted context omitted.

PHP is usually run multi-process, so may have more parallel activity than a single Node instance running single threaded

The answer here is non-trivial. I have since long stopped developing with PHP, but PHP has several run modes (And multiple runtimes like HHVM), that may bring different performance and concurrency characteristics. Last I remember, PHP still runs all inside of a single process, so it still all share the same memory space, and it no longer has the overhead of starting a new process with every request. The piece of engi…

I used PHP but probably should have specified modphp or phpfpm.

Re: SQLite the only database you will ever need in most cases (2021)

#167

Post by sanderjd »

pastor_bob wrote: post #121
sanderjd wrote: post #102

Earlier quoted context omitted.

> It's not a good option for what you appear to want. Ok but the article you're replying in the comments to says "SQLite is all you need for nearly everything", and what the comment you're replying to is describing is, to use their very apt word choice, entirely ordinary . So how do we square this circle of somebody being told both "SQLite is all you need for everything" and "it is not a good option for your totally…

Distributed databases are rarely needed. I wouldn't call it an ordinary use case. The article isn't claiming Elon Musk can run Twitter off SQlite

It is honestly difficult for me to imagine what kind of applications people are working on that have these - to me - very lax reliability constraints. But we're just disagreeing over what "most" applications are like, based on our experiences, without any empirical data to say either way, so :shrug:.

Re: SQLite the only database you will ever need in most cases (2021)

#168

Post by 0xbadcafebee »

I agree. Most uses of databases definitely don't need to grow larger than, say, a single filesystem, or a single application, or a single host, or a single network, or a single geographical region, or a single customer, or a single organization, or a single global network of customers in organizations in regions on networks on hosts on applications on filesystems.

There could not be any features of any other database that SQLite might not have, or that an application might need, or want.

We definitely should not, like, read a book on databases, or read the manual of another database, or something else crazy like that. There's no reason we might learn about other databases. They are just "shiny stuff", meaning, there's something going on with them that I can't see, because of all the glare.

Honestly, the existence of all those other databases, and database models, and the billions of dollars spent on them, is a fluke, probably. It's unlikely you will ever in your life see or work on an application that needs a database other than SQLite. Because the only applications you will ever work on won't ever run on more than one virtual host, or be used by more than one application. And definitely you will probably not need a feature that isn't in SQLite.

SQLite is very fast, it is very simple, it is very well written, and it has a lot of tests. Therefore we can conclude that you should never look at or learn about another database, because considering the previously states information, we know that no other database could possibly be desired or needed.

I don't know a lot about databases. And, granted, I only just found out about SQLite. But I am quite sure I am correct that SQLite is the only one you'll ever need in most cases.

Re: SQLite the only database you will ever need in most cases (2021)

#169

Post by OrvalWintermute »

> In contrast to many other database management systems, SQLite is not a client-server database engine, but you actually very rarely need that. If your application software runs on the same physical machine as the database, which is what most small to medium sized web applications does, then you probably only need SQLite.

Disagree.

If you think about it from an attack surface perspective, there are numerous advantages to isolating the database. There are performance, availability, sharding, and columnar options out there also that may better meet the use-case (just to name a few). I have ran Postgres on endpoints when developing with performance akin to SQLite. Further, there are numerous ways in which to increase performance, availability, or to pursue some of the more customized versions of Postgres depending on use-case. One of the times I used Postgres was with Oracle DBAs, and they found the transition pretty simple.

Various customizations / extensions / versions of PG

There are security versions e.g. https://www.crunchydata.com/products/hardened-postgres

Columnar / high performance Parallelized extensions e.g. https://www.citusdata.com/product

General Purpose / Oracle transitions e.g. https://www.citusdata.com/product

Yandex even has an embedded Postgres https://github.com/yandex-qatools/postgresql-embedded

If you'd like to see a full list of features see https://www.postgresql.org/about/featurematrix/

More than this though, PG has a really excellent community with a large amount of talented folks, available both individually and through OSS oriented companies https://www.postgresql.org/support/professional_support/ and willing to help out on Libera https://www.postgresql.org/about/news/migration-of-postgresq...

Re: SQLite the only database you will ever need in most cases (2021)

#170

Post by napsterbr »

umvi wrote: post #157

Everytime I try to use SQLite I run into db locking issues where I seemingly have to try to run my query in a retry loop. Am I doing something wrong or does SQLite just not play nice in multi threaded contexts?

SQLite is a great but complex software. In order to properly use it, you do need to read the documentations, guides and resources available at the official website. If you don't, you will shoot yourself in the foot.

In that sense, I have the impression SQLite is different from other database software. You can usually get by with Postgres or MySQL (after they are set up) without looking at their docs.

I spent several hours reading the SQLite docs. It wasn't wasted time: actually learning SQLite made me a better professional. But for those thinking of using it on their side (or main) project, definitely understand how it works and what are the trade-offs involved.

Now, to answer your question specifically: it depends on a few factors. You can have concurrent readers (wal). With newer developments (wal2 + begin concurrent), you can also have concurrent writes as long as they happen at different pages.

If you are blindly doing multi-threaded connections without understanding the implications, you also risk corrupting the database entirely.

Post reply on HN