Ask HN: Have you used SQLite as a primary database? • Hacker News Board

Re: Ask HN: Have you used SQLite as a primary database?

#171

Post by simonw » Mon, Apr 25, 2022, 3:15 PM UTC

sicp-enjoyer wrote: post #121

I have used SQLite for Django applications with a few thousand users. It has had no problems. However, I just use the ORM and never configure the SQL directly. The vast majority of LAMP stack style web applications would be an ideal use case. However, I would consider how important RDMS features are to you which are not available in SQLite: - less sophisticated type and constraint system. - a severely limited ALTER T…

SQLite has a pretty powerful permission model via the set_authorizer hook - you can register a callback function which will be called each time SQLite attempts to read data. It's not widely used though from what I've seen.

It's also really easy to add new custom SQL functions to a SQLite connection, which means the missing math functions shouldn't be a limitation. Here's an extension for example: https://github.com/nalgeon/sqlite-stats

Re: Ask HN: Have you used SQLite as a primary database?

#172

Post by mekster » Mon, Apr 25, 2022, 3:15 PM UTC

post-it wrote: post #162

mekster wrote: post #160

Earlier quoted context omitted.

Which part of running MySQL instead SQLite is over engineering?

- Setting up a MySQL server on both your dev machine and server, and making sure they're the same version (extra fun if they're on different OS versions) - Setting up an out-of-repo config file on the server with your MySQL credentials - Setting up a backup script for your server data It's only about an hour of work total, but it's an hour of work that I hate doing.

So you trade for some risk for an hour.

Re: Ask HN: Have you used SQLite as a primary database?

#173

Post by zoomablemind » Mon, Apr 25, 2022, 3:17 PM UTC

sicp-enjoyer wrote: post #113

zoomablemind wrote: post #103

> ...I periodically hear about projects that use/have used sqlite as their sole datastore. SQLite==exclusive access, no sharing, unless read-only. Basically, it provides a SQL convenience for local usage.

If you need to safely have multiple processes read and write to the same data, it does that great. The writes are serialized, but that's typically how an in-memory shared resource would be implemented as well. Do you mean shared across networks?

My understanding is that SQLite suppprts only system locks. So multiple writers will need to be either blocking on system level or implement some other form of locking to ensure integrity.

A great deal of complexity of DBMS is in granularity of locks, its escalation/deescalation, and shared use performance.

I wonder if one day SQLite would support synchronization/replication protocol. In a way Fossil SCM is an attempt at SQLite replication, albeit a specialized one.

Re: Ask HN: Have you used SQLite as a primary database?

#174

Post by simonw » Mon, Apr 25, 2022, 3:23 PM UTC

I'm running a bunch of different read-only sites and APIs on top of SQLite using Cloud Run and Vercel - abusing the fact that if the database is read-only you can package up a binary DB file as part of a Docker container or application bundle and run it on serverless hosting.

This means it won't cost any money if it's not receiving any traffic, and it can scale easily by launching additional instances.

I wrote about my patter for doing this, which I call Baked Data, here: https://simonwillison.net/2021/Jul/28/baked-data/

A few examples are listed here: https://datasette.io/examples

Re: Ask HN: Have you used SQLite as a primary database?

#175

Post by kyawzazaw » Mon, Apr 25, 2022, 3:25 PM UTC

mekster wrote: post #172

post-it wrote: post #162

Earlier quoted context omitted.

- Setting up a MySQL server on both your dev machine and server, and making sure they're the same version (extra fun if they're on different OS versions) - Setting up an out-of-repo config file on the server with your MySQL credentials - Setting up a backup script for your server data It's only about an hour of work total, but it's an hour of work that I hate doing.

So you trade for some risk for an hour.

Pieter Levels has been using SQLite for nomadlist and I think it's been going well for him.

Re: Ask HN: Have you used SQLite as a primary database?

#176

Post by shofetim » Mon, Apr 25, 2022, 3:25 PM UTC

I use SQLite in production and it works great.

You should understand whichever RDBMS you use, and how to get the best performance out of it. Previously I used Postgres extensively, and it worked fine, and before that I managed MySQL servers. They are all fine, but SQLite is as simple as it gets, and more than adequate for most workloads.

Re: Ask HN: Have you used SQLite as a primary database?

#177

Post by xaxaxb » Mon, Apr 25, 2022, 3:27 PM UTC

This article could help a little: https://www.unixsheikh.com/articles/sqlite-the-only-database...

But you probably won't see it since at the time of writing my response there are already 172 comments.

Re: Ask HN: Have you used SQLite as a primary database?

#178

Post by srcreigh » Mon, Apr 25, 2022, 3:29 PM UTC

cillian64 wrote: post #15

A slightly unusual use-case but for my work we have our own file format which is a thinly-veiled sqlite database. Originally we used a json file but we moved to sqlite for performance reasons once the files started getting to multi-gigabyte sizes. It works great - there are ergonomic APIs in most languages, it’s fast and reliable, and great to be able to drop into an SQL shell occasionally to work out what’s going on…

Considering adobe Photoshop use(d) SQLite for application file format, this could be very far from unusual.

Re: Ask HN: Have you used SQLite as a primary database?

#179

Post by Mertax » Mon, Apr 25, 2022, 3:30 PM UTC

matthewaveryusa wrote: post #87

I’ve worked on several projects with sqlite, both read and write heavy, all with high concurrency, with databases in the few hundred MB with 400k server clients, and 100 bare-metal servers running at capacity. The sqlite part of our system is never the problem. In our case sqlite has been an alternative to custom files on disk or replacing a spaghetti of hashmaps in memory. we also replaced a single postgresql instan…

What pain points have you experienced with "many sqlites per customer". I'm considering transitioning to something similar but would love to know what pitfalls I might not be considering.

Re: Ask HN: Have you used SQLite as a primary database?

#180

Post by astine » Mon, Apr 25, 2022, 3:30 PM UTC

qwerty456127 wrote: post #62

syntaxfree wrote: post #57

Earlier quoted context omitted.

Why?

I dunno, just felt like conventional (since long ago) knowledge that MyISAM is the fastest of all SQL DBs in simplistic non-RAM scenarios. I'm not sure this is true so I ask.

The engine might be faster (I'm not sure) but SQLite has the advantage that it doesn't have to connect over a socket. Instead you load the SQLite library into your code and your application directly manipulates the database files. That's potentially a lot faster.