20 ms·

Why you should probably be using SQLite

• xnorswap 3y ago
  Saying that something has "Zero latency" because it's "on disk" is off-putting from a technical honesty perspective.
  • JohnBooty 3y ago
    It's frustratingly close to correct. Since it's on local disk, it winds up being cached in in-process RAM, which is..... still not zero latency... but has so much less latency compared to talking the to database over a network stack... that I'm alllllllllmost sorta kinda okay with it. Almost. lol
  • deleted 3y ago
    [deleted]
  • rmbyrro 3y ago
    He's comparing to a networked database. In that context, a local disk has virtually zero latency for most use cases. Also, SQLite can run in-memory. If your process is long running and keep the DB in RAM, the latency is even closer to zero.
  • thaumasiotes 3y ago
    Wait until you see my all-storage-in-the-registers database. ;D
  • isoprophlex 3y ago
    Especially if you tout it as the solution for the n+1 problem...
    • Ensorceled 3y ago
      When really what you are really doing is putting off your n+1 problem until 6 months from now when you have actual active users with real data ...
      • fodkodrasz 3y ago
        Isn't that a rational business decision?
        • Ensorceled 3y ago
          Finding out your entire app can't scale with real data is not really something you want to find out in production. I'm not saying "don't use SQLite", I'm saying "reduced n+1" problems ISN'T a good reason.
          • fodkodrasz 3y ago
            AFAIK SQLite can scale quite a bit with WAL2, and then probably it is easy to upgrade it to Postgres. If your data model was bad from the start up, it doesn't matter if you used Postgres initially. If you find out your data model was bad, and is a constraint to scaling, you can find it out without spending a lot on insanely scaled RDB instances. I'm for the build one to throw away approach, and SQLite is often a good tool in the toolbelt for the initial attempt of this.
            • Ensorceled 3y ago
              Now we're getting silly. If you have n+1 issues you already have a bad data model which is why what you are really saying is "SQLite lets you have a bad data model which gives you velocity!!!!!" which is obviously (I hope it's obvious) not a good thing.
              • yawaramin 3y ago
                But the thing is that in SQLite you effectively don't have N+1 issues.
              • fodkodrasz 3y ago
                From a business perspective: You might not have to solve that problem at all in 6 months, because you might not get so many users for the product, that scalability is a problem. You often need velocity more than clean code, because you are not sure if the idea will get traction at all. In that case, from a business perspective, to be able to experiment you need velocity more than a good data model. I did this kind of exploratory development both with SQLite and Postgres, both had different strengths and weaknesses. Had the purist clean data model with Postgres cost more than it should have when the product had to pivot the product. Also have seen the scalability limits of SQLite. Just shutting down a POC project backed by SQLite, which failed to get traction mostly because of business execution problems, and SQLite saved a lot of effort for the implementation, and using a "cleaner" and "more scalable" solution would have only reduced velocity, but would not have got us more users, the system worked fine this way (even has N+1 queries in some places). Should we got to the point that SQLite starts to become a bottleneck we could have afforded to clean the data layer (we had to rework countless times already as requirements were in a flux) up a bit and move to more scalable solution. Others might stick Firebase or MongoDB there, and it also does work for a while, and that is also a valid decision IMHO from a business perspective. I think this is a kind of topic where the premature optimization is the root of all evil meme can apply, depending on the product you are making and the available resource pool. Not for the hello world examples of course, and not for the well defined well funded project, but there are lot of exploratory attempts out of the bigco/vc funded unicorn world.
    • refset 3y ago
      Exactly, the n+1 problem is really about query optimization, latency just makes it more pronounced.
  • acomjean 3y ago
    I see the idea of sql lite, but a mysql / Postgres store a lot in memory so no disk access required (if not the data the indexes stay memory resident) We have an old web based tool that uses Java and luncene indexed file system based database. It’s a pain to work with frankly. https://lucene.apache.org/ https://lucene.apache.org/
    • dham 3y ago
      True but the new hotness is edge and your database can be really far from where you actually deploy. This is what this article is trying to push back against. Mainly because Kent is all about Remix and Remix is more about just deploying to traditional servers.
      • willsmith72 3y ago
        remix is actually great for the edge. Lots of people run it on cloudflare workers in particular https://remix.run/docs/en/main/guides/performance#this-website--flyio https://remix.run/docs/en/main/guides/performance#this-websi...
    • _joel 3y ago
      > We have an old web based tool that uses Java and luncene indexed file system based database. It’s a pain to work with frankly. Elasticsearch waves
    • RedShift1 3y ago
      Note that your operating system caches file system reads so SQLite doesn't need to put anything extra in RAM. Most reads will be from RAM, so no disk access required.
  • hahn-kev 3y ago
    Compared to something over lan or internet, it is an order of magnitude less latency, so from an internet perspective it is 0
    • j-pb 3y ago
      Ram on a different machine via ethernet has lower latency than spinning disks in the same machine.
      • Cthulhu_ 3y ago
        You're reaching. Modern filesystems cache disk access in memory, and modern hardware doesn't use spinny disks for applications.
        • yawaramin 3y ago
          Also, SQLite has a configurable local page cache which it maintains directly in your application's process. There's a very likelihood that you're just hitting that in-memory cache directly, let me emphasize, in your own process. In-process memory cache hit is faster than separate-process memory cache hit over ethernet.
        • bravetraveler 3y ago
          We're actually to a point where storage bandwidth can exceed memory! NVMe arrays are weird, can be better to just rip it from the drives if you need bandwidth over latency Even then, with how easy the memory cache is to invalidate... I'm not sure even that's worthwhile. The caching isn't some Paragon of performance - there's only so much it can do, when looking at it funny invalidates it.
      • merpnderp 3y ago
        We've spent ridiculous amounts of money on faster disk and network and we're still at around 30ms for our average server ping to the DB.
        • 3y ago
• endisneigh 3y ago
  Sure if you don’t care about consistency use litefs smh. You’re going to end up creating a cobbled mess and were better off using a server side database
• password4321 3y ago
  Kent is HN's Steve Gibson (https://news.ycombinator.com/item?id=37999197#38001051 https://news.ycombinator.com/item?id=37999197#38001051) of full stack development. https://news.ycombinator.com/item?id=35903878#35915789 https://news.ycombinator.com/item?id=35903878#35915789 > Kent is the only blogger that I've felt compelled to tell all my less experienced colleagues to completely ignore
  • yawaramin 3y ago
    Yeah, it's interesting to see what happens when people with confirmation bias hit an unexpected opinion and can't explain it away. So all they can do is ignore it and ask everyone else to pretend it doesn't exist.
• bborud 3y ago
  I always use SQLite as the first database when I start a new project. It is a great way to get started quickly and not waste time while you are figuring out what problem you are solving and how you want to structure your data. On some projects I've been able to make do with SQLite only for over a year before having to bother with other databases. However, I always design with the intent to use PostgreSQL or some other database. Sometimes with the intent of using two different data stores in the scaled up version. For instance an SQL database for stuff that has low volumes, and perhaps a time-series database for the high volume stuff. This is why I always design a persistence interface. An internal persistence API layer inside the application. I also write the test suite to this interface. This makes adding support for other databases much easier later. Note that I say "add", because I usually keep the SQLite support after I've added support for the databases I want to use in production. Keeping SQLite is extremely useful for when people want to do integration testing or even when they are learning to use your server/service. Rather than having to fire up a database, people can just run with the built in SQLite. You download and run the binary and stuff just works. It also makes cleanup a lot easier. Not least if you run an in-memory database (use ":memory:" as the DB spec). In the persistence layer I tend to avoid using too much abstraction. The interface is my persistence abstraction. I don't need more layers. I think ORMs are incredibly limiting and unnecessary. However, I do use sqlx (Golang) to make the SQLite implementation of the persistence layer less verbose (faster turnaround when figuring stuff out). I usually use it for PostgreSQL too since the performance penalty usually isn't significant enough to matter. (If you haven't used sqlx before: it is essentially like using the DB driver directly, but with a lot less boilerplate). sqlx is trivial to just rip it out if you think it introduces too much overhead. Usually you will know ahead of time when you potentially need more than one data store. I tend to use SQLite for storing everything during initial development, but the API is usually designed in a way that doesn't make it awkward to split the store in two or more domains. I sometimes write storage API middleware. For instance statistics, adapters that can combine different permutations of different storage domains, sharding, throttling, specialized logging etc. I've done ACLs, failover, housekeeping middleware as well, but usually they do not belong in persistence middleware. I never put business logic or "cleverness" in the persistence layer. It should worry about storing and retrieving data. You can probably use SQLite as your database for much longer than you think. And some projects honestly will never need more than SQLite. However, I think that if you start doing sharding, replication and dealing with situations where throughput and concurrency is high, you really want to use something a bit more beefy. You have to think about how much time you'd spend getting SQLite to do things versus how much effort it is to just fire up database instance(s).
• georyb 3y ago
  I'd like SQLite to run on client side with persistency. Not specific to SQLite itself, but that would enable a lot of uses for it.
  • simonw 3y ago
    SQLite in WebAssembly can do that today.
• dboreham 3y ago
  s/probably/probably not/
• steve_adams_86 3y ago
  I’ve helped too many teams migrate from SQLite (after a ton of growing pains and suffering from tech debt) to agree with this. I think SQLite is awesome for some things. It’s also hard to beat in some cases. But if you use it in a non-ideal situation, you need to know when, why, and what your refactor path will be to get to the right technology. And there should be a good reason for doing that, and I can’t think of one that makes sense. The teams I helped often said something to the effect of “SQLite is great because it got us this far!”, and while I appreciate the optimism, they wouldn’t have gone a shorter distance with MySQL or Postgres. They made the wrong choice, period, and lost a huge amount of productivity to it. Yet there are people out there who have chosen something like firebase where SQLite would be perfectly fine and far simpler, too. I love SQLite, but I think it’s promoted incorrectly at times. It makes for some brutal growing pains when it’s used in the wrong places.
  • yawaramin 3y ago
    You get growing pains when you grow. This is a classic case of survivorship bias.
    • steve_adams_86 3y ago
      I get where you're coming from, but a few of these examples didn't grow in that sense. More so evolved and discovered how SQLite couldn't meet their needs efficiently (or at all). It became technical debt before they could properly get off the ground. I fully agree that there are projects out there which don't need to grow, won't grow, or otherwise perfectly suit the use of SQLite. I've encountered a lot of cases where it was simply the wrong choice, though. Growth or no growth. I think Kent likes doctrine and tends to encourage practices without addressing nuance sufficiently. The kinds of people he teaches aren't likely to understand where SQLite falls short, and it's also difficult to explain to people with less experience. The advice rubs me the wrong way as a result; in practice, I don't see people use SQLite properly more often than not. I'd say the same thing about MongoDB. It gets abused like crazy. It has a great fit in some applications and I love using it when that's the case. Yet it was promoted as the easy and scalable database for ages, and I can't count how many projects I've encountered which were badly encumbered by the misuse of that database. Is it a bad database? No, it works well in the right place. In the wrong place, it's a remarkably poor database though. SQLite is much the same (though I'd argue a better piece of technology all around).
• clarkmoody 3y ago
  Coming from a Rust perspective, I really value strong typing, so the SQLite type system leaves some things to be desired. Is there an alternative embedded database with stronger types?
  • mattrighetti 3y ago
    I’ve used sqlx and SQLite for a lot of projects and sqlx’s macros have prevented me from inserting the wrong types in columns, but I agree that it’s not a viabile long term solution, especially if you don’t use sqlx macros in the first place which is not something you can always do. On the contrary, I think that everything should be fine with Diesel since that leverages the rust ecosystem to manage types and migrations of the sqlite database.
  • jmull 3y ago
    A type system like what a programming languages have doesn't make sense for databases. For inserts, sure (but then, it's straightforward to use the host language for that). But once you move beyond simple cases, data changes over time, not atomically. It's also common for data to be distributed. Static type system can handle either of these things. If your database can't go down for the duration of a migration (which will need more time now, to type-check all the data), and you don't have a way to ensure all clients are updated at the same time also, then you can't use static type checking like programming languages have.
  • simonw 3y ago
    SQLite with strict mode turned on.
• timzaman 3y ago
  This post is incredibly untechnical
• frithsun 3y ago
  Funny this article would pop up for me today, as I'm currently migrating my web app from sqlite to mysql. I got jazzed up on articles like this, only to figure out the hard way that it's ill-suited to apps where concurrency matters. Sqlite is an incredible piece of software, but it doesn't do scalable, concurrent things well.
  • 0xbadcafebee 3y ago
    It's almost as if reading trending blog posts isn't a great way to decide software architecture
    • frithsun 3y ago
      I'm still soyfacing over sqlite and how elegant and powerful it is, and I'll continue deciding software architecture based on random blog posts. I have learned nothing.
  • simonw 3y ago
    Were you using SQLite in WAL mode?
    • frithsun 3y ago
      WAL Mode worked as advertised. But the project ended up being split into several autonomous workers, and even with WAL mode, sqlite gets problematic when you have several separate processes trying to read from and write to the same file.
  • snovv_crash 3y ago
    Concurrent writes, no. Reads are fine though.
• mgaunard 3y ago
  A database is essentially a server synchronizing access to the filesystem. Except in the case of sqlite, which is serverless and relying on the filesystem itself for synchronization. In theory it's going to be worse, though if there is low contention you might have some latency advantages due to not having the server hop.
• bob1029 3y ago
  The following is our current strategy. We used to be all-in on SQLite but we have discovered some nuance as we grow. We do B2B SaaS/PaaS for banking sector. If we have a customer that is OK with RPO measured in minutes-to-hours and they have fewer than 5k active users, we are perfectly happy running with SQLite on a customer-managed VM and instructing them to use snapshots for backup & restore. Everything on one simple cheap VM in Azure/AWS/on-prem. One storage volume to snapshot. No weird tricks. If the customer demands stronger consistency between their underlying record system and our system (specifically at time of catastrophe) and/or has more than 5k active users, then we are starting to push towards a separate cloud-native stack. Azure SQL Server Hyperscale with geographic replication to another region within ~100ms of the primary region. I looked long & hard at some of the SQLite replication options, but there is no way in hell I could get them to pass due diligence with the kinds of CTOs I have to argue with regularly. SQLite is incredible for keeping it light & simple, and we still advocate for this where it fits the overall technical strategy. In terms of performance, before you reach a certain breakpoint, it is very hard to beat SQLite. You have to do it wrong on purpose to make it slower than a single node hosted solution on equivalent hardware.
• whateveracct 3y ago
  config.services.postgresql.enable = true; ^ NixOS one-liner to get a local Postgres going. I tried SQLite for a single VPC startup MVP but then just used Postgres because it was just as easy and it's just better to build backend software with. And then when it made sense to use a managed Postgres instance, it was trivial to migrate.
• d1l 3y ago
  This is just yet another attempt to piggyback off the HN crowd's enthusiasm for Sqlite in order to advertise fly.io.
  • azemetre 3y ago
    I mean not fly.io, the author Kent C Dodds makes his living from selling web courses.
• wg0 3y ago
  One possible strategy is to have one directory/file per customer which is one SQLite file. But then as the user logs in, you have to look up first what database they should be connected to. OR somehow derive it from the user ID/username. Keeping all the customer databases in a single directory/disk and then constantly "lite streaming" to S3. Because each user is isolated, they'll be writing to their own database. But migrations would be a pain. They will have to be rolled out to each database separately. One upside is, you can give users the ability to take their data with them, any time. It is just a single file. [0]. https://litestream.io/ https://litestream.io/
  • MagicMoonlight 3y ago
    Or… just make their data a row in the database. SQLite tables can store huge amounts in their columns
• xenophonf 3y ago
  If I was going to give folks advice, it would be to use an ORM, instead. That let me build low-investment prototypes using SQLite. When I outgrew it, I could easily switch to PostgreSQL by adding the appropriate test framework plugin and changing the database connection string. SQLite, for all it's features, has some pretty significant limits. In my case, I needed better date/time and decimal type handling than what's on offer in SQLite. I still test my code against SQLite because that is a useful way to lint my data model—plus it's nice for small-scale proof-of-concept deployments—but I expect deployers to use PostgreSQL or MariaDB in production. Edit: I've had a chance to read the article. The comment about "zero latency" is wrong because it ignores disk I/O. And yes, I know SSDs are super fast. Still not zero. The bit about Docker Compose rings hollow. It sounds like they need to learn how to use multiple Compose files, e.g., https://mindbyte.nl/2018/04/04/overwrite-ports-in-docker-compose.html https://mindbyte.nl/2018/04/04/overwrite-ports-in-docker-com.... As for development and testing, mock database setup should be handled by your unit test framework. I'm familiar with pytest, which makes testing the same model or ETL across multiple database fixtures very, very easy. SQLite has nothing to do with that.
• jimbokun 3y ago
  I'm finding Sqlite DBs in S3 to be a great solution for ETL tasks. It's a project where multiple other applications want to process data produced by my application. Instead of forcing them to make API calls to retrieve all of the data and all the network overhead and latency that entails, periodically write Sqlite DBs to S3 and post an event that the database is available. Can create one database per customer, or whatever your natural partition key is. Then it's just pull the database to local disk, pull all the data you need with SQL queries, and delete from the bucket when every client has had a chance to process it. I believe it offers much better throughput than going through an API.
• dbrgn 3y ago
  SQLite is great, but the article misses the greatest weakness: It is almost entirely untyped. Feel free to specify fancy types for your columns. But SQLite doesn't care in the least. You can create an INTEGER column and write text to it. Or bytes. Or anything you'd like! The other thing I miss often is better support for schema migrations. For a lot of things (like adding certain constraints after table creation) the "create new table, copy data over, delete old table" workaround is needed. However, it's only a minor annoyance, and if it allows the codebase to be more maintainable, that's OK with me (given the fantastic quality track record of SQLite).
  • Pako 3y ago
    SQLite has a solution for this! There's called strict tables and final SQLite to get mad at you when you try to put a string into a number column, etc. https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html
    • dbrgn 3y ago
      Oh wow! Thanks a lot for this link, I wasn't aware of that.
• laurent123456 3y ago
  Is that our monthly "you should always use SQLite except when you should not" article?
• 015a 3y ago
  I see posts like this and I can't help but think: This sounds exactly like something an academic would recommend, and then you hit the reality of industry engineering. Its a similar take to a lot of the negativity surrounding Next 14's stabilized server actions. The negativity is academic; the productivity is industrial. Here's my counter-hype take: SQLite actually kind of sucks. It has a place, but that place isn't significantly different from where it was five years ago despite all the "serverless read replicated VC funded hacker news hype startups" work that's happened since then. If you're in crazy-enterprise hell-engineering; no one is going to reach for SQLite when more robust alternatives like Postgres exist. If you're trying to get something out the door fast, you've got Postgres on Supabase, you've got MySQL on Planetscale, you've got Firebase, fifteen years of database-platform development, all of these aren't just cheap, they're free, and zero maintenance, you're not going to pay more money to do more work for a worse product by setting up SQLite on your single DigitalOcean VPS. You probably won't even reach for something like Cloudflare D1; sure, its interesting, but why? Its just Planetscale, but not better in any way and worse in plenty (its not even really much cheaper). SQLite doesn't support alter table. It doesn't have a date-time type. It doesn't enforce columnar types outside of strict mode, which isn't enabled on e.g. Cloudflare D1. Someone stop me. Everyone says "KISS, you can get so much performance out of one application server with SQLite running locally" but you can get the same f^cking performance out of one application server, and a DBaaS, like Planetscale, and its easier, and its cheaper, and you get backups, and you get full MySQL, and you get a paved-road to actually paying them $30/mo if you need to instead of hitting a bottleneck and suddenly having to wonder how the hell you're going to scale SQLite beyond this one instance ("we need VC funding for more engineers", you'll think in that moment) Kent's statement "However, SQLite is capable of handling databases that are an Exabyte in size" actually makes me mad. Its so ridiculously academic I'm aghast anyone takes this seriously. "Oh, well, hc-tree uses 48 bit page numbers so its an exabyte, and you'll hit other application problems first anyway". No; you'll hit problems with SQLite first, ten times out of ten, it'll be long before you hit an exabyte, it'll be before you even hit 100gb.*
• josegonzalez 3y ago
  Who wrote this, an SQLite vendor? On a serious note, there is definitely a time and a place for SQLite - it's a great technology - but the engineering being done to make it scalable across multiple servers etc. seems ill-placed. For applications of moderate complexity or scale, I suspect developers will quickly see issues trying to monitor and interact with SQLite in ways that they wouldn't with other tech.
• jarrell_mark 3y ago
  Started a Minimum Lovable Product last week that helps people work through their anxieties or fears. Using SQLite as a backend for everything from session management to logging. No elk stack, no separate db. Just a single file. Development is so simple so far. Maybe I’ll need to move off of it later, but who knows maybe not. And the speed of development and simplicity is what matters more now. https://bravesteps.love https://bravesteps.love
• deleted 3y ago
  [deleted]
• matharmin 3y ago
  SQLite is by far my favorite database for any client-side application - mobile and desktop (and perhaps embedded). I still wouldn't use it server-side for anything serious. While you can do it, especially with tech such as LiteFS, I much prefer using database systems with built-in support for replication, failover, and splitting data storage from the app server.
• pphysch 3y ago
  The first two items are just wrong. I use PostgreSQL like SQLite all the time. That is: Colocated with the application, connected only via UNIX socket. In these cases, the database is not meant to be a separate application, so I don't treat is as something separate. Ironically, this is also the easiest way to set up PostgreSQL. At least in the distributions I use, it is already configured to connect locally over UNIX sockets, and all I have to do is create a system user to connect with. PostgreSQL has a bad reputation for being difficult to set up because people expect it to be exposed on the network by default, and then they have to dig into the obscurely named pg_hba.conf and change multiple lines. There is ongoing cargo cult wisdom that "the RDBMS HAS to be a standalone service" which is just nonsense and extremely counterproductive for the median use case.
• AYBABTME 3y ago
  SQLite is cute and very appropriate in certain cases: - Local storage for a mobile/desktop app, instead of a DIY file type. - Local cache storage for a distributed system, instead of a DIY file type. While you can use it as a general backend, and some people work hard to make it usable in distributed systems, it continues to be a fancy elaborate project to turn SQLite into something it isn't. It's a thought experiment that happens to get corporate funding. It's fun and interesting in the same way as Dogecoin is. There's plenty of free DBaaS these days that are frankly incredible and FREE. I work for one, so might sound like I'm chilling that space. But I recently went to work for one exactly because I think the new offerings are an incredible leap forward in developer productivity, and there's a lot of cool stuff to be done here. I could instead have gone to work for one of those who are working on SQLite, but I just don't believe they're real products for real web workloads. They're a marketing catch for curious learners and experimenters. I respect the intellectual curiosity but it's not how I would build anything.
  • deleted 3y ago
    [deleted]
  • simonw 3y ago
    Free is a difficult promise to keep for the long run, which makes it hard to strategically depend on. Heroku was free for 15 years!
    • AYBABTME 3y ago
      If you're building a business, the Free tier should be a starting point, not a forever solution. Either the business dies, or it grows and you'll graduate to a paid option very quickly.
      • simonw 3y ago
        Sure, but building a business isn't the only reason for people to select a hosting solution. Personal side projects are the most obvious example. I do a lot of work in journalism. Newsrooms don't have a budget for maintaining interactive apps they built for a story that ran ten years ago, so they often made the sensible (at the time) decision to deploy them as a free, scale-to-zero Heroku instance. Those apps are all gone now, thanks to Heroku bait-and-switching. Many of those same newsrooms were paying customers of Heroku for other purposes!
• hermitcrab 3y ago
  I have a desktop app that stores all it's info in a single XML. The XMl file is read into memory and written to disk when they save. I lock the XML file so that only user can have it open to write. Customers never need more than a few thousand records and it works fine. But some of my customers want to be able to read/update data from more than one PC/Mac on a local network. Performance requirements are low (generally <10 concurrent users and <1 update per second). I don't want to use a heavtwight database. I wondered if SQLite could be a way to handle this? But the SQLite FAQ seems to have quite a few caveats about accessing it from multiple processes across a network. Or is there a simpler/more reliable way to transition a single-user app to multi-user?
  • simonw 3y ago
    This is one of the few cases where I would recommend staying clear of SQLite, because the maintainers are very clear they running it on the network file system with multiple processes is accessing is a bad idea.
    • hermitcrab 3y ago
      Ok, thanks. Back to the drawing board.
  • deleted 3y ago
    [deleted]
• wave84 3y ago
  As a guy running a few Debian web servers for some small client projects and a few personal projects (some of which are medium sized, e.g. a website that gets 10 million pageviews / month) I actually tried to move from MariaDB to SQLite. The idea seemed great, at least in theory. Mainly to get away from mysqld, which actually crashed on me and corrupted some data once or twice in a timespan of 15 years.. think it was due to package updates every time. Anyway, query and app logic modifications aside, I quickly ran into two unsolvable issues for me. 1. The lack of a robust web based admin tool comparable to phpmyadmin (no, phpliteadmin doesn't even come close) 2. No alter table. This hit pretty hard. Plus lots of other idiosyncrasies - no enums, no type for dates, etc. In the end I decided it's not worth the extra work, trouble and risks and stayed on Maria.
  • simonw 3y ago
    My main takeaway from this thread is that I need to do a much better job promoting my solution to the SQLite ALTER TABLE problem. https://simonwillison.net/2020/Sep/23/sqlite-advanced-alter-table/ https://simonwillison.net/2020/Sep/23/sqlite-advanced-alter-... My Datasette tool doesn't quite serve the same purpose of phpMyAdmin unless you're OK with read-only access, in which case it works great: https://datasette.io https://datasette.io
  • Sammi 3y ago
    I've been having a good time with this vs code plugin for viewing and editing sqlite db files: https://marketplace.visualstudio.com/items?itemName=yy0931.vscode-sqlite3-editor https://marketplace.visualstudio.com/items?itemName=yy0931.v... I have also found the CHECK constraint on an INTEGER column to serve well as an ENUM: https://stackoverflow.com/questions/5299267/how-to-create-enum-type-in-sqlite https://stackoverflow.com/questions/5299267/how-to-create-en... For dates I happily use unix epoch integers or iso8601 date strings.
• thrownaway561 3y ago
  SQLite is the MSAccess of this generation. MSAccess was an absolutely wonderful database when you were hosting multiple small sites on a server and wanted quick deployment, easy setup and segregation. It connected easily with ODBC to a website and was robust and feature rich enough for hobby projects. They was no way I would want to bet the farm on it for a enterprise project.
  • simonw 3y ago
    That's demonstrably not true, and not a useful comparison. A growing number of real-world production apps are running on SQLite these days, precisely because it's so mature, robust and performant at this point - and, unlike Microsoft Access, is very actively maintained.
• chasil 3y ago
  I actually get to see a little more of databases than most so: "SQLite is a sql-based database with a particularly unique feature: the entire database is in a single file." Digital Equipment Corporation (DEC) developed a SQL database known as Rdb for VMS. It could store everything in a single file. Oracle bought it, and maintains it here: https://www.oracle.com/database/technologies/related/rdb.html https://www.oracle.com/database/technologies/related/rdb.htm... SQLite's ATTACH command allows the spanning of files. Be careful how you use it in WAL mode. "SQLite being a file on disk does make connecting from external clients effectively impossible." SQLite does support access over NFS and SMB, unless you are in WAL mode.
• siliconc0w 3y ago
  For side projects or non-prod environments where a single instance is fine, I use sqlite + litestream and it works reasonably well. I've looked into the more complicated setups with LiteFS/Consul/RAFT and at that point I'm not sure you're saving yourself much headache over running your own DB.
• zzo38computer 3y ago
  > SQLite being a file on disk does make connecting from external clients effectively impossible. Not quite impossible (if you design an appropriate VFS), but it doesn't work as well as a real client/server database, since SQLite is not designed for this use. > SQLite does not support enums which means you're forced to use strings. ... The main drawback to this is when it comes to the typings for the client which doesn't allow you to ensure all values of a column are only within a set of specific possible values for the string. In SQLite you can use CHECK in a table definition to require all values of a column within a set of specific possible values. However, I think there are some actual flaws in SQLite, such as: 1. It uses Unicode (but only partially; case-insensitive is only with ASCII). This can make it less efficient than it should be if you are dealing with ASCII only, and makes it difficult (and somewhat inefficient) to deal with non-Unicode text. Although it is possible to store non-Unicode text in a TEXT value or in a BLOB value, and to use CAST everywhere or to override the built-in functions with your own, neither is really ideal, for several reasons (including causing some optimizations to not work, making your code even longer and less efficient, etc). It is also possible to patch SQLite to do this, but then if you upgrade, you must patch that one too. 2. The URI file name mechanism is a bit messy. Using a separate argument for the parameters might be less messy. 3. There is no standard way to define the time zone used for functions that deal with local time. (You can override the function to find the current time by the VFS, but overriding conversion to local time is only possible by use of an undocumented function (which is not guaranteed to stay the same in future versions).) Better (in my opinion) would be to add such a function into the VFS (since the current time function itself is in the VFS anyways, so it should go together). 4. It might have been better to store the journal in the same file as the database. One idea how this might be done: Set the read version to 1 and the write version to 3. If you begin a write transaction, lock the database and then make copy-on-write of any modified pages into free pages, but do not change the references to the free pages in the header (so other programs still believe they are free). To commit a transaction, make a table of the required page linking changes in the file, and then temporarily change the read version to 3, and then change all of the links (so that the pages containing the old data would now be considered free), and then change the read version back to 1 (the write version will still be 3) and then unlock the file. To roll back a transaction, simply unlock the file (which will be done automatically if the program crashes); there is no need to write or delete any file. However, one disadvantage of this method is that the database file is now up to twice as big (depending on how many pages need to be modified for each transaction), and there may be other disadvantages too. 5. You need file names (which is necessary for separate journal files, anyways). However, in my opinion it might be better to pass file descriptors or stream objects. (SQLite and some other libraries annoy me that they do not do such a thing, and require file names.)
• boring_twenties 3y ago
  I want to love sqlite but its lack of support for 64-bit unsigned integers has been frustrating to say the least.
• WuxiFingerHold 3y ago
  It's always much easier to work with fully hosted turnkey-ready solutions like Supabase, Planetscale, Neon, or the like. And you get all of their huge benefits. Latency of some ms are hardly an issue for most of the use cases. So, no, you should probably not be using SQLite.
• PeterZaitsev 3y ago
  SQLite is great, the article is not. The author seems to be compiling bunch of marketing claims and tries to sell it as a best practices. Anyone seend Exabyte sized SQLite databases in production ?
• incomingpain 3y ago
  My typical process of why I don't ultimately use sqlite. Before any DB, you're trying to just do it all in memory. Then we get to storing for persistence and going from memory -> pickle -> disk file is pretty quick; but we know a real DB is needed. So you deploy sqlite3 and it'll work amazingly right up until the dreaded: SQLITE_ERROR: "database table is locked" and now you're not on sqlite3 anymore.
• skybrian 3y ago
  Seems like this assumes you have a local disk, which isn't true for "serverless?"
  • dgb23 3y ago
    Serverless is just someone else's bash script.
  • dist-epoch 3y ago
    There are quite a few extensions allowing SQLite to run over S3.
    • Ensorceled 3y ago
      I'm trying to figure out how S3 qualifies as "on-disk" AND would be faster than PostgreSQL ...
    • maxmcd 3y ago
      I believe they do not generally support concurrent use and would be challenging/impossible to use with serverless environments like aws lambda
  • Cthulhu_ 3y ago
    Serverless doesn't mean hard-drive-less, it just means that you, the person deploying the software, don't have to manage servers, only your application which may or may not access files on the filesystem that may or may not be a local disk.
    • skybrian 3y ago
      Right, but I think it means this won’t be available for that kind of setup until a service provider adds a SQLite feature. For Deno Deploy, they went in a different direction using FoundationDB, even though they use SQLite for Deno itself. (It’s also true that some data centers have machines with no local storage. They use network storage for everything.)
• fourfour3 3y ago
  For an app running on one VPS? Sure, this might make a decent amount of sense. But... for an app running multiple instances, the article suggests a lot of extra complexity. Managing that complexity makes less sense to me than just firing up a managed database server (RDS, or whatever). Even if you're self hosting, I think running a MySQL/Postgres cluster is a lot less complex than the options this article calls out. edit: and more to the point - MySQL / Postgres replication is boring. It's done by a ton of deployments, and isn't going to surprise you, at least at smaller scale.
  • eternityforest 3y ago
    What about fully independent instances, each serving N clients, with shared stuff done as a DHT? Is there a framework for that?
    • anacrolix 3y ago
      Why use a DHT if you control all the nodes?
      • eternityforest 3y ago
        If you had something that needed very easy setup but also scalability, you could do it all as a monolith and not have anything else to set up besides just the one app and your certificates, and they'd just all automatically find each other. I'm not sure how else you could do that besides a DHT without a central database or manually assigning certain pieces of data to specific servers.
  • starcraft2wol 3y ago
    I think the excitement about SQLite comes from LAMP stack fatigue Every Wordpress site could have been built on sqlite just fine, with better security too. But beyond that, you probably want a real RDMS with auth and alter table.
    • zdragnar 3y ago
• codedokode 3y ago
  SQLite is not easy to use with migrations, because it doesn't support many ALTER TABLE options [1]: you need to create a new table instead of modifying a column, for example. Also, foreign keys are ignored by default and you need to explicitly enable them after connecting. Also, column types are not checked and you can easily insert a string into numeric column. Also it doesn't allow you to use multiple application servers. So it can be used only with small, simple sites. [1] https://www.sqlite.org/lang_altertable.html https://www.sqlite.org/lang_altertable.html
  • llimllib 3y ago
    simonw's sqlite-utils can be helpful for executing table changes: https://sqlite-utils.datasette.io/en/stable/python-api.html#transforming-a-table https://sqlite-utils.datasette.io/en/stable/python-api.html#... https://sqlite-utils.datasette.io/en/stable/cli-reference.html#transform https://sqlite-utils.datasette.io/en/stable/cli-reference.ht... > Also it doesn't allow you to use multiple application servers. Not in the same way you use postgres etc, but you can do it with sharding or with LiteFS, but you do have to consider carefully how you scale your app. I'm not _really_ disagreeing with you, but I think you're painting with a bit too broad of a brush.
    • modo_ 3y ago
      Thanks! Came to this thread hoping for something like these. They look useful!
  • yawaramin 3y ago
    > you need to create a new table instead of modifying a column If modifying the column's type, yes. But you don't if you're just renaming, adding, or dropping a column. > column types are not checked and you can easily insert a string into numeric column. Can be fixed with CHECK constraints in the table definitions. > Also it doesn't allow you to use multiple application servers. This is mentioned in OP. > So it can be used only with small, simple sites. It can be used in a large variety of applications, but it's more appropriate for some than for others.
  • simonw 3y ago
• happyweasel 3y ago
  >the entire database is >in a single file Microsoft Access since at least 30 years? MdB file? Accessible with various dB APIs? And wasn't dBASE also the same thing?
  • GnarfGnarf 3y ago
    No, dBASE (Clipper, FoxPro, etc) uses a separate file for each table (.DBF), index (.CDX) and blob (.FPT).
  • deely3 3y ago
    Also Sybase.
• segfaltnh 3y ago
  Saying it's not uncommon for a database to be tens or even hundreds of milliseconds away is ignorant at best. If you care about performance, you're not jumping across the US for every database query. Honestly this is the kind of stuff written to fluff out a social presence and offers nothing to a technical audience.
• Ensorceled 3y ago
  One thing I don't get ... are people really having problems getting PostgreSQL, or even MySQL, up and running? Is this REALLY a dev/devops/ops hurdle for people? Just seems strange that is actually a problem. Don't get me wrong, I use SQLite ... it's just not the database for my company's management web portal.
  • squirtlebonflow 3y ago
    The vast majority of my development time for any project is setting up a server, database, web framework, etc. It's a massive pain in the ass and gets in the way of what I'm actually trying to do.
    • Ensorceled 3y ago
      I've literally put a new django app live on AWS using PostgreSQL in a morning. Now ... it didn't do anything but it was live and you could login, create users etc.
  • yawaramin 3y ago
    Administering an RDBMS server is only part of the problem. It's also mentioned quite clearly there that by having data locality (just disk I/O) you get massive efficiency gains in queries and effectively don't have to worry about the N+1 query problem. Queries run in the order of microseconds, not milliseconds because of network latency.
    • winrid 3y ago
      A DB server won't have to go to disk for every query, sqlite almost always will, or at least reach out from user space to virtual memory, which is still slower than reading from a buffer table. So ya, in process is faster for trivial stuff. But it's not faster when doing heavier workloads that benefit from things being in memory or having a better query planner.
      • simonw 3y ago
• kunley 3y ago
  I think it is just an indirect marketing of some fly.io features ;)
  • tptacek 3y ago
    No, it isn't. If we want to be on the front page, we'll publish a blog post; we literally rate limit ourselves on that. We're just a piece of the stack, and this person happens to use us to make a point about SQLite. LiteFS isn't a Fly.io feature; it's Ben Johnson's open source project, and it runs just fine on GCP and AWS. Try it out!
    • kunley 3y ago
      Ah ok. Yes, indeed I meant "marketing" done by the user, in this case - satisfied user. Btw, I totally understand self rate-limiting thing. Thanks for mentioning the LiteFS origin, Ben Johnson of BoltDB writes good stuff
  • deleted 3y ago
    [deleted]
  • rrdharan 3y ago
    Yep, this and the many other “why aren’t you always using SQLite” articles popping up here every month are classic submarine PR: http://www.paulgraham.com/submarine.html http://www.paulgraham.com/submarine.html Which makes sense given they are a Y Combinator company. See also: https://news.ycombinator.com/item?id=35915789 https://news.ycombinator.com/item?id=35915789
• Cthulhu_ 3y ago
  Doesn't multi-instance replication (the article mentions LiteFS and Turso, I also know about RQLite and DQLite) negate the "one less service" heading?
• deleted 3y ago
  [deleted]
• qwertox 3y ago
  I'm logging traffic data (IP-pairs and packet+byte-count) into a SQLite database on a couple of machines, and what bothers me is that I'm unable to query them whenever I want. So the files end up being rotated whenever I want to inspect one closer. While it's good to have a simple storage solution, it does have its drawbacks. Then again, yesterday I was working a bit with MBTiles, which are SQLite databases with per-z,x,y-protobuf-blocks of vector data, and in that case it does make sense to use SQLite. Or the things browsers use them to store bookmarks and stuff.
  • simonw 3y ago
    What's stopping you from querying them? You could SSH to the server and query them with the sqlite3 CLI tool. You can enable WAL mode if you're having trouble querying them due to locking issues.
• aaviator42 3y ago
  I'm a huge fan of SQLite! My org's apps use it heavily, often via this simple key-value interface built on sqlite: https://github.com/aaviator42/StorX https://github.com/aaviator42/StorX Handles tens of thousands of requests a day very smoothly! :) As an aside, has anyone tried using a RAM-disk as the storage medium for SQLite DB files? We've started experimenting with it lately and results have been promising!
  • mharig 3y ago
    Use tempfs.
    • mharig 3y ago
      Sorry, it's tmpfs.
      • aaviator42 3y ago
        Yes, we are!
  • andreidd 3y ago
    Why not use “:memory:”?
    • aaviator42 3y ago
      Multiple processes cannot access the same :memory: sqlite db, which makes it incompatible for our use cases.
  • hwd 3y ago
    Have you considered https://www.sqlite.org/inmemorydb.html https://www.sqlite.org/inmemorydb.html
    • aaviator42 3y ago
      Yes! Like I mentioned in the other comment, multiple processes cannot access the same :memory: sqlite db, which makes it incompatible for our use cases.
• Aurornis 3y ago
  I think this speaks more to the target audience of the courses he sells: > So, can you use SQLite? For the vast majority of you reading this, the answer is “yes.” Should you use SQLite? I’d say that still for the majority of you reading this, the answer is also “yes.” If you’re making a simple proof of concept app, a side project you just need to get working, or a toy project for learning then SQLite will be fine. If you’re trying to learn how to build maintainable web apps for a business, getting PostgreSQL or MySQL or similar up and running shouldn’t be that big of a hurdle. He also either doesn’t understand the limitations and complexities of using SQLite (migrations, foreign key quirks, other issues mentioned in this thread) or he’s deliberately ignoring them because it would weaken his point. For what it’s worth, we’ve had to un-teach some of this author’s material to junior devs after they read into it a little too literally. He likes to push his way as the “right” way to do things, which can turn into a cargo cult mentality for junior devs buying his courses. He pushes his new “Epic web stack” in the same way.
• fb03 3y ago
  I don't know what SQLite has gotten over a 5 line docker-compose.yml running PG locally for my project. It's ready for scale-up when needed, supports more features and foreign keys without setting a flag manually, proper types etc. I get SQLite for mobile and toy projects but really, a PG set up in docker-compose.yml in your project is the easiest thing to do these days.
  • TehShrike 3y ago
    "no docker" is a huge selling point for me
    • fb03 3y ago
      Can you expand on that? my use case is pretty simple and I haven't been able to find something on it that really bothers me. Also, about PG: I run each project in a different port. Not defaulting to PG's default 5432 makes it able to easily run several projects at once without clashing, just like you would with several SQLite files. I don't really see any drawbacks in using a little docker compose for your local dev set-up.
      • TehShrike 3y ago
        > Can you expand on that? I spent a little time trying to learn docker fundamentals and it was so obtuse and broad that it really scared me off. I like containers in theory, but I'm not huge on the overhead of VMs, and I'm leery of getting such a complex tool involved with my deployments.
        • fb03 3y ago
          I see. Thank you for the reply. For something as simple as having your own little DB side by side to your project, it is really not that complicated. A 5 line docker-compose.yml gets you a fully encapsulated PG instance on a custom port. One per project. I hope you give it a try someday if you get the chance - also, it's a process in a "jails"-like environment, not a VM, not another full blown kernel running - so really the performance overhead is minimal to none - just like if you were running PG "locally" (well, you actually are)
• beoberha 3y ago
  SQLite is an awesome tool, but I wish we’d stop trying to force it into situations it wasn’t made for. It doesn’t belong anywhere near a web app deployment that currently or will span two or more machines. Once you have a “distributed system” and the complexities of a DR scenario, reach for a tool that was built for such. You can’t go wrong with PG/MySQL. You can go very wrong with a VC-hyped replicated file system from a company known for outages.
  • pookha 3y ago
    I've seen people use a few hundred lines of javascript to create full blown distributed systems that are offline and end to end encrypted and backed by sqlite. They only use the server as a glorified caching system to sync to\from. It's esoteric (logical clocks, conflict free replicated data types), but that's a hell of a lot more distributed than a client-server architecture and it's backed entirely with sqlite.
    • beoberha 3y ago
      I mean this is kinda what I’m trying to get at. You need to build all this bespoke infra in order to fit SQLite into the round role where a database with native replication is a far more proven and battle tested solution.
      • pookha 3y ago
        When I say a few hundred lines of javascript (and I'm no fan of javascript) I literally mean a few hundred lines of javascript. No frameworks. No complicated server logic. Just CRDTs and a hybrid logical clock (written in fifty or so lines of JS) and a server running a little message bus...I once heard JLong talk about this and demo'd the capability to a military contractor in an office in old town alexandria and they thought it was hilarious. In just a couple crappy javascript files I was syncing data offline between twenty five different clients regardless of internal system clock speeds (I used a laptop that was running hot and had a fast system clock) or other bullshit. It was magical. And all of that was ran through SQLite. I even used some SQlite PostGIS-like extension during the demo. So yes, sqlite can be distributed in the purest sense of the word.
• Ilasky 3y ago
  I’ve been using Pocketbase[0] on my projects and I can’t recommend it enough. It is built on a SQLite db and has real-time pub/sub capabilities. Its JS SDK is incredibly easy to use and setup for CRUD as well. For side projects and some medium tasks, I’d say SQLite/Pocketbase has been super easy to work with. [0] https://pocketbase.io https://pocketbase.io
• munk-a 3y ago
  Postgres is so incredibly easy to setup and has really low system requirements for simple applications and small quantities of data. SQLite has some really interesting features when it comes to portability - it's a great solution if you want to be able to trivially bundle your application and data state together and distribute it as a binary... but the barrier to running a postgres instance is incredibly low.
  • massysett 3y ago
    For simple applications and small quantities of data Postgres is a lot more complicated than SQLite. Just the requirement of a database server is a hurdle. Factor in that the data is stored across multiple files in some different directory, and you’ve got a lot of headache when you’re just dealing with a simple application and small amount of data.
    • munk-a 3y ago
      There is no requirement for a database server - until your scale requires it it's perfectly reasonable to run postgres on the localhost.
      • massysett 3y ago
        True, but it’s still a separate server process, with access control and interprocess communication requirements. SQLite is just a C library so there’s no separate process to talk to.
• belter 3y ago
  Just a reminder, that if you have a phone...You are already using SQLite...
  • dinosaurdynasty 3y ago
    Or Firefox or Chrome
• masfoobar 3y ago
  There could/can be cases to use SQLite over SQL Server, MySQL, Postgres, etc. It really comes down to reasoning with the problem. For me personally, I have only used SQLite for 2 scenarios. 1) When developing. SQLite is a simple method to getting something together, before building a proper MySQL/SQLServer/Postgres one. 2) Client software. Some client software needs a temporary data store and find SQLite great for this. These are not complicated databases. They barely have about 5 tables. Nothing more than a Queue system than anything, which goes over the wire via Rest API to a Messsaging Queue. Once sent over with a reply of "success" - it is removed from SQLite. It is a nice, simple system. There can be a few other reasons. Other than the above, I don't think I would be confident of SQLite file for a moderately sized database, likely to be used for some web portal or CMS system we created where more than one request could be talking to the database at the same time. This is why you should stick to those central, relational databases. MySQL and Postgres, especially, are not that difficult to install, whether on the same system or its own dedicated server. I hope my comment is not seen as a negative for SQLite. If anything, my attitude is the opposite. I really like the work and effort that has gone into SQLite. A virtual pat-on-the-back by those involved! It is a great approach to having data in 1 file, rather than writing your own.. or having lots of "records" in their own files (in a directory) which I have done.
  • rmbyrro 3y ago
    I think it can be used for most things that have a low concurrency level and don't need real-time replication. There are replication "solutions" for SQLite, but then it stops being simple and you're better off using Postgres.
    • masfoobar 3y ago
      Agreed. Admittedly, I cannot comment on replication options for Sqlite. I have not bothered to even look into this. To me, it already goes beyond the purpose of what makes Sqlite great --- to be a powerful sql in one file. Great for programs that need a storage solution without the reliability of being connected to another machine. In my client programs using Sqlite, it has been designed in such a way that if the Sqlite file is corrupted then it is not the end of the world. We know what didn't get sent to the server so we recreate the file and resend, etc. (However, our Sqlite files have been very reliable with the exception of software updates, but we ensure the sqlite db tables are empty before you can update the client software) Thats why I specified "client software" in my original comment, but I don't see why server-side daemons cannot use it as well. I guess If more than 1 program needs it, though, a combination of CRUD operations, I would recommend something else.
• pjmlp 3y ago
  Sure, if the website is a basic one.
• thelastgallon 3y ago
  Mongo DB Is Web Scale: https://www.youtube.com/watch?v=b2F-DItXtZs https://www.youtube.com/watch?v=b2F-DItXtZs
• throwawaaarrgh 3y ago
  This post would have said "MongoDB" years ago, and soon one will say "Postgres" instead, and then after that whatever's trendy next. Probably CSV files. You should probably be architecting your application to use the data management solution that fits your use case.
• kosolam 3y ago
  I will comment on each point: Zero Latency - irrelevant for 99% of use cases One Less Service - premature optimization - saves almost nothing Multi-instance replication - where is the advantage over say postgres? Database size - irrelevant for 99% of use cases today Development and Testing - no advantage here
• frud 3y ago
  I can't believe that video games still have a "saving data. Please do not turn off your computer" like two-phase commit hasn't been a thing since 1987.
• wackget 3y ago
  Does SQLite have any good RDBMS programs which support it? MySQL has the excellent SQLYog which is like a better version of MySQL Workbench.
  • Austizzle 3y ago
    Lately I've been using HeidiSql which supports postgres, MySQL, sqlite, and others
    • wackget 3y ago
      Thank you. I gave it a try but I can't get along with the non-native window GUI.
• J_Shelby_J 3y ago
  Honest question from someone currently in the middle of implementing my first SQLite deployment for a local data store in a desktop application (and is rather new to software development.) Is SQLite not ergonomic? I’ve previously played with mongodb through work and it was super simple and intuitive. But SQLAlchemy is super verbose and not at all intuitive despite being intellectually easy to grasp. It feels to me like it has decades of cruft built into the syntax and while I can appreciate that it means options it has not at all been nice to work with. I understand the issues with mongodb, but I managed to get it working in 30 minutes. Meanwhile, I’m 3 days into trying to get SQLAlchemy working. It feels to me that perhaps there may be a hole in the market for “GPT” friendly databases. Because SQLAlchemy is so verbose it kills the context window of GPT lmao.
  • malwrar 3y ago
    For local/single-instance apps? Heck yeah, sqlite is a great choice. Just wrap all your queries behind functions in a db.py file and never think about application state until you’re forced to. SQLAlchemy is tempting, but honestly sql is already easy enough that I’ve found I spend far less time just writing plain sql queries. Most important thing is to get your actual application idea functioning quickly. Sqlite is fast to work with and low maintenance (your db is one file), and later if you found you made a mistake (too slow, no support for advanced sql features you need, you want concurrent access across multiple boxes) you can write a program to migrate your data in a single afternoon. Wasting time on more complex solutions early on is a pitfall that has stolen years of project time from me, just avoid misusing dependencies too badly and get your thing working first.
  • simonw 3y ago
    Sounds like your problem is with SQLAlchemy, not with SQLite. My https://sqlite-utils.datasette.io https://sqlite-utils.datasette.io library might be a better fit for you. It's a much thinner abstraction than SQLAlchemy. Or just use sqlite3 from the Python standard library directly.
• miohtama 3y ago
  Why you should not use SQLite: Does not handle concurrent writers
  • starcraft2wol 3y ago
    This doesn’t meant what you think it does. They lock and take turns. You can have hundreds of clients (on the same machine), and it works fine.
  • simonw 3y ago
    In practice I don't think this actually matters. SQLite can apply tens of thousands of small writes a second, so queueing them up works fine for all but the absolute largest scale applications.
    • eatonphil 3y ago
      Without too much difficulty you can get into the 100,000s inserts per second range, and even near 1M inserts per second. https://github.com/eatonphil/databases-intuition#go-mattngo-sqlite3-1 https://github.com/eatonphil/databases-intuition#go-mattngo-...
  • astrostl 3y ago
    Between its speed of operation and https://www.sqlite.org/src/doc/begin-concurrent/doc/begin_concurrent.md https://www.sqlite.org/src/doc/begin-concurrent/doc/begin_co... I'm not sure when or even if this becomes a functional limitation.