24 ms·

SQLite is not a toy database

• snicker7 6y ago
  > SQLite is serverless. Maybe if you use SQLite as a file format. But if you use it like an actual database (e.g. in a web application), I find that one is best off setting up a daemon thread to queue/batch transactions.
  • code-scope 6y ago
    I use a different pattern. A lot sqlite files for diff purposes (UserSession, User Files, etc each store in separate files) This way diff threads of webserver can open/query/read/write a lot of files concurrently without any issue.
    • saalweachter 6y ago
      I, too, enjoy creating deadlocks. [I assume if you are using this pattern successfully in production you are already aware of and taking proper steps to avoid them, but the pedant in me takes issue with "without any issue", since once you have multiple resources being locked in multiple threads, you need to be careful to acquire and release locks in such a way that deadlocks do not occur, either never acquiring more than one lock at a time, acquiring locks in specific orders, or acquiring batches of locks at a time.]
    • snicker7 6y ago
      > without any issue It's easier than you think to corrupt SQLite if you access from multiple threads and especially from multiple processes (yup, I've done this before). Also, there are no concurrent transactions in SQLite. The entire db file gets locked (using POSIX locking, which is known to be broken [0]). Better to queue/batch transactions on a single connection. If your web server consists of multiple processes, then this requires a separate daemon. [0]: http://0pointer.de/blog/projects/locking.html http://0pointer.de/blog/projects/locking.html
  • II2II 6y ago
    One of the disappointing developments of the past quarter century is the near demise of general purpose databases as an end user application. Yes, client/server models are useful when dealing with a large number of transactions. On the other hand, it is usually too complex to justify for personal or small office use. I miss the days when databases were included in office suites or could be purchased as relatively inexpensive standalone applications since it was easy to create a database and associated forms, rather than depending upon domain specific applications that use a database as a file format but may be poorly suited for a particular scenario. (Yes, the phrasing "file format" verses "actual database" rubbed me the wrong way.) Edit: I realized that my post comes off as dismissive of SQLite, which is not entirely true. I use it as a database and appreciate it's role, but embedding it in an application is frequently more than a given application requires.
    • fpoling 6y ago
      Access DB is still included in Microsoft Office
      • OldHand2018 6y ago
        Only some versions of Office, mainly the non-cheapest business versions.
      • zokier 6y ago
        And LibreOffice has Base, a sadly often neglected component.
        • II2II 6y ago
          Another issue with Base is the required JRE. Installing the JRE may be trivial, but it means that an error message is the first experience most people will have with Base. As for Access, it's access is limited due to the much higher price point of Business/Professional versions of Office. In terms of office suites, that version is about two to three times more expensive than an equivalent office suite from the mid-1990's (adjusted to 2021 dollars). In other words, people will only have Access if they feel that the additional cost is justified. That additional cost may be fine for business use, yet it also means that people have less exposure to databases to start with. With respect to exposure, there also appears to be an absence of general purpose databases for home users these days. (By that I mean in terms of cost and ease of use.)
• annoyingnoob 6y ago
  Thanks for the info. Probably would not have considered SQLite in place of something like pandas.
• bob1029 6y ago
  Don't forget about User-Defined Functions. https://www.sqlite.org/appfunc.html https://www.sqlite.org/appfunc.html We just started enhancing our SQL dialect with new functions which are implemented in C# code. One of them is an aggregate and it is really incredible to see how it simplifies projections involving multiple rows. One huge benefit of SQLite's idea of UDFs is that you can actually set breakpoints and debug them as SQL is executing.
  • foepys 6y ago
    Application-defined functions are very useful. One of my company's applications is already designed to work with different SQL systems and a new customer desperately wanted SQLite for a very special use case. As SQLite is quite simple and doesn't support many functions that are standard in SQL Server, MySQL, Oracle, etc., we used application-defined functions to implement all functions the application needs in C#. It's not very fast but also not slow and the customer is happy, which is what really counts.
  • hnrodey 6y ago
    Sounds very interesting. Can you elaborate on how you're leveraging C# for this?
    • bob1029 6y ago
      Directly through Microsoft's SQLite provider - Microsoft.Data.Sqlite. See: https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite/user-defined-functions https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite...
  • TeMPOraL 6y ago
    They're awesome! One common use case is REGEXP. SQLite has a keyword for regular expression matching, but it has no implementation for it. What your application needs to do is to take whatever regex library it's using, make (through whatever FFI method it's using) a C function that interfaces with it, and register it with SQLite. A more advanced feature of this binding mechanism is that, if you provide a bunch of specific callbacks to SQLite, you can expose anything you like as a virtual table, that can be queried and operated on as if it was just another SQLite table. See: https://www.sqlite.org/vtab.html https://www.sqlite.org/vtab.html. The plugin for full-text search is implemented in terms of this mechanism.
• maxbaines 6y ago
  Thanks, always agreed. And a good read.
• myth2018 6y ago
  I'd also like to add the possibility of using SQLite databases as an application file format: https://news.ycombinator.com/item?id=23508923 https://news.ycombinator.com/item?id=23508923 I had to work on a data import/export tool some time ago and SQLite has simplified the design a lot.
  • indigo945 6y ago
    While this makes things easy, you should not do this for any application file format where you except your users to share files. This is because opening a SQLite database file makes the SQLite library execute any arbitrary code that may be stored in that file. [0] Therefore, SQLite is really only suitable for local-only file formats, such as configuration files, and not for files that users will e-mail to each other. [0] https://media.ccc.de/v/36c3-10701-select_code_execution_from_using_sqlite https://media.ccc.de/v/36c3-10701-select_code_execution_from...
    • adamrezich 6y ago
      Weren't all of these issues quickly fixed by the developers after being reported? E: oh whoops missed the dev post above mine
    • myth2018 6y ago
      Thanks for the heads-up! In my use case, files are shared between different instances of the application, usually without user intervention, but there's an attack vector to be addressed here.
    • SQLite 6y ago
      This is misinformation. SQLite does not execute arbitrary code found in the data file. There was a bug, long since fixed, that could be used by an attacker to cause arbitrary code execution upon opening the database file. The referenced video talks about it. It was a very clever attack. But the bug that enabled the attack was fixed even before the talk shown in the video was given. Let me say that again: SQLite does NOT execute arbitrary code that it finds in the database file. To suggestion that it does is nonsense. See https://www.sqlite.org/security.html https://www.sqlite.org/security.html for additional discussion of security precautions you can take when using SQLite with potentially hostile files. The latest SQLite's should be safe right out of the box, without having to do anything mentioned on that page. But defense in depth never hurts.
• tgb 6y ago
  Does anyone use SQLite as their daily-driver in lieu of R or pandas for data analysis? I don't think I can use the sqlite command-line since I'd want a fully-developed plotting utility, and it seems less convenient to do the actual analysis part through a sqlite connection in python, say.
  • nalgeon 6y ago
    I do. Apache Superset, Metabase, Redash - all support SQLite. Other BI tools also do.
  • iagovar 6y ago
    You can perform sql queries with many of the DB clients available, such as Heidi, or Deaver, and also through python. Sqlite is OLTP though, for analytical purposes I'd use an OLAP DB like DuckDB. IMO dumping workload in DB is nice, specially when your dataset doesn't fit in RAM.
  • 6gvONxR4sf7o 6y ago
    You can always execute SQL in pandas by passing it a connection, getting the best of both worlds.
  • kwhitefoot 6y ago
    Take a look at DB Browser for SQLite: https://sqlitebrowser.org/ https://sqlitebrowser.org/ It has some plotting built in. Not sure if it is sophisticated enough for what you need but it might be worth a try.
  • tfehring 6y ago
    It's complementary, since unlike R/Pandas it can fit too-big-for-memory data - I've used it ad-hoc to store big-ish datasets to pull into R. Though if you're using Python I think that use case is mostly superseded by Dask.
  • abhgh 6y ago
    Not "in lieu", but I have found SQLite to be a good companion to pandas. pandas is good when you want to lend some easily navigable structure to your data without leaving python, but becomes cumbersome (it feels cumbersome esp if you know SQL) when you want to do some serious processing. At such times, I just dump my data into a SQLite DB, and from then on my code has SQL queries to it, which I find are far more readable than corresponding pandas statements (also easier for the reviewer). Another advantage you get is if you wanted to look at these intermediate data, you don't need to run your code in debug mode and view the dataframe at a breakpoint - you can use something like datasette[1] or a standard SQLite DB viewer. So a function that does complex data processing has approximately this structure my code: (1) <non-pandas python code to fetch data, other "normal" stuff> (2) <pandas code to do basic navigation, filtering, grouping etc> (3) <dump to SQLite, followed by SQL queries for the really heavy stuff> (4) <back to python, possibly pandas, to return results, write to a file, plot etc> Step (3) used to be pandas for me before, but depending on how complex your operations are this can become hard to read and/or review. [1] https://github.com/simonw/datasette https://github.com/simonw/datasette - datasette doesnt replace a standard DB IDE, but is a very good lightweight alternative to one if don't intend to perform updates/inserts directly on a table.
• jarym 6y ago
  Oh it’s totally a toy. A great toy though! Love it to bits. SQLite + Postgres here and maybe will add DuckDB to the mix soon
• deleted 6y ago
  [deleted]
• michaelmior 6y ago
  > There is nothing more convenient than SQLite for analyzing and transforming JSON. You can select data directly from a file as if it were a regular table. Personally I love jq[0] for this purpose. I haven't really used SQLite for working with JSON, but the examples given are very verbose. [0] https://stedolan.github.io/jq/ https://stedolan.github.io/jq/
  • hobo_mark 6y ago
    I never managed to wrap my head around jq syntax however
    • michaelmior 6y ago
      I agree it can be a little obtuse at times and I still do occasionally find myself having to consult the documentation despite using it pretty regularly for some time. I do also sometimes make use of gron[0] since it integrates well with standard Unix tools. The pipelines I write with gron are probably pretty brittle in the sense that I expect they might be incorrect for some edge cases. But for some quick exploratory analysis, it's a tool I like to have handy. [0] https://github.com/tomnomnom/gron/ https://github.com/tomnomnom/gron/
    • busterarm 6y ago
      It takes a while. jq really needs a repl.
      • bionhoward 6y ago
        Check out ijq...used it this past week to deal with some ugly nested FHIR data. Not perfect, a bit laggy, sometimes crashes your terminal, but really cool in a lot of cases https://sr.ht/~gpanders/ijq/ https://sr.ht/~gpanders/ijq/
        • michaelmior 6y ago
          Seems like you can get something pretty close with fzf. echo '' | fzf --print-query --preview 'jq {q} input.json'
• andrewmcwatters 6y ago
  SQLite is so robust, that I bet most websites could use it without really needing to move onto a client/server RDBMS.[1] I use MySQL, and I know PostgreSQL has a large marketshare now, but I wonder how much of either is really necessary when you think about traffic usage alone. I know at least in my use cases, neither seem necessary. [1]: https://sqlite.org/whentouse.html https://sqlite.org/whentouse.html
  • Thaxll 6y ago
    SQLite is very limited because of its threading model, imo it's not usable outside of the single app model where you have a single user. https://sqlite.org/threadsafe.html https://sqlite.org/threadsafe.html https://sqlite.org/lockingv3.html https://sqlite.org/lockingv3.html
    • andrewmcwatters 6y ago
      I've used SQLite a bit, but not enough to say I know where you run into performance issues. I would anticipate though, if it's similar to fread/fwrite with some marginal overhead for dealing with SQL, after considering most queries aren't very complex, I think most people are going to have a hard time hitting those limits. And that's assuming you're making queries every time an event occurs versus persisting data at particular points in time.
    • busterarm 6y ago
      Most websites/frameworks access their database through a singleton pattern/single-connection anyway. Edit: Sometimes you have to lie and lead people down the wrong path to enlightenment... ;)
      • Thaxll 6y ago
        That's not the case no, you usually access your database with thread pool. Otherwise everyone would wait until the single connection is free. Once you have a bit more users that tries to write everything will fall appart.
• ben509 6y ago
  > There is a popular opinion among developers that SQLite is not suitable for the web, because it doesn’t support concurrent access. No, the issue is it doesn't have high availability features: failover, snapshots, concurrent backups, etc. (Edit: oops, comment pointed out it does have concurrent backups.) SQLite isn't a toy DBMS, it's an extremely capable embedded DBMS. An embedded DBMS is geared towards serving a single purpose-built client, which is great for a desktop application that wants a reliable way to store user data. Once you have multiple clients being developed and running concurrently, and you have production data (customer accounts that are effectively legal documents that must be preserved at all times) you want that DBMS to be an independent component. It's not principally about the concurrent performance, rather it's the administrative tasks. That requires a level of configuration and control that is contrary to the mission of SQLite to be embedded. They don't, and shouldn't, add that kind of functionality.
  • nalgeon 6y ago
    I think that the absence of 'high availability' is not an issue for small websites or web apps. Transactions are ACID, concurrent readers are fully supported. Backups and administrative tasks are super-easy.
    • voxic11 6y ago
      Also if you really need HA there are nice projects like https://dqlite.io/ https://dqlite.io/
      • SahAssar 6y ago
        Looking at https://dqlite.io/docs/protocol https://dqlite.io/docs/protocol it seems like it does not allow for embedded usage though. Doesn't this turn it into more of a traditional client-server RDBMS?
        • yawaramin 6y ago
          From the home page: > Dqlite is a fast, embedded, persistent SQL database with Raft consensus that is perfect for fault-tolerant IoT and Edge devices.
• deleted 6y ago
  [deleted]
• michaelmcmillan 6y ago
  I've built a complex CRM that handles 2.1 million USD in transactions every year. It is running sqlite with a simple in-memory lru cache (just a dict) that gets purged when a mutating query (INSERT, UPDATE or DELETE) is executed. It is very simple and more than fast enough. Friendly reminder that you shouldn't spend time fine tuning your horizontal autoscaler in k8s before making money.
  • midrus 6y ago
    Do you mean I don't need Go microservices talking gRPC deployed in multiple kubernetes clusters with bash script based migrations via GitOps with my hand made multi cloud automation (in case we move clouds) following all the SCRUM practices to ship working software? Mindblowing.
    • kwertyoowiyop 6y ago
      You will for your blog series that you mention prominently on your resume that gets you your next Senior Architect gig.
      • midrus 6y ago
        Agh!... that's the catch... what I'm gonna give talks about and what do I write on medium then.... now I get it. Thanks!.
    • anticristi 6y ago
      > my hand made multi cloud automation (in case we move clouds) Aren't these symptoms of a deeper problem? Many Product Manager I talk to wanted me to build something that is as flexible as possible and solves all problems for everyone, everywhere. Go microservices with gRPC and Kubernetes feels like the only high-level technical decisions I can take in light of such information. :)
  • juancampa 6y ago
    How do you ensure data is not lost to oblivion if a catastrophic system failure occurs?
• unnouinceput 6y ago
  SQLite is definitely not a toy DB when it comes to its features, none will deny that. However, when you start to deal with hundreds of millions of records in a table, you kinda consider it a toy.
  • _wldu 6y ago
    It is the most widely deployed DB in the world. That fact, alone, shows that it is not a toy DB.
  • nijave 6y ago
    Sqlite can handle that https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-qps-on-a-single-server/ https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q... I've been toying around with it locally for playing with big data sets (50-100s GB) uncompressed and it works pretty well. It's much easier than postgres and mysql which have a lot more knobs and tuning required)
• petercooper 6y ago
  With no sense of overstatement here, SQLite is one of my favorite creations in the entire world, so I have a bunch of links some of you might find interesting if you want to dig further: https://github.com/sql-js/sql.js https://github.com/sql-js/sql.js - SQL.js lets you run SQLite within a Web page as it's just SQLite compiled to JS with Emscripten. https://litestream.io/blog/why-i-built-litestream/ https://litestream.io/blog/why-i-built-litestream/ - Litestream is a SQLite-powered streaming replication system. https://sqlite.org/lang_with.html#rcex3 https://sqlite.org/lang_with.html#rcex3 - you can do graph-style queries against SQLite too (briefly mentioned in the article). https://github.com/aergoio/aergolite https://github.com/aergoio/aergolite - AergoLite is replicated SQLite but secured by a blockchain. https://github.com/simonw/datasette https://github.com/simonw/datasette - Datasette (mentioned at the very end of the OP article) is a tool for offering up an SQLite database as a Web accessible service - you can do queries, data analysis, etc. on top of it. I believe Simon, the creator, frequents HN too and is a true SQLite power user :-) https://dogsheep.github.io/ https://dogsheep.github.io/ - Dogsheep is a whole roster of tools for doing personal analytics (e.g. analyzing your GitHub or Twitter use, say) using SQLite and Datasette.
  • dheera 6y ago
    Which if any of these is efficient enough to run on mobile and can cerealize an entire database to localStorage?
    • WJW 6y ago
      SQLite underpins large parts of both iOS and Android, so... all of them? Whether you can serialize "an entire database" just depends on the size of the database and whether it will fit in the local storage. SQLite scales up to a few TB at least, so that is unlikely to be the bottleneck.
      • yrrr 6y ago
        localStorage is a web API, the storage limit is ~5MB. However it can only store strings so it's pretty taxing. Chrome and Safari actually embed SQLite directly as WebSQL, however it's not becoming standardized because Mozilla doesnt consider "add SQLite" as a sensible web standard.
• cute_boi 6y ago
  Nobody seems to mention that SQlite is one of the secure database. Just look at its rigorous testing, fuzzing and everything.
  • juancampa 6y ago
    nit: security is like the speed of light for a massive object, you can never reach it, you can only get closer and closer. I'd say instead "SQLite is one of the _most_ secure databases"
• hackeraccount 6y ago
  Is it the unix version of MS Access? It's sort of interesting to compare the two.
  • stjohnswarts 6y ago
    Well the database part maybe, but it's not going to help you generate forms and reports or build small guis around it. You'll have to find those tools for yourself or build a web app around your sqlite db. I haven't done a lot with Access though, mostly help build small database interfaces/reports for their inventory systems (whether a lab or comic book collection).
    • buescher 6y ago
      You can do all that with tcl/tk. Sqlite was in fact originally a tcl extension so the tcl interface for sqlite is first-rate. With tcl/tk it's sort of a Unix analog to VB6. I keep thinking it would be sort of fun to do a tk app again; is there a good model CRUD app for tcl/tk/sqlite out there? Something like the Northwind thing for Access?
  • II2II 6y ago
    SQLite is a database that is intended to be embedded in applications rather than run as a separate server. You could make an Access-like application from it, but it cannot replace Access on its own. It's commonly used in applications that use it as a convenient container to store data (the "file format" scenario) or as a domain specific database. One of the better examples of the latter is Calibre, an e-book library manager, since it exposes some of the database functionality to the end user. For example: the end user can add columns to store custom data for each book.
  • SigmundA 6y ago
    No it would be closer to the Jet db engine that Access uses, they are both file based in process relational db engines. Back in the day for a Windows app if you wanted a in process file based db you would use Jet since the engine was included with windows even though Access was a separate product.
• tompazourek 6y ago
  Anyone knows what is the current situation regarding parallelism in SQLite (utilizing multiple cores when executing a complex query)? Last time I heard about this, it wasn't looking very good.
• candiddevmike 6y ago
  I wish SQLite had a PostgreSQL compatibility layer. Right now, to add SQLite support to a Go project (or anything without an ORM), you have to rework all your queries and migrations. It's probably an impossible ask, but having a compatibility flag within SQLite so it would accept PostgreSQL formatted queries would be extremely helpful.
  • smilliken 6y ago
    Abstracting over two complex and evolving systems is a bottomless well of bugs. Contributers would shy away from this sort of compatibility because it would encourage users to ignore the differences at first, and then complain loudly when they are discovered.
  • justsomeuser 6y ago
    https://www.jooq.org/translate/ https://www.jooq.org/translate/ This does simple syntax translation, have not used it though. Combine with a Golang AST lib and you might be able to make a CLI translate tool like `fix`.
  • deleted 6y ago
    [deleted]
• Toutouxc 6y ago
  > not bothering with optimization (≈200 requests per page) What amount of SQL queries per page render is considered sensible? When I run more than 20 queries per request in my Rails apps (smallish internal tools for different companies) I get uneasy. I usually deploy the app on the same machine where the DB (not SQLite) runs, but I imagine if that weren't the case the app-DB roundtrips could soon dominate the whole thing.
  • vbsteven 6y ago
    When using an external db every query needs to cross the connection boundary and thus perform network IO and deal with the threading implications for that IO. With SQLite running in-process a round trip just means a function call, and when data is available in the cache it won’t even hit the disk.
  • deckard1 6y ago
    > app-DB roundtrips could soon dominate the whole thing and they do. We have a GraphQL API backed by multiple microservices at a place I work. You become increasingly paranoid at every call you need to make. It's also where ORMs completely fall over. ORMs are designed for, generally, one record to one query mapping. Which is why they are always leaky abstractions which need heroic acts of clever hacking to overcome. You know, rather than doing the sensible thing and just writing a single query that fetches data from multiple tables. SQLite sort of encourages many queries, though, due to their design. I recall their docs even mention this point. But I've seen a few people in various threads that get bitten by this when they switch to a client/server DB. I think it's perfectly fine to design your app around the characteristics of your database. Otherwise, you're missing out on optimizations and features you could be using and what would even be the point in favoring one DB over another if it's all the same to your app? You should pick the DB that matches the characteristics of your app anyway.
  • renewiltord 6y ago
    The sqlite position on this is articulated here https://sqlite.org/np1queryprob.html https://sqlite.org/np1queryprob.html > N+1 Queries Are Not A Problem With SQLite > > The SQLite database runs in the same process address space as the application. Queries do not involve message round-trips, only a function call. The latency of a single SQL query is far less in SQLite. Hence, using a large number of queries with SQLite is not the problem.
• kragen 6y ago
  This is a great introductory guide! I like SQLite a lot, but I found SQLite's recursive CTE implementation to be somewhat limited: https://dercuano.github.io/notes/why-html-is-not-a-programming-language.html#addtoc_7 https://dercuano.github.io/notes/why-html-is-not-a-programmi... Has that improved? (sorry about the embarrassing arrogant pedant attitude in that note, but it's too late to fix it now)
  • samatman 6y ago
    It has! Search for "2020-12-01 (3.34.0)" here: https://www.sqlite.org/draft/changes.html https://www.sqlite.org/draft/changes.html The 3.35 release added more good stuff for CTEs, as well as a RETURNING clause and a much more flexible UPSERT.
    • kragen 6y ago
      After discussing it with you, I think it would be bad to make SQLite's recursive CTE support more powerful; making SQL Turing-complete is a clear case of Turing overreach. I think it's still the case that SQLite doesn't permit the use of recursive CTEs in subqueries.
      • SQLite 6y ago
        SQLite requires that the self-reference be in the top-level FROM clause of the recursive part of a recursive CTE. PG apparently allows the self-reference to be down inside of subqueries, as long as there is only one reference. I have make a copy of the Collatz Conjecture CTE that you linked to and was going to see if I could get it to work in SQLite for the next release cycle. I don't (yet) see any reason why it shouldn't work to have the recursive reference down inside a subquery, as long as there is only one recursive reference. No promises. We'll see how it goes.
        • kragen 6y ago
          Oh dear. I was hoping you wouldn't notice this discussion. Another step off the cliff into the Turing Tarpit... well, I hope my twisted piece of appalling SQL brings joy to your heart, at least, even if supporting it makes SQLite worse. And you are of course in a much better position to judge what would make SQLite better or worse than I am. Have you thought about offering an interface with an intentionally-Turing-incomplete subset of SQL in SQLite, for making queries that can be guaranteed to terminate? Because it doesn't sit right with me that SQL is Turing-complete now. https://news.ycombinator.com/item?id=26529789 https://news.ycombinator.com/item?id=26529789 goes into more detail on decidable query languages that can still accommodate transitive closure.
• D13Fd 6y ago
  My only "problem" with SQLite was that it was so fast, running locally, that it hid just how much my app was needlessly hitting the database. It was a surprise when I transitioned to a networked Postgres server and performance completely tanked due to my crappy code...
  • re 6y ago
    I know you put "problem" in quotes, but in case you haven't seen it, this document was posted here a little while ago: https://sqlite.org/np1queryprob.html https://sqlite.org/np1queryprob.html It describes how access patterns that would be bad practices with networked databases are actually appropriate for SQLite as an in-memory DB.
    • D13Fd 6y ago
      That's interesting! Now I have to resist the urge to move back...
• francoisp 6y ago
  SQLite is a really neat thing. I was looking at extensions and how to augment it; you could even add a pg_notify -like feature: https://sqlite.org/c3ref/update_hook.html https://sqlite.org/c3ref/update_hook.html and have worker processes doing what would amount to out of process stored procedures in postgres (or UDF in SQLite) -- in any language you'd like. You can only register one callback per table tho, although you could from this callback fire other functions... All in all it's an awesome tool for a project like tailscape, but I think the hackers there went for a flat file. Personally I'd would love to see in process postgres; a build of postgres that is geared for integrating a set of your threads, and builds the whole of postgres with your app on all major OSs, only listening to the inside by default. For the same reason I'm using nodejs; to be able to run the same code anywhere. I think bundle size would be a minor issue, really, I downloaded Sage9.2 yesterday, it's 2GB! VSCode is 100MB download, and they refer to it as a small download... cheers! happy coding,
  • fiddlerwoaroof 6y ago
    The Firebird database can run both as a separate server and in embedded mode. I wish more databases works design for this sort of flexibility.
• Derpdiherp 6y ago
  ---Was talking about a previous company who made steps to shift from it for pricing reasons. Seems they where misinformed.
  • eurleif 6y ago
    Huh? SQLite is public domain.[0] There are paid extensions for encrypted and compressed databases[1], but those cost a flat fee for unlimited devices:[2] "Your license is perpetual. You have paid a one-time fee that allows you to use and modify the software forever. You can ship as many copied of the software to your customers as you want so long as you ensure that only compiled binaries are shipped (you cannot distribute source code) and that your customers cannot make additional copies of the software to use for other purposes." [0] https://www.sqlite.org/copyright.html https://www.sqlite.org/copyright.html [1] https://www.sqlite.org/prosupport.html https://www.sqlite.org/prosupport.html [2] https://sqlite.org/see/doc/release/www/readme.wiki https://sqlite.org/see/doc/release/www/readme.wiki
    • Derpdiherp 6y ago
      Interesting - it's been a long time since I used / looked at it. There was a big uproar in the company though and a drive to move away from it due to licensing and cost from there. Maybe they where misinformed.
      • masterofmisc 6y ago
        I would say terribly so.
  • tomcam 6y ago
    How is it not free for commercial use? It’s public domain code. I just rechecked the license after reading your comment.
• fouc 6y ago
  PostgreSQL is not a toy database. MySQL is not a toy database. I feel like the "toy database" part is clickbait
  • guenthert 6y ago
    Not sure about that. I can't recall having seen PostgreSQL being referred to as toy database. I have seen however texts cautioning SQLite in production use, e.g. in [1], [2]. [1] https://docs.bareos.org/bareos-18.2/IntroductionAndTutorial/InstallingBareos.html https://docs.bareos.org/bareos-18.2/IntroductionAndTutorial/... [2] https://docs.bareos.org/bareos-18.2/DeveloperGuide/catalog.html https://docs.bareos.org/bareos-18.2/DeveloperGuide/catalog.h...
  • dpix 6y ago
    I don't think the author is trying to call out SQLite as somehow better than others database tech - just that most people perceive SQLite to be a tool for development or exploration when in fact it much more powerful than most believe
• unixhero 6y ago
  SQLite: The Database at the Edge of the Network with Dr. Richard Hipp https://www.youtube.com/watch?v=Jib2AmRb_rk https://www.youtube.com/watch?v=Jib2AmRb_rk
• beached_whale 6y ago
  If I have a choice of making a file format and doing the I/O or using SQLite, I’ll choose SQLite. It’s hardened to a degree that I won’t be for filesystem shinanigans. If I need to export a file, that’s still easy enough.
• polyrand 6y ago
  You can also have stored procedures in SQLite https://cgsql.dev/ https://cgsql.dev/
• megous 6y ago
  I'd still rather use PostgreSQL for my data needs. psql is quite nice too as an interface, and "setup and forget" replication for everything in the cluster and remote access makes up for slightly harder deployment and upgrades. Knowing that I can do anything and the change hits 3 hard drives in a span of few seconds on various machnies is nice. Tolerance for arbitrary down time of master/slave clusters is also nice. But yeah, for quick one-off data munging, sqlite looks quite nice.
  • benbjohnson 6y ago
    I agree that PostgreSQL has some great tooling around it. For SQLite replication, you might want to checkout Litestream: https://litestream.io/ https://litestream.io/
    • megous 6y ago
      I know about litestream, it's not the kind of "setup and forget" I have in mind. But it's nice that it exists.
      • benbjohnson 6y ago
        That's good to know. I'm always trying to improve the developer ergonomics. What would you want to see added to make it "setup and forget"?
        • megous 6y ago
          I don't think there's much to be done. I just like the fact that I don't have to think about replicating individual databases, regardless if I rename, delete, re-create them. It's being done at cluster level, it's one time setup and I can forget about it.
• ransom1538 6y ago
  SQLite powers iMessage. Legit. You can use sqlite commands to look through your messages on your macbook.
  • avinassh 6y ago
    it powers the client side iMessage app and most mobile applications use SQLite.
• ajsnigrutin 6y ago
  For me, the main killer feature of sqlite is portability. (Like moving it around, not porting it to new systems). Just write the code to create an empty database if the db file does not exist, move your code elsewhere, and the database will be created on the first run. No usernames, passwords, firewall rules, IP addresses, no nothing... just a single file, with all the data inside. Miration? Copy the whole folder, code and the database. Clean install.. copy the folder, delete the database. Testing in production? Just backup the file, do whatever, then overwrite the file.
• RedComet 6y ago
  It seems as though there is a new article about this daily.
• doomleika 6y ago
  Two major gripe I had with SQlite 1. SQLite doesn't really enforce column types[0], the choice is really puzzling to me. Since schema enforced type check is one of the strong suit of SQL/RDMBS based data solution. 2. Whole database lock on write, this make it unsuitable to high write usages like logging and metric recording. WAL mode will help but it will only alleviate the issue, you will need row based lock solution eventually. Just like the offical FAQ said, SQLite competes with fopen[1] instead of RDBMS systems. -- [0]: https://sqlite.org/datatype3.html https://sqlite.org/datatype3.html [1]: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
  • lsb 6y ago
    If you are in WAL mode, you can have unlimited readers as one writer is writing.
    • mastre_ 6y ago
      Also, another issue when not in WAL mode is that a long read will actually have a write lock and block writes.
  • haberman 6y ago
    > SQLite doesn't really enforce column types[0], the choice is really puzzling to me. This is acknowledged as a likely mistake, but one that will never be fixed due to backward compatibility: > Flexible typing is considered a feature of SQLite, not a bug. Nevertheless, we recognize that this feature does sometimes cause confusion and pain for developers who are acustomed to working with other databases that are more judgmental with regard to data types. In retrospect, perhaps it would have been better if SQLite had merely implemented an ANY datatype so that developers could explicitly state when they wanted to use flexible typing, rather than making flexible typing the default. But that is not something that can be changed now without breaking the millions of applications and trillions of database files that already use SQLite's flexible typing feature. https://sqlite.org/quirks.html https://sqlite.org/quirks.html
    • doomleika 6y ago
• qmmmur 6y ago
  Did anyone say it was?
• incanus77 6y ago
  I had seen Dr. Hipp speak at a conference[1] around 2009 and it really put SQLite on my radar as a (then) Mac developer. It ended up surfacing from the back of my mind around then at Mapbox when I put together the prototype of the MBTiles file format[2] which is still in use today. The idea of an on-disk relational database was new to me and just a perfect fit for where mobile devices were at that point in time, and for our use case of offline maps. [1] https://en.wikipedia.org/wiki/C4_(conference)#C4[2] https://en.wikipedia.org/wiki/C4_(conference)#C4[2] [2] https://medium.com/devseed/portable-map-tiles-format-released-2a0fa3aa0bf4 https://medium.com/devseed/portable-map-tiles-format-release...
  • durkie 6y ago
    thanks for mbtiles! I spend lots of time using and abusing that format every day :)
• fmajid 6y ago
  Surprised no one has mentioned how Expensify use SQLite as their main data store: https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-qps-on-a-single-server/ https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...
• people_not_bots 6y ago
  If coding can't be a toy then I dont know what is
• jstrong 6y ago
  > There is nothing more convenient than SQLite for analyzing and transforming JSON. the example query given: select json_extract(value, '$.iso.code') as code, json_extract(value, '$.iso.number') as num, json_extract(value, '$.name') as name, json_extract(value, '$.units.major.name') as unit from json_each(readfile('currency.sample.json')) ; that sure looks fun to type into a repl! nothing against sqlite, which I like and use, just found the idea of that query being convenient for one-off analysis to be off.
• agumonkey 6y ago
  Someone told me they build a world class DNS mostly around sqlite. I guess you left the toy area by that point.
• D_Guidi 6y ago
  spatialite rulez!
• JimmaDaRustla 6y ago
  Weird. They never mentioned the amazing power of inserting text and numbers into a boolean column.
  • justin_oaks 6y ago
    I assume you're being sarcastic because I haven't yet thought of a reason why that'd be a good thing. Although I've never been bitten by SQLite's dynamic types, I wish they weren't a thing. I'm genuinely curious if there are any real use-cases for this behavior.
  • samatman 6y ago
    BOOLEAN in SQLITE is spelled `flag INTEGER NOT NULL CHECK (flag = 0 or flag = 1)`. Less than ideal, sure. But typing is there when you want to reach for it.
• eleog 6y ago
  Sqlite is not a toy, but it's so sloppy that it's not really a tool either. Like a saw with a loose blade, it can help you cut but it can also hurt you. My pet peave is the way sqlite accepts non-aggregated columns in a GROUP BY query. You'll get a result in the column, but it's not clear what it means. More of sqlite's sloppyness is detailed here https://sqlite.org/src/wiki?name=StrictMode https://sqlite.org/src/wiki?name=StrictMode
  • outworlder 6y ago
    MySQL is also very "sloppy". People have learned to work around its quirks. Honestly, SQLite is a wonderful tool if properly used. Calling it "sloppy" is a disservice.
  • leijurv 6y ago
    > My pet peave is the way sqlite accepts non-aggregated columns in a GROUP BY query. Funnily enough, this is my favorite feature of sqlite that I wish other RDBMSs had :)) There is no easy equivalent to `SELECT order_id, status AS latest_status, MAX(updated_at) AS updated_at FROM orders GROUP BY order_id`. Well, it's possible, but for example in Postgres you need some silliness with nested queries, `PARTITION BY`, and `ROW_NUMBER()`.
• eb0la 6y ago
  Sqlite is also an EXTREMELY well tested database. Last time I checked test code was about 2x productionncode. This is also good indicator to spot software that is not a toy.
  • eb0la 6y ago
    Turns out it is much, much, more tested than I remembered: https://www.sqlite.org/testing.html https://www.sqlite.org/testing.html
• drenvuk 6y ago
  sqlite is my caching layer. It's crazy fast on ssds and keeps my memory empty. There's no reason to read blobs from disk or keep them stored in memory if i'm using it.
  • sigjuice 6y ago
    What is the benefit of keeping memory empty?
    • drenvuk 6y ago
      Running lots of other things on the same machine. Most of the stuff programs store is just sitting idle doing nothing but taking up space. Move it to sqlite or disk if it's an ssd. The latency hit negligible unless you're running a gaming server.
• lamelydiminish 6y ago
  I love SQLite. Am building a webapp + mobile app that needs to sync user created content. Is there an easy way to sync SQLite DBs?
  • simonw 6y ago
    SQLite has a "session" extension which is designed to help handle the sync problem: https://sqlite.org/sessionintro.html https://sqlite.org/sessionintro.html
    • lamelydiminish 6y ago
      TIL. Can't find anything on sessions for sql.js though... Interested in using sql.js since I'm building a web PWA first, and eventually native desktop&mobile app. Perhaps this is overkill for SQLite at this point?
• qwerty456127 6y ago
  SQLite is beautiful. Almost a perfect database for all single-user and simple multi-user projects. I just wish it had some built-in (not as an extension) encryption, native (non-DIY) date-time and UUID types - it would actually be perfect then.
• codykochmann 6y ago
  The thought that devs believe javascript would ever be fast enough to make sqlite actually need to run in parallel makes me giggle.
• diehunde 6y ago
  From the SQLite website: Situations Where A Client/Server RDBMS May Work Better Client/Server Applications If there are many client programs sending SQL to the same database over a network, then use a client/server database engine instead of SQLite. SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, file locking logic is buggy in many network filesystem implementations (on both Unix and Windows). If file locking does not work correctly, two or more clients might try to modify the same part of the same database at the same time, resulting in corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it. A good rule of thumb is to avoid using SQLite in situations where the same database will be accessed directly (without an intervening application server) and simultaneously from many computers over a network. High-volume Websites SQLite will normally work fine as the database backend to a website. But if the website is write-intensive or is so busy that it requires multiple servers, then consider using an enterprise-class client/server database engine instead of SQLite. Very large datasets An SQLite database is limited in size to 281 terabytes (248 bytes, 256 tibibytes). And even if it could handle larger databases, SQLite stores the entire database in a single disk file and many filesystems limit the maximum size of files to something less than this. So if you are contemplating databases of this magnitude, you would do well to consider using a client/server database engine that spreads its content across multiple disk files, and perhaps across multiple volumes. High Concurrency SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Writers queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution.
• amaccuish 6y ago
  It's a shame Nextcloud works terribly with SQLite
• mey 6y ago
  Just went looking, there doesn't seem to be a great actively developed solution for a Java implementation or JNI bridge. The JDBC driver that seemed to have popularity was brought into the SQLite repo, but the documentation is lacking a bit. [1] Even .Net Core has a integrated ADO.NET bridge to SQLite [2] There are quite a few mature embedded DB options for Java actively developed, for example Apache Derby/HSQLDB/H2. The SQLite fileformat is very portable, which is very handy and useful, the SQLite C Library is very powerful. It'd be nice for the Java community to tap into that power. 1) https://www.sqlite.org/java/file?name=doc/overview.html&ci=tip https://www.sqlite.org/java/file?name=doc/overview.html&ci=t... 2) https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite/?tabs=netcore-cli https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite...
• flyflyFenix 6y ago
  I was a fan of SQLite until I needed a full outer join of two tables. It was such a pain to create a workaround when I found out it isn't supported, and the result was still sub-optimal and needlessly complex. Is running a full outer joins such a unreasonable assumption within a DBMS?
  • justin_oaks 6y ago
    I hear you. MySQL still doesn't have full outer joins either. I would think this would be one of the easier features to add. Of course, I'm sure the people working on MySQL or SQLite would say "Patches welcome!"
    • yawaramin 6y ago
      Actually, for SQLite at least, they wouldn't. https://www.sqlite.org/copyright.html#notopencontrib https://www.sqlite.org/copyright.html#notopencontrib
• valzam 6y ago
  If I recall correctly https://www.foundationdb.org/ https://www.foundationdb.org/ is also built on "distributed sqlite" at it's core.
• breck 6y ago
  What really blew me away years ago: SQLite is public domain! (https://www.sqlite.org/copyright.html https://www.sqlite.org/copyright.html) I recommend getting a book on it. I have the O’Reilly one. (https://sqlite.org/books.html https://sqlite.org/books.html). I’ve used it so much in so many different scenarios. Such a great tool to have on the toolbelt.
• thyrsus 6y ago
  http://howfuckedismydatabase.com http://howfuckedismydatabase.com
• gigatexal 6y ago
  Love this article. It is an love note to one of my favorite data bases.
• 02020202 6y ago
  i am fond of kvdb, if you can work with ids or single indices. i would love to use sqlite but it has no native Go implementation. i even made simple layer on top of kvdb that supports filtering on multiple indices and sorting. but native sql(ite) would mean less work :)
• Cthulhu_ 6y ago
  I'm (re) building a management interface for our product which offers a GUI to generate a configuration file with a lot of knobs and whatnot, and we're continuing to use SQLite. There's three major things I'm still struggling with though: - Should I switch from using an ORM (gorm) to using more raw SQL with some utilities to reduce tedium and boilerplate? - Versioning; users will want to be able to revert to a previous version of a configuration (not just a single table, a set of tables) - Redundancy; if the primary node fails, it should switch over to a secondary node, ideally in another location. The existing app copies the .db file to a `sync` folder after certain operations, which is then sent to the secondary server via rsync. It's actually pretty elegant if you think about it? Anyway, none of these are really SQLite specific issues.
  • akpa1 6y ago
    I use SQLite with GORM pretty regularly, and it works pretty nicely unless you're working with composite primary keys[0]. I've ended up writing some raw SQL to work around that, it's kinda annoying. [0]: https://github.com/go-gorm/gorm/issues/3585 https://github.com/go-gorm/gorm/issues/3585
• ReflectedImage 6y ago
  It's really slow compared to normal SQL databases
• tmaly 6y ago
  I have been using sqlite in a professional setting since 2006. I find it great for staging large amounts of report data before writing it out to excel or other format.