5 ms·

It's a c library. Other languages will have a library/package/whatever to use it. You point it at a file.

paranoidrobot 4y ago
"Just point it a file" skips all the bits you need for a production system. How do you back it up, replicate it, handle two different processes/containers/servers wanting to access the same data. Using a PAAS solution for a database, you get all that functionality.
- samtho 4y ago
  I think the point to be learned here is that SQLite fundamentally does not fit in a PaaS model. They are even transparent with this limited use case[0]. I work with embedded systems so I use it a lot, and all the web work I do nowadays is one-off project site and small utilities that are usually a single process so I end up use SQLite 90% of the time I’m reaching for a solution. 0: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
  - irskep 4y ago
    I think it's more that PaaS vendors aren't interested in first-class SQLite support when they can sell overpriced managed Postgres instead. Sure, it doesn't scale the same way, so it's hard to move upmarket and sell to Enterprise, but it's a shame that there's no one-click solution like there is for a managed database.
    - azurelake 4y ago
      Let's say the following product existed: 1. You can only run one instance of your app. 2. There is a small window of downtime each deploy. 3. Your app has access to 10 GB of storage. The storage is persisted across deploys and can be used for sqlite. 4. Your sqlite data is automatically backed up and can be restored / downloaded as needed. How much per month would you pay for that product?
      - irskep 4y ago
        Interesting question. I think you could probably get those features by deploying Piku or Dokku for like $4/mo on a VPS, and the equivalent managed Postgres on Render would be like $20/mo (16GB SSD), so something like $6-10 seems like the right range to me. Maybe not a booming business, but a nice margin percentage and worth paying for good UX. And the cheap version doesn't need to be the only version if people want more RAM, etc. I'd find such a product really appealing, especially with a dedicated backup strategy that uses the SQLite backup API to do hourly snapshots or uses Litestream to S3 by default.
        azurelake 4y ago
        Let's say the price was 12. If this idea is compelling enough for you at that price point to venmo me an advance for your first month, I have some ideas to explore that could actually make this viable. FWIW, I've worked professionally on a PaaS and a managed database offering.
        canadiantim 4y ago
        If you make it so I can have one SQLite database per user, I’d be a customer too
        azurelake 4y ago
        When you say per user, you mean per user of your app?
        canadiantim 4y ago
        yeah exactly. I want each user's data to be silo'd in their own sqlite database each. It's basically just a multi-tenant setup using sqlite.
        azurelake 4y ago
        Cool, thanks for the clarification!
- danielmarkbruce 4y ago
  That's why "most cases". If you are trying to replicate or make available to a bunch of machines or similar - it's likely the wrong thing (although there are tools to do this, I've never used them). If you are just trying to back it up there is a pretty simple back up command.
  - paranoidrobot 4y ago
    I think we disagree on the "most cases" aspect that For most of the things I touch, having a single-point-of-failure data store is typically not acceptable for production environments, except perhaps caching.
    - danielmarkbruce 4y ago
      If you are a good software engineer, you probably work at a place that needs good software engineers, which by definition tends to be a place with a system that needs high availability or low latency or both or something else, while serving a lot of customers or load or something, all of which basically results in a pretty complicated distributed system... So in your day to day job as a good software engineer, yeah sqlite isn't going to be a good choice. It doesn't work well for the sorts of systems you work on. But if you were going to build a new social network for your college or something trivial like that, and you just want to move fast and have a crazy simple deployment setup, you might well use sqlite.
- lelandbatey 4y ago
  It's a file is relevant. It allows you to think of it like other files: - how do you back up a JPG? You copy it, cause it's a file - how do you replicate it? You copy it, cause it's a file. Unless you're talking about fancy DB replication, in which case well, that's not really a thing we do with files much. You'll have to do more research. but that's cause you're trying to do non-file things to a file. - how do you handle two different processes accessing a JPG? They both open and read the JPG. Same for SQLite. For other concerns, you're trying to do things that don't map well to files, so once again more research needed.
  - sanderjd 4y ago
    I don't update and append to jpgs though...
  - billyhoffman 4y ago
    Only you can’t copy it if it’s in the middle of a transaction or you corrupt it and have to roll back the Journal. And coping the journal, WAL, and DB itself directly and trying to backup from that isn’t recommended. In fact SQLite itself has a purpose built backup API. https://www.unixsheikh.com/articles/sqlite-the-only-database-you-will-ever-need-in-most-cases.html https://www.unixsheikh.com/articles/sqlite-the-only-database... Yes, technically a SQLite database is just a file on disk. And for the most part, you can treat it like other files on disk. Except for when you can’t. The GP’s questions are valid
  - aembleton 4y ago
    how do you handle two different processes writing to a JPG?
irskep 4y ago
The sarcasm and intentionally-missing-the-point here is not really in the spirit of HN, but I'll try to address what you seem to be trying to say, which is that "it's obvious and I'm an idiot for not seeing that it's obvious": - Will multithreading make it break? (Not with WAL mode, but you have to set it manually, as this article suggests.) - When using a PaaS, you need to explicitly add a volume and mount it on your server machine, which you might not think to do if you're a brand new bootcamp grad - You can't(?) run migrations from another process (or at least, people don't seem to talk about doing this), so you need to prevent your server from writing while you run the migration. Even if I'm wrong, and I would love to be, it's frustrating that people don't talk about the completely ordinary need to run migrations on a database. - Backups just mean copying the file somewhere, which is nice, but you might need to configure that yourself instead of just using somebody's managed Postgres backup feature So, there is at least some complexity that comes with managing a "real" SQLite web server. (I'm probably at least 25% wrong on some details above, but that's kind of the point, it's not always easy to figure this stuff out.)
- danielmarkbruce 4y ago
  It's not sarcasm. The fact that it's a library and you point it at a file matters, and should be thought about. It implies that it's not built for distributed systems. It's not supposed to be a managed service. It's not a good option for what you appear to want. You deploy it as part of your application.. it's a library.
  - sanderjd 4y ago
    > 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 ordinary use case"? I think the answer is that you are a different person than the author of the article and you don't agree with the article's claim. Which, great, that was irskep's entire point! I tend to agree with irskep that this SQLite architecture is really interesting, but I also don't quite get it. There seem to be a lot of missing details for totally bog standard applications, that people frustratingly seem to just not even mention - like the migrations thing, or how to do zero downtime deploys in general, or a few other things - as if those concepts are irrelevant or uncommon. But they aren't, they're important and typical things to think about.

"Just point it a file" skips all the bits you need for a production system. How do you back it up, replicate it, handle two different processes/containers/servers wanting to access the same data. Using a PAAS solution for a database, you get all that functionality.

samtho 4y ago
I think the point to be learned here is that SQLite fundamentally does not fit in a PaaS model. They are even transparent with this limited use case[0]. I work with embedded systems so I use it a lot, and all the web work I do nowadays is one-off project site and small utilities that are usually a single process so I end up use SQLite 90% of the time I’m reaching for a solution. 0: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
- irskep 4y ago
  I think it's more that PaaS vendors aren't interested in first-class SQLite support when they can sell overpriced managed Postgres instead. Sure, it doesn't scale the same way, so it's hard to move upmarket and sell to Enterprise, but it's a shame that there's no one-click solution like there is for a managed database.
  - azurelake 4y ago
    Let's say the following product existed: 1. You can only run one instance of your app. 2. There is a small window of downtime each deploy. 3. Your app has access to 10 GB of storage. The storage is persisted across deploys and can be used for sqlite. 4. Your sqlite data is automatically backed up and can be restored / downloaded as needed. How much per month would you pay for that product?
    - irskep 4y ago
      Interesting question. I think you could probably get those features by deploying Piku or Dokku for like $4/mo on a VPS, and the equivalent managed Postgres on Render would be like $20/mo (16GB SSD), so something like $6-10 seems like the right range to me. Maybe not a booming business, but a nice margin percentage and worth paying for good UX. And the cheap version doesn't need to be the only version if people want more RAM, etc. I'd find such a product really appealing, especially with a dedicated backup strategy that uses the SQLite backup API to do hourly snapshots or uses Litestream to S3 by default.
      - azurelake 4y ago
        Let's say the price was 12. If this idea is compelling enough for you at that price point to venmo me an advance for your first month, I have some ideas to explore that could actually make this viable. FWIW, I've worked professionally on a PaaS and a managed database offering.
        canadiantim 4y ago
        If you make it so I can have one SQLite database per user, I’d be a customer too
        azurelake 4y ago
        When you say per user, you mean per user of your app?
        canadiantim 4y ago
        yeah exactly. I want each user's data to be silo'd in their own sqlite database each. It's basically just a multi-tenant setup using sqlite.
        azurelake 4y ago
        Cool, thanks for the clarification!
danielmarkbruce 4y ago
That's why "most cases". If you are trying to replicate or make available to a bunch of machines or similar - it's likely the wrong thing (although there are tools to do this, I've never used them). If you are just trying to back it up there is a pretty simple back up command.
- paranoidrobot 4y ago
  I think we disagree on the "most cases" aspect that For most of the things I touch, having a single-point-of-failure data store is typically not acceptable for production environments, except perhaps caching.
  - danielmarkbruce 4y ago
    If you are a good software engineer, you probably work at a place that needs good software engineers, which by definition tends to be a place with a system that needs high availability or low latency or both or something else, while serving a lot of customers or load or something, all of which basically results in a pretty complicated distributed system... So in your day to day job as a good software engineer, yeah sqlite isn't going to be a good choice. It doesn't work well for the sorts of systems you work on. But if you were going to build a new social network for your college or something trivial like that, and you just want to move fast and have a crazy simple deployment setup, you might well use sqlite.
lelandbatey 4y ago
It's a file is relevant. It allows you to think of it like other files: - how do you back up a JPG? You copy it, cause it's a file - how do you replicate it? You copy it, cause it's a file. Unless you're talking about fancy DB replication, in which case well, that's not really a thing we do with files much. You'll have to do more research. but that's cause you're trying to do non-file things to a file. - how do you handle two different processes accessing a JPG? They both open and read the JPG. Same for SQLite. For other concerns, you're trying to do things that don't map well to files, so once again more research needed.
- sanderjd 4y ago
  I don't update and append to jpgs though...
- billyhoffman 4y ago
  Only you can’t copy it if it’s in the middle of a transaction or you corrupt it and have to roll back the Journal. And coping the journal, WAL, and DB itself directly and trying to backup from that isn’t recommended. In fact SQLite itself has a purpose built backup API. https://www.unixsheikh.com/articles/sqlite-the-only-database-you-will-ever-need-in-most-cases.html https://www.unixsheikh.com/articles/sqlite-the-only-database... Yes, technically a SQLite database is just a file on disk. And for the most part, you can treat it like other files on disk. Except for when you can’t. The GP’s questions are valid
- aembleton 4y ago
  how do you handle two different processes writing to a JPG?

irskep 4y ago

The sarcasm and intentionally-missing-the-point here is not really in the spirit of HN, but I'll try to address what you seem to be trying to say, which is that "it's obvious and I'm an idiot for not seeing that it's obvious": - Will multithreading make it break? (Not with WAL mode, but you have to set it manually, as this article suggests.) - When using a PaaS, you need to explicitly add a volume and mount it on your server machine, which you might not think to do if you're a brand new bootcamp grad - You can't(?) run migrations from another process (or at least, people don't seem to talk about doing this), so you need to prevent your server from writing while you run the migration. Even if I'm wrong, and I would love to be, it's frustrating that people don't talk about the completely ordinary need to run migrations on a database. - Backups just mean copying the file somewhere, which is nice, but you might need to configure that yourself instead of just using somebody's managed Postgres backup feature So, there is at least some complexity that comes with managing a "real" SQLite web server. (I'm probably at least 25% wrong on some details above, but that's kind of the point, it's not always easy to figure this stuff out.)

danielmarkbruce 4y ago
It's not sarcasm. The fact that it's a library and you point it at a file matters, and should be thought about. It implies that it's not built for distributed systems. It's not supposed to be a managed service. It's not a good option for what you appear to want. You deploy it as part of your application.. it's a library.
- sanderjd 4y ago
  > 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 ordinary use case"? I think the answer is that you are a different person than the author of the article and you don't agree with the article's claim. Which, great, that was irskep's entire point! I tend to agree with irskep that this SQLite architecture is really interesting, but I also don't quite get it. There seem to be a lot of missing details for totally bog standard applications, that people frustratingly seem to just not even mention - like the migrations thing, or how to do zero downtime deploys in general, or a few other things - as if those concepts are irrelevant or uncommon. But they aren't, they're important and typical things to think about.