Sqlite 3.30.0

    >>> import sqlite3
    >>> con = sqlite3.connect(':memory:')
    >>> cur = con.cursor()
    >>> q = '''select x, sum(a * power(x, e))
    ... from (select -2 as x union select -1 union select 0 union select 1 union select 2) xt,
    ... (select 1 as a, 2 as e union select 4, 1 union select 2, 0) et
    ... group by x'''
    >>> cur.execute(q).fetchall()
    Traceback (most recent call last):
      File "<stdin>", line 4, in <module>
    sqlite3.OperationalError: no such function: power
    >>> con.create_function('power', 2, lambda base, power: base**power)
    >>> cur.execute(q).fetchall()
    [(-2, -2), (-1, -1), (0, 2), (1, 7), (2, 14)]

As you mentioned, it ships in every major browser, most OSes, and a number of extremely popular instances of consumer software. If that's still a "toy" I don't know what isn't…

It's fast, simple, and easy. Perhaps my favorite feature is that backups are just a copy (in fact, when I'm debugging an app, I often copy the database into a temp directory and go to town).

I totally agree that SQLite is a great first option for startups. If your database will fit on your filesystem (gigabytes, not terabytes), it'll probably do really well. It's really easy to switch to something like Postgres later, and it's really nice to not have to worry about the complexities of a database server until you actually need them (e.g. you're considering hiring dedicated IT/DBA staff instead of just developers). And there's a good chance you'll never need anything other than SQLite.

Which is mostly the reason why I created RediSQL[1], to allow a more widespread use of it also in "modern"/"cloud native" application where you want to just connect to a database via the network, ideally through common API like the Redis one.

It has the great benefit, that if you create your database in memory is crazy fast, and it retains the persistency properties of Redis, which are good enough in most cases.

[1] https://RediSQL.com

Modern startups need their services to be available, and this is difficult to achieve with SQLite because you can't run long database migrations without blocking writes during the migration. Personally, I'd use SQLite more often if there was a good solution to this (and also DROP COLUMN).

https://github.com/rqlite/rqlite

Postgres can run in a 256MB VPS(if one can find one these days) and has more features that you might need later.

Installing is as easy as 'apt-get install postgresql', create user, create database, done.

>Many modern startups could get pretty far with SQLite + aggressive caching

Not only did we get "pretty far", we got to what is effectively a final destination for this generation of our product. We don't even need "aggressive caching" beyond what the filesystem provides to us by way of accessing the SQLite db file.

Today, we have a single SQLite database persisting stateful data for an application that is used concurrently by 100+ users. I have benchmarked 512KB state update writes in a multithreaded scenario at >500 per second. 64KB updates exceed 1000 per second. It took us a lot of time and understanding to get to this point. At the end of the day with SQLite, less is always more. The first attempts we did involved connection pools, queues and all kinds of other fancy over-engineering in an attempt to extract more performance. We finally ended up with just the following:

- Enable Write Ahead Logging

- Set Synchronous to Normal

- Use a single SQLiteConnection

Justification for a single connection: We are using .NET with application-level locking and an RWLS where appropriate. This is in contrast to relying on the database to push these transactional scopes to disk. For us, losing an in-flight transaction tracked in memory is not catastrophic so we can tolerate this 1-100ms danger zone for the huge upside we get in throughput. This is a great example of something you can only do with SQLite. No hosted database solution offers this degree of exclusivity over your data store from an application perspective.

One other application note to keep in mind is that SQLite (by default) operates in serialized mode, so the above approach is consistent regardless of the fact that we are hitting a single connection from multiple threads at once. The usages of RWLS are required in our case simply because we need to do multiple dependent sequential operations (select+modify+update) or insertion of new items (insert+last insert row id from connection). It is hypothetically possible to avoid locking altogether (aside from the implicit SQLite serialization) if you can structure your database such that you perform all operations in a single command - E.g.: Use application-generated keys upon insertion rather than waiting for the DB to generate one.

Also, backups and restores are super trivial as mentioned elsewhere in this discussion. I have considered writing a backup agent directly into our software that is transactionally-aware. E.g.: During a backup request, I can take out a write lock on the RWLS for the SQLiteConnection while copying the database to a backup destination. Alternatively, since the DB lives on the same machine as the application, you can just take a single VM snapshot throughout the day, but this won't be as consistent a backup (tolerable for our use case though).

The biggest advantage we see with using SQLite as our principal data store is that it has allowed us to completely avoid operational annoyances regarding how we actually ship our stack to various environments. .NET Self-Contained Deployments + SQLite = one bin folder that "just works". We were able to build custom deployment tooling because of the simplicity of this model, rather than relying on off-the-shelf tech that doesn't quite fit 100% into our use case.

I see that even with "INTEGER" affinity, I can insert and read back text to columns :/

  sqlite :memory:
  CREATE TABLE test(
    x INTEGER, y TEXT);
  INSERT INTO test
   VALUES("a", "b");
  SELECT * FROM test;
 
  a|b

In other words, typechecking is also a cost for language designer.

Even SQL itself has not particularly strong type system. Perhaps you're looking for something else entirely.

Unless you have really strict requirements (generally not the case), SQLites storage classes (NULL, INTEGER, REAL, TEXT, BLOB) are sufficient. If it really bothers you, you can use extensions to provide whatever abstractions you like. If you're mostly doing CRUD, you really shouldn't have any issues.

If you have only one thread/process, use it as is; if you need multiple readers and one writer (who do not block each other), use WAL mode; if you need simultaneous multiple readers and writers, use Postgres.

If you are using SQLite, be aware that the “copy the file to move it to a different system” comes with a caveat - a regular Unix “cp” may get the file in an inconsistent state if it was modified while copying, or in the middle of a transaction.

Iirc “rsync” if used to copy, will verify that the file was not modified while copying and restart the copy if it did (a number of times and then error out). But that’s still not enough - you may need to also copy the shm file (in wal mode) and the journal file - and you need to do that atomically. Which is harder still.

The right way to copy an SQLite file from place to place us:

Any way you like, if it was properly committed and closed last time it was used, and is not currently open by any other process;

Or creating a backup (e.g. with the sqlite3 command), which will be a nonblocking operation if in WAL more - and then you can ship the backup whichever way you want.

With SQLite, it's as simple as dumping the .db file from one machine to another and running your analysis tools over it - aka executing simple SQL queries from various reporting views. We also see really good compression on this type of structured data (we just compress the entire .db file on disk).

Our analysis tools are typically just DB Browser for SQLite - A tool I would say is 10x better & faster than SSMS: https://sqlitebrowser.org

Recent versions of SQLite's CLI also has a "sqlar" mode [1] that lets you treat sqlite database files as an archive file. Unlike some other archive files, adding and removing files to an existing sqlar file is a first class operation, since it's just a database file. It's basically what I'm doing for my "ton of tiny files" case, just with a documented schema.

1 = https://www.sqlite.org/sqlar.html

This seems like a misunderstanding of the types that SQLite was able to store natively and which one was right for your use-case. While SQLite certainly has less options out of the box than a larger database has for types, this seems like it could happen in another database as well with a wrongly chosen type (ex. integer vs bigint in postgres).

> - Issues with accessing the DB from multiple threads.

How SQLite acts under multiple thread access is well documented[0]. You can even get closer to bigger systems by using WAL mode[1]. The fact that SQLite isn't the best for concurrent writes is also discussed in the page on when to use SQLite[2]:

> Many concurrent writers? → choose client/server

> If many threads and/or processes need to write the database at the same instant (and they cannot queue up and take turns) then it is best to select a database engine that supports that capability, which always means a client/server database engine.

> SQLite only supports one writer at a time per database file. But in most cases, a write transaction only takes milliseconds and so multiple writers can simply take turns. SQLite will handle more write concurrency that many people suspect. Nevertheless, client/server database systems, because they have a long-running server process at hand to coordinate access, can usually handle far more write concurrency than SQLite ever will.

A bit of a reach but I'm also willing to bet that the architectural pattern that you were using that required multiple threads to write to the DB at the same time might benefit from passing that responsibility to a single thread which could possibly employ some batching, which is normally one of the low hanging fruits of DB write performance tuning.

[0]: https://sqlite.org/lockingv3.html

[1]: https://sqlite.org/wal.html

[2]: https://www.sqlite.org/whentouse.html

https://github.com/BurntSushi/xsv

This was probably the right decision 5 years ago, but I'm not sure Sqllite is the proper solution for "ad hoc" analysis now. Excel now has plugins to handle very large datasources. And of course, the company leading this charge here is Tableau, with many others doing something similar.

Sqllite can still do all the manipulation that these others can, but the others can also be used by non developers and come with pretty graphs and pictures. For large CSV analysis on an ad hoc basis, SQLlite doesn't stack up well to the competition.