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?
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".
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.
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.
> 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.
> Have you tried to figure out where to install the server or asked what the system requirements were for it?
Not exactly, but asking "where to install the server & client and what are the system requirements for each" is not that different to asking "where to install the client and what are the system requirements for it", even when there is no server.
> Have you grown concerned that once the system moves into production the O&M team won't know how to operate "yet another database"?
Yes. Because the production concerns of SQLite are not nil.
> Do you spend agonizing hours trying to figure out if it supports multithreaded connection pools for multi-user writes?
Not agonizing hours, but it is just a slightly rephrased version of a valid question about SQLite w.r.t. concurrent file access (as with locks on file access for any file). Other commenters have brought this up in terms of multi-user access slowing down applications, and setting up intermediary DB access processes using IPC to facilitate this.
> The people asking these questions are stupid
I disagree
> no different than "CSV is serverless" or "JSON is server-less"
CSV and JSON lack any protocol or queryable interface: unless you're using some ancillary tool like `jq` as a comparison, CSV and JSON as filetypes are both "serverless" and "clientless" so not particularly comparable. An article on those would be quite different.
That was a later Edit. The original did not have the word "not".
I make these errors sometimes too and need to edit. HN's method beats Twitter, but it would be nice if we could see a versioned history of each message...
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.
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?
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)
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).
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.
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).
FirebirdSQL is pretty close to what you're talking about... it's library can do either embedded or you can run a shared server instance. It's really pretty neat, but on the one side, PostgreSQL is probably better, and on the other SQLite is nicer.
I worked on a project a few years ago, where I chose Firebird so I could use literally the same database on potentially offline sites that regularly sync up to a main office (shared) deployment. I worked pretty well and was still a lot of work.
SQLite is a file format with a familiar API and uses SQL as the logic for searching/adding data to the file.
Approach it exactly the same way you'd approach using a CSV file and all the confusion and overthinking about it goes away. Approach it as a stripped down RDBMS and you end up with all kinds of questions about support for this or that RDBMS familiar service.
You can write your own SQLite file reader/writer. Here's the specs (includes the specs for the Journal and WAL files and semantics as well) https://www.sqlite.org/fileformat.html
Here's an example of somebody who's done this.
https://sqljet.com/ - this is not a wrapper on the sqlite C code, this is a re-implementation of that code that is binary compatible with SQLite files.
The Journal file only exists as a temporary file until transactions complete. The .sqlite file you make is the entire atomic file that follows the SQLite file format. The Journal has its own file format. Same goes for the Write-Ahead log.
RDBMSs also manage connection queues, account management, rights and permissions, and so on. Many overcome various OS limitations by providing their own entire file management, fopen(), virtual memory and other subsystems that are tuned to their workloads.
SQLite is a file format. SQLite uses familiar relational paradigms to make it easy to read/write data to the format without having to learn yet another API and domain language. The API code is extraordinarily well tested, and it makes simple complex logic like transaction journaling, indexing and so on.
>Consider: it’s totally possible to strip down Postgres until all you have left is an embedded RDBMS of the style of SQLite.
No! SQLite is not an embedded RDBMS. It's a file format.
If there was a library you could import, and it provided methods to read and write directly to files that PostgreSQL could read/write to and there was nothing else to install, no runtime, no daemons, no servers, etc., then we could pass around self-contained PostgreSQL files to each other. Then PostgreSQL files would be a file format as well.
Have you ever used a library to read/write from a CSV, JSON, JPEG? It's no different than doing so for a SQLite file!
> SQLite is a file format with a familiar API and uses SQL as the logic for searching/adding data to the file.
That description is for a library, not a file format. SQLite is a library, that saves to a convenient format and allows you to query the file using the SQL syntax. File formats dont have "logic".
You are arguing the equivalent that Word is a file format. While there is a Word file format, Word itself is an application.
So when you read/write to any other file format, you just read/write bytes directly to/from disk and re-implement the parsing and read/write logic in your own code every time?
> File formats don't have "logic".
Every file format has logic otherwise it's just random entropy in an arbitrarily long byte stream on a disk. How to read/parse and interact with that format depends entirely on the logic and scheme for that file. For example, many file formats have an index of some kind that you must read and parse before you can figure out where the other data lives, compressed file formats often store a dictionary, image formats often have a compression/decompression logic that must be followed for reading/writing to them.
> You are arguing the equivalent that Word is a file format. While there is a Word file format, Word itself is an application.
.docx is the file format for Word documents. There are many APIs and programs that can read/write to/from .docx files.
You can read/write CSV files, JSON, JPEG, WAV, MP3, MP4, etc. into memory as well. That doesn't make any of them an RDBMS.
SQLite is a file format. It has a nice API and uses SQL as the domain language for read/write logic. If it didn't use SQL for the logic, would you still be confused?
Being a file format is only one aspect of what SQLite is. SQLite describes itself as "SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine." I think that is a better, more encompassing, description than "it's a file format"
Edit:
If SQLite lacked any ability to persist data to disk, it would still be very useful as an in-process SQL engine for many sorts of problems. Certainly not as useful as it currently is, but nevertheless still useful.
I'd say the file format without the SQL engine, or the SQL engine without the file format, would be like peanut-butter without jelly. Certainly not pointless, but the real magic comes from the combination of the two.
Sure, I can get behind that. The logic that handles the read/writing and SQL parsing is all part of the library for sure.
But that library is absolutely not required nor is SQL. One could build their own library that read/write to SQLite files entirely without any of that if they wished. Some people have done ground up rewrites in other languages other than C, but have more or less stuck with the same internal logic and the use of SQL as the read/write logic.
All other RDBMS products also store their information inside files. I can take a mdb file from SQL Server and copy it to another server and attach it there.
SQLite is obviously a library that stores it's data to a file like millions of other libraries. SQLite is to it's data file the way LAME is to MP3. LAME is not the file format, it's the library.
> All other RDBMS products also store their information inside files.
No they don't. There are many RDBMS (and other DBMSs) that do not store their information inside files.
> I can take a mdb file from SQL Server and copy it to another server and attach it there.
Yes, SQL Server is an RDBMS.
MDB files are files defined by a file format. This format defines something called a database. There are many libraries and other pieces of software than can read/write mdb files (https://jackcess.sourceforge.io/)
SQLite files are files defined by a file format (https://www.sqlite.org/fileformat.html). You can write code that reads and writes to a SQLite database file without using the library. So long as you follow the format specification, you will produce or be able to read from an aribtrary sqlite file that is produced by any code that implements the specification.
The SQLite library is a reference implementation of the specification as well as some sample logic for reading/writing to the files and using SQL to define the interactions with the file. The SQLite library is not required for this, nor is SQL, nor is the logic for ACID compliance.
SQLite is not an RDBMS and offers almost nothing that an RDBMS (or DBMS) might offer. If you wish to use SQLite files with an RDBMS you either have to build the RDBMS yourself, or find somebody else who's done so.
The more you consider the SQLite a file format, the easier it becomes to work with and understand. The more you try to consider it an RDBMS the less it makes sense.
Just because SQL is involved, doesn't mean it's an RDBMS.
SQLite is a whole library with complicated locking protocol options etc. and works with multiple files of different formats (e.g. separate transaction log).
Since the file format specification is available, you can write your own code that directly reads/writes to SQLite files. You can't even have to use the logs and journaling options. You don't even have to use SQL.
That’s the SQLite database file format. SQLite itself is a library that can deal with multiple files in different formats (transaction log and database file). It also reads .sqliterc and maybe others.
> SQLite itself is a library that can deal with multiple files in different formats (transaction log and database file). It also reads .sqliterc and maybe others.
Yup you also got it! SQLite is not an RDBMS and shouldn't be approached that way.
The more you try to ram RDBMS ideas into what SQLite is, the more it won't be that. The more you try to treat it like a file format, the more it will be what you want.
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.
> 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.
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.
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?
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) it will be readable by any other code that implements the specification (e.g. 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!
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.
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).
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 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.
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.
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.
> ...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.
First of all, you still haven't answered the question: What is it that an RDBMS has that SQLite doesn't have?
> It's no more an RDBMS than a .docx file is.
Thanks for the idea. Your argument is like saying this:
Microsoft Word is not a word processor -- it's a file format.
I mean, yes, Word has a file format; but it's far more than just a format specification.
> Sure they do. If you write the logic to do so,
Right, but you don't have to write the logic if you're using SQLite. That's the point. SQLite is a library, which provides a way to do SQL operations on data. Like Word, SQLite has a file format, but it is far more.
I just don't get where you're coming from. Do you not know that the SQLite libray can actually do complex SQL queries on data? Or do you think that people shouldn't do that for some reason? Or do you just value SQL queries so little that you don't see any difference between being able to do complex queries and doing `file.Write(json.Marshal(data))`? What is it you're trying to accomplish with this line of argument?
> First of all, you still haven't answered the question: What is it that an RDBMS has that SQLite doesn't have?
And RDBMS is a well defined thing and is literally what the acronym expands to mean. This is very old technology with an interesting history and I really implore you and anybody reading this to go read up on it. It's not just whatever we assume it to be or some kind of data bucket with SQL.
> Microsoft Word is not a word processor -- it's a file format.
No, don't be obtuse. I'm saying that .docx is a file format.
Word is both an application for editing documents and contains a reference implementation for reading/writing .docx formatted files. There are many libraries that can read .docx files and some of them are also part of document editing software.
> Right, but you don't have to write the logic if you're using SQLite. That's the point. SQLite is a library, which provides a way to do SQL operations on data. Like Word, SQLite has a file format, but it is far more.
I just don't get where you're coming from. Do you not know that the SQLite libray can actually do complex SQL queries on data? Or do you think that people shouldn't do that for some reason? Or do you just value SQL queries so little that you don't see any difference between being able to do complex queries and doing `file.Write(json.Marshal(data))`? What is it you're trying to accomplish with this line of argument?
Precision of thought. People don't go around calling fish oceans, or forks restaurants. The SQLite library does what you've described to SQLite files. But you don't need the SQLite library to work on SQLite files. You don't need even need SQL. e.g. https://sqljet.com/
Just because a library offers SQL as a convenient tool to read/write data into its file format everybody loses their minds and starts to think the library is some kind feature reduced Oracle cluster. Go back to my first post. People are approach what SQLite is from the wrong direction (RDBMS) and its confusing the fuck out of everybody who gets near it.
This is important. IT departments and governments make very large, very expensive decisions based on if people know that SQLite is closer to CSV files than to Oracle databases.
I literally sat in a meeting last week where a senior decision-maker at a client wouldn't accept delivery of some software because it used SQLite and didn't want to add maintenance of yet another database to their overworked DBA staff and didn't want to hire a dedicated person to manage it. So now, instead of just taking delivery of the software, some of it has to be rewritten to use the client's RDBMS system, which in turn actually will add workload to the overworked DBA staff and will also perform worse.
SQLite IS A FILE FORMAT with a really nice library for reading/writing to that format.
> SQLite IS A FILE FORMAT with a really nice library for reading/writing to that format.
You keep repeating that, but it is just not the case. SqlLite is the name of the library, not the file format. Just see the definition on Wikipedia:
> SQLite is a relational database management system (RDBMS) contained in a C library. In contrast to many other database management systems, SQLite is not a client–server database engine. Rather, it is embedded into the end program.
> > Microsoft Word is not a word processor -- it's a file format.
> No, don't be obtuse. I'm saying that .docx is a file format.
I'm afraid I'm not the one being obtuse. That statement is a mirror; please have a look.
> Precision of thought.
Which is why "SQLite is only a file format" is a false statement, and you shouldn't be making it.
> But you don't need the SQLite library to work on SQLite files. You don't need even need SQL.
That's like saying btrfs isn't a filesystem, because grub knows how to read it. The core functionality of SQLite is the query system. The fact that it's got a well-defined file specification which other projects can read is one of its features, not the sum of everything that SQLite is.
> I literally sat in a meeting last week where a senior decision-maker at a client wouldn't accept delivery of some software because it used SQLite and didn't want to add maintenance of yet another database to their overworked DBA staff and didn't want to hire a dedicated person to manage it. So now, instead of just taking delivery of the software, some of it has to be rewritten to use the client's RDBMS system, which in turn actually will add workload to the overworked DBA staff and will also perform worse.
Finally, something remotely concrete, rather than a repetition of the same false statement.
So the problem you're trying to solve is that people see "SQL" and think "Oracle": A massive installation which requires separate resources, both in terms of servers and manpower to maintain it.
I can see why you want to try to correct that false belief. But your solution seems to be to introduce another false belief. Imagine you're successful in getting people to accept that "SQLite is just a file format". Five years from now, someone else will be posting this to HN:
"I literally sat it a meeting last week where a senior decision-maker at a client wouldn't accept delivery of some software because it used SQLite, and he said SQLite is just a file format like JSON; and they need advanced SQL querying, safe transactions, and safe access by multiple accounts. So now, instead of taking the delivery of the software, some of it has to be rewritten to use the client's RDBMS system."
You're not going to fix one misconception by introducing another. One better thing to say would be the truth:
"SQLite allows us to embeds database functionality into your application, so there's no need for a separate stand-alone database."
Or, in fact, to do what this article does, and try to hijack current hype around "serverless":
"SQLite is a serverless database -- you don't need to install and maintain a new RDBMS; it's embedded inside the application itself. No additional maintenance necessary."
At a minimum it is multiple file formats. But really it is multiple file formats + a fairly intricate library for dealing with them along with locks etc.
According to their home page the SQLite database file format is a file format, and SQLite is a library.
No but close. A SQlite file is a single format. The journal file and the WAL file are different formats used for bookkeeping by the library to attempt to be ACID compliant. The library implements some complex logic to ensure this, but reading/writing a SQLite file does not require any of this.
You could write your own code tomorrow that reads/writes SQLite files but does not produce, read, or write WAL or Journal files. So long as the resultant SQLite file follows the specification, it can be read by any other piece of software that implements the specification, such as the SQLite library or SQLJet (https://sqljet.com/).
> 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.
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.
>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.
[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.
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.
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.
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.
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.
Why, because it offers SQL support? That just makes it a relational database that supports SQL. MS Access supports SQL.
If you were to draw up a feature list of Oracle, PostgreSQL, MS SQL and SQLite, SQLite would have almost none of the features of any of the actual RDBMSs.
Here's some examples from MS SQL:
- Support to PMEM devices and bypassing OS storage mechanisms for optimal file read/write access
- Availability Groups and synchronous replica pairs
- Users and permissions
- Secure Enclaves
- Certificate management functionality
- BI tools
- Database tuning advisor
- Machine Learning services
- Service Broker
- Replication services
- Analysis Services
- Reporting services
- Notification services
- Integration services
and so on.
Draw up a set of features for JSON files and jq and compare to SQLite. Is it closer to MS SQL or JSON?
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.
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.
You're making some basic assumptions that do not make sense.
The database can be physically stored in any arbitrary format. One can build an RDBMS that stores all its data in tar or JSON files. No matter how inefficient as long as software exists that manages the database.
>Are .tar files RDBMSs?
This question doesn't make sense because you are asking if databases can be management systems which is obviously false by definition.
If the 5 steps you have described are implemented in software then that software would be considered an RDBMS and the .tar file clearly would be a database. There is no confusion.
>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.
Those features are not necessary for a piece of software to be called RDBMS but most industry standard RDBMS do indeed support these features and SQLite clearly is an RDBMS that pursues a certain niche that only makes sense in certain situations.
> This question doesn't make sense because you are asking if databases can be management systems which is obviously false by definition.
Yes! And by extension the tar utility is not a DBMS even if it checks some of the boxes for one. And thus SQLite files are not RDBMSs. Looks like you and I agree.
> The database can be physically stored in any arbitrary format. One can build an RDBMS that stores all its data in tar or JSON files. No matter how inefficient as long as software exists that manages the database.
Sure! One can come up with all kinds of very terrible software. But what's the distinction between some random software that just allows CRUD operations on a file format and an RDBMS by your definition? Because you've defined something close to 100% of all software as an RDBMS which makes the distinction between software and RDBMSs meaningless.
There has to be something more than just that to be an RDBMS doesn't there?
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.
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?