SQLite 3.32

[iagovar]

As a non-dev intruder I have to say that I love SQLite. I do a lot of data-analysis and it makes everything easy, from fast SQL Wizardry to sharing the DB just coping a file! Just how amazing is that?!

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

[mmsimanga]

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/

[StavrosK]

> 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.

[jventura]

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..

[hobs]

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.)

[dragonshed]

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.

[ak39]

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.

[giancarlostoro]

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.

[bob1029]

For our B2B application, we've been using SQLite as the exclusive means for reading and writing important bytes to/from disk for over 3 years now.

We still have not encountered a scenario that has caused us to consider switching to a different solution. Every discussion that has come up regarding high availability or horizontal scaling ended at "build a business-level abstraction for coordination between nodes, with each node owning an independent SQLite datastore". We have yet to go down this path, but we have a really good picture of how it will work for our application now.

For the single-node-only case, there is literally zero reason to use anything but SQLite if you have full autonomy over your data and do not have near term plans to move to a massive netflix-scale architecture. Performance is absolutely not an argument, as properly implemented SQLite will make localhost calls to Postgres, SQL Server, Oracle, et. al. look like a joke. You cannot get much faster than an in-process database engine without losing certain durability guarantees (and you can even turn these off with SQLite if you dare to go faster).

[rakoo]

> 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/)

[seektable]

+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.

[hobs]

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.

[Kaze404]

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)?

[lenkite]

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

[dtf]

While reading the documentation for iff(), I noticed the command line function edit(), which is pretty cool.

  UPDATE docs SET body=edit(body) WHERE name='report-15';
  UPDATE pics SET img=edit(img,'gimp') WHERE id='pic-1542';

[ha470]

While I love SQLite as much as the next person (and the performance and reliability is really quite remarkable), I can’t understand all the effusive praise when you can’t do basic things like dropping columns. How do people get around this? Do you just leave columns in forever? Or go through the dance of recreating tables every time you need to drop a column?

[why-el]

One of the great things one can learn from SQLite is the degree to which they unit (and integration) test their source code. It's honestly the best unit test document I have read in my career to date: https://www.sqlite.org/testing.html.

[ardy42]

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.

[wenc]

SQLite is great but its decision in not having a standard datetime/timestamp datatype -- a standard in all other relational databases -- has always struck me as a surprising omission, but in retrospect I kind of understand why. Datetimes are undeniably difficult.

So sqlite leaves the datetime storage decision to the user: either TEXT, REAL or INTEGER [1]. This means certain datetime optimizations are not available, depending on what the user chooses. If one needs to ETL data with datetimes, a priori knowledge of the datetime type a file is encoded in is needed.

In that sense, sqlite really is a "file-format with a query language" rather than a "small database".

[1] https://stackoverflow.com/questions/17227110/how-do-datetime...

[combatentropy]

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

[trashburger]

>Increase the default upper bound on the number of parameters from 999 to 32766.

I don't want to know the use case for this.

Keep rocking on, SQLite. It's the first tool I reach for when prototyping anything that needs a DB.

[oefrha]

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.

[dtf]

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

[zubairq]

Thanks so much for SQLite. Amazing and stable database. Yazz Pilot (https://github.com/zubairq/pilot) is built on it

[oefrha]

Good to see a ternary function iif() added. Case expressions are usually pretty painful and/or unreadable when using query builders.

[RivieraKid]

Is it reasonable to assume that in most current deployments of PostgreSQL or MySQL, SQLite would be at least an equally good choice?

I was recently choosing a database for a medium-size website and SQLite seemed like an obvious choice. One thing I was worried about was that the database locks for each write - but this is apparently not true anymore with write-ahead log.

[duskwuff]

Depends on the environment. SQLite will scale out reasonably well so long as it's only needed on one machine. As soon as you need a network-accessible database, traditional database servers start looking like a better option.

[Carpetsmoker]

I ran some performance/reliability benchmarks on the product I'm working on (which supports SQLite and PostgreSQL), and SQLite was about 30% faster than PostgreSQL.

This won't hold true for all use cases; one table now has 11 million rows, and I'm not sure how well SQLite would perform on that. The benchmark was very simple anyway, and it's mostly a read-only where users don't update/insert new stuff. Would be interesting to re-test all of this.

[justinmeiners]

Yes, most wordpress or joomla sites come to mind. There is typically only one application communicating with it, the user doesn't typically doesn't admin the database directly (and if they did they want a file), medium traffic load (hundreds per second), and most of the queries are reads, with the occasional content update.

As soon as you get into privilege levels or heavy loads, then those others make more sense.

[emadda]

I’ve been using SQLite on GCP for a few small projects and it seems to work well.

I use docker volumes to write to disk. I pass the disk directory to my process via a CLI arg.

When running on a VM these disk writes are replicated between zones (this is default for regional GCP disks). So you get zero config high availability (if you can tolerate down time during a reboot).

[rhencke]

You might find DQLite of interest.

https://dqlite.io/

[devwastaken]

Are there resources for good practices on database formatting? I feel that what I make 'works', but I'd be curious on what experienced databases look like.

For example I have an app that you upload files through. Files can be local to the server or on s3 and have metadata. I end up making a new table for the API points. Like a table for listing files/directories. A table for local files and a table for s3 files. Then a table for the metadata, and a table for the kind of file it is, etc. It works, but it feels like a heavy hammer.

[vbezhenar]

You might want to check out Codd books. He invented relational model after all and his books cover database design.

[nattaylor]

A few nice little conveniences like IFF(). I like reading SQLite released because they seem good at avoiding adding cruft. (The refusal to implement JSONB comes to mind.) Now if only I could get my shared web host to upgrade to a recent version...

[jventura]

If you have ssh access to your web host, you may be able to upgrade it yourself. I needed something more recent for django 2.2 and had to download the latest sqlite, compile it, put the lib in some folder and add the lib to .bashrc so that python3 could use it (ld_include_flags or something like that).

Look for it on google, it’s possible to do it..

[cptnapalm]

Recommendations for learning SQL with SQLite? I've recently started doing the Khan Academy videos, and am liking them, but I'd like more practice problems and explanatory text.

[justinclift]

Some of these may be useful:

https://github.com/sqlitebrowser/sqlitebrowser/wiki/Tutorial...

https://github.com/sqlitebrowser/sqlitebrowser/wiki/Video-tu...

One of our developers (Manuel) started putting together lists of tutorials and video's for SQLite + DB Browser for SQLite a while back.

There are probably more we've missed, and contributions to those pages (etc) are welcome. :)

[fibers]

try importing this data and play around with it

https://www.percona.com/blog/2019/01/24/a-quick-look-into-ti...

[RivieraKid]

One possible disadvantage of SQLite is that it only allows one writer at a time (but writes don't block readers with write-ahead log enabled). I'm really curious about whether Postgres performs better at concurrent writing, couldn't find any benchmarks. In theory, disk writes are always sequential, so I'm skeptical Postgres would do substantially better.

[justinclift]

> I'm really curious about whether Postgres performs better at concurrent writing ...

Very much so. PostgreSQL easily handles lots of concurrent writing. It's a use case where PostgreSQL is much better than SQLite. :)

[therealdrag0]

SQLite isn’t a db-server like most other mainstream databases. It’s more of a db-file; almost an excel file. This means it’s usecases are quite different and perf comparisons don’t make sense.

[pachico]

I have running in production a SQLite powered service for the free Geonames gazetteer. It's a read only service so it fits perfectly and providing really good performance. I also use it to work with data coming in CSV format. What a great piece of software!

[me551ah]

Where can you use sqlite?

Embedded: Yes

Raspberry Pi: Yes

Mobile Apps : Yes

Desktop Apps: Yes

Microservices: Yes

Big Monolith : Yes

Browsers. : No

[goutham2688]

check this out for browsers https://github.com/sql-js/sql.js

[stephen82]

Did you mean whether browsers use it or not?

If that is the case, if not all, the majority of them already use it for ages now; else, please clarify what you mean.

[zeroimpl]

Why is it “iif” instead of “if”? I don’t recall “if” being a keyword in SQL

[hn_1234]

is SQLite used for big data storage ? What are high end use cases than small data points which I mostly use it for. excuse me if its a dumb question

[boksiora]

my favorite db format