I work in data analysis too and SQLite is to me what MS Excel is to other people in the industry. I have development background so I never picked up in MS Excel to same degree as my colleagues from statistics backgrounds. In corporate organisations where DBAs monitor the use of their database with an eagle eye SQLite is a fantastic option to analyse several millions of lines of data on your desktop. I find DB Browser for SQLite (DB4S)[0] is invaluable when using SQLite.

[0]https://sqlitebrowser.org/

> It must sound naive to some of you, but the first time stumbled upn sqlite I was so excited!

Why naive? That's the experience of all of us with SQLite.

Most of my web apps’ databases are an SQLite file. It’s more than enough for the ammount of traffic they serve and the db files are easy to set up and backup..

I can't even tell you how many times a dev came to me with some weird question that was very simply answered by "just use sqlite."

I've never thrown more than 20GB of data at it, but it always handles things admirably, and it often (to me) makes the right calls about the level of complexity required (accepts column type defs but really just doesnt care if it maps to string.)

I totally agree.

I'm a front-end dev that can wing backend from time to time, and I use SQLite as much as possible. On multiple projects now I've run into complications due to complexity or environments, and adding a simplified local development backend with sqlite kept down time to a minimum.

SQLite is awesome.

Same. The last time I was this excited about an RDBMS in your pocket was when BerkleyDB released their Java database in a single JAR file. I am not sure when they initially released it but I remember doing some hobby projects in 2003.

At my job we started using H2 which much like SQLite has shareable databases. We now wish more databases were this simple. Its just too efficient to share a db in its total current state and someone with a little more experience or who does not have tunnel vision can probe and resolve issues. Plus you can always go between states of broken vs working.

> build a business-level abstraction for coordination between nodes, with each node owning an independent SQLite datastore"

There even are solutions that do this already:

- rqlite (https://github.com/rqlite/rqlite)

- actordb (https://www.actordb.com/)

+1 we use SQLite in our BI tool to keep information about user accounts, data sources (cubes) / reports configs, access control rules etc. SQLite works like a charm even in cloud version where we have > 6,000 registered user accounts.

I love sqlite, but just a wonder on how big you are going?

I regularly see 50TB total of databases on SQL Server, and scaling up to thousands of clients.

I often connect to production databases in read only users to do various data analysis. Is this something you can do with SQLite (besides maybe SSHing into the machine)? If not, how do you get around it (if it ever even comes up)?

How do you sync db data across nodes ? Custom built solution or use something off the shelf ?

If you like edit, you might like :memory: too :)

https://sqlite.org/inmemorydb.html

I also didn't know about it before! Cool

SQLite is for storing data in an environment where SQL is meaningful. Anyone wanting to do database admin tasks (like adjusting table schema) would be well advised to go with a Real Database.

SQLite really shines when you have a more-or-less final idea of what tables you want to build and don't have to worry about users inputting crazy data. Anything up to that and it is a model of simplicity. Anything beyond that and it is undercooked.

I just sucked the existing table into RAM and recreated the table. I did it on a transaction so there was no risk of data loss.

In my case the data was always 10s of MBs.

Remember, the point of SQLite is a replacement for generating a file format. Although it's a database, it lets us (developers) re-use our knowledge of databases when doing basic file I/O.

When do you need to drop a column in a production DB? Maybe my anecdotal bubble is about to burst, but I work in the public sector, and have for a while and on our 200 different production DBs behind around 300 systems we’ve never dropped a column.

Don’t many MySQL backends also recreate the whole table when you drop a column? They just hide it from you better.

I agree with you. SQLite drove me nuts when it came to changing your database. This is one of the reasons I just use DB Browser for SQLite (DB4S). It takes care of all the complexity.

The general strategy is to create a new table, insert data from the old table, drop the old table, rename the new table, and re-create the indexes:

  create table foo2 (
   col1 int,
   col2 text
  );
  insert into foo2 sleect col1, col2 from foo;
  drop table foo;
  alter table foo2 rename to foo;
  create index on foo(col1);

See: https://www.sqlite.org/lang_altertable.html#making_other_kin...

As for the reason, see the next section on that link. It's not perfect, but it is what it is. SQLite wasn't designed to solve 100% of the use cases, and that's not a bad thing.

We use sqlite as a smaller cache of a much larger postgres db. The cache is generated by a job and yes is regenerated every time before being pushed to machines that need it.

Think of SQLite as a file format which happens to have a query interface, and not a database.

MySQL did DML for years with full table rewrites behind the scenes. It's not particularly hard to emulate, though not entirely pleasant.

(Although I really raise an eyebrow when people talk about performance. Every time I write anything server-like in sqlite I end up regretting it, and get enormous speed boost, not to mention actual concurrency, switching to PostgreSQL.)

For data analysis workloads i just load in my raw source data and then develop a series of scripts that create new tables or views on top of those raw inputs.

For my use cases I've thusly never had to drop/alter a column... but I understand it could be very annoying.

Yeah that's a problem, and I admit that I use some workarounds. I'd like them to implement easy drop, rename etc.

It's pretty fine to just create a new table and insert the data you want into it.

IIRC, some company wanted to use SQLite on an airplane, so they paid the devs enough to bring the test suite up FAA standards. IIRC, they have code coverage of every machine instruction.

"SQLite does not compete with client/server databases. SQLite competes with fopen()." --- https://www.sqlite.org/whentouse.html

Simple. Bulk insert with a 999-parameter limit is just painful; if each entry has 9 columns, you can’t even insert 112 rows at once. In practice distros already compile with higher default; e.g. Debian compiles with -DSQLITE_MAX_VARIABLE_NUMBER=250000, still way higher than this new default.

For instance, you might want to update a large subset of rows via WHERE id IN (?,?,?,...) instead of WHERE id IN (SELECT ...)