23 ms·

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

• tmpfile 4y ago
  I wish sqlite made their terminal interface a bit more robust or emulated psql’s interface. Simple things like \d tablename would be great.
  • srcreigh 4y ago
    .schema tablename
    • tmpfile 4y ago
      The output is apples and oranges tho. Since I was downvoted by someone I'll added a simple example to show the difference between the two interfaces. I shouldn't have assumed anyone here was familiar with the respective representations. Sample data: CREATE TABLE users (user_id serial, name text); CREATE TABLE comments (comment_id serial, user_id int, comment text unique); CREATE VIEW user_comment_view as select u.user_id, u.name, c.comment from users u, comments c where u.user_id = c.user_id; INSERT INTO users VALUES (1, 'Bob'); INSERT INTO users VALUES (2, 'Sally'); SQLITE3 OUTPUT sqlite> .schema CREATE TABLE users (user_id serial, name text); CREATE TABLE comments (comment_id serial, user_id int, comment text unique); CREATE VIEW user_comment_view as select u.user_id, u.name, c.comment from users u, comments c where u.user_id = c.user_id /* user_comment_view(user_id,comment) */; sqlite> .schema users CREATE TABLE users (user_id serial, name text); sqlite> select * from users; 1|Bob 2|Sally POSTGRESQL OUTPUT test=# \d List of relations Schema | Name | Type | Owner --------+-------------------------+----------+---------- public | comments | table | postgres public | comments_comment_id_seq | sequence | postgres public | user_comment_view | view | postgres public | users | table | postgres public | users_user_id_seq | sequence | postgres (5 rows) test=# \d users Table "public.users" Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+---------------------------------------- user_id | integer | | not null | nextval('users_user_id_seq'::regclass) name | text | | | test=# select * from users; user_id | name ---------+------- 1 | Bob 2 | Sally (2 rows) Postgres also supports adding + to commands to get additional extended information, eg, \d+. You can also filter by tables (\dt), filter by views (\dv), filter by functions (\df), etc. It's allows much more natural enumeration of the DB which I wish sqlite had as well.
• posharma 4y ago
  I love sqlite but can't hold myself from saying: another day, another sqlite post on HN :-)
  • BudaDude 4y ago
    HN needs a bingo card with this and rust on it.
    • ZephyrBlu 4y ago
      At least Rustaceans are a bit more pragmatic and don't try to tell you that Rust is the only programming language you will ever need.
      • habitue 4y ago
        But they should.
• onlypositive 4y ago
  > I have run SQLite as a web application database with thousands concurrent writes every second, coming from different HTTP requests, without any delays or issues. Is this with nodejs or something single threaded as the webserver? I would kind of assume you'd run into issues with something like PHP.
  • Nican 4y ago
    Why do you believe that PHP would cause issues that Nodejs would not?
    • paulryanrogers 4y ago
      PHP is usually run multi-process, so may have more parallel activity than a single Node instance running single threaded
      • calt 4y ago
        Your mental model of concurrency is not correct. Node.js uses non-blocking IO to achieve massive amounts of concurrency on a single thread, while one-thread-per-request models such as PHP rely on OS threading to do the exact same thing. If anything, the node.js model is often capable of more concurrency because of the inefficiencies involved in OS threading and context switching. EDIT: of course, event driven PHP exist. I don't know much about the current state of it.
      • Nican 4y ago
        The answer here is non-trivial. I have since long stopped developing with PHP, but PHP has several run modes (And multiple runtimes like HHVM), that may bring different performance and concurrency characteristics. Last I remember, PHP still runs all inside of a single process, so it still all share the same memory space, and it no longer has the overhead of starting a new process with every request. The piece of engineering that made node.js so fast back in the day was Libuv, which allowed for non-blocking IO, greatly reducing the number of system calls/context switches. But I am also going to guess that PHP developers have since caught on to the performance optimizations of non-blocking IO, and integrated the improvements into the runtime. Doing a quick search for "php vs nodejs benchmark" on Google [1], it seems like the performance of Nodejs is comparable to that of HHVM. So as usual, use the right tool for the job. This is more of a problem of using your tooling correctly, more than choosing the correct language. [1] https://yuiltripathee.medium.com/node-js-vs-php-comparison-get-the-job-done-purpose-d3d63351ea8a https://yuiltripathee.medium.com/node-js-vs-php-comparison-g...
• adamkf 4y ago
  >The only time you need to consider a client-server setup is: Where you have multiple physical machines accessing the same database server over a network. In this setup you have a shared database between multiple clients. This caveat covers "most cases". If there's only a single machine, then any data stored is not durable. Additionally, to my knowledge SQLite doesn't have a solution for durability other than asynchronous replication. Arguably, most applications can tolerate this, but I'd rather just use MySQL with semi-sync replication, and not have to think through all of the edge cases about data loss.
  • RcouF1uZ4gsC 4y ago
    https://litestream.io/ https://litestream.io/ does streaming replication to S3 (or similar service). With this, you probably have better data durability than a small database cluster.
    • colobas 4y ago
      Came here to say this
    • itake 4y ago
      even with litestream, how do you do deployments? do you just terminate the process and re-launch it on the same machine?
      • killingtime74 4y ago
        I guess this is an interview level question. 1) Drain connections from your instance. Stop taking new connections and let all existing requests timeout. This could be by removing it from a load-balancer or dns. This ensures your litestream backup is "up-to-date". 2) Bring up the new deployment, it restores by litestream. When restore is complete, register it with the load balancer (if you are using one) or dns. 3) Delete the old instance. Instance can be process, container or machine.
        • itake 4y ago
• deathclassic 4y ago
  I like sqlite as much as the next guy but it's built-in datatypes are limited. Things like arrays, UUIDs, geometry stuff, JSON, etc. Sure you can store more advanced stuff as blobs or text but then you have to mess around with deserializing it in the host language and you lose the ability to query it directly in the db engine.
  • jrk 4y ago
    https://www.sqlite.org/json1.html https://www.sqlite.org/json1.html ?
  • massysett 4y ago
    I agree. For my little applications I've looked at Postgres because it has much richer data types, but I can't justify the huge complexity increase of Postgres. So SQLite it is.
    • gerdesj 4y ago
      There are some right old noddies around here! You (masstsett) expressed a preference for something with some working shown and ended up in DV land. That's not fair on many levels and reflects harsher on the casual readership hereabouts than yourself. Your comment is probably rated stellar by the time I hit enter ...
  • aeturnum 4y ago
    That's true - but I think it goes back to "you will need." It's nice to query these things in the DB, but for most users you can just load everything based on associations and sort it out in memory. It's less efficient, but most of the time you will be ok.
    • deathclassic 4y ago
      It's ok until you have to deal with loading a bunch of point cloud or geometry data based on associations and sort it out in memory. Then PostGRES becomes your friend.
• kris-nova 4y ago
  SQLite — works 80% of the time — every time.
  • ZephyrBlu 4y ago
    Excuse me, are you disparaging our lord and saviour SQLite? I'll have you know that SQLite works perfectly for all use cases. If you use it in production you don't even need SLAs because it literally works 100% of the time.
  • deleted 4y ago
    [deleted]
• ZephyrBlu 4y ago
  From a technical perspective, almost every tool is more than what you need. There is a lot of mature software that does amazing things. Whether the tool fits with your architecture and specific use case is much more important than whether it does the job. You can make most tools work for most use cases, but it might not be a natural fit. For example, an in-memory database is probably not conducive with a serverless environment and you would prefer to either host your own DB server or use a serverless DB. Or perhaps there are specific Postgres plugins that enable your use case, or a specific Postgres feature like n-gram search (I don't know if SQLite supports that), etc. Technical maximalism ("it does all the things!") is great for marketing, but a poor way to choose the appropriate technology for your application.
• margorczynski 4y ago
  Yeah, it works when it works. Just that in many cases you'll run into scenarios with it when it completely doesn't or is missing something crucial and we'll get another prodigal son story about going back to Postgres.
  • chillfox 4y ago
    Having learnt SQLite before PostgreSQL I get that experience every so often with PostgreSQL... Both have got nice features that the other does not and what you got used to is going to determine what you miss when picking up the other.
  • srcreigh 4y ago
    Such as?
    • aembleton 4y ago
      Strong typing and exclusion constraints are two off the top of my head.
      • srcreigh 4y ago
        SQLite does have strict typing and check constraints, I suspect the R*Tree module with check constraints would provide rectangular exclusion, though not circles. Have any applications you’ve build needed both of these features?
• jxf 4y ago
  > The only time you need to consider a client-server setup is: Where you have multiple physical machines accessing the same database server over a network. In this setup you have a shared database between multiple clients. Am I misunderstanding this or is this not the vast, vast majority of all cases?
  • chillfox 4y ago
    Most of the time people separate the app and database into two different VMs that the infrastructure team then runs on the same box. edit: This is done not because of any considered technical reasons, but because that's how one learned to deploy apps.
    • crazygringo 4y ago
      It's because the very first step in scaling will usually be separate machines for webserver and database. And it costs almost nothing to write it that way from the start, but it's a pain to separate them out later. I'd call that a considered technical reason.
      • chillfox 4y ago
        And so it is, but I have never gotten that as an answer when asking. edit: I feel like I should be more specific here. I would only call it a considered technical reason if it was actually considered. The fact that it is possible to come up with good reasons is not relevant if no thought went into it at the time of design/development.
    • VintageCool 4y ago
      If you are running your webserver and your database on the same box, how are you big enough to have an infrastructure team?
      • chillfox 4y ago
        Most organisations run a lot of server applications and most of them don't use much resources considering the size of servers these days.
• irskep 4y ago
  This sentiment pops up regularly on HN, and I've seen at least one article per month for the past few months, but the trouble is, none of them seem to help you actually deploy it. They assume you're comfortable spinning up public web servers. If you want to use a PaaS to deploy an app, because you don't want to spend your time learning to be a sysadmin, then all the tutorials are going to put you on the Postgres path, because that's what's supported. (Of course, you'll then end up paying $15+/mo for Postgres, which is hilarious for most hobby projects storing 50MB of data.) But in reality, you could just scale vertically on one machine and be completely fine. No need for "distributed" anything, in theory. I took a shot at productionizing SQLite here as an experiment: https://cheapo.onrender.com/ https://cheapo.onrender.com/ But I'm not sure I did it right, because I don't have much experience working below the level of a PaaS. I'm an application developer and I do not want to become a release engineer. I resent even having to learn Docker. :-) Anyway, if somebody wants to nitpick my Flask+SQLite deployment, I'd really appreciate it, because it seems really silly that people have to keep writing this stuff from scratch when 90% of hobby sites have the exact same needs. And the Fly.io/Render configs would apply just as well to Node, Ruby, etc. https://github.com/irskep/cheapo_website https://github.com/irskep/cheapo_website Edit: Somebody got mad at the Docker bit. I tossed it in for effect, but I promise you I don't hate learning new skills. I wish people would recognize that weekend coding projects should be fun, and sometimes that means avoiding certain kinds of things that a person experiences as difficult or frustrating. Arguing on the internet sucks, who knew?
  • canadiantim 4y ago
    Note with Neon now you don't need to spend $15+/mo for Postgres because they separate compute from storage. So compute can scale down to 0 and storage is cheap.
    • hamandcheese 4y ago
      Neon only recently entered public preview and I am unable to find any pricing information.
      • 4y ago
• bravura 4y ago
  I would love to use SQLite for all my Django webapps that have only several simultaneous users, but this article suggests there are too many footguns for me to be able to do that. Is there a "using SQLite for a multi-threaded webapp for dummies" package that does all the config I need so I can just drop it in and go and not tune anything? Paging fly.io founders etc! If I have a persistent volume can my fly.io apps use SQLite? What are other good micro-hosting options?
  • cldellow 4y ago
    IMO, the config you need is: 1) When you open the database: pragma journal_mode = wal; pragma synchronous = normal; 2) When you want to do a transaction that does writes, use `BEGIN IMMEDIATE`, not `BEGIN`. 3) Don't have long-running transactions. 4) Have some process to do backups. (3) might be a big ask for some systems. Long-running transactions should be avoided even in systems like Postgres, but on a SQLite system with writers, they're the difference between an amazing experience and a garbage one. I'm hopeful that Fly can eventually make (4) painless by having super-easy out-of-the-box litestream and S3 backups. Until then, roll your own cron scripts or what-have-you.
    • bravura 4y ago
      Thank you for the thoughtful response. I was looking at https://github.com/irskep/cheapo_website https://github.com/irskep/cheapo_website from commenter irskep above, and they make a nice point that render.com has automatic daily backups, solving 4) However, in another comment they mention "You can't(?) run migrations from another process" and that "people don't talk about the completely ordinary need to run migrations on a database". I guess this is also the piece that I'm missing. How do I run migrations? Do I deploy a new version with the migration and temporarily take down the server? I'm glad to do that. I guess I'm also walking through this because---as I said---I'd love just to switch to SQLite but I'm still not sure how many simple non-esoteric gotchas will pop up.
      • cldellow 4y ago
        You can run migrations from another process. Migrations are just writes, and SQLite supports writes from multiple processes. A single transaction that does a very large write will likely impact the reader -- the reader will be blocked while the write finishes. I use SQLite in a web scraper on my laptop. The scraper runs as 16 processes hammering the database, doing about 5,000 write transactions/sec. Occasionally, simple SELECT queries experience high latency (what would be a 1ms query takes 100-200ms), because they're blocked while the WAL gets checkpointed into the main database. If you have much lower volume of writes, the checkpoint is smaller and so completes much faster, and so the worst case latency is much better. Since crawling is a non-interactive process, I don't care about the worst case latency. If I was writing a website, I'd feel differently--but most websites won't do 5,000 writes/second.
• xwdv 4y ago
  Why learn SQLite when you could just learn Postgres and have a database that is virtually guaranteed to be enough in almost all cases?
  • Gigachad 4y ago
    HN users pride themselves on finding the least capable tool for the job that only just works for the task but no more. It’s not about logic or practicality. It’s that they feel some kind of mental pain using Postgres as it is too “bloated”.
    • doodlesdev 4y ago
      To be fair, if you were to consider yourself an engineer (which I imagine many of HNers would) that's essentially your whole job, overall what you want is to get the requirements fulfilled with the least complexity, cost, time, etc. If deployment difficulty or hardware usage is a consideration in the requirements then it makes sense to try and use a lighter-weight "serverless" database (SQLite doesn't use a client-server model, so it's serverless, got it??? I'll see myself out).
      • throwawaylinux 4y ago
        "Anybody can organize their data, but it takes an engineer to barely organize their data."
    • russelg 4y ago
      Or maybe its because SQLite is just... easier to deploy? Cheaper? There are many reasons to choose it over a "fatter" solution.
      • somsak2 4y ago
        definitely not easier to deploy, at least if you're talking about how most people build software these days (what sqlite managed service are you familiar with? for mysql or postgres there's hundreds of companies offering this)
• erulabs 4y ago
  See I’ve been using Vitess on Kubernetes for even personal projects and I gotta say I love that I can run, for 10 bucks a month on Linode, the same tools that I know by experience I can scale to a multi-billion dollar valuation worth of customers. Heck I even run it in development on my laptop thanks to Skaffold. Sure it’s all insane overkill - but I use Linux for the same reasons - I want one API that I can use everywhere, from my toaster to my spaceship, from hobby to enterprise. The simplicity is not the API. The simplicity is having one API.
  • ospider 4y ago
    AFAIK, you can only buy 2 smallest nodes with $10 on Linode, how do you create a k8s cluster with that?
  • srcreigh 4y ago
    You also run a company which offers kubernetes self hosting as a business, which is quite the bias. Anyways, the question isn't whether client-server databases scale, it's whether SQLite doesn't scale. What were the database size, resource constraints, architecture for the multi-billion $ company? How did this scale over tiem? Do you have any reason to believe that a SQLite architecture couldn't scale to support the service offerings you saw?
    • erulabs 4y ago
      If you’re exceptionally careful and skilled about writing smart queries, SQLite can for sure scale to the point you can afford to resign anything you want. The idea behind vitess and sharded MySQL (and for that matter kubernetes too) is that you can move fast and not make a terrible mess of things. I can split off one poorly designed table with Vitess - with SQLite that would be an application redesign. But in general I compare the intended scale to successful startups I’ve been at - where several TB of data are being read by hundreds of thousands of users per second. Typically this requires a fleet of the largest instance types most cloud providers offer. Anyways - you’re not wrong and neither is the author - but if I’m going to choose one tool - I’d rather it work for all intentions and be a bit more complex than the other way around.
• thedudeabides5 4y ago
  We should teach SQL in high school
  • RajT88 4y ago
    And scripting. Most white collar jobs these days involve software, so most white collar jobs probably would benefit from being able to script.
    • msm_ 4y ago
      I partially agree, but then I remember my high school IT lessons, where people in my class (our profile was math and IT, mind you) struggled with excel and very basic programming. Scripting may sound trivial for people reading HN, but certainly is not for everyone. Not to mention that to really benefit from scripting you need programs that you can actually execute. As far as I know Windows (which most people use) is not very friendly in that regard. Powershell improves things a bit, but I'm pretty sure you can't just manipulate .xlsx files with a simple shell script on Windows, and this is one of the lowest hanging fruits I could imagine for white collar workers.
      • RajT88 4y ago
        Powershell and whatever else can that instantiate COM objects can edit excel files. Windows is a lot more friendly than you realize. Probably powershell is a lot more powerful than you realize. I'll also make the point that people who are doing OK in excel probably can model things in their head well enough to get into scripting. Powershell also has a one-liner "export to *.csv" cmdlet, which is pretty amazingly handy.
• hammock 4y ago
  I used to use SQL Server on a PC to deal with tables over 1 million rows. I'm on a Mac now... can I use SQL Lite? What is my best option? I am not a coder, I just know enough how to query SQL databases.
  • dav43 4y ago
    A Million rows will be easy.
  • jll29 4y ago
    The Mac OS download is also available from https://www.sqlite.org/download.html https://www.sqlite.org/download.html - and in any case SQLite is a single-file C program, so you could easily compile your own (but no need for the Mac).
  • squinteyedrogue 4y ago
    Check out: https://sqlitebrowser.org/ https://sqlitebrowser.org/ You may also need the SQLite command-line tools from the SQLite website; https://www.sqlite.org/download.html https://www.sqlite.org/download.html Are you coming from using SQL Server Management Studio on a PC? If yes, you may be better off using something like PostgreSQL on Mac OS X, and a PostgreSQL GUI browser tool, because it may be harder to use SQLite in this situation as you don't have the benefit of using a computer language to make up for the datatypes that SQLite does not have. For example SQLite does not have a DateTime datatype. If you need to do a lot of date/time field manipulation in SQLite, this can be made easier when using a programming language like C#, because you can use a SQLite integer datatype to store the DateTime data, and then convert the integer to a C# DateTime datatype and do the DateTime manipulation in C#. But if you aren't a coder, then this option isn't available to you, and you will have to have other ways to manipulate DateTime data in SQLite like using the string datatype to store the date time values and then use corresponding SQL queries to handle the "DateTime stored as a string" situation. (Maybe I've over-explained here. Sorry) Here a PostgreSQL Mac OS X GUI browser download page: https://www.pgadmin.org/download/pgadmin-4-macos/ https://www.pgadmin.org/download/pgadmin-4-macos/
• vouaobrasil 4y ago
  Except for people running Wordpress on shared webservers. Then MySQL is the only database you can ever use.
• user3939382 4y ago
  I’ve been learning the idiosyncrasies of MySQL since 2003, I doubt whatever benefit SQLite has outweighs 20 years of experience.
• xenator 4y ago
  A spoon is the only instrument you will ever need in most cases.
• ripdog 4y ago
  I have wondered why Synapse, the most feature-complete Matrix homeserver, so vehemently recommends against use of SQLite as it's backing db. They say that the performance is insufficient and it's only appropiate for testing purposes. That would make sense if you assume that Synapse is only going to be used in instances with hundreds/thousands of users, but plenty of people host their own instances for themselves only. Surely SQLite would be plenty for single-user instances, or family instances?
  • Arathorn 4y ago
    The problem is that even a single user matrix server can be very resource intensive if that user joins big rooms with thousands of users spread over thousands of servers. Synapse is very database heavy, so the parallelism in Postgres helps a lot - plus some of the hot DB paths have special cased queries for Postgres to use some of its more obscure features that Sqlite lacks. Finally, we don’t dogfood or optimise Synapse with Sqlite, so there’s a risk of perf regressions.
    • ripdog 4y ago
      Thank you, Matthew! This would be fantastic information to include in the Synapse documentation, for those of us being seduced by the operational simplicity of SQLite, and posts like this. :)
      • irskep 4y ago
        Adding my voice to this - I was actually looking at Synapse a few days ago and was wondering why SQLite was not recommended. This would have answered a question I didn't know the answer to until just now.
    • srcreigh 4y ago
      Which obscure Postgres optimization features does SQLite lack? Can you share any extra info about the table layouts or queries which are slower in SQLite vs Postgres? In particular which postgres-specific optimizations have been made?
  • 4y ago
• revskill 4y ago
  In memory SQlite for unit testing is about 45 miles/h, which is the fastest db engine i've used, like a cheetah.
• jameshart 4y ago
  Back in 1999 or so, before SQLite was a thing, I used to throw little ASP websites together with an MS access .mdb file as the backend connected up through ODBC. It was neat and quick and easy to get running right on your regular windows desktop. By the criteria of this article, that was apparently the only database I ever needed. It could handle the multiple reads and occasional write of a small scale website. Backing it up consisted of copying the file. It was accessed through a simple standard library (ODBC) and supported SQL. Since that was the only database we needed, what does SQLite bring to the table?
• ReflectedImage 4y ago
  Might as well be using MongoDB if you are going to use SQLite in prod. https://youtube.com/watch?v=b2F-DItXtZs https://youtube.com/watch?v=b2F-DItXtZs
• mythz 4y ago
  Also worth mentioning Fly.io's work on LiteStream [1] and LiteFS [2] giving SQLite important S3 DR/reliability & multi-node replication and scalability - opening SQLite up to even more use-cases. We're making use of this ourselves in https://blazordiffusion.com https://blazordiffusion.com which runs entirely on SQLite, using Litestream to replicate it to Cloudflare's R2 object storage which is running on a single Hetzner US Cloud VM at €13 /mo. As we believe SQLite + Litestream is a very cost effective solution that can support a large number of App's data requirements we've added first-class support to add SQLite + Litestream support in our .NET Project templates [3] which uses GitHub Actions to run Docker compose App deployments along with setting up Litestream replication to AWS S3, Azure Blob Storage and SFTP in a sidecar container that also includes support running DB Migrations on Server with Rollback on failure. If anyone's looking to do something similar, the GitHub Actions + Docker compose configuration that enable this are being maintained at https://github.com/ServiceStack/mix/tree/master/actions https://github.com/ServiceStack/mix/tree/master/actions [1] https://litestream.io https://litestream.io [2] https://fly.io/blog/introducing-litefs/ https://fly.io/blog/introducing-litefs/ [3] https://docs.servicestack.net/ormlite/litestream https://docs.servicestack.net/ormlite/litestream
• bob1029 4y ago
  SQLite does it all if you look closely enough. Even for performance it can turn out to be the best option. If you dare combine (properly-configured) SQLite with a local NVMe disk, you will find yourself well beyond what many hosted solutions can provide (clustered or otherwise). To be clear - the biggest reason for this is the incredible latency reduction, not raw IO bandwidth or disk IOPS (although this helps massively too). Millions of transactions per second doesn't mean much when there exist no dependencies between them. The figure I am more concerned with is serial transactions per second. 1 logical thread blocking on every command. SQLite is the only database engine I have ever used that can reliably satisfy queries in timeframes that are more conveniently measured in microseconds rather than milliseconds.
  • jay_kyburz 4y ago
    I really wish I could get reliable fast internet here at home. I would be perfectly happy to be serving my sites from a computer under my desk.
• sammy2255 4y ago
  What if I want things to connect to it?
• lerax 4y ago
  saying the obvious for the new comers, sometimes is really necessary in that generation of master of overengineers and not only that, but with pretty useless and unecessary abstractions
  • hummus_bae 4y ago
    What the author says is true, but the implementation in the linked sqlite isn't. The cache is implementation defined. The locking is implementation defined. The index does not work on foreign keys. The planner isn't thread safe. The only reasons for 90% of use cases to use sqlite are that it can be embedded and it has an sql parser. In a major application written by a company with an actual software engineering department, you would use Postgres or MySQL.
• irjustin 4y ago
  > If your application software runs on the same physical machine as the database, which is what most small to medium sized web applications does, then you probably only need SQLite. That's the adoption problem. It has a hard ceiling. Besides embedded, most engineers use a different DB professionally - dare I say pg or mysql? And because of that they'll reach for the tool they already know. Sure, I get the argument that SQLLite fits "more exactly" for many many applications because the sheer number that never move off of one machine, but it has this hard ceiling of "what if I need more" and well "I could just use this other tool that goes the full distance, in case one day... oh and I already use it at work." That's why SQLLite excels for embedded applications. It fits perfectly. There is no "what if" and the performance is astounding esp in low power.
  • srcreigh 4y ago
    The question is what kind of "more" is needed. Out of disk space or hitting CPU/mem limits? Splitting off a 2nd service which handles isolated functionality could help, migrating to larger storage could help, migrating some data to cold/external storage could help, eliminating low value high storage cost features could help. The only "more" which pushes a move to client-server DB is a CPU- or memory-limited, not easily sharded, persistent service. But to have such a problem is highly uncommon. Usually optimization will solve such problems easily. I've seen Python services which use 1GB RAM/process, each process can handle 1 concurrent request, and there's only 20GB of RAM per instance. The solution there is to use one of many sane async frameworks to handle more than 1 concurrent request/process. Some problems, such as latency of DB queries/N+1s and the query complexity which ensues, will not even arise in the first place if you were using SQLite.
• bikamonki 4y ago
  I have similar arguments for "Firebase is the only database you will ever need in most cases" for web apps, be it that you need real-time capabilities or not. I can also confidently say: a static HTML landing page is the only website you will ever need in most cases. I suffer every time I see a one-page site, hardly ever updated, built with Wordpress. Why hire an 18-wheeler to deliver a pizza?
• deleted 4y ago
  [deleted]
• joshspankit 4y ago
  I’ve argued this before and I’ll argue it here now: Modern computers are fast enough that in many cases “the only database you will ever need” can be files on the filesystem. For example “1 row = 1 file”. It brings additional benefits as well: for low-write applications you can use git to get a history (+transactions if you store them in the log), backups are super easy, replication is trivial. For higher-write applications it gets more complex but you can still plan and implement most of the traditional DB scaling techniques (and even implement them one at a time as you go grow). Computers are “stupid fast” now that we’ve gotten off platters.
  • EGreg 4y ago
    Transactions, really? How do you lock rows, how do you have relations, how do you do joins? In fact, ext3 can only handle about 50,000 files in one directory. So you'll have to split up your "primary key" into letters like abc/def/foo like we do
    • joshspankit 4y ago
      The bad but valid answer to locking rows and doing relations is that you write the logic in to your application. The better answer is of course that if you’re doing specific types of complex things a DB is a better fit. Honestly splitting in to nested subfolders is not big deal anymore, it’s a single function you can write even if you’re having a “.10X” day.
    • RedShift1 4y ago
      Postfix turning eyes away
  • Apocryphon 4y ago
    http://howfuckedismydatabase.com/letters/ http://howfuckedismydatabase.com/letters/ > Name: Edward I'm using grep and find and the Unix file system > Name: Toni Why do you need a database? I'm using CSV files! > Name: Carlos I came after a long journey to your website to seek enlightenment : am I fucked? And it didn't answer that question clearly. Let me re-phrase it: I first search an LDAP directory, then I remotely execute a quota status, after that I query a PostgreSQL database, and then I generate a .txt file with the timestamp as its name or a .csv file with an hash as its name, and then I look up the files from a web page, load it all to a multi-dimensional array, and generate a nice report, re-loading the entire file every time the user wants to, say, sort by another field. Something this complex can't ever possibly fuck up, can it?
• umvi 4y ago
  Everytime I try to use SQLite I run into db locking issues where I seemingly have to try to run my query in a retry loop. Am I doing something wrong or does SQLite just not play nice in multi threaded contexts?
  • napsterbr 4y ago
    SQLite is a great but complex software. In order to properly use it, you do need to read the documentations, guides and resources available at the official website. If you don't, you will shoot yourself in the foot. In that sense, I have the impression SQLite is different from other database software. You can usually get by with Postgres or MySQL (after they are set up) without looking at their docs. I spent several hours reading the SQLite docs. It wasn't wasted time: actually learning SQLite made me a better professional. But for those thinking of using it on their side (or main) project, definitely understand how it works and what are the trade-offs involved. Now, to answer your question specifically: it depends on a few factors. You can have concurrent readers (wal). With newer developments (wal2 + begin concurrent), you can also have concurrent writes as long as they happen at different pages. If you are blindly doing multi-threaded connections without understanding the implications, you also risk corrupting the database entirely.
    • RedShift1 4y ago
      If you are blindly doing multi-threaded connections without understanding the implications, you also risk corrupting the database entirely. This is false? SQLite has always been able to handle multiple processes and threads, reading and writing to the same database?
      • napsterbr 4y ago
        > SQLite has always been able to handle multiple processes and threads This is true for the vast majority of cases, although there's at least one documented scenario where using modern SQLite coupled with an old threading implementation (the one that predates NPTL in Linux) may lead to database corruption. I guess that's why I said "blindly": as a way to incentivize OP to look this up. It's not that the SQLite database is fragile, but rather that it expects to be used in a certain way, and if you don't, you risk corrupting it.
• 0xbadcafebee 4y ago
  I agree. Most uses of databases definitely don't need to grow larger than, say, a single filesystem, or a single application, or a single host, or a single network, or a single geographical region, or a single customer, or a single organization, or a single global network of customers in organizations in regions on networks on hosts on applications on filesystems. There could not be any features of any other database that SQLite might not have, or that an application might need, or want. We definitely should not, like, read a book on databases, or read the manual of another database, or something else crazy like that. There's no reason we might learn about other databases. They are just "shiny stuff", meaning, there's something going on with them that I can't see, because of all the glare. Honestly, the existence of all those other databases, and database models, and the billions of dollars spent on them, is a fluke, probably. It's unlikely you will ever in your life see or work on an application that needs a database other than SQLite. Because the only applications you will ever work on won't ever run on more than one virtual host, or be used by more than one application. And definitely you will probably not need a feature that isn't in SQLite. SQLite is very fast, it is very simple, it is very well written, and it has a lot of tests. Therefore we can conclude that you should never look at or learn about another database, because considering the previously states information, we know that no other database could possibly be desired or needed. I don't know a lot about databases. And, granted, I only just found out about SQLite. But I am quite sure I am correct that SQLite is the only one you'll ever need in most cases.
  • crazygringo 4y ago
    I'm dying. I "agree" in the same way you do, but what a way with words! But seriously, those points exactly.
  • srcreigh 4y ago
    I bet most non-FAANG programmers have indeed never worked on an application which could not be built on a single host with SQLite. And SQLite is in fact more fully featured in some ways than some client-server DBs (when will Postgres add support, even via a plugin, for primary indexes?) I agree with you that it is important to realize where a client-server DB may be needed, but the it really almost never is.
• OrvalWintermute 4y ago
  > In contrast to many other database management systems, SQLite is not a client-server database engine, but you actually very rarely need that. If your application software runs on the same physical machine as the database, which is what most small to medium sized web applications does, then you probably only need SQLite. Disagree. If you think about it from an attack surface perspective, there are numerous advantages to isolating the database. There are performance, availability, sharding, and columnar options out there also that may better meet the use-case (just to name a few). I have ran Postgres on endpoints when developing with performance akin to SQLite. Further, there are numerous ways in which to increase performance, availability, or to pursue some of the more customized versions of Postgres depending on use-case. One of the times I used Postgres was with Oracle DBAs, and they found the transition pretty simple. Various customizations / extensions / versions of PG There are security versions e.g. https://www.crunchydata.com/products/hardened-postgres https://www.crunchydata.com/products/hardened-postgres Columnar / high performance Parallelized extensions e.g. https://www.citusdata.com/product https://www.citusdata.com/product General Purpose / Oracle transitions e.g. https://www.citusdata.com/product https://www.citusdata.com/product Yandex even has an embedded Postgres https://github.com/yandex-qatools/postgresql-embedded https://github.com/yandex-qatools/postgresql-embedded If you'd like to see a full list of features see https://www.postgresql.org/about/featurematrix/ https://www.postgresql.org/about/featurematrix/ More than this though, PG has a really excellent community with a large amount of talented folks, available both individually and through OSS oriented companies https://www.postgresql.org/support/professional_support/ https://www.postgresql.org/support/professional_support/ and willing to help out on Libera https://www.postgresql.org/about/news/migration-of-postgresql-irc-channels-2216/ https://www.postgresql.org/about/news/migration-of-postgresq...
  • infamia 4y ago
    > If you think about it from an attack surface perspective, there are numerous advantages to isolating the database. The attack surface on PG or MySQL is a lot larger and there are a lot more moving parts than SQLite (which is just a file). Notably, there is no service exposed to the network that someone can attack, which is a huge attack vector with lots of different types of vulnerabilities that don't exist in SQLite.
• osigurdson 4y ago
  SQLite is great if you just have one process accessing the db, other wise it is a dumb choice.
• nikeee 4y ago
  I really like SQLite and I use it a lot. There are only two things that are missing to make it near-perfect: - A type for Instants / time handling - being strict with types. No inserts of ints into a string column
  • srcreigh 4y ago
    SQLite added strict mode recently, check it out. Agreed time handling is sub par - could use builtin date formatting from epoch to ISO which would fix all the problems IMO.
    • nikeee 4y ago
      I know about STRICT tables [0], but they still follow the quirky coercion rules. The reasoning seems to be that other DBMs have a similar behaviour. However, I want _errors_ if I insert '123' into an INT column, so it's easier to find problems in my code. [0]: https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html
      • srcreigh 4y ago
        The quirky coercion rules that PG, MySQL, SQL server and oracle also all follow? Let’s be clear, if this is a problem it’s a problem with all SQL DBs, not just SQLite. I’m curious why ‘123’ in an INT column is so bad? I suspect the conversion rules are in place because they shouldnt ever cause logical errors. I personally appreciate using created_at < ‘2021-05-23’ in Postgres queries. The query would only be more verbose if I had to explicitly construct a date object for it.
        • nikeee 4y ago
          The reason for that being not that good basically has the same reason as with coercion rules in weakly typed languages like JavaScript. If I'm passing a string to an int column, there is most likely an issue in my application code. If there currently is none, there might will be. For example, I might have forgotten to parse the string properly in my application code. If I'm doing '10' * 2 in JS, it returns 20. If I later change it to '10' + 10, it will be '1010'. Say I then save the result of that compilation in the DB. Raising on '1010' would have prevented me from persisting the error and gave me an opportunity to investigate the situation. Without an error, there will be a much harder debugging session. These coercions were popular back in the 90s/00s, which is why I think most DBMs have them. At least that's why JS has it.
• SnowHill9902 4y ago
  The only database you’ll need 60% of the time every time.
  • hajile 4y ago
    Everyone thinks they have big data and 99.99% of the time, their entire DB could be served from a single 100GB SQLite file on a single SSD on a random dev's laptop with better performance than the expensive AWS deployment they've rented. They think they have super-concurrent stuff and locks are important, but modern machines are so fast that data can be added faster than their users can input data. People don't realize just how big 1MB is if it's not multimedia. If you had 100,000 users consistently writing 1kb to your DB every single day, it would still take a 10,000 days or over 27 YEARS to fill a 1TB harddrive. Most companies have 1,000 users per day updating mostly existing data or adding small snippets here and there. That 1TB drive would die of old age long before your average company could come close to filling even 10% of the capacity.
• zzzeek 4y ago
  and if you need to migrate your database schema in any non-trivial way.....well then you're on your own. for anything beyond adding a column to a table, you'll have to copy the whole table to a new one with the structure you want, drop the old table, then rename your new table, carrying along all the foreign key constraints and other constraints while you do so. Or use a tool which does this (I write one such tool and it's not fun to maintain). if SQLite allowed for custom commands, at least there could be ALTER commands that run this process behind the scenes, which the SQLite developers wouldn't have to maintain.
  • srcreigh 4y ago
    SQLite has commands to rename columns (this is somewhat new). Which other migration is not supported without new table/copy/drop old table process? Also, MySQL can't run a migration on a FK-constrant table without downtime. To do this you need an online schema migration tool which generally requires the absence of foreign keys.
    • zzzeek 4y ago
      > SQLite has commands to rename columns (this is somewhat new). Which other migration is not supported without new table/copy/drop old table process? adding or dropping any constraints, including nullability, foreign keys, check constraints, changing the structure of the primary key, etc. changing a type also, even given SQLite's squishy typing model. pretty much anything is not allowed except adding and renaming columns. > Also, MySQL can't run a migration on a FK-constrant table without downtime. That's not an issue for the overwhelming vast majority of MySQL databases in production, which are to be clear not running or aspiring to run at Facebook / OLTP-level scales. A table with a few million rows can be migrated in seconds, the table gets locked for a few seconds, everything keeps running after a brief pause. This is not a problem for the "most cases" use case the article refers towards. > To do this you need an online schema migration tool which generally requires the absence of foreign keys. if you are running at Facebook / OLTP scales or aspiring to be, then yes. Otherwise, not usually. The article here is referring to SQLite being used for "most cases", not just "running at Facebook / OLTP -level scales". Running at Facebook / OLTP-level scales is still one of the places where you most certainly would *not* be using SQLite for your primary database.
• Dig1t 4y ago
  This post inspired me to switch one of my projects to SQLite, but then I remembered that it uses PostGIS, and the ORM I use does not support the SQLite GIS types :( I wish SQLite had GIS support built in.
• ilrwbwrkhv 4y ago
  Pocketbase which uses sqlite is a total game changer. I am slowly moving away from Supabase to Pocketbase.
  • testTED 4y ago
    Where are you hosting it?
    • ilrwbwrkhv 4y ago
      Hetzner Dedicated Physical Servers
• dang 4y ago
  Related: SQLite the only database you will ever need in most cases - https://news.ycombinator.com/item?id=26816954 https://news.ycombinator.com/item?id=26816954 - April 2021 (370 comments)
• albert_e 4y ago
  AWS has a habit of taking a open source project and creating a "managed service" offering of it. Is it possible to offer SQLite as a managed / serverless offering? A light weight and cheap relational data store that we just consumer using an API
  • srcreigh 4y ago
    Nope. SQLite is already available in the same process and using the same file system as your server. In some cases (ex Python) without adding any new dependencies. It's downright silly to try to think of a way to make it easier. Here's your easy cheap and lightweight relational datastore API: import sqlite3 conn = sqlite3.connect("db.sqlite3") cur = conn.cursor() cur.execute("SELECT * FROM products LIMIT 25") print(cur.fetchall())
    • albert_e 4y ago
      Hmm. We have a bunch of serverless functions that are generating records that fit a relational data structure. (AWS Lambda) We would like to write these records to a persistent relational data store so another downstream process can read it. Amazon RDS seems like an overkill. Amazon DynamoDB (NoSQL) seems like a misfit because we want to execute relational queries and some joins against this data. We could write to CSV on S3 and query using Athena/Presto. Seems clunky and slow. Am I missing any obvious solution here OR there is a space here for a service that offers a lightweight relational datastore that multiple loosely coupled readers and writers can use.
      • srcreigh 4y ago
        Use RDS. SQLite only works if you have one persistent machine with a disk and file system. The problem with AWS lambda and SQLite are 1) network file systems often don’t support the APIs that SQLite needs to implement concurrent access; SQLite is not a database server 2) local storage for AWS lambda is ephemeral, your DB will be deleted, and if there’s two lambdas, they won’t be using the same DB. Just use one EC2 instance and SQLite, or lambda with RDS.
  • 4y ago
• leke 4y ago
  As someone who uses free hosting for personal projects, I thought I couldn't use SQLite because it wasn't advertised as being available. I'm beginning to find this isn't true.
• freilanzer 4y ago
  I'm using DuckDB atm instead of SQLite: https://duckdb.org/ https://duckdb.org/ For data science purposes, it seems to be quite interesting.
• sireat 4y ago
  I am ashamed to admit I've run into a case where SQlite was the wrong choice. I have a cluster of 20 Windows 7,8,10,11 machines spread across multiple sites that I wanted to run some log analytics. This was for some custom software running on all 20 machines which had a SQlite API. So I did the simplest and cheapest thing that would work. I setup sync.com folder on all machines (think dropbox/ onedrive) to write logs to the same log.db file across all machines. This worked great at first (I could analyze the db remotely). However you see the potential problem here... Logging was maybe a few writes an hour PER MACHINE but inevitably you start getting conflicts (since sync takes a 5-30 seconds to actually sync). Now I am faced with merging 20 conflicting log.db files. In theory I should have used a server based SQL database. Or perhaps I should have just lived with 20 different log.db files. In my defense there was only SQlite API so I would have had to write some middleware to transfer to another DB.
  • somsak2 4y ago
    not necessarily a problem with the db. rather the syncing strategy. i figure using a Cron with something like rsync would have worked
    • sireat 4y ago
      Indeed if those were Linux / BSD based machines Setting up cron and rsync on different Windows machines (WSL, cygwin?) was not something I would have looked forward to.