7 ms·

I love SQLite, but people approach it from a classic RDBMS angle which confuses them. Here's the deal: SQLite is a file format with a nice API that uses SQL as

by bane 7y ago

I love SQLite, but people approach it from a classic RDBMS angle which confuses them. Here's the deal: SQLite is a file format with a nice API that uses SQL as the paradigm for reading/writing to the file. That's it. Stop overthinking it. Can you write a microservice that stores its data in a big JSON file that you've built some code around to read/write to? Yes. It's just a file, but you have to build all the read/write methods. SQLite is not really any different, except the read/write work is already done, and you use SQL to format the data values and encode the read/write logic instead of the language you are working in. The file format has some cool extensions like text indexing, geospatial, etc. But it's no more a RDBMS than reading and writing a JSON file is. "But there's indexes!" Yes, just liked you might build an index on your JSON file and read that before reading the JSON file to know where things are faster -- and then you have to write all the code to do that. SQLite is just a file format, where you can also build indexes and all the code for that is already done for you. It's just a file format with an API that's similar to the ones you'd use for any regular old RDBMS and uses SQL as the domain language to read/write data. It's just a file format. Anything you can do/can't do with a file format you do with SQLite. It's just a file format. A nice convenient one that you are probably better off using than most other things for most purposes. It's just a file format. edit I'm glad this comment is getting such a response. I'm not trying to be mean, just help clarify thinking. Here's two thought experiments: 1) If SQLite didn't require you to use SQL as the read/write logic and was called "datalite", and instead just forced you to use C function calls, exactly like you would if you were working with literally any other file format on the planet, would you still be confused as to what it is? 2) Do you consider reading and writing to any other file format anywhere in the hierarchy of RDBMSs? Consider Python's csv module. Is that an RDBMS? Let's move away from tabular data, how about python-docx?

lucideer 7y ago
I'm not sure I understand your comment. What's the signifigance of it being "just a file format"? What are the aspects of "approaching it from a classic RDBMS angle" that are incompatible with it being "just a file format"? I've always seen it as "just a file format" myself, and I think I've always approached it "from a classic RDBMS angle", but I've never felt confused, and I don't see what I'm overcomplicating.
- jvanderbot 7y ago
  If you are not confused, this might be an indication that the comment will not provide any new information for you. If the comment provided no new information to you, it might be an indication you're not confused (and it was therefore not farted in your general direction)
- bane 7y ago
  > and I think I've always approached it "from a classic RDBMS angle" Have you tried to figure out where to install the server or asked what the system requirements were for it? Have you grown concerned that once the system moves into production the O&M team won't know how to operate "yet another database"? Do you spend agonizing hours trying to figure out if it supports multithreaded connection pools for multi-user writes? Have you wondered if your organization has the budget to add another DBA to the team if you add SQLite to your tech stack? If the answer is no to all of the above you aren't approaching it from a classic RDBMS angle. Believe it or not, there are tens of thousands of questions about SQLite struggling to figure out the answer to the above questions. The people asking these questions are not stupid, they're just approaching the technology from the wrong direction. This post is no different than "CSV is serverless" or "JSON is server-less" with a blog post about classic vs neo-serverless JSON technologies.
  - hartator 7y ago
    The original document is from SQLite documentation. I think it’s fair for them to make a case why sometimes a file database > a server database. People asking these questions are not stupid. We all have to start somewhere.
    - bane 7y ago
      Please check it again, I wrote "not stupid". The SQLite creators have a great deal of documentation that's targeting database administrators and users and trying to explain what this thing is, when I think they really should have just targeted people who need a nice file format and a clean API. But hell, there's like a trillion SQLite files in use so what do I know?
zachthewf 7y ago
I'm no SQLite expert but by this logic aren't all single-host databases that tick the "Durability" ACID checkbox "just file formats" in the sense that yeah, the bytes we care about exist somewhere in the filesystem? Moreover I'm having trouble coming up with things that I'd associate with a RDBMS and not "just a file format" that SQLite doesn't support. Transactions? SQLite has them. Relational constraints? SQLite has those too. Could you elaborate on some of the confusion that you've seen around this?
- bane 7y ago
  Look at the other comments just in this post.
- tracker1 7y ago
  edit - removed
  - deleted 7y ago
    [deleted]
  - simonw 7y ago
    The R in RDBMS stands for Relational, not Remote.
- bane 7y ago
  Sorry for the previous terse reply. Longer answer: SQLite files do not guarantee ACID compliance. You can write code tomorrow that produces SQLite files, and so long as you follow the specification (https://www.sqlite.org/fileformat.html https://www.sqlite.org/fileformat.html) it will be readable by any other code that implements the specification (e.g. https://sqljet.com/ https://sqljet.com/) An RDBMS is not a database, nor is it SQL, nor is it data. It is a kind of DBMS software that manages relational databases, and access to the data (such as users and user rights). Most modern RDBMSs run as servers and offer network connectivity, connection pooling, advanced buffering options, various memory usage schemes. Many have their own memory allocation and file handling routines that are separate from the OS. Some offer clustering, partitioning and so on. SQLite does not offer any of these things. If you were to write a comprehensive list of things that Oracle, MS SQL Server, DB2, PostgreSQL, MySQL and SQLite offer, SQLite would offer almost none of the features that the rest do. A relational database uses the relational model to store data. SQL is the most common language for describing what you want to put into or retrieve from the relational database, but it is not required. There are many kinds of databases. Some of them store data in memory, in a file, in multiple files, and so on. Some of them follow various models, some of them are unique. If you have the file format for a database that stores its data in files, you can read/write to the file freely without any management system and without ACID compliance. SQLite files are examples of a kind of database file that stores data using a relational model. So are MDB files that Microsoft Access uses. By conflating a file format with an RDBMS, it's like conflating a fork for a restaurant, or a chair for a house. ACID compliance is not something guaranteed by the file format. SQLite files do not guarantee ACID compliance. If you write some code tomorrow that can read/write SQLite files based on the spec, you haven't created and ACID compliant SQLite file, nor is your code ACID compliant. The SQLite library implements the properties that make SQLite ACID compliant. It does so by various clever means like a journal file format, and a write-ahead-log file format and various other well thought out approaches. If you were to write your own code that implemented the SQLite file spec, and you wished your code to also offer ACID compliance, you would have to implement those things yourself -- and you are under no obligation to use the SQLite journal and WAL file formats nor the internal logic that the SQLite library uses. You can do it entirely your own way!
bshipp 7y ago
I think most rmdbs systems are just a file (or set of distributed files) when you strip away all the helper functions. The fact that Sqlite squirrels everything into a single archive doesn't mean many of the abilities housed within a more comprehensive database aren't there. You can implement JSON columns and full text searching and numerous other fancy systems. The main argument I hear for why Sqlite deserves second class status in the DB marketplace is the difficulty in handling multiple writes simultaneously. To that point I'd say it's more of a simplicity in design choice. Search for 'DB race conditions' and you'll see that every database struggles with handling multiple writes nested inside complex transactions. Sqlite avoids the whole mess and requires the programmer to think through I/O instead of offloading all that logic to the rdbms software.
- bane 7y ago
  > The main argument I hear for why Sqlite deserves second class status in the DB marketplace is the difficulty in handling multiple writes simultaneously. SQLite is not in the DB marketplace. It's in the file format marketplace. It handles multiple writes in exactly the same way CSV handles multiple writes. If you want to handle multiple writes with SQLite, you handle it the same way as CSV.
  - bshipp 7y ago
    I disagree. CSVs don't have write-ahead logging and are not able to selectively lock portions file for writes. And I'd say that not only is sqlite in the db marketplace (albeit for a specific subset of database application types) it's one of the largest players.
    - bane 7y ago
      Why not? Just roll your own write-ahead log when you are writing to CSVs! That's all the SQLite code is doing. SQLite does not selectively lock portions of the file for writes. It locks the entire file using the Operating System's own file handling services. SQLite is a file format. CSVs, XML and JSON are also huge in the dB marketplace. That doesn't make them RDBMSs.
derefr 7y ago
Consider: it’s totally possible to strip down Postgres until all you have left is an embedded RDBMS of the style of SQLite. (I’m not sure why nobody has done this yet, actually.) Would you call the result “just a file format”? Such an instance of “embedded Postgres” would still have a huge sprawling catalog (PG_DATA) directory attached to each use of it, so it wouldn’t be contained to a single file. But neither is SQLite contained to a single file—SQLite maintains a journal and/or WAL in a second file. And, yes, this “embedded Postgres” would require things like vacuuming. But... so does SQLite. Have you never maintained an application that maintains a long-lived “project” as a single SQLite file, where changes are written into this project file repeatedly over a long period? (Think: the “library” databases of music/photo library management software.) SQLite database files experience performance degradation from dead tuples too, and need all the same maintenance. Often “database version migrations” of such software is written to either rewrite the SQLite file into a clean state, or—if it has the possibility of being too big for that to be a quick task—to call regular VACUUM-like SQL commands to clean the database state up. —— Now, I get what you’re trying to say; the point that you’re trying to make—that SQLite might be a relational database, but it’s not a relational database management system in the sense of sitting around online+idle where it can do maintenance tasks like auto-vacuuming. Unlike an RDBMS, SQLite doesn’t have its own “thread of execution”: it is a library whose functioning only “happens” when something calls into it. By analogy, regular RDBMSes are like regular OS kernels, while SQLite is like a library kernel or exokernel. But that doesn’t mean that SQLite is a file format! It can be used as one, certainly, but what SQLite is is exactly the analogy above: the kernel of an RDBMS, externalized to a library. As long as you “run” said kernel from your application, and your application is a daemon with its own thread of execution, then your application is an RDBMS. This can be seen most directly in systems like ActorDB, that simply act as a “transaction server” routing requests to SQLite. ActorDB is, pretty obviously, an RDBMS; but it achieves that not due to its own features, but 99% due to embedding SQLite. All it does is call into SQLite, which already has the “management system” part of an RDBMS built in, just not called unless you use it—just like exokernels often already have things like a scheduler, just not called into unless you as the application layer do so.
- faizshah 7y ago
  Great comment! Respectful of GP and constructively critical. The other thing I would mention is that SQLite can operate totally in memory which makes it useful without even using it to persist data (say you have a language with a slow dataframe API, just use SQLite in memory to process your data).
lazyant 7y ago
Sure. Or a light RDBMS where the main not supported case is concurrent writes.
- bane 7y ago
  No, SQLite provides no RDBMS functionality. It is not an RDBMS. It is a file format. Saying that a program that opens and reads/writes a file through a file format API is a light RDBMS turns almost every program in history into an RDBMS. If SQLite didn't force you to use SQL as the read/write logic, absolutely nobody would confuse it for an RDBMS. That's because it's a file format.
  - gwd 7y ago
    > No, SQLite provides no RDBMS functionality. I think your comment would be more comprehensible if you gave some examples of the kind of functionality you think is missing. I don't know what you mean by "RDBMS", but JSON and XML don't do joins, don't do views, don't do efficient query plans, and so on. It's either ignorance or obstinacy to say SQLite is just a file format.
    - bane 7y ago
      > ...but JSON and XML don't do joins, don't do views, don't do efficient query plans, and so on. It's either ignorance or obstinacy to say SQLite is just a file format. Sure they do. If you write the logic to do so, and put it behind a nice API, you can make all of this come true. In fact, millions of people every day do joins with JSON and XML in their code every day. You can probably just use Apache Drill as the "library" in this sense to facilitate joins and whatnot. The creators of the SQLite library simply build that stuff into their library for you. SQLite is a file format. It has a nice library full of wonderful utility functions for reading/writing that file format and a simple to use API that is operated by sending SQL to it. It is exactly the same as reading and writing any other file format with any other API and library. The more you understand SQLite as a file format with a nice reference API implementation, the more it makes sense. It is not the same as using an RDBMS and offers almost none of the things and RDBMS might offer. The more you try to figure out how it's not like PostgreSQL or Oracle or MongoDB, the more confused you'll make yourself. It's no more an RDBMS than a .docx file is.
andreareina 7y ago
In their own words: "SQLite does not compete with client/server databases. SQLite competes with fopen()." [1] https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
nickthemagicman 7y ago
If there's no Inter Process Communications and it doesn't use the OS to write to the filesystem...how does an application write to SQLlite db? I'm a noob and just curious.
- bane 7y ago
  It uses the OS to write to the filesystem. It just does some clever management of data needing to be written. You can build your own if you are clever enough to read write to your own file format too. If you want to build an RDBMS using SQLite as the core, you can. You can also do it using uncompressed WAV audio files if you are clever and hate yourself enough. To use SQLite in such a scenario you simply have to write the entire RDBMS minus the file handling routines. This includes a connection pooling mechanism and a single process to isolate the connection to the SQLite file so that the OS doesn't get angry when you try to have multiple things writing to it.
travbrack 7y ago
I've seen several software projects that are built for rdbms let you use sqlite. It works.
- bane 7y ago
  They're using SQLite as the file format for persisting data. It's a great file format for this. You can even build an RDBMS over top of uncompressed WAV files if you want. It doesn't make WAV files RDBMs.
  - travbrack 7y ago
    But SQLite has rdbms features. I remember being able to show tables and do SQL queries in SQLite DBs.
    - bane 7y ago
      Those are relational database features, not RDBMS features. The SQLite file format specifies a way to organize, store and retrieve fairly arbitrary data using a relational database model. The library knows how to handle SQL to describe the work being done. The SQL is optional, one can, with the specification, read/write SQLite files in many other ways. There are almost no RDBMS features in SQLite. There are many many other file formats that store data that offer features that are very similar to SQLite files: indexes, journaling, write logging, etc. Thought question, you can ask a .tar file to give you a listing of what files and directories are stored in the file. Are .tar files RDBMSs? Consider: 1 - If you consider each file in the .tar file a "table" you can get list of tables. 2 - If each file follows a regular format, say JSON, you can search the "tables" by extracting the file and grepping it or using jq or whatnot. 3 - You can store a special file that is an index of some kind that lets you know in which file some data is, or even where in the file it is. 4 - You can build logic such that when you want to do other CRUD operations you can record a journal and a write ahead log to help build in ACID compliance. 5 - You can build buffer logic to support Write-ahead-logging, transactions and what not to improve performance. Are .tar files RDBMSs? Trivially no. But maybe, if you do all these things, you've invented a terrible database and database engine. However, you need to build a server, user access controls, connection pooling, import/export tools, partitioning, clustering, etc. before you start to arrive at an RDBMS that uses this engine.
hartator 7y ago
MySQl, Postgres, or MongoDB still store your date in files. So there are also “just a file format”. You do have one extra step - the db server process - to access the files.
- bane 7y ago
  Yes! You've defined the difference between an RDBMS (the server process and whatever else it does) and the file format the data is stored in. SQLite is just a file format. If you want it served up over some kind of server, you have to build your own (and most people do), or use a server that somebody else has built for you (there's a couple out there).
  - goto11 7y ago
    SQLite is not "just a file format" anymore than say MS SQL Server is a file format. SQLite is a RDBMS in the form of a library and uses a particular file format for persistent storage. But it can also run purely in-memory: https://www.sqlite.org/inmemorydb.html https://www.sqlite.org/inmemorydb.html without any file at all. Being a RDBMS is not defined by whether the engine runs in-process or as a server in its own process.
hinkley 7y ago
I think you’re stuck in 90/10 rule territory here. But even so, SQLite was 220,000 lines the last time they measured, which was five years ago. You can do a lot of functionality in 22kloc, even ignoring the other 90%, which you shouldn’t Lodash, for instance, is much smaller than 22k lines, and it “just” manipulates objects and lists. If you downplay others like this, I wonder how you feel about your own work. Have you been working hard for years on something that “just” accomplishes a straightforward task? Are you happy? I know I wasn’t.
- bane 7y ago
  I'm not downplaying anybody's work. Many people approach SQLite as something in the RDBMS territory. It's not. Almost all of the confusion I've ever seen related to SQLite comes from starting from that basis. If one simply thinks of it as an alternative to fopen() then it makes very simple and intuitive sense. The people in this thread seem to be very resistant to this simple clarity of thought, but whatever, they can stay confused and keep coming up with feature comparisons of SQLite vs Redshift vs Elasticsearch or some such. If one were to draw a spectrum: file-format:<-x----------------------------->:DBMS SQLite is the x on this line and .txt files are about the only thing that any further left on it.
  - imtringued 7y ago
    There is no such spectrum. SQLite is a piece of software that implements some but not all commonly expected RDBMS features. Software is not a file format but software may be written with the expectation that a given file follows the requirements of a certain file format and the software may be written in a way that it produces files that follow the file format specification. Since SQLite - the software - is an RDBMS the files it produces can be considered to be databases.
    - bane 7y ago
      I just drew the spectrum. It exists now. SQLite is a file format. You can read/write SQLite databases without the SQLite library. You can write your own custom reader/writer/creator. You don't have to use SQL. You don't have to be ACID compliant. I can make for you right now a SQLite database that did not touch any SQLite software, put data into it and you can open it with another piece of software that implements the SQLite file format specification. Likewise, you can use the SQLite library software to create a SQLite database file, put data into it, and I can read it/update it using any other software that implements the SQLite file format specification. The SQLite library offers some very very basic features, such as ACID compliance, and so on, but those are not part of or guarantees of the file format or the database files. The software that you write that implements the SQLite file format specification does not have to do any of these things to work with or produce a valid SQLite file. An RDBMS is a kind of DBMS for managing relational databases and providing access to the databases (for example users and permissions). Modern RDBMSs offer extensive features (look at an Oracle or MSSQL Server spec sheet) that are not even hinted at with the SQLite library software. This is because SQLite is not an RDBMS, it's a file format.
nebulous1 7y ago
I've spent half an hour or so looking at this thread, and my take away is that you're largely just confusing matters even further. > It's just a file format. This is clearly incorrect. It does encompass a file format, but it also contains code to manage that file. The existence of sqljet does not change this, it's merely a different database management system that uses the same file format. You also seem to mostly ignore that the data it's managing is a relational database, not some other form of data. This is why you can't compare it to python's csv module or python-docx, neither of these do anything to restrict you to a relational data model, nor do they provide a system to query them as if they were a relational database. On the other hand, if for some unknown reason you rewrite the storage engine of postgresql to use the either the sqlite file format or csv/docx, I assume you agree it would still be an RDBMS. Ultimately I think you're just adding to the confusion by saying that a project with 139,000 lines of C code is "just a file format".
- szatkus 7y ago
  It's a file format with a very mature and nice library around it. At the end of a day it's just fwrite() and fread() with SQL syntax.
  - ldiracdelta 7y ago
    And perhaps python is just glorified assembly, but it seems to me to be a "difference in degree that leads to a difference in kind."
- bane 7y ago
  You can read and write sqlite files entirely without the library. The file format is available on the sqlite site. SQLJet proves the point that you don't need 139,000 lines of C code to use sqlite files.
  - goto11 7y ago
    I'm sure you could make a tool which could read and write to SQL Servers mdf files. That wouldn't change that SQL Server is a RDBMS.
jturpin 7y ago
Sqlite implements all of the features you would expect from a relational database, including indexes, transactions, write ahead logging, consistency, and provides a SQL wrapper on top of its file API. _This is what makes it a database and not just a file format_. To the client, whether or not the API is happening over a socket or a locally linked library is irrelevant. How connection pooling works is also not relevant to whether it is a database or just a file format. Calling Sqlite just a file format is kind of like calling Python "just a syntax specification." That's part of it, but we're talking about the actual implementation of it (probably CPython).
- bane 7y ago
  https://sqljet.com/ https://sqljet.com/
kgwgk 7y ago
> SQLite is a file format with a nice API that uses SQL as the paradigm for reading/writing to the file. <insert-your-favourite-relational-database-management-system-here> is a collection of bits with a nice interface that uses a query language as the paradigm for reading/writing data.
- bane 7y ago
  No, an RDBMS is a piece of software for managing databases in the relational model, access to the databases (such as users and permissions) and provides services such as a server, connection pooling, and so on. SQLite provides almost no RDBMS features. This isn't just semantics. A car is not an engine. A fork is not a kitchen. A SQLite file is not a DBMS.
  - imtringued 7y ago
    https://en.wikipedia.org/wiki/Database#Database_management_system https://en.wikipedia.org/wiki/Database#Database_management_s... >Connolly and Begg define database management system (DBMS) as a "software system that enables users to define, create, maintain and control access to the database".[24] >The functionality provided by a DBMS can vary enormously. The core functionality is the storage, retrieval and update of data. Codd proposed the following functions and services a fully-fledged general purpose DBMS should provide:[25] [x] Data storage, retrieval and update [x] User accessible catalog or data dictionary describing the metadata [x] Support for transactions and concurrency [x] Facilities for recovering the database should it become damaged [ ] Support for authorization of access and update of data [ ] Access support from remote locations [x] Enforcing constraints to ensure data in the database abides by certain rules Under this definition SQLite - the library - clearly is an RDBMS that leaves out some common features that do not make sense within its niche but is otherwise fully functional and under this definition the files that SQLite manages are the database, not just merely a file format.
    - bane 7y ago
      The zip utility. [x] Data storage, retrieval and update [x] User accessible catalog or data dictionary describing the metadata [ ] Support for transactions and concurrency [x] Facilities for recovering the database should it become damaged [x] Support for authorization of access and update of data [ ] Access support from remote locations [x] Enforcing constraints to ensure data in the database abides by certain rules Congratulations, apparently zip files are as much of an RDBMS as SQLite. If I bundle zip with ssh (Access support from remote locations) and Linux (Support for transactions and concurrency) did I just create a new RDBMS? How many checkboxes do I have to have in order to call anything an RDBMS? Is SSH an RDBMS (Access support from remote locations). Can I just put a catalog in a .txt file and check that box? Is XML an RDBMS because it enforces constraints and supports storage retrieval and update? Are chmod and chown an RDBMS because they support authorization of access and update of data? > SQLite manages are the database, not just merely a file format. It turns out databases can be just files. Those files must follow a described file format. SQLite files are relational databases that are instantiations of the file format specification for SQLite files. The SQLite library implements that file format as well as some clever logic to support SQL and ACID compliance. Some SQLite libraries do not support these things.

by bane 7y ago

lucideer 7y ago

I'm not sure I understand your comment. What's the signifigance of it being "just a file format"? What are the aspects of "approaching it from a classic RDBMS angle" that are incompatible with it being "just a file format"? I've always seen it as "just a file format" myself, and I think I've always approached it "from a classic RDBMS angle", but I've never felt confused, and I don't see what I'm overcomplicating.

jvanderbot 7y ago
If you are not confused, this might be an indication that the comment will not provide any new information for you. If the comment provided no new information to you, it might be an indication you're not confused (and it was therefore not farted in your general direction)
bane 7y ago
> and I think I've always approached it "from a classic RDBMS angle" Have you tried to figure out where to install the server or asked what the system requirements were for it? Have you grown concerned that once the system moves into production the O&M team won't know how to operate "yet another database"? Do you spend agonizing hours trying to figure out if it supports multithreaded connection pools for multi-user writes? Have you wondered if your organization has the budget to add another DBA to the team if you add SQLite to your tech stack? If the answer is no to all of the above you aren't approaching it from a classic RDBMS angle. Believe it or not, there are tens of thousands of questions about SQLite struggling to figure out the answer to the above questions. The people asking these questions are not stupid, they're just approaching the technology from the wrong direction. This post is no different than "CSV is serverless" or "JSON is server-less" with a blog post about classic vs neo-serverless JSON technologies.
- hartator 7y ago
  The original document is from SQLite documentation. I think it’s fair for them to make a case why sometimes a file database > a server database. People asking these questions are not stupid. We all have to start somewhere.
  - bane 7y ago
    Please check it again, I wrote "not stupid". The SQLite creators have a great deal of documentation that's targeting database administrators and users and trying to explain what this thing is, when I think they really should have just targeted people who need a nice file format and a clean API. But hell, there's like a trillion SQLite files in use so what do I know?

zachthewf 7y ago

I'm no SQLite expert but by this logic aren't all single-host databases that tick the "Durability" ACID checkbox "just file formats" in the sense that yeah, the bytes we care about exist somewhere in the filesystem? Moreover I'm having trouble coming up with things that I'd associate with a RDBMS and not "just a file format" that SQLite doesn't support. Transactions? SQLite has them. Relational constraints? SQLite has those too. Could you elaborate on some of the confusion that you've seen around this?

bane 7y ago
Look at the other comments just in this post.
tracker1 7y ago
edit - removed
- deleted 7y ago
  [deleted]
- simonw 7y ago
  The R in RDBMS stands for Relational, not Remote.
bane 7y ago
Sorry for the previous terse reply. Longer answer: SQLite files do not guarantee ACID compliance. You can write code tomorrow that produces SQLite files, and so long as you follow the specification (https://www.sqlite.org/fileformat.html https://www.sqlite.org/fileformat.html) it will be readable by any other code that implements the specification (e.g. https://sqljet.com/ https://sqljet.com/) An RDBMS is not a database, nor is it SQL, nor is it data. It is a kind of DBMS software that manages relational databases, and access to the data (such as users and user rights). Most modern RDBMSs run as servers and offer network connectivity, connection pooling, advanced buffering options, various memory usage schemes. Many have their own memory allocation and file handling routines that are separate from the OS. Some offer clustering, partitioning and so on. SQLite does not offer any of these things. If you were to write a comprehensive list of things that Oracle, MS SQL Server, DB2, PostgreSQL, MySQL and SQLite offer, SQLite would offer almost none of the features that the rest do. A relational database uses the relational model to store data. SQL is the most common language for describing what you want to put into or retrieve from the relational database, but it is not required. There are many kinds of databases. Some of them store data in memory, in a file, in multiple files, and so on. Some of them follow various models, some of them are unique. If you have the file format for a database that stores its data in files, you can read/write to the file freely without any management system and without ACID compliance. SQLite files are examples of a kind of database file that stores data using a relational model. So are MDB files that Microsoft Access uses. By conflating a file format with an RDBMS, it's like conflating a fork for a restaurant, or a chair for a house. ACID compliance is not something guaranteed by the file format. SQLite files do not guarantee ACID compliance. If you write some code tomorrow that can read/write SQLite files based on the spec, you haven't created and ACID compliant SQLite file, nor is your code ACID compliant. The SQLite library implements the properties that make SQLite ACID compliant. It does so by various clever means like a journal file format, and a write-ahead-log file format and various other well thought out approaches. If you were to write your own code that implemented the SQLite file spec, and you wished your code to also offer ACID compliance, you would have to implement those things yourself -- and you are under no obligation to use the SQLite journal and WAL file formats nor the internal logic that the SQLite library uses. You can do it entirely your own way!

bshipp 7y ago

I think most rmdbs systems are just a file (or set of distributed files) when you strip away all the helper functions. The fact that Sqlite squirrels everything into a single archive doesn't mean many of the abilities housed within a more comprehensive database aren't there. You can implement JSON columns and full text searching and numerous other fancy systems. The main argument I hear for why Sqlite deserves second class status in the DB marketplace is the difficulty in handling multiple writes simultaneously. To that point I'd say it's more of a simplicity in design choice. Search for 'DB race conditions' and you'll see that every database struggles with handling multiple writes nested inside complex transactions. Sqlite avoids the whole mess and requires the programmer to think through I/O instead of offloading all that logic to the rdbms software.

bane 7y ago
> The main argument I hear for why Sqlite deserves second class status in the DB marketplace is the difficulty in handling multiple writes simultaneously. SQLite is not in the DB marketplace. It's in the file format marketplace. It handles multiple writes in exactly the same way CSV handles multiple writes. If you want to handle multiple writes with SQLite, you handle it the same way as CSV.
- bshipp 7y ago
  I disagree. CSVs don't have write-ahead logging and are not able to selectively lock portions file for writes. And I'd say that not only is sqlite in the db marketplace (albeit for a specific subset of database application types) it's one of the largest players.
  - bane 7y ago
    Why not? Just roll your own write-ahead log when you are writing to CSVs! That's all the SQLite code is doing. SQLite does not selectively lock portions of the file for writes. It locks the entire file using the Operating System's own file handling services. SQLite is a file format. CSVs, XML and JSON are also huge in the dB marketplace. That doesn't make them RDBMSs.

derefr 7y ago

Consider: it’s totally possible to strip down Postgres until all you have left is an embedded RDBMS of the style of SQLite. (I’m not sure why nobody has done this yet, actually.) Would you call the result “just a file format”? Such an instance of “embedded Postgres” would still have a huge sprawling catalog (PG_DATA) directory attached to each use of it, so it wouldn’t be contained to a single file. But neither is SQLite contained to a single file—SQLite maintains a journal and/or WAL in a second file. And, yes, this “embedded Postgres” would require things like vacuuming. But... so does SQLite. Have you never maintained an application that maintains a long-lived “project” as a single SQLite file, where changes are written into this project file repeatedly over a long period? (Think: the “library” databases of music/photo library management software.) SQLite database files experience performance degradation from dead tuples too, and need all the same maintenance. Often “database version migrations” of such software is written to either rewrite the SQLite file into a clean state, or—if it has the possibility of being too big for that to be a quick task—to call regular VACUUM-like SQL commands to clean the database state up. —— Now, I get what you’re trying to say; the point that you’re trying to make—that SQLite might be a relational database, but it’s not a relational database management system in the sense of sitting around online+idle where it can do maintenance tasks like auto-vacuuming. Unlike an RDBMS, SQLite doesn’t have its own “thread of execution”: it is a library whose functioning only “happens” when something calls into it. By analogy, regular RDBMSes are like regular OS kernels, while SQLite is like a library kernel or exokernel. But that doesn’t mean that SQLite is a file format! It can be used as one, certainly, but what SQLite is is exactly the analogy above: the kernel of an RDBMS, externalized to a library. As long as you “run” said kernel from your application, and your application is a daemon with its own thread of execution, then your application is an RDBMS. This can be seen most directly in systems like ActorDB, that simply act as a “transaction server” routing requests to SQLite. ActorDB is, pretty obviously, an RDBMS; but it achieves that not due to its own features, but 99% due to embedding SQLite. All it does is call into SQLite, which already has the “management system” part of an RDBMS built in, just not called unless you use it—just like exokernels often already have things like a scheduler, just not called into unless you as the application layer do so.

faizshah 7y ago
Great comment! Respectful of GP and constructively critical. The other thing I would mention is that SQLite can operate totally in memory which makes it useful without even using it to persist data (say you have a language with a slow dataframe API, just use SQLite in memory to process your data).

lazyant 7y ago

Sure. Or a light RDBMS where the main not supported case is concurrent writes.

bane 7y ago
No, SQLite provides no RDBMS functionality. It is not an RDBMS. It is a file format. Saying that a program that opens and reads/writes a file through a file format API is a light RDBMS turns almost every program in history into an RDBMS. If SQLite didn't force you to use SQL as the read/write logic, absolutely nobody would confuse it for an RDBMS. That's because it's a file format.
- gwd 7y ago
  > No, SQLite provides no RDBMS functionality. I think your comment would be more comprehensible if you gave some examples of the kind of functionality you think is missing. I don't know what you mean by "RDBMS", but JSON and XML don't do joins, don't do views, don't do efficient query plans, and so on. It's either ignorance or obstinacy to say SQLite is just a file format.
  - bane 7y ago
    > ...but JSON and XML don't do joins, don't do views, don't do efficient query plans, and so on. It's either ignorance or obstinacy to say SQLite is just a file format. Sure they do. If you write the logic to do so, and put it behind a nice API, you can make all of this come true. In fact, millions of people every day do joins with JSON and XML in their code every day. You can probably just use Apache Drill as the "library" in this sense to facilitate joins and whatnot. The creators of the SQLite library simply build that stuff into their library for you. SQLite is a file format. It has a nice library full of wonderful utility functions for reading/writing that file format and a simple to use API that is operated by sending SQL to it. It is exactly the same as reading and writing any other file format with any other API and library. The more you understand SQLite as a file format with a nice reference API implementation, the more it makes sense. It is not the same as using an RDBMS and offers almost none of the things and RDBMS might offer. The more you try to figure out how it's not like PostgreSQL or Oracle or MongoDB, the more confused you'll make yourself. It's no more an RDBMS than a .docx file is.

andreareina 7y ago

In their own words: "SQLite does not compete with client/server databases. SQLite competes with fopen()." [1] https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html

nickthemagicman 7y ago

If there's no Inter Process Communications and it doesn't use the OS to write to the filesystem...how does an application write to SQLlite db? I'm a noob and just curious.

bane 7y ago
It uses the OS to write to the filesystem. It just does some clever management of data needing to be written. You can build your own if you are clever enough to read write to your own file format too. If you want to build an RDBMS using SQLite as the core, you can. You can also do it using uncompressed WAV audio files if you are clever and hate yourself enough. To use SQLite in such a scenario you simply have to write the entire RDBMS minus the file handling routines. This includes a connection pooling mechanism and a single process to isolate the connection to the SQLite file so that the OS doesn't get angry when you try to have multiple things writing to it.

travbrack 7y ago

I've seen several software projects that are built for rdbms let you use sqlite. It works.

bane 7y ago
They're using SQLite as the file format for persisting data. It's a great file format for this. You can even build an RDBMS over top of uncompressed WAV files if you want. It doesn't make WAV files RDBMs.
- travbrack 7y ago
  But SQLite has rdbms features. I remember being able to show tables and do SQL queries in SQLite DBs.
  - bane 7y ago
    Those are relational database features, not RDBMS features. The SQLite file format specifies a way to organize, store and retrieve fairly arbitrary data using a relational database model. The library knows how to handle SQL to describe the work being done. The SQL is optional, one can, with the specification, read/write SQLite files in many other ways. There are almost no RDBMS features in SQLite. There are many many other file formats that store data that offer features that are very similar to SQLite files: indexes, journaling, write logging, etc. Thought question, you can ask a .tar file to give you a listing of what files and directories are stored in the file. Are .tar files RDBMSs? Consider: 1 - If you consider each file in the .tar file a "table" you can get list of tables. 2 - If each file follows a regular format, say JSON, you can search the "tables" by extracting the file and grepping it or using jq or whatnot. 3 - You can store a special file that is an index of some kind that lets you know in which file some data is, or even where in the file it is. 4 - You can build logic such that when you want to do other CRUD operations you can record a journal and a write ahead log to help build in ACID compliance. 5 - You can build buffer logic to support Write-ahead-logging, transactions and what not to improve performance. Are .tar files RDBMSs? Trivially no. But maybe, if you do all these things, you've invented a terrible database and database engine. However, you need to build a server, user access controls, connection pooling, import/export tools, partitioning, clustering, etc. before you start to arrive at an RDBMS that uses this engine.

hartator 7y ago

MySQl, Postgres, or MongoDB still store your date in files. So there are also “just a file format”. You do have one extra step - the db server process - to access the files.

bane 7y ago
Yes! You've defined the difference between an RDBMS (the server process and whatever else it does) and the file format the data is stored in. SQLite is just a file format. If you want it served up over some kind of server, you have to build your own (and most people do), or use a server that somebody else has built for you (there's a couple out there).
- goto11 7y ago
  SQLite is not "just a file format" anymore than say MS SQL Server is a file format. SQLite is a RDBMS in the form of a library and uses a particular file format for persistent storage. But it can also run purely in-memory: https://www.sqlite.org/inmemorydb.html https://www.sqlite.org/inmemorydb.html without any file at all. Being a RDBMS is not defined by whether the engine runs in-process or as a server in its own process.

hinkley 7y ago

I think you’re stuck in 90/10 rule territory here. But even so, SQLite was 220,000 lines the last time they measured, which was five years ago. You can do a lot of functionality in 22kloc, even ignoring the other 90%, which you shouldn’t Lodash, for instance, is much smaller than 22k lines, and it “just” manipulates objects and lists. If you downplay others like this, I wonder how you feel about your own work. Have you been working hard for years on something that “just” accomplishes a straightforward task? Are you happy? I know I wasn’t.

bane 7y ago
I'm not downplaying anybody's work. Many people approach SQLite as something in the RDBMS territory. It's not. Almost all of the confusion I've ever seen related to SQLite comes from starting from that basis. If one simply thinks of it as an alternative to fopen() then it makes very simple and intuitive sense. The people in this thread seem to be very resistant to this simple clarity of thought, but whatever, they can stay confused and keep coming up with feature comparisons of SQLite vs Redshift vs Elasticsearch or some such. If one were to draw a spectrum: file-format:<-x----------------------------->:DBMS SQLite is the x on this line and .txt files are about the only thing that any further left on it.
- imtringued 7y ago
  There is no such spectrum. SQLite is a piece of software that implements some but not all commonly expected RDBMS features. Software is not a file format but software may be written with the expectation that a given file follows the requirements of a certain file format and the software may be written in a way that it produces files that follow the file format specification. Since SQLite - the software - is an RDBMS the files it produces can be considered to be databases.
  - bane 7y ago
    I just drew the spectrum. It exists now. SQLite is a file format. You can read/write SQLite databases without the SQLite library. You can write your own custom reader/writer/creator. You don't have to use SQL. You don't have to be ACID compliant. I can make for you right now a SQLite database that did not touch any SQLite software, put data into it and you can open it with another piece of software that implements the SQLite file format specification. Likewise, you can use the SQLite library software to create a SQLite database file, put data into it, and I can read it/update it using any other software that implements the SQLite file format specification. The SQLite library offers some very very basic features, such as ACID compliance, and so on, but those are not part of or guarantees of the file format or the database files. The software that you write that implements the SQLite file format specification does not have to do any of these things to work with or produce a valid SQLite file. An RDBMS is a kind of DBMS for managing relational databases and providing access to the databases (for example users and permissions). Modern RDBMSs offer extensive features (look at an Oracle or MSSQL Server spec sheet) that are not even hinted at with the SQLite library software. This is because SQLite is not an RDBMS, it's a file format.

nebulous1 7y ago

I've spent half an hour or so looking at this thread, and my take away is that you're largely just confusing matters even further. > It's just a file format. This is clearly incorrect. It does encompass a file format, but it also contains code to manage that file. The existence of sqljet does not change this, it's merely a different database management system that uses the same file format. You also seem to mostly ignore that the data it's managing is a relational database, not some other form of data. This is why you can't compare it to python's csv module or python-docx, neither of these do anything to restrict you to a relational data model, nor do they provide a system to query them as if they were a relational database. On the other hand, if for some unknown reason you rewrite the storage engine of postgresql to use the either the sqlite file format or csv/docx, I assume you agree it would still be an RDBMS. Ultimately I think you're just adding to the confusion by saying that a project with 139,000 lines of C code is "just a file format".

szatkus 7y ago
It's a file format with a very mature and nice library around it. At the end of a day it's just fwrite() and fread() with SQL syntax.
- ldiracdelta 7y ago
  And perhaps python is just glorified assembly, but it seems to me to be a "difference in degree that leads to a difference in kind."
bane 7y ago
You can read and write sqlite files entirely without the library. The file format is available on the sqlite site. SQLJet proves the point that you don't need 139,000 lines of C code to use sqlite files.
- goto11 7y ago
  I'm sure you could make a tool which could read and write to SQL Servers mdf files. That wouldn't change that SQL Server is a RDBMS.

jturpin 7y ago

Sqlite implements all of the features you would expect from a relational database, including indexes, transactions, write ahead logging, consistency, and provides a SQL wrapper on top of its file API. _This is what makes it a database and not just a file format_. To the client, whether or not the API is happening over a socket or a locally linked library is irrelevant. How connection pooling works is also not relevant to whether it is a database or just a file format. Calling Sqlite just a file format is kind of like calling Python "just a syntax specification." That's part of it, but we're talking about the actual implementation of it (probably CPython).

bane 7y ago
https://sqljet.com/ https://sqljet.com/

kgwgk 7y ago

> SQLite is a file format with a nice API that uses SQL as the paradigm for reading/writing to the file. <insert-your-favourite-relational-database-management-system-here> is a collection of bits with a nice interface that uses a query language as the paradigm for reading/writing data.

bane 7y ago
No, an RDBMS is a piece of software for managing databases in the relational model, access to the databases (such as users and permissions) and provides services such as a server, connection pooling, and so on. SQLite provides almost no RDBMS features. This isn't just semantics. A car is not an engine. A fork is not a kitchen. A SQLite file is not a DBMS.
- imtringued 7y ago
  https://en.wikipedia.org/wiki/Database#Database_management_system https://en.wikipedia.org/wiki/Database#Database_management_s... >Connolly and Begg define database management system (DBMS) as a "software system that enables users to define, create, maintain and control access to the database".[24] >The functionality provided by a DBMS can vary enormously. The core functionality is the storage, retrieval and update of data. Codd proposed the following functions and services a fully-fledged general purpose DBMS should provide:[25] [x] Data storage, retrieval and update [x] User accessible catalog or data dictionary describing the metadata [x] Support for transactions and concurrency [x] Facilities for recovering the database should it become damaged [ ] Support for authorization of access and update of data [ ] Access support from remote locations [x] Enforcing constraints to ensure data in the database abides by certain rules Under this definition SQLite - the library - clearly is an RDBMS that leaves out some common features that do not make sense within its niche but is otherwise fully functional and under this definition the files that SQLite manages are the database, not just merely a file format.
  - bane 7y ago
    The zip utility. [x] Data storage, retrieval and update [x] User accessible catalog or data dictionary describing the metadata [ ] Support for transactions and concurrency [x] Facilities for recovering the database should it become damaged [x] Support for authorization of access and update of data [ ] Access support from remote locations [x] Enforcing constraints to ensure data in the database abides by certain rules Congratulations, apparently zip files are as much of an RDBMS as SQLite. If I bundle zip with ssh (Access support from remote locations) and Linux (Support for transactions and concurrency) did I just create a new RDBMS? How many checkboxes do I have to have in order to call anything an RDBMS? Is SSH an RDBMS (Access support from remote locations). Can I just put a catalog in a .txt file and check that box? Is XML an RDBMS because it enforces constraints and supports storage retrieval and update? Are chmod and chown an RDBMS because they support authorization of access and update of data? > SQLite manages are the database, not just merely a file format. It turns out databases can be just files. Those files must follow a described file format. SQLite files are relational databases that are instantiations of the file format specification for SQLite files. The SQLite library implements that file format as well as some clever logic to support SQL and ACID compliance. Some SQLite libraries do not support these things.