Is a single-table relational database similar to a simple NoSQL database?
No - a single table relational database is more akin to a spreadsheet than a NoSQL database. NoSQL is not about having no schema, it's more about having a flexible schema!
Wikipedia puts it very well when it says NoSQL:
provides a mechanism for storage and retrieval of data that is modeled in means other than the tabular relations used in relational databases.
The key (pardon the pun) word here is modeled - i.e. there is a model, it's just not a relational one!
As an aside, it's well worth reading the entire article - it's detailed and well written!
IBM says:
It's important to emphasize that the "No" in "NoSQL" is an abbreviation for "not only" and not the actual word "No." This distinction is important not only because many NoSQL databases support SQL like queries, but because in a world of microservices and polyglot persistence, NoSQL and relational databases are now commonly used together in a single application.
And indeed many NoSQL systems (it's difficult to say "vendors" since many of these systems are Open Source) are scrambling (or have scrambled) to put SQL interfaces on top of their systems because so many programmers are familiar with that paradigm (and it's one which has worked well over the years).
One could write entire essays (as indeed I have in College) on NoSQL vs. the classic RDBMS, and I'll go no further down this avenue other than to urge you to read around the topic.
It's fascinating to watch the evolution of the database ecosphere - new SSD chip technology, and for me, the most interesting development is the recent focus on LSM (Log Structured Merge tree) which is widely used in NoSQL systems and also NewSQL ones. IMHO, these NewSQL systems are the way of the future and I expect to see Oracle, SQL Server &c. to adopt many of their paradigms (or embrace and extend them to coin a phrase).
Again, it's a complex technology about which reams could be written, but from here:
At the heart of the LSM algorithm is a rolling merge process:.... LSM trees cascade data over time from smaller, higher performing (but more expensive) stores to larger less performant (and less expensive) stores.
Instead of updating in place, data is "chunked" and from here:
Once the in-memory tree reaches a threshold size, a new in-memory tree is created and the old tree synced to disk. Once written to disk, trees are read-only, though they are merged in the background with other on-disk trees to reduce the cost of reads.
You mention SQLite in your question - it may surprise you to learn (it did me) that there is a Key-Value "version" of SQLite that was developed between 2012 and 2014. From here:
Lessons learned from SQLite4 have been folded into SQLite3 which continues to be actively maintained and developed. This repository exists as an historical record. There are no plans at this time to resume development of SQLite4.
I'll leave the last word to Brian Aker, former Director of Architecture for MySQL who presents his amusing take on NoSQL which is available on YouTube here. I think his views can be best summed up by a cartoon which he presented during that talk:
+1 for an interesting question (which I hope won't be closed!) and welcome to the forum!
Yes, you can use a single-table relational database as if it were a NoSQL database. You can also use a spoon to paint your house.
You have to use a tool the way it was intended else you're fighting both the tool and the problem you're trying to use the tool to solve. If you want normalisation and the absence of data anomalies it brings you're likely to have many tables. If you push all the logical entity types into a single physical table you'll have many columns (and all DBMS have limits), most of which will be null making queries a nightmare, as will be DRI. You could shove all those values in a JSON or XML column. If all shredding occurs in the application you've just invented the key-value store. If you expect the DBMS to query it you have a tree parser pretending to be a SQL parser pretending to be a schemaless parser. Who needs performance anyway.
Now, where's my paint spoon.
At it's most basic, a NoSQL system is a single table with exactly two fields: a limited length key field, and a larger "generic" value field, which would commonly hold json, but might also hold binary data (images or other files, for example) or some other object notation as an alternative to json.
However, there are three points where this breaks down.
First, I see this statement:
"In the case of a single-table relational database, there are no relationships between multiple tables, and new columns can be easily added to the single table."
The idea that new columns could easily be added to such a table as needed won't hold up for very long. Instead, individual records would often be single (complex) entries in the value field, again often in a json format.
Second, rather than looking up by exact key, NoSql systems often allow you to retrieve sets of records where keys match some pattern. This pattern matching is less common in relational databases, and when it is present it's not as likely to be well supported in terms of index performance.
Third, most NoSql data stores provide the ability to peer into those complex json values. The ability is limited, but still far greater than what you'd expect to find in a relational database. It lets you do things like retrieve a set of values for all keys that match a pattern and where the value of a json property in those fields meets some criteria, or update a record matching some specific key, where you only change the value of a single json property in the record.
I've used JSON here as an example, but it's not limited to JSON, either, with some different products using or supporting other mechanisms for the internal schema of records.