25 ms·

SQLite as an Application File Format (2014)

• fatboy 6y ago
  We transitioned the file format for the images in our climbing-topo app from a pile of files to SQLite (the images are tiled). Going from having high hundreds of tiny files on disk per topo photo to just one was an incredible boon to productivity for things like data backup and transferring files onto the device for testing.
  • wetmore 6y ago
    Tell me more about your app
    • fatboy 6y ago
      It acts as a digital window into our print guidebook catalogue. The books are mostly for the uk, with the rest spread across Europe. I take the finished desktop-publishing documents and extract and package the data to put into the app, including tiling the images so we can have very high resolution photos on low-end devices. There's a semi-technical article about the topo-view implementation here: https://www.ukclimbing.com/articles/features/rockfax_app_deep_dives_part_0_topos-12679 https://www.ukclimbing.com/articles/features/rockfax_app_dee...
• overcast 6y ago
  What happened to storing blobs in a database being giant rookie mistake? I'm currently dealing with this at the office, where now a SQLServer database has grown to unmanageable size from helpdesk software attachments.
  • foldr 6y ago
    The suggestion here is to use it as a file format, not as a file system.
  • GordonS 6y ago
    I think that refrain usually applies to when the database is used to store relational data, rather than when it's being requisitioned for use as a file format.
  • MaxBarraclough 6y ago
    I've never faced this problem so perhaps I'm missing something obvious, but what's the big difference storing binary blobs on the DB vs, say, storing URLs on the DB and hosting the blobs elsewhere? Is the size of the database the main concern?
    • raphaelj 6y ago
      Both approaches have their advantages. Storing blobs on the DB makes the data more consistent (the database is always in sync with the file content), and allows you to use advanced features of the DB on these files (transactions, SQL queries, data rollback ...). You also only have to backup the DB. Storing links to the objects is usually more scalable, as your DB will not grow as fast. DBs are usually harder to scale, and also more expensive (at least 10x per GB). It really depends on the project, but I'm favoring more and more storing the data in BLOBs, as it makes backups easier and as I can use SQL queries directly on the data. Databases as a service also make it easy to scale DBs up to +/- 2TB. But the cost might still be an issue.
      • ginko 6y ago
        >and also more expensive (at least 10x per GB). Expensive in what way? Memory/Compute? It can't be licensing money since SQLite is public domain.
        • raphaelj 6y ago
          I'm just comparing the per stored GB cost of on demand database services vs on demand storage services.
        • Tajnymag 6y ago
          raphaelj is probably talking about classic database machines like PostgreSQL and MariaDB. In my view, storing blobs in sqlite doesn't have a huge disadvantage tied to it. Sqlite grows pretty linearly and as it stores the whole db in a single file, hosting provider doesn't even have to know about you using it.
  • ojnabieoot 6y ago
    Did you read the article? This isn’t about storing blobs as data in SQLite, but rather using SQLite-formatted databases as the file format for an application. As a simple example, Word documents are just zipped XML text files (try unzipping a .docx and looking inside). Instead of using this, you could a SQLite .db file (probably with a different extension), translating the XML files into tables, and folders into databases. The OpenOffice case study has more details: https://sqlite.org/affcase1.html https://sqlite.org/affcase1.html
    • overcast 6y ago
      Apparently I'm misreading the following paragraph. Any application state that can be recorded in a pile-of-files can also be recorded in an SQLite database with a simple key/value schema like this: CREATE TABLE files(filename TEXT PRIMARY KEY, content BLOB);
      • roenxi 6y ago
        It is reasonable to adopt that style if it is data you don't control. Storing blobs is probably a mistake if it is data you have control over. Sometimes all that is known about data is it exists - in that case, into the database as a blob it shall go. If it can be decomposed it probably should be.
      • ojnabieoot 6y ago
        I agree that is unclear for an introduction, and that the article could have been written better. The “pile-of-files” they are referencing isn’t a filesystem of arbitrary and unstructured binary data stored for humans to look at later (like your Helpdesk attachments), but rather structured and specific data for an application to read in a well-defined manner. Your Helpdesk example used SQLServer problematically because a SQL database shouldn’t be used as an arbitrary file store. But if you know what the file structure is and have a reasonable grasp for how it might scale (that each binary blob is small, that each user only adds one row to the database, etc), there are huge advantages to “a SQLite table with lots of binary and text columns” versus “a folder with lots of binary and text files.” And if those text files are just small key-value pairs then maybe they should also go in SQLite.
  • adrianmonk 6y ago
    I don't think there is a consensus that storing blobs in the database is always the wrong decision. Yes, there are drawbacks, but it really depends on the circumstances, and it might be the right choice. Suppose, for example, that you were going to make your own implementation of Git. The normal implementation has a bunch of blobs under one directory. There's not much use in manipulating this directory separately from everything else under .git. The blobs and most of the non-blob stuff are only useful together anyway, so you already manage them as a single collection. It could create problems for certain backup tools, so that's a disadvantage. But it also simplifies application development. So it's not a slam dunk one way or the other.
    • yellowapple 6y ago
      Relatedly, this is (from what I understand) exactly how Fossil works for version control; it's literally a single SQLite file storing blobs representing changes to the repository.
  • agumonkey 6y ago
    attaching oversized unreadable scanned pictures of digital text files I guess
  • JulianWasTaken 6y ago
    It's less of a rookie mistake than it seems these days: https://danluu.com/file-consistency/ https://danluu.com/file-consistency/.
  • TheRealPomax 6y ago
    That's the thing: this isn't "a database" anymore when it's used as an application file format, it's now "an application file format" that is conveniently also a sqlite db file so everything in it is trivially accessible. Storing huge blobs then becomes a matter of "is this my data, or is this general data that I'm merely also making use of". Examples abound of both: - Adobe Lightroom (a non-destructive photo editor) goes with "these are not my images" and leaves them out of its sqlite "lrcat" files, instead maintaining a record of where files can be found on-disk. - Clip Studio (a graphics program tailored for digital artists) on the other hand stores everything, because that's what you want: even if you imported 20 images as layers, that data is now strongly tied to a single project, and should absolutely all be in a single file. So the key point here is that, yes: sqlite files are database, but because they're also single files on disk, their _use_ extends far beyond what a normal database allows. As application file format, the rules for application files apply, not the rules for standard databases.
  • HelloNurse 6y ago
    Storing blobs is good if the blobs are what you actually want to store; the rookie mistake is storing blobs instead of the well-structured, meaningful, indexable and much more compact columns and additional tables that you should be storing but you are too rookie to to design.
  • bob1029 6y ago
    Storing your blobs outside of the database when they are an inherent part of some larger transactional model is the giant rookie mistake IMO. We did this for a little while for the reasons of "oh my gosh look how big the .db is getting" and it ended up a complete shitshow. Simple tasks like cleaning up old entries can quickly turn into a nightmare if you have to go around disk and make sure you are not leaving garbage behind. There are also other concerns outside the box like VM snapshots. This could leave you in a very inconsistent state regarding what blob you have on disk vs what the database is expecting. Is having everything in one gigantic .db file an actual downside? What makes a database's size "unmanageable"? Presumably, you'd have to store that business information somewhere anyways, right? I don't understand how 1 unified file is unmanageable, but splitting that concern up into 2 different domains magically makes it better.
    • jzoch 6y ago
      It can trash db performance. A relatively simple alternative is to store references to immutable blobs in the db so that everything remains transactionally consistent (and easily restorable to any point-in-time). This is what many large companies do. I happen to work at a company that runs the largest + most "shall not be named" databases and has struggled for years to evict all the blobs people shoved in em.
      • aembleton 6y ago
        If you index on the file_name in a table where you have file_name and a blob then why would it trash performance? SQLite can quickly locate where to start reading the blob.
• chrisbennet 6y ago
  I used it with a different extension to save application for a product I wrote. Any classes that need to be saved had serialize() and deserialize() functions. Serialize before saving to SQLite and if read in, deserialize after reading it from the DB.
• teleforce 6y ago
  Shouldn't this has 2014 inside the title? The story has also appeared several times in HN before. Very interesting concept but now I think perhaps application file format using TileDB will be much better since it can support sparse data as well [1]. [1] https://github.com/TileDB-Inc/TileDB https://github.com/TileDB-Inc/TileDB
  • HelloNurse 6y ago
    TileDB uses a "pile of files", as the article calls it, it doesn't have SQL, and it has a rather embarrassing point of view on race conditions (https://docs.tiledb.com/main/basic-concepts/consistency https://docs.tiledb.com/main/basic-concepts/consistency). It could be a good choice for applications that rely on large and simple multidimensional arrays, hardly the same "market" as application file formats. Moreover, database tables are a very good fit for sparse multidimensional arrays.
    • teleforce 6y ago
      Perhaps SQlite or any SQL engine is not a good idea for application file format [1],[2], or is it just bad for web applications? [1]https://news.ycombinator.com/item?id=23517663 https://news.ycombinator.com/item?id=23517663 [2]https://support.google.com/chrome/thread/17537877?hl=en https://support.google.com/chrome/thread/17537877?hl=en
  • airstrike 6y ago
    That's not at all similar it doesn't support SQL
• zubairq 6y ago
  Yeah you can use SQLite as a file format. We use it to store our project data at https://github.com/zubairq/pilot https://github.com/zubairq/pilot
• tonyedgecombe 6y ago
  Maybe. I've used XML as a file format for all my recent projects. It's well supported in the .Net framework, I don't need any 3rd party assemblies. Using SQL to write out an object graph would involve more work.
  • simias 6y ago
    A tree I could understand since after all XML uses a tree-like structure, but for a graph you'll need to store references anyway so flattening and denormalizing it completely doesn't sound like a lot more work to me. Also XML makes me weep and I'll never willingly opt to use it if there's an alternative, but that's my own prejudice.
  • 737maxtw 6y ago
    Depends on the size or structure of the data. If you're having to do things like updates over a large dataset, SQLITE can be nice because of the performance boosts from indexing.
  • gwbas1c 6y ago
    It depends on how large and complicated your XML file is. Once your XML file hits a certain size, minor updates incur a gigantic performance penalty because you have to write out the entire XML file every time. Random writes in the middle of an XML file are impossible. For example, if you were to change an attribute so that it's one character longer, you still have to rewrite the remainder of the file in order to shift everything over by one character. That's the main reason why SQLite is so popular for applications. (I know that's from personal experience. I had to support an old version of an application that constantly wrote out XML while the new version that we hadn't shipped used SQLite. A customer that made heavy use of the old version basically hit the limits of XML but the version that used SQLite wasn't ready for them yet.) But, I'm going to be honest here: We had some tables that only had a few rows, so I moved those to XML files. QE really liked it because it was easy to diagnose issues.
    • tonyedgecombe 6y ago
      I think it depends on whether you need a file format or a database. All the benefits of a database are wiped out if you are implementing File->Save for a document based application.
• rb808 6y ago
  I keep switching between csv, parquet and sqlite. I dont need another article to encourage me to switch again! :)
• dragly 6y ago
  From experience, I can also recommend using SQLite as an application file format. I landed on SQLite after looking for solutions for a file format for an educational app we made for simulating biological neural networks. The app is cross-platform, written in Qt and the simulations needed to be stored as a JSON describing the network, a thumbnail and some metadata. It was also intended to be extensible with more features and backwards compatible if new versions were released. I considered creating our own simple format, using ZIP files, HDF5, Qt resource files or SQLite. I landed on SQLite for many of the reasons outlined in this article and in particular because of how easy it was to implement and maintain. SQLite is supported natively in QtSql, which made it extremely easy to write the save and load functions, and later extend these with more data fields. In addition, we did not have to worry about cross-platform support since this was covered by SQLite and Qt already.
  • bob1029 6y ago
    It sounds like future schema changes may be a potential concern for your application. One thing you can look into using is the SQLite user_version pragma. We use this right now to roll our own migrators and it's light years better than how migrators work for Entity Framework, et. al. https://www.sqlite.org/pragma.html#pragma_user_version https://www.sqlite.org/pragma.html#pragma_user_version
  • radarsat1 6y ago
    Interesting, I used HDF5 in a similar situation because we needed to save a lot of same-sized rows of data (simulation time steps), so a matrix-oriented format seemed to make sense but it wasn't entirely without some need for cross-referencing between tables, so it does make me wonder now if sqlite would have been a comparable or better choice. Any reason for rejecting HDF5 in your case?
    • st1ck 6y ago
      Is it cheap to insert/update/delete data in HDF5? If not, that should be the answer. I'm also curious if Parquet would fit your requirements.
• rlpb 6y ago
  Would such an app hold the sqlite database open while the user has the document "open", and live-write user changes back to the database immediately? Or would it follow the traditional model of the user choosing to "Save"? I worked on an app that did the former many years ago (to an Access database, not sqlite), and it did not go well because this broke user expectations on the usual "open/save/save as" model.
  • pmilot 6y ago
    The save model at the UX level is completely orthogonal to the application file format. You could implement either model with an SQLite DB file.
  • mkl 6y ago
    I think it would depend on the app (e.g. how document-like it is), but nowadays people will be much more used to the instant-save model due to things like Google Docs, OneNote, and phone apps.
  • catalogia 6y ago
    I think the former is preferable and most modern users will prefer it. However if your users are upset about it, you could do the later, or you could do the former and give them a dummy/placebo "save" option that does nothing, or maybe VACUUMs the DB.
    • mark-r 6y ago
      The problem is not Save, the problem is when you close the app without using Save - the user will expect that to work like Cancel and discard all session changes.
  • EvilTerran 6y ago
    I guess you could send changes to the DB on-the-fly inside a transaction, so when the user clicks "save" it's just a matter of running COMMIT? Not sure what effect that would have on performance as the transaction grows, though.
  • yellowapple 6y ago
• praveen9920 6y ago
  The article has great points. Main reason for existence of lot of file formats is that enterprises don't want just about everyone access their files and modifying them. It greatly reduces the usage of their proprietary software hence their revenues. I remember the days when open office trying to render doc file but formatting used to suck big time. Open source softwares should leverage this kind of file formats for inter operating.
  • agumonkey 6y ago
    it's the old business paradox, if everything was totally lean and solved, how do people earn a living ? you need a strong force to overcome this hurdle and show how to reorganize the system in the large
  • catalogia 6y ago
    > Main reason for existence of lot of file formats is that enterprises don't want just about everyone access their files and modifying them. It greatly reduces the usage of their proprietary software hence their revenues. I'm sure this does happen, but it seems more like MBA-paranoia than a legitimate concern. For instance I sincerely doubt Adobe has lost any revenue by using SQLite with lightroom, despite various open source tools being able to interact with their lrcat (sqlite) files.
• boudewijnrempt 6y ago
  I was surprised, but also intrigued when I discovered that Manga Studio (Clip Studio these days) used sqlite for the native file format, including the pixel planes. I wish my predecessor at Krita had made that choice, instead of choosing to re-use the KOffice xml-based file format that's basically a zip file with stuff in it. It would have made adding the animation feature so much easier.
  • im3w1l 6y ago
    What's wrong with xml + zip?
    • 7thaccount 6y ago
      I don't personally know anything wrong with it, but SQL queries are powerful powerful powerful, so although XML might not have any huge issues, in my personal opinion it is still sub-optimal compared to what is possible with SQL (at least for my uses, which could be very different to yours so take with a grain of salt). In particular, SQLite is a tiny executable with a HUGE amount of tests and is used very widely, so reliable for a lot of tasks as long as the data size stays relatively small and you caste your types properly. SQLite also has a lot of tutorials and books and language support. I've used it with Python, C#, Perl, TCL, and Powershell with no issues. You can access it via the command-line or you can hook into it with a fully graphical SQL IDE like DB-Visualizer (I really recommend using an IDE for interactive SQL use). If your language doesn't have built-in or library support, even a novice programmer like myself can roll a few functions together to build the tables, update, delete, and run queries to analyze the data if you can run some system commands. It's a wonderful little technology that I feel comfortable reaching to when I need it. One thing I've shied away from over the years are technologies which require running complex installers as it makes things more confusing and makes it harder for me to share with colleagues that aren't as interested in programming. Both SQLite and DB-Visualizer require zero installation. I just put each in a folder and then run the executable. This is really easy to use to me and easy to get others started too. Note that this is not commercial software, but internal business apps to help people do complex tasks easier. So you have a script that does some data processing pushes that data to SQLite and then the user can bring up DB-VISUALIZER, point it to the little SQLite .db file I created and then get to work. We have a lot of little apps like this and since most of our engineers are really good with SQL, they can do whatever they need efficiently.
• juskrey 6y ago
  Years pass, now decades, yet SQLite is still the most pleasant DB to work with.
  • TedDoesntTalk 6y ago
    Agree, but it also side-steps some complex topics like sharding, replication, high-availability, etc. I’m not suggesting those topics should be addressed by SQLite; that simplicity is an asset. Has anyone used SQLite remotely over a network?
    • trashburger 6y ago
      I think the main reason SQLite doesn't have them is because it's not a goal for the project. SQLite would be monumentally more complex if it supported networking, sharding, etc. If you need those use cases then perhaps it's a good idea to switch to a more scalable DBMS. My favorite is PostgreSQL. If I'm building something which will have user-generated content then I always pick Postgres. Otherwise, SQLite.
    • easytiger 6y ago
      It lives in your process. It has no network features. It's not a DBMS. People have built layers over it though
    • rlpb 6y ago
      Dqlite exists - it's SQLite with some of these feature added. https://dqlite.io/ https://dqlite.io/
    • mjw1007 6y ago
      When Subversion changed their working copy format to rely on an SQLite database, I had a number of problems using working copies on network file systems. No doubt this was an indication that the network file system was incorrectly configured, but I think the fact that it worked in practice with the file-based format and didn't with the SQLite format is a strike against the idea of using SQLite for what the user sees as saving a file.
• michaelbuckbee 6y ago
  IIRC one of the attempts to deliver on "Github but for Design files" used SQLite as an app file format.
• twarge 6y ago
  Quickbooks for Mac uses sqlite for its file format and this allowed users to query the books with any SQL browser! Alas, they encrypted the file starting two years ago.
• adamnemecek 6y ago
  I have been thinking about this as well lately. For my app (http://ngrid.io http://ngrid.io) I'm using a custom built Entity-Component-System (https://en.wikipedia.org/wiki/Entity_component_system https://en.wikipedia.org/wiki/Entity_component_system) based UI framework in Rust. In the ECS paradigm, everything is stored as struct-of-arrays as opposed to array-of-structs (https://en.wikipedia.org/wiki/AoS_and_SoA https://en.wikipedia.org/wiki/AoS_and_SoA) and as a result, your serialization becomes trivial. You are not chasing pointers to other objects when serializing. Apple's Core Data takes the object graph approach and it's a real clusterfuck. ECS is very similar to databases in that your data is normalized and things are referenced by offsets rather than pointers https://floooh.github.io/2018/06/17/handles-vs-pointers.html https://floooh.github.io/2018/06/17/handles-vs-pointers.html). Why roll your own thing as opposed to use say SQLite? If you use SQLite, you will probably have some OOP on top of it which introduces a serious impedance mismatch. With ECS, you cut that whole layer out. I'm guessing that I'm losing on some atomicity but for my use case that doesn't matter that much.
  • lxdesk 6y ago
    ECS is useful for real-time, but it means that you are rolling your own query logic. This is fine for the runtime of game engines because they generally aren't dealing with that many data types(for the very most complex AAA games, perhaps a few hundred components) and the queries used at runtime are simple and tend to demand optimization at the data structure level. If your purpose is an editing tool you may want to reconsider. Editing changes the goal in a very substantial way and the complexity of your queries goes way up, which is where SQL syntax absolutely shines.
    • TeMPOraL 6y ago
      FWIW, a game I'm writing uses in-memory SQLite as its ECS, mostly because I wanted to see if I could. SQLite is surprisingly performant. My game is not realtime, but I tested it, and I could actually redraw the screen at 60+ FPS while querying for render data every frame, and I still had lots of frame time to spare.
    • adamnemecek 6y ago
• ori_b 6y ago
  Please no. This is an incredibly complicated file format that is not particularly well suited for file saving. It's also very slow ((edit: correction) ~10 transactions per second) if you aim for integrity, or unsafe if you tune for performance (~50k transactions per second, but if your program or computer dies half way, your file is hosed). So, keeping files up to date in place makes your ui janky. You can work around it by working in memory and writing out a whole new database from in memory structures on save and then do the atomic rename. But if you do that, you are probably better off with json, protobuf, or similar. The libraries around these formats are similarly battle tested, but they fit the needs better, supporting working in ram fully and then saving cleanly and easily.
  • tzs 6y ago
    > Please no. This is an incredibly complicated file format that is not particularly well suited for file saving. It's also very slow (~100 transactions per second) if you aim for integrity, or unsafe if you don't. The kind of application files they are talking about (things like word processor documents, spreadsheets, drawings, source code control system data) would only be writing sporadically. During one of those sporadic writes they might need to update thousands of rows but those could all be done in one transaction.
    • ori_b 6y ago
      I made a mistake -- it's an order of magnitude slower. That means a single transaction is within the threshold for human perception. If you block the UI thread on a transaction, you're now dropping 6 frames at 60fps. If you block other operations on the transaction completing, you end up with lag. Reference: https://www.sqlite.org/faq.html https://www.sqlite.org/faq.html, question 19. (I've seen similar when testing on SSDs locally).
      • lostmyoldone 6y ago
        If you have an FPS or interactivity target that needs to be guaranteed to be better than about 0.1-1 second - depending on platform - you can't have any disk IO at all on the UI thread, or any thread that's supposed to react at interactive rates at all times. It has generally become much better in the last decade or two, but one should still expect most OS's to sometimes pause for excessive amounts of time on disk IO unless the API is specifically guaranteed to never pause. Even then one would be wise to measure/log deviations if it's critical for the application. OS guarantees might also be contingent on driver/subsystem guarantees, and bad drivers might sometimes affect what seems completely unrelated upstream systems.
• bstar77 6y ago
  Is anyone doing this in Javascript (via electron) and encrypting the file in such a way that the data is protected and not an obvious sqlite db? I have some ideas on how to do this, but I'm curious if there's a "preferred" way to do it.
  • gregmac 6y ago
    Why would you want to "protect" the data in this way? It's user-hostile, and anyone determined enough will be able to get at it no matter what you do. If it's popular and valuable enough, instructions and/or code to break it automatically will then be published, regardless of how much money or time you invested into the protection. (For proof, look no further than the game industry's DRM over the past 30 or so years)
    • bstar77 6y ago
      It's not meant to be user hostile, it's for a game. I allow data exports, but I don't want to expose the inner workings of the game via direct access to the DB.
      • Vendan 6y ago
        You do realize, exposing the "inner workings of the game" seems to be at least somewhat responsible for massively increasing the appeal of the game in other cases? see Minecraft, Rimworld, KSP as a few examples...
        • serf 6y ago
          ... see Minecraft, Rimworld, KSP as a few examples... I mean, I agree that games should be moddable, but not from the premise of game profitability and popularity. The highest-grossing video game franchise list is over-ran by products that are , pretty much, famously unmoddable.[0] [0]: https://en.wikipedia.org/wiki/List_of_highest-grossing_video_game_franchises https://en.wikipedia.org/wiki/List_of_highest-grossing_video...
• TheRealPomax 6y ago
  The great thing about applications that use sqlite as their application file format is that you can write secondary apps and utilities to supplement the applications themselves. For example, Adobe's Lightroom uses sqlite as their application file format, which means that it's almost trivial to write an application to help it do things it's either really bad/exceedingly slow at (like removing images from a catalogue that no longer exist on disk) or literally can't do (like generating a playlist of files-on-disk for a specific collection or tag), without being locked into whatever janky scripting solution exists in-app. If there even is one to begin with. All you need is a programming language with a sqlite connector, and you're in the driving seat. And sure, you'll need to figure out the table schemas you need to care about, but sqlite comes with sqldiff so it's really easy to figure out which operations hit which tables with only a few minutes of work. Good luck reverse engineering proprietary file formats in the same way!
  • TheRealPomax 6y ago
    Heck, I wrote https://github.com/Pomax/lightroom-catalog-helper https://github.com/Pomax/lightroom-catalog-helper exactly because lrcat is an open format that takes barely any time to figure out, which means the only limit to what you can do with "lightroom" is what your programming skills allow for.
  • mongol 6y ago
    I tried to do that with a Firefox SQLite database files but it had an exclusive lock.
    • TheRealPomax 6y ago
      This is the case for any application that constantly writes to their dbs, so you usually run your own application/utility on them when the "parent" application isn't running.
    • simonw 6y ago
      You can usually work around that by creating a copy - I run "cp /path/to/locked.db /tmp" all the time for this.
• catalogia 6y ago
  Is there any good way to use SQLite as an application file format while also using it in WAL mode? They seem sort of mutually exclusive; near as I can figure the way to do this would be to execute `PRAGMA journal_mode=DELETE` when the user requests a save file, then copy the DB to the location the user specifies. I think this negates a few of the advantages of using sqlite as an application file format though.
  • Redoubts 6y ago
    I think the backup api or VACUUM INTO are the preferred ways, though you're gonna probably want to turn off wal on the exported copy still.
    • HelloNurse 6y ago
      Shrinking the database is the equivalent of saving the file when the application shuts down or saving an extra copy to make a backup.
• corysama 6y ago
  “Atomic transactions” is a feature needs formal support in random file formats way more often than people realize. Simply writing to a file at all in an guaranteed-atomic way is much harder than it looks. That guarantee becomes important when your app gets widely distributed. If you have a million users of your free mobile app, 1 in a million events happen every day. For example: random hardware shutdown midway through a file write operation. How is your app going to react when it reads back that garbled file? I’ve used SQLite on mobile apps to mitigate this problem. I’ve used LMDB on a cloud app where the server was recording a lot of data, but also rebooting unexpectedly. Would recommend. I’ve also gone through the process of crafting an atomic file write routine in C. https://danluu.com/file-consistency/ https://danluu.com/file-consistency/ It was “fun” if your idea of fun is responding to the error code of fclose(), but I would not recommend...
  • dfox 6y ago
    I second the recomendation of LMDB. With one important caveat: under heavy write load it is perfect demonstration of brokenness of semaphore implementation on freebsd and macos.
    • skrebbel 6y ago
      In what way is LMDB better than eg SQLite or Redis? For what kinds of use cases would you recommend it?
      • corysama 6y ago
        It's very simple. Single C file implementation. Binary blob keys : binary blob values. The end. If all you need is a bunch of blobs written and read back reliably while in a statistically unreliable situation, LMDB is great.
        • namibj 6y ago
          Well, sqlite is also just a single .c with a single .h.
• em500 6y ago
  A lot of well known apps are exactly doing this, using a sqlite db as the internal file format: Whatsapp, WeChat, Google Keep, Apple iMessage, Notes and Calendar... Probably tons more if you care to dig around.
• ris 6y ago
  Amongst all the praise we're seeing for this approach here, may we please just have a minute's silence for the various exploits sqlite has seen when loading arbitrary database files over the years? My recent favourite being https://media.ccc.de/v/36c3-10701-select_code_execution_from_using_sqlite https://media.ccc.de/v/36c3-10701-select_code_execution_from...
  • empthought 6y ago
    This is a security flaw of the system or application using SQLite, though. It is trusting data it should not trust and executing code provided with that data.
    • skrebbel 6y ago
      Huh? If you're using SQLite as an application file format, isn't opening untrusted files a key use case? What else, never share files with people? That must be a pretty boring application.
      • empthought 6y ago
        Sure but just like a web browser, it’s the application’s responsibility to sandbox or verify the code, not the http library’s.
        • skrebbel 6y ago
          That translates to "don't use SQLite", right? How would an application that uses SQLite as a file format be able to scan for malicious database files that trigger buffer overflows in the SQLite engine? I'm really not sure what you're suggesting.
          • empthought 6y ago
            RTFM https://www.sqlite.org/security.html https://www.sqlite.org/security.html
        • 6y ago
• gjsman-1000 6y ago
  I worked with an SQLite Database on a personal C#/WinForms app. It was a bit complicated to get started, but afterwards, what a handy trick in comparison to using something like the discontinued SQL Server Compact. I did, however, "enhance" it a bit (or "proprietarize" it) by encrypting it with a short password and an AES algorithm with some uncommon settings. I never shipped it that way, but the output file looked like any other proprietary app's file - a mess of random symbols.
• booleandilemma 6y ago
  SQLite is a database that tries so hard to not be a database :)
• GnarfGnarf 6y ago
  I just converted a 90's-vintage xBase app to SQLite. I just love it. Should have done this years ago. The only thing I miss is the ability to jump X% into an index. Not a deal-breaker, though.
• 6gvONxR4sf7o 6y ago
  I used to advocate for this, then tried opening a sqlite file I'd been using on another computer and it said it was corrupted. I took it back to the original computer and it opened just fine. So there was something going on that made it more complex and less portable than something simple like json, which is super frustrating because otherwise it's a really useful format for datasets, and now I can't really trust it.
  • curiousmindz 6y ago
    If you use certain new features of SQLite, the database file gets "upgraded". At that time, you must use a version of SQLite that understands these new features. So, the other computer you used was probably running an older version of SQLite. Just update it to make it work.
    • bollockitis 6y ago
      That’s a good catch. I’d there any way to tell which version of SQLite created the file? Aside from trial and error, I mean?
      • lbutler 6y ago
        The version is stored in byte 96 of the header of the sqlite file[1]. I haven't looked but there will be some sqlite command to query it and I'm sure some viewer tools will display it as well. [1] https://www.sqlite.org/fileformat.html https://www.sqlite.org/fileformat.html
        • pjscott 6y ago
          You can print the version with this magical incantation: $ python -c 'import sys, struct; print(struct.unpack(">I", open(sys.argv[1], "rb").read()[96:100])[0])' foo.db
      • banana_giraffe 6y ago
        Recentish versions of file can show you the version: $ file example.db example.db: SQLite 3.x database, last written using SQLite version 3007017
• hexmiles 6y ago
  are there any other "file format oriented" data storage system?? I see software tend to use zip file, sqlite or good old ole structured storage. In my job we used a fat filesystem as a storage system and recently switched to sqlite, and while i love it i didn't really find any alternative.
• mstade 6y ago
  SQLite is amazing – trivial to embed in almost every context I've tried, rock solid, incredibly flexible, performs well enough for most use cases and for the others it probably still performs better than the solution I had in mind. I wouldn't dream of using anything else for application data, unless you need something text based for human readers as well, and can't deal with having people use a SQLite client. And even then, really consider whether you need this. With SQLite embeddable in websites thanks to wasm, and the ability to create object URLs it's also pretty trivial to make full blown (read-only) SPAs delivered as a single HTML file. This last bit might seem crazy – and it kind of is – but if your clients are all on a LAN in a corporate network so bandwidth and latency aren't really an issue it makes a bit more sense. I love SQLite, hands down one of my favorite tools.
• polyrand 6y ago
  I think SQLite is underrated. With WAL mode + increasing the page cache you can get some excellent concurrency, even if doing reads and writes at the same time. With rqlite it's easy to make it a server database and have a cluster of SQLite databases (https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite). I wouldn't try to create the new Instagram with it, but I think it'd be capable enough for many apps that are built on top of more complex DBs.
  • edwinyzh 6y ago
    You are right! In our Delphi/Pascal community we have https://github.com/synopse/mORMot https://github.com/synopse/mORMot, which is a full client/server DB, remote ORM and RPC system based on top of SQLite.
• nathan_f77 6y ago
  I've been reading through many of the other pages on the SQLite website. I didn't realize that SQLite is developed by a company (Hwaci) and funded by selling support, licenses, and testing services. I had always assumed it was an open source project backed by a non-profit, similar to the Apache Software Foundation. This is another very interesting example of an open-source business. I would be interested to learn more about how the Hwaci company operates (revenue, number of employees, etc.). I find this very interesting: > We are a 100% engineering company. There is no sales staff. Our goal is to provide outstanding service and honest advice without spin or sales-talk. They list some "$8K-50K/year" and "$85k" price tags directly on their "Pro Support" webpage. These would usually be behind a "Schedule a Call" or "Get Quote" button. I've been thinking about doing something similar with my own on-premise licenses and support contracts. I'm not very good at sales and I don't really want to hire a sales team, so I'd be interested to know how this worked out for them. I also liked this sentence, which is very similar to Basecamp's philosophy (and both companies were started around the same time - 1999 vs 2000): > Hwaci intends to continue operating in its current form, and at roughly its current size until at least the year 2050. It's interesting to think that SQLite could have raised money and grown into a billion-dollar public company with thousands of employees. I'm going to listen to this Changelog interview with Richard Hipp now [2], and also this talk on YouTube [3]. [1] https://sqlite.org/prosupport.html https://sqlite.org/prosupport.html [2] https://changelog.com/201/ https://changelog.com/201/ [3] https://www.youtube.com/watch?v=Jib2AmRb_rk https://www.youtube.com/watch?v=Jib2AmRb_rk
  • Varriount 6y ago
    As someone who has had to evaluate and recommended enterprise software for purchasing, a straightforward price tag is going to make me come your product in a better light. Plus, I have to wonder how much extra profit the "call us" route actually takes in, after you've subtracted costs for the marketing staff it requires (especially if you try to renegotiate the cost after the subscription/license expires).
  • TheRealPomax 6y ago
    Also important to note is that enterprise pricing is way higher than you'd think because when an enterprise needs a solution, something like $1000 is literally too cheap to justify the cost of procurement (which involves not just getting a license, but also internal documentation, which in turn needs to be entered into whatever process certification programme is in place, etc.)
• garganzol 6y ago
  Everything old is new again. I can recall a similar movement at the end of 90s regarding databases as application file formats. That time it was Microsoft Jet Database engine.
• barking 6y ago
  Are we talking about good old fashioned CRUD applications here or is this something different?
• renewiltord 6y ago
  GeoPKG is an open format that is just a SQLite schema. It's cool!
• pvg 6y ago
  Previously: https://news.ycombinator.com/item?id=18754634 https://news.ycombinator.com/item?id=18754634
• chrismorgan 6y ago
  How would SQLite go as for something like Audacity projects? Audacity projects are a pile of files: an XML file that declares the project metadata, tracks, channels, envelopes (an example of non-destructive editing), and references to the zillions of audio files that it drops in a directory beside the XML file (typically less than 1MB each). I presume it splits all the audio up into small files so that most types of edits can only need to touch a small area. If you directly ported that to SQLite, would it work fairly well, or would you want to restructure it somehow? Things like additions or deletions, would it need to write lots of extra data to the disk (would it be doing something like defragmenting, or would it grow larger than it should, or are there other tricks that I don’t know about to delete a chunk from the middle of a file without needing to rewrite all of the file beyond that point)?
  • catalogia 6y ago
    Adobe Lightroom seems comparable (non-destructive image editing.) It uses a sqlite database that points to image files on the filesystem. I think precisely the same approach could be taken with an audio editing application. You could also consider putting the audio files into the sqlite db, which might work alright. I've heard of image thumbnails being stored in sqlite dbs (maybe by Lightroom iirc?) though those are probably smaller than your audio clips I'm guessing.
• networkimprov 6y ago
  SQLite is a fine single-file application file format, but if you want average users to be able to inspect and perhaps fix application data when things go wrong, a tree of JSON-format files is more friendly. True, this requires care to ensure that such files are updated reliably, but that's not quite rocket science.
• wodenokoto 6y ago
  To my very limited knowledge it is very common for iOS and android apps to store data in SQLite. Is it possible to extract or change these databases? I have a few (offline) apps on my phone that I’d love to append data to
  • SonOfLilit 6y ago
    It's possible on Android if you have root access, I think. Android is very serious about apps not accessing each other's data.
  • muxator 6y ago
    As a sibling comment says, if you are on Android and have root access, it is very easy. Just have a look at /data/data/appname/. For example, this is what I copy to make backups of my contacts on my Android phone: > # pwd > /data/data/com.android.providers.contacts/databases > > # ls -1 > calllog.db > contacts2.db > profile.db This is a simple way for moving data around, restoring applications, performing backups, editing your data (if you know your way inside the app). Beware of the selinux labels if you are moving files across different devices, as recent android version now run with selinux in enforcing mode. Just be sure to adjust them (useful commands if you need a reference: ls -lZ, chcon, semanage).
  • kccqzy 6y ago
    For unjailbroken iOS the only way to extract these databases is to make an unencrypted device backup in iTunes (Finder in Catalina) and then look at the backup file. The backed up files won't be conveniently named but you can still find it.
    • wodenokoto 6y ago
      Can I push my edits back onto my iPhone or is that a pipe dream?
      • kccqzy 6y ago
        I suppose it's possible, but you need to study the way backups are organized and verified.
• zzo38computer 6y ago
  They say that Microsoft Word, PowerPoint, etc are "fully custom formats", but actually those formats are "wrapped pile-of-files formats" (and 7-Zip can open them), although the contents of the files inside are custom formats (and they seem to have failed to consider that category). The application ID number in the SQLite header can be used to identify application file formats. The application ID number is a 32-bit number, and there have been a few different ways to handle it; I have seen the use of hexadecimal and of ASCII; I used base thirty-six, and I have then later seen the suggestion to use RADIX-50. Additionally, there is a document about "defense against dark arts" in case you need to load untrusted files. TeXnicard uses a SQLite database file (with application ID 1778603844) for the card database file. The version control file (optional, and not fully implemented yet) uses a custom format (which is fully documented), and it does support atomic transactions. It consists of a header followed by a sequence of frames, which are key frames and delta frames. The header of the version control file contains two pointers: one to the beginning of the most recently committed key frame, and the other one to the end of the most recently committed frame (whether a key frame or a delta frame; if all frames are fully committed, this will be equal to the length of the file). These pointers are written only after the rest of the file is written; if it gets interrupted, reads will ignore the partially written data, and further writes will overwrite the partially written data. ZZ Zero uses a Hamster archive of custom (but documented) formats as its world file format. (A Hamster archive is zero or more "lumps" concatenated together. A lump consists of a null-terminated ASCII filename, 32-bit PDP-endian data size (measured in bytes), and then the data of that lump. The preceding text in these parentheses is the full definition of the Hamster archive format; you can use this to implement your own.) Free Hero Mesh uses a "pile-of-wrapped-pile-of-files" format. A puzzle set consists of four files: .class (which stores class definitions), .xclass (which stores pictures and sounds to be used by the class definitions), .level (which stores levels), and .solution (which stores solutions). The .class file is a plain text file; the other three are Hamster archives. These are four logically distinct parts of a puzzle set; this allows you to split them apart, to create symlinks to share class definitions with puzzle sets, to substitute your own graphics, to work with multiple solution sets (e.g. per user), etc. If you need to do more than that, then you can of course extract the lumps if needed. For class definitions, you can just copy and paste the text. MegaZeux used a fully custom format before, but now it uses a ZIP archive with the stuff inside being custom formats (one of which is the "MegaZeux Property List" format, which I have documented in Just Solve The File Format wiki; the authors of MegaZeux did not seem to document this format themself anywhere, so I figured it out and did it by myself). For some cases, SQLite database is a good application file format; other times, I think other formats (such as text formats) may be better. It depends on the application. XML is too often used for stuff that isn't text markup stuff, and XML is especially bad for stuff that isn't text markup stuff, I think. If you use SQLite though, you will get more than just the database access. It also gives you the string builder functions, the sqlite3_mprintf function, a page cache implementation, memory usage statistics, and a SQL interpreter; the SQL interpreter can be used as one way to allow user customization and user queries (including batch operations), without having to make an entirely new scripting language to embed. They mention interfaces of SQLite are available for many other programming languages, although at least one that doesn't seem to have a interface to SQLite is PostScript (although you can use %pipe%, it doesn't work so well especially since it is only a one way pipe), and I am not sure if awk has it either.
• me551ah 6y ago
  Where can you use SQLite? Embedded Systems: Yes Raspberry Pi : Yes Mobile Apps. : Yes Desktop Apps : Yes Browsers : No Servers : Yes Supercomputers : Yes
  • gwbas1c 6y ago
    You used to be able to use it in a browser with Google Gears. The reason why it was never adopted was because the browser makers wanted to be able to independently implement their own database instead of everyone having to use the same source code.
• trevor-e 6y ago
  While we are on the topic of file formats, I stumbled across a cool project the other day that aims to make parsing well-known binary formats much easier: http://kaitai.io/ http://kaitai.io/ In high school I was obsessed with the game Starcraft. It came with its own map editor that would save to its own proprietary format. Some smart people came along and reverse engineered that format and allowed us to do all kinds of neat things we weren't supposed to. I see modding communities for games are more popular than ever, and finding this project brought back lots of great memories.
  • mdaniel 6y ago
    I'm super-on-board with declarative binary parsing schemes (i.e. not lua or python) but my own attempt to use kaitai was met with the same frustration that presumably caused PDF to be missing from the format gallery list (http://formats.kaitai.io/ http://formats.kaitai.io/): jump or offset-based structures At the time, I tried using a stunt like defining one huge blob that "eats" the main file, then reaching back into it as we learned more, but it looks like somewhere along the way they acquired a "substream" behavior (https://github.com/kaitai-io/kaitai_struct_doc/blob/c53060f773acd80d9a27f7a199d03256d9763713/user_guide.adoc#choosing-a-substream https://github.com/kaitai-io/kaitai_struct_doc/blob/c53060f7...) so maybe it's worth another look
    • thechao 6y ago
      Can you explain more? I deal with a shocking number of poorly supported binary formats, and this tool looks awesome. Buuuuttt... a lot of these formats are “headers with offsets to structured data”. They’re intermixed logs, so multiple incompatible bi art formats are freely mixed in the same file.
      • mdaniel 6y ago
        I can most easily speak to the PDF example, which is harder than the target I was going after due to it being a mixture of text and binary, with a lot of optional behavior (EOL is CR or LF or CRLF seemingly at random, for one painful example) I took a few minutes just to kick the tires on the startxref of PDF, to get a feel for how the substream business plays out, and then stopped when I got to the part about how the offset position is written in a dynamically sized ascii string but represents an offset in the file meta: id: pdf file-extension: pdf endian: le seq: - id: magic_bytes contents: '%PDF-' instances: startxref_hack: type: startxref_hack0 pos: _root._io.size - 24 # pick a reasonable guess to wind backward size: 24 - 5 eof_marker: # contents: '%%EOF' type: str encoding: ASCII size: 5 # this isn't strictly accurate, # due to any optional CRLF trailing bytes pos: _root._io.size - 5 types: eat_until_lf: seq: - id: dummy type: u1 repeat: until repeat-until: _ == 0xA startxref_hack0: seq: # this isn't accurate, since we may have jumped # into "endobj\n" or worse - id: junk type: eat_until_lf - id: startxref_kw contents: 'startxref' - id: startxref_crlf type: eat_until_lf - id: startxref_offset type: str encoding: ASCII terminator: 0xA As best I can tell, the actual definition would involve a hypothetical `repeat: until-backward` where it starts at EOF (-5 in our case, due to the known EOF constant), reads backward until it hits LF (and/or CR!), captures that as the startxref offset, reads backward eating CR/LF, skips backward `strlen("startxref")` bytes, and then is when the tomfoolery starts about reading the "xref" stanza, which, again, is a ascii description of more binary offsets, using zero-prefix padded numbers because of course it does Don't get me wrong -- it's entirely possible that kaitai is targeting _strictly binary_ formats written by sane engineering teams, but the file format I was going after had a boatload of that jumping-around, repeating structs-of-offsets, too, so my holding up PDF as a worst-case example isn't ludicrous, either
• waynesonfire 6y ago
  What is an application file format?
  • detaro 6y ago
    The first section of the article explains it.
• Nihilartikel 6y ago
  Recently I've been using SQLite to store state for a data-syncing CLI utility in Python where I was considering using CSV or JSON at first. The Python 'Dataset' library, https://dataset.readthedocs.io/en/latest/ https://dataset.readthedocs.io/en/latest/ has taken almost all of the friction out of using SQLite for trivial state persistence. I don't have to manually manage schema or create tables - they're all lazily created from Dict keys on insertion, but indexes and SQL can still be used as desired. This lends itself really well to Jupyter notebook assisted development, where functions can be quickly and interactively iterated without having to muck around with existing tables whenever data changes shape. It's been a real productivity boost, and I've been looking around for something similar to use in Clojure.
  • vdm 6y ago
    Thanks for posting this, it's exactly what I was looking through this thread for.
• gwbas1c 6y ago
  One minor point that needs to be raised: Using SQLite as your application file format locks you into the SQLite source code. (And in most cases that's not a bad thing, it's free and open source.) But, if you truly want an open file format, someone needs to be able to independently write a program that can read your file without relying on third party dependencies. This is why the browser vendors decided not to put SQLite into the HTML and JavaScript standards. It is an awesome product! I've worked with it for over a decade and I'm a fan!
  • rswail 6y ago
    > it's free and open source It's more than "open source" it's in the public domain. The file format is fully documented and if someone like ISO or ANSI wanted to, they could make a standard out of it. It's also forward compatible since inception and versioned. The browser vendors decided not to put SQLite into their browsers because "key/value good, SQL bad" and "not invented here". IndexedDB is a clumsy reinvention with minimal ACID properties and isn't far advanced from ISAM. They could have used the SQLite file format and implemented IndexedDB as an API over the top. They could have allowed both standards to be implemented and then let reality take its course to choose which one was successful.
• tracker1 6y ago
  Concurrent access to SQLite is the main reason I haven't really looked into Deno. For that matter, I really think that WebAssembly file system implementations should have enough flexibility to be able to work with a SQLite database file in the host OS. The needs for concurrent SQLite pretty much covers the need for a robust file system.
• geff82 6y ago
  Haha!! I thought it was only me using sqlite as my go-to file format in apps.
• AtlasBarfed 6y ago
  Does SQLLite have a more text-friendly storage format? This would enable Text editors to do edits/modifications, which is critical to debugging and testing and learning. Savefiles don't need the same ongoing access optimizations, or size optimizations for wire protocols, so a less efficient format that is text editor friendly would help this cause. Also, does SQLLite have libraries in the native code for the major languages to read and write to them? XML (ugh), JSON, and YAML have managed to get decent implementations in almost all major languages.
  • HelloNurse 6y ago
    Using a text editor to mess with a SQLite database doesn't make any sense. Do you use text editors to edit, say, MPEG media? SQLite databases can be edited with with generic GUI and command line tools, both SQL-based and tabular editors, which are safer and more convenient than a text editor could ever be.
• chocolatkey 6y ago
  When I was reversing the XMDF E-Book format, I was surprised to find an embedded SQLite database... in an E-Book?? After reading this article, it makes more sense why someone would make that decision
• mythz 6y ago
  Where's a good place to find some useful public SQLite data sets?
• blackrock 6y ago
  Is SQLite able to handle multiple processes that want to read or write to the file database at the same time? Or nearly at the same time. They say that SQLite is more in competition with fopen, for file open, rather than a true RDMS system.