5 ms·

I remember reading about a company that upgraded their production database from a JSON file to SQLite. We really need this counter culture in software developm

I remember reading about a company that upgraded their production database from a JSON file to SQLite. We really need this counter culture in software development that emphasis simplicity over the "Start with Kafka-on-k8s" madness.

inportb 4y ago
Tailscale https://tailscale.com/blog/database-for-2022/ https://tailscale.com/blog/database-for-2022/
- Xeoncross 4y ago
  But tailscale is a distributed, multi-region SaaS provider. It's easy for them to use SQLite. What about a real-world workload? For example, I have 10 users a day on my new next.js app so I clearly need a RDS cluster for burst traffic.
  - bigdubs 4y ago
    Tailscale has some server components (account management etc.) that are powered with SQLite. Can read more here: https://tailscale.com/blog/database-for-2022/ https://tailscale.com/blog/database-for-2022/
  - deleted 4y ago
    [deleted]
  - batch12 4y ago
    One of my websites has been using SQLite3 as a backend database since 2018ish and works perfectly fine.
  - lillecarl 4y ago
    I mean, aws makes it quite easy to run postgres so I don't see why not either, you don't need a dba more or less than with SQLite + rqlite. If you wanna run onprem you can usually do with one container for quite awhile too. I wouldn't call postgres a "complex dependency" anymore.
  - samb1729 4y ago
    The existing replies to this comment of yours make it apparent why “/s” is still necessary :)
    - Illniyar 4y ago
      I find it hard to believe that someone would mistake the comment as not sarcastic if they read the second paragraph. So if people comment based only on the first paragraph, would they even get to the /s in the end ?
bazmattaz 4y ago
I thought that SQLlite databases are not suitable for large multi user websites. Something got to do with only being able to handles one transaction at a time. Isn’t that right?
- teirce 4y ago
  I think this is kind of their point. If you're starting out, you may not need to meet the same considerations as a "large multi user website". So you don't get stuck trying to do that - you start with what you need (appropriately) and adjust down the line. It is hard to do right.
  - Gigachad 4y ago
    The problem is you have SQLite which works some of the time and Postgres which works all of the time. Postgres is a great option for websites of all sizes. There is vanishingly few reasons to ever pick SQLite for a website. When the only real advantage is not having to run a second process which only really matters for local apps.
- themerone 4y ago
  SQLlite can do handle concurrent reads, writes are a problem. A site like this which is mostly a read-only archive is a perfect use case for SQLite.
- colechristensen 4y ago
  Sure, but you can have raised millions with large numbers of customers before this is an issue. Having multiple customers using your platform at the same time to the extent that their transactions actually end up having to wait a meaningful amount of time requires a lot of usage that you often don’t reach for a long long time.
  - derefr 4y ago
    Or just having any long OLAP queries mixed into your OLTP workload. A readers-writer lock can often end up with tons of writers waiting on one last long read to drain.
vsroy 4y ago
Context: I see a bunch of people here recommending SQLite. I have a suggestion, try out LMDB. It's kind of like noSQL SQLite. It's a simple in-process K/V store with a few features like compound keys that allow you to model relational data well enough. I recently used lmdb for webhighlighter.com (specifically the wrapper: https://www.npmjs.com/package/node-lmdb https://www.npmjs.com/package/node-lmdb), and it was a fantastic decision. A lot of people here say "use SQLite for small projects". But even using SQLite can be significant over-engineering. Running migrations? Writing SQL? That's too much effort for me. For example: In my application, people can leave comments on a (what is effectively) a post. A SQL-native solution might have a table for comments, with foreign keys to post IDs. That's 10x more engineering then I want to do for an MVP. I just store all the comments as an array on a post. This means reads read all the comments and writes require reading all the comments, appending, and then re-writing. That's totally fine, and will probably scale me to 100x my current traffic. LMDB-JS is great. It allows you to serialize arbitrary JS objects to LMDB using the message pack encoding system. This makes for some super concise code. Here's my entire data layer: - Interface: https://github.com/vedantroy/grape-juice/blob/main/site/app/db/types.server.ts https://github.com/vedantroy/grape-juice/blob/main/site/app/... - Implementation: https://github.com/vedantroy/grape-juice/blob/main/site/app/db/lmdb.server.ts https://github.com/vedantroy/grape-juice/blob/main/site/app/... TL;DR -- I won't use SQLite, for, I don't know, my first 10K users?
- pfraze 4y ago
  Flashback to when leveldb was hot in the node ecosystem KV stores are fun but you end up writing a lot of queries and indexes manually
- ascar 4y ago
  > But even using SQLite can be significant over-engineering. Running migrations? Writing SQL? That's too much effort for me. Key-value stores are not a replacement for SQL, they solve a simpler problem. If you actually can do with a key value store I would argue a SQL table with two columns key and value with key being a primary key will do just fine. And using it needs simple select and 'insert on duplicate key update' statements. The effort for migrations won't be higher than whatever you had to configure for your key-value store. However, if you can't actually do with a key-value store you will end up writing a lot of the features SQL provides in application code. Indices, joins, grouping, ordering, etc. You might not notice it at first, but you will blow up complexity in your business logic reinventing existing SQL features and chances are high you're doing it worse and less performant than what e.g. Postresql offers out of the box. SQL is just such a powerful tool that is at the same time incredibly easy to use for simple scenarios. Added benefit of using SQLite, you can migrate to a more powerful SQL database later without having to reengineer your whole data layer.

inportb 4y ago

Tailscale https://tailscale.com/blog/database-for-2022/ https://tailscale.com/blog/database-for-2022/

Xeoncross 4y ago
But tailscale is a distributed, multi-region SaaS provider. It's easy for them to use SQLite. What about a real-world workload? For example, I have 10 users a day on my new next.js app so I clearly need a RDS cluster for burst traffic.
- bigdubs 4y ago
  Tailscale has some server components (account management etc.) that are powered with SQLite. Can read more here: https://tailscale.com/blog/database-for-2022/ https://tailscale.com/blog/database-for-2022/
- deleted 4y ago
  [deleted]
- batch12 4y ago
  One of my websites has been using SQLite3 as a backend database since 2018ish and works perfectly fine.
- lillecarl 4y ago
  I mean, aws makes it quite easy to run postgres so I don't see why not either, you don't need a dba more or less than with SQLite + rqlite. If you wanna run onprem you can usually do with one container for quite awhile too. I wouldn't call postgres a "complex dependency" anymore.
- samb1729 4y ago
  The existing replies to this comment of yours make it apparent why “/s” is still necessary :)
  - Illniyar 4y ago
    I find it hard to believe that someone would mistake the comment as not sarcastic if they read the second paragraph. So if people comment based only on the first paragraph, would they even get to the /s in the end ?

bazmattaz 4y ago

I thought that SQLlite databases are not suitable for large multi user websites. Something got to do with only being able to handles one transaction at a time. Isn’t that right?

teirce 4y ago
I think this is kind of their point. If you're starting out, you may not need to meet the same considerations as a "large multi user website". So you don't get stuck trying to do that - you start with what you need (appropriately) and adjust down the line. It is hard to do right.
- Gigachad 4y ago
  The problem is you have SQLite which works some of the time and Postgres which works all of the time. Postgres is a great option for websites of all sizes. There is vanishingly few reasons to ever pick SQLite for a website. When the only real advantage is not having to run a second process which only really matters for local apps.
themerone 4y ago
SQLlite can do handle concurrent reads, writes are a problem. A site like this which is mostly a read-only archive is a perfect use case for SQLite.
colechristensen 4y ago
Sure, but you can have raised millions with large numbers of customers before this is an issue. Having multiple customers using your platform at the same time to the extent that their transactions actually end up having to wait a meaningful amount of time requires a lot of usage that you often don’t reach for a long long time.
- derefr 4y ago
  Or just having any long OLAP queries mixed into your OLTP workload. A readers-writer lock can often end up with tons of writers waiting on one last long read to drain.

vsroy 4y ago

Context: I see a bunch of people here recommending SQLite. I have a suggestion, try out LMDB. It's kind of like noSQL SQLite. It's a simple in-process K/V store with a few features like compound keys that allow you to model relational data well enough. I recently used lmdb for webhighlighter.com (specifically the wrapper: https://www.npmjs.com/package/node-lmdb https://www.npmjs.com/package/node-lmdb), and it was a fantastic decision. A lot of people here say "use SQLite for small projects". But even using SQLite can be significant over-engineering. Running migrations? Writing SQL? That's too much effort for me. For example: In my application, people can leave comments on a (what is effectively) a post. A SQL-native solution might have a table for comments, with foreign keys to post IDs. That's 10x more engineering then I want to do for an MVP. I just store all the comments as an array on a post. This means reads read all the comments and writes require reading all the comments, appending, and then re-writing. That's totally fine, and will probably scale me to 100x my current traffic. LMDB-JS is great. It allows you to serialize arbitrary JS objects to LMDB using the message pack encoding system. This makes for some super concise code. Here's my entire data layer: - Interface: https://github.com/vedantroy/grape-juice/blob/main/site/app/db/types.server.ts https://github.com/vedantroy/grape-juice/blob/main/site/app/... - Implementation: https://github.com/vedantroy/grape-juice/blob/main/site/app/db/lmdb.server.ts https://github.com/vedantroy/grape-juice/blob/main/site/app/... TL;DR -- I won't use SQLite, for, I don't know, my first 10K users?

pfraze 4y ago
Flashback to when leveldb was hot in the node ecosystem KV stores are fun but you end up writing a lot of queries and indexes manually
ascar 4y ago
> But even using SQLite can be significant over-engineering. Running migrations? Writing SQL? That's too much effort for me. Key-value stores are not a replacement for SQL, they solve a simpler problem. If you actually can do with a key value store I would argue a SQL table with two columns key and value with key being a primary key will do just fine. And using it needs simple select and 'insert on duplicate key update' statements. The effort for migrations won't be higher than whatever you had to configure for your key-value store. However, if you can't actually do with a key-value store you will end up writing a lot of the features SQL provides in application code. Indices, joins, grouping, ordering, etc. You might not notice it at first, but you will blow up complexity in your business logic reinventing existing SQL features and chances are high you're doing it worse and less performant than what e.g. Postresql offers out of the box. SQL is just such a powerful tool that is at the same time incredibly easy to use for simple scenarios. Added benefit of using SQLite, you can migrate to a more powerful SQL database later without having to reengineer your whole data layer.