`RETURNING` will substantially clean up my code, and I already have one migration which could have just been a `DROP COLUMN`, so this is great news.
On the subject of "it's called 'lite' for a reason", my wishlist does include library functions for working with RFC3339 timestamps. SQLite already ships with a fairly large suite of JSON tools, which are optional to compile into the library, so there's precedent.
Datetimes are of those things which is incredibly annoying to get right, and really belongs inside the database. RFC3339 timestamps are already well designed, since if you stick to UTC (and if you don't store timezone data separately you deserve those problems), lexical order is temporal order, but queries which would be rendered in English as "return all accounts where last payment is ninety days prior to `now`" isn't really possible with string comparisons.
Also, with the JSON library, you can use a check constraint to fail if a string isn't valid JSON, another affordance I would love to have for datetimes.
Grateful for what we just got, though! Just daydreaming...
You're right, that was a bad example. Maybe it's just me, but I've never figured out how to get SQLite to do a query like "select from orders where the order was on a Tuesday in Pacific time". I don't think you can; that requires predicates, and all I see are strftime and some useful pre-cooked variations on it.
On the other hand: Wrong from March 14th to November 7th of this year.
This is where a richer standard library of datetime functions would come in handy. Another example: strftime's %W starts on the first Monday of the year, when the ISO week calendar starts the first Monday of the week containing the first Thursday (so the first week is sometimes found in the previous calendar year).
At that point I'd switch to using additional custom SQL functions. I wrote a plugin called datasette-dateutil https://datasette.io/plugins/datasette-dateutil that exposes some extra functions, but it doesn't have anything useful for timezone calculations yet.
If C++ is your thing,
https://github.com/yangacer/sqlite3cpp
might be interesting. It intelligently wraps C++ functions as SQL functions.
Author here and looks forward feedbacks :).
The good news is that you could use both libraries. `sqlite3cpp::database` supports "attaching" mode that won't close a `sqlite3` instance after sqlite3cpp::database going out of scope. Here is an example for coexisting with SqliteModernCpp:
// db inited by SqliteModernCpp
sqlite::database db(":memory:");
{
sqlite3cpp::database attached_db(db.connection().get());
// create the SQL function as you like
attached_db.create_scalar("mutiply", [](int x, int y) {
return x * y;
});
} // attached_db is going out of scope
// SQL scalar function `multiply` is still availble
db << "CREATE TABLE numbers (n1 INTEGER, n2 INTEGER);";
db << "SELECT multiply(n1, n2) from numbers;";
Note that SqliteModernCpp provides customization of SQL function as well as sqlite3cpp. Though it doesn't seem to have aggregate function supporting
(i.e. sqlite3cpp::database::create_aggregate).
returns ISO weekdate format ('2021-W10-6') the when the input column `yyyymmdd` is in year-month-day format ('2021-03-13'). Not pretty, but it works. The annoying part for me is actually that '%w' returns 0 for Sunday, 1-6 for Mon-Sat, rather than the ISO 1-7 for Mon-Sun.
If sqlite supported macros, we could have put the entire expression in a new "function". But you could (ab)use m4 or even the C preprocessor for that :).
We store 8 million transactions a day in UTC...customer time zone is merely an integer offset stored alongside...simple and easy, what’s wrong with that?
For daylight savings, the offset is generated in real-time in our app at the time of transaction and then stored inside the db...so if 2x TXs occurred in CST at 1:58am (1 before and 1 after DST went into effect at 2am) they would both show up as being between 1-2am on the day of time change. However, the spike in data would show the anomaly for our use case. For a more mission critical app, you could have an additional tinyint field that would get set as 1 for all transactions that occurred in these transitional periods to be able to show data with or without the daylight savings.
I've been storing my datetimes as 64 bit unix timestamps. If I need any additional timezone information, it lives in additional columns. The only edge case we have just requires looking at the user's timezone to project certain facts correctly.
Wow, finally. I Love SQlite. It saved my a* in so much projects where I had to implement an ETL, I just spawned some in memory SQLite database instead of writing some painful custom code in Language X or Z over and over again. Also used it to generate static search results in a flat file blogging platform since it does have some full text search capabilities. And let's not even talk about custom functions, including custom aggregation functions, in any client language, directly in your application code...
SQLite is really great at crunching data! I definitely prefer it over pandas in most cases, as SQL is naturally fit to joins, aggregates etc. Also SQLite works natively with JSON, which is a huge time saver.
I prefer it over pandas for joins etc too. My workflow is (1) do the simple stuff in Python using pandas (2) for some of the complex stuff, I just start creating sqlite tables. If you have datasette installed, you can also view the tables (choosing to write intermediate ones for greater debuggability) pretty easily in your browser.
datasette is a wonderful piece of software! Simon Willison has created a great tooling around SQLite. Can't imagine how much time and energy he has invested in these projects.
Honest question because I haven't messed with Datasette much beyond skimming the home page - how does it improve on a general SQL client like DataGrip or Squirrel or DBeaver?
Obviously I'm biased, so I'd love to hear answers to this from other people (plus I've not really used any of those alternatives much).
Datasette is very "webby". Queries you execute end up in your URL bar as ?sql= parameters, which means you can navigate to them in your history, bookmark them, share links with other people (if your Datasette is shared) and open them in new tabs.
It also does web-style tricks like turning foreign key references into hyperlinks through to the associated records.
Datasette's table browsing feature has faceting, which is enormously powerful. I don't know if those alternatives have this feature or not, but I use this constantly. Demo here (the owner, country_long and primary_fuel columns): https://global-power-plants.datasettes.com/global-power-plan...
All of that said, I don't really see Datasette as competing with existing SQL clients. It's intended more as a tool for exploratory data analysis - I've put very little work into running UPDATE/INSERT statements for example, it's much more about turning a set of relational data into something people can interactively explore.
I work in a research organization where I am responsible for crunching data and produce reports highlighting the most "notable" results. Not that the other data is uninteresting, but the volume is such that Excel cannot handle it and even distributing it can be challenging for non-computer-technical folks without dedicated solutions.
Instead, I can dump all of the processed results into a table, create some views highlighting analysis X vs Y, and share links that give others the ability to ask questions I had not even considered. Now the user is empowered to ask anything and they do not need to engage me for "simple questions". Everybody wins. I believe there is also an extension that allows you to generate and save new queries through the web interface.
It is not a tool for a professional analyst, but a means to collaborate with others. There are heavier/more feature rich alternatives, but Datasette is my favorite tool for getting results out the door without hassle (can run it off of a laptop after a pip install).
I have had such enthusiastic feedback from granting people access to the ~full dataset. They have been conditioned to expect whatever subset can fit inside an email or a powerpoint slide. I feel a little embarrassed when people fawn over the utility because it is so easy to get running.
Have not yet had a chance to try the idea, but I am toying with using render-images to bake in pre-built plots + markdown for reporting the output. Queryable report in a file. Dynamic Vega plotting (RShiny-ish) is also in the back of my mind, but that feels too close to magic.
It is an incredibly useful tool, and I appreciate the workflows you have enabled.
I use datasette, datagrip and excel to process sales data.
- datasette to surface data via rest to excel (power query)
- datagrip to get the data how I want it, the json1 extension is so so much easier to work with than power query and for my use cases extremely fast.
This gives you (arguably) the best data grid in the world (excel) but without the horrible experience of building a pipeline in power query that will eventually become too slow and/or randomly crash and hang.
I would really, really like an in the box Regex extension so I can create SQLite views without the crazy lengths I have to go with SQL to, for example split a comma delimited list in a field.
I get the feeling we’ve been trained to expect less, with poor, incredibly slow legacy products. But really for many use cases all the opensource relational databases give instant results.
Does someone here use DuckDB in production? Is it as stable as SQLite?
The hosting company I have to work with has a very old version of SQLite installed on the server and they don't want to update it. So I was looking at whether I could replace it with DuckDB since it seems to be easy to install with pip.
pysqlite3-binary is a Linux only package in PyPI that includes a recent version of SQLite
apsw is a cross-platform package, it brings in a modern version as well, and additionally exposes everything SQLite can do (useful for me, you can write VFS drivers in python for it). The version of apsw in pypi is hopelessly out of date, the homepage has more details on how to install the latest version.
I very, very much like the idea of DuckDB, but have had many headaches with what I would consider "low-medium" amounts of log data (400 million rows, 180GB+, after stripping out any un-needed text) which works without issue using SQLite, btw, other than the expected slowness of row-oriented storage on the column-aggregate queries that I needed.
I have tried many different ways of importing the data with no luck. Usually it turned into a memory issue. This was irrespective of how I tried to import, whether it was in batches through a script, or using the built-in import functionality, or breaking the data into chunks prior to import, or anything else I tried. I occasionally keep trying to find a way, because I like the idea and features of DuckDB so much.
Of note, MonetDB slurped it right in without issue, but I would much rather use something not so "heavy" like DuckDB.
Also, regarding SQLite, I have DBs with billions of rows that work flawlessly, though not as fast as postgres.
You could try Clickhouse. It's a bit heavier than DuckDB and the default mode is server-client. But you can also use the client (a single binary) without a server to directly query data from csv or parquet files.
edit: added a better link, the stand-alone mode is called clickhouse-local
DuckDB is designed for query processing, not updating data - I guess you would not want to use it for the transaction processing workloads just because it's easier to install. If you are doing mostly complex queries, then DuckDB is great, but I think there is going to be little usage directly in production, I'd expect it's more used for data processing
And how great is SQLite for transactional workloads? Every transaction locks up the entire db. There is no page or even table level locking. DuckDB can't be worse than that.
In practice, I've found that most SQLite write operations take low-milliseconds to complete - so even under a very heavy write load (assuming WAL mode) you're unlikely to see any contention between writes.
For my own stuff I maintain an in-memory Python queue of write operations and apply them in order against a single write connection - while running numerous other read-only connections to serve SELECTs.
I'm curious about this as I always prefer files and collections of structures or classes than having to deal with databases and ORMs. What else can you do aside from joining and aggregation?
I love SQLite. Some of the best, most reliable code out there made by a small handful of people who really care about excellence. I’m curious if anyone here as experience with DQlite (https://dqlite.io/ ) from Canonical. It’s basically a distributed version of SQLite that combines it with the RAFT consensus protocol. It’s sounds like a great idea and I’m considering it for a project I’m working on now, but I’d love to hear what people think of it in practice.
Is there a site equivalent to "caniuse.com" for all the different database features by product and version? If not, someone should build one. Often some DB would say they added feature X but what does that mean? Did they implement this in advance of other DBs or behind others?
The SQL spec is barely comprehensive of the SQL dialects found “in the wild”, let alone the entirely independent and much more interesting set of non-table managing/querying features databases provide.
It provides at best a baseline for comparison.. and a fairly shallow one at that
The `RETURNING` is so awesome! I'm implementing a set of data structures on top of SQLite, one of them is a queue[0], and I had to do a transaction to lock a message and then return it, but this makes it easier.
There's one little issue I keep finding with SQLite, and it's that most virtual servers / VM images ship with version 3.22.0, and upgrading often means building from source.
In any case, SQLite is absolutely wonderful. My favorite way of building products is having a folder for all the DBs that I mount to docker-compose. This release makes it even better.
> However, SQLite is incredibly powerful. It can scale to thousands of concurrent users with the correct settings
I never thought SQLite can do something like this. How does one go about scaling it for thousands of users? Are these just reads or read, writes? Are there any known real world use cases where people are actually doing this in production?
Yes, I’m aware. I usually compile it during a multi stage docker build. There’s also pysqlite3 for Python when I need the latest version.
However, my issue is when I want to access the DBs from the CLI to check something. If I am using features from 3.34.0 (inside my app) I will not be able to use them outside of it. I guess I will have to add the SQLite compilation as an extra step in my base bootstrapping script when I create servers. I hope some day apt-get will give me the latest version by default.
I don’t use SQLite as my main data store for any project that runs on a server because a dedicated DB server has advantages. But that doesn’t mean that I don’t use it for production use. Here is an example from a few years ago:
I needed to pull in a large dataset from CSV (several dozen gigabytes), do a bunch of transforms on it, extract a small subset of data that I actually was interested in, and load that subset into the main DB. I needed to do this in a way where it wouldn’t bog down the main DB server (which stored less data than what was in the CSVs), and also in a way where I could potentially load diffs of the CSV files as updates. So the solution was that a dedicated job would run on a server where the SQLite database was used to load the CSVs and to manipulate the data with SQL because that was way more convenient than doing it with ad hoc objects in Python. Once done, extract rows from SQLite and load into Postgres. The local DB would stick around to use the diffs for incremental updates, but if it was lost it was easy to recreate it from the last full dataset. Not highly available but highly understandably, cheap, fast, and dead simple.
I technically only used it for logfiles, but as a key part of a chain of scripts / utils forming a simple custom test automation suite I cobbled together. Having done that, I'd reach for it first for any ETL-like task given structured data on the order of a few hundred thousand or maybe a few million rows.
I recently used SQLite in my side project [1]. Here's what I learned from using it:
* Shaving the overhead of network calls for queries sped up my site significantly.
* Most CI/CD providers include SQLite in their base linux images and setup in local envs is easy as well. Running tests against the actual database is simple.
* Replication is not available out of the box. To share a database with multiple instances of your app you will have to use a shared storage volume or some of the available solutions at [2][3][4], but they each come with their caveats.
Litestream author here. Let me know if you have any questions. It's built to run as a separate process and to be super easy to get up and running. We have a GitHub discussion board and an active Slack group as well if you need any help.
Hey, lead of Debezium here, a change data capture tool for a number of databases (not SQLite, though). Out of curiousity, how are you implementing change ingestion, is there some interface/API in SQLite which lets you do this? Or are you manually parsing its log files?
Hi Gunnar, good to meet you. Litestream works by reading off the SQLite WAL file which acts as a circular buffer. It takes over the checkpointing process to control when the buffer rolls over so it doesn't miss any frames. Those frames get copied over and each buffer is recreated as a sequential set of WAL files that can be replayed to reconstruct the state of the database at a given point-in-time.
It sounds like Litestream differs from Debezium in that it provides physical replication rather than logical row changes. However, I've been toying with the idea of determining row-level changes from the WAL frames by using ptrmap pages to traverse up the b-tree and determine the owner table. There's a bunch of stuff on the roadmap before that like live read replication though.
There's some additional info on the site about how Litestream works[1] and I'm planning on making a video similar to this Raft visualization[2] I did a while back.
Thanks for sharing those insights. Indeed Debezium is based on logical replication. I'll definitely keep an eye on Litestream, perhaps there may be some potential for collaboration at some point? SQLite hasn't come up really in our community so far, but personally I find it very interesting.
Yeah, for sure. I'm up for some collaboration where it makes sense. I can understand how SQLite probably wouldn't come up so far. If someone is running CDC on Kakfa (Debezium) then they're probably running a client/server database instead of an embedded one.
Hit me up on Twitter[1] if you have any questions or we have a pretty friendly, active Slack[2] too.
Yes, it reads off the WAL then compresses the frames using LZ4 and then uploads to S3. The SQLite WAL acts as a circular buffer so Litestream takes over the checkpointing process to control when it rolls over so it can recreate that buffer as separate files. There's additional information on the web site: https://litestream.io/how-it-works/
This release looks amazing. I use sqlite in almost every one of my sideprojects because it works everywhere and I can just open a file to inspect my db state which is really useful when mashing and trying to decide on table formats etc. The column delete and RETURNING additions are godsends, someone else said it already but this makes replacing postgres a lot easier.
I am going to have to do some testing. Right now, we lock on a single SQLiteConnection instance because of the fact that insert & id retrieval are 2 separate invocations. If we can get away with just a single ExecuteQueryAsync<long>(MyUpdateSql, MyObject) call and it's thread safe, then we could drop a ton of locking abstractions at the application level.
> If we can get away with just a single ExecuteQueryAsync<long>(MyUpdateSql, MyObject) call and it's thread safe, then we could drop a ton of locking abstractions at the application level.
Not only would you have to ensure you're always setting the db to SQLITE_OPEN_FULLMUTEX, https://sqlite.org/c3ref/errcode.html and https://sqlite.org/c3ref/changes.html are still not coherent in multithreaded contexts. Calling them won't corrupt anything, but they'll return garbage. The latter probably doesn't matter much, but the former seems quite relevant if you want to handle errors beyond "this failed".
Correct - We open our databases in serialized mode and access a single connection from multiple threads.
We actually are not concerned with handling detailed error information. Our usage of these databases is very well bounded. All of the SQL that will ever execute against them is contained in 1 series of constant declarations, and the mappers ensure request data is well-formed before attempting inserts & updates. SQLite is really just there to provide identity and persistence for our business objects.
We are still able to use these fields effectively in local development and unit testing, since in these contexts there is only ever 1 thread hitting the database at a time.
Now only MySQL is missing `RETURNING` from the relational databases that still matter. Postgres had it for ages, SQL Server has it with OUTPUT (with a caveat of breaking when using triggers) and SQLite finally added them on this release.
Using `RETURNING` is so much nicer than spending four round-trips to `BEGIN`, `INSERT`, `SELECT` and `COMMIT` otherwise...
> Especially since last_insert_rowid() can never be free of race condition
I think that's overstating it. It's per-connection, not per-database. If you don't share connections between threads, it's fine. If you keep the connection locked / checked out for the span of the two statement, it's fine. You're probably doing the latter anyway if you use transactions.
Returning looks nice, though! Particularly that it can return "one result row for each database row that is deleted, inserted, or updated" rather than just one.
> Returning looks nice, though! Particularly that it can return "one result row for each database row that is deleted, inserted, or updated" rather than just one.
That… seems normal? Returning just one row would make no sense, how would even a trivial `RETURNING id` work otherwise?
Ah I see, I was a bit confused because I'm used to RETURNING from pg and I've been waiting for sqlite to add it for a very long time. Especially since ON CONFLICT was added as it made the issue even worse.
It's not part of any standard, so they'd need to pick what to implement. They docs say they're modeling it on postgres's, which is a perfectly reasonable extension.
True, though SQL isn't much of a standard anyway. I literally don't know of a single SQL database that implements the SQL standard to spec with no fiddling. Or more precisely, the vast majority of SQL databases out of the box behave differently, so anything more complex than a basically SELECT + JOIN generally isn't very cross-compatible.
One thing to note about the `RETURNING` clause is that it can't be used in subqueries[0] or CTEs. One thing I do with Postgres is using multiple CTEs with `RETURNING` clauses to have a query with many steps, without having to open a transaction and doing many queries. The SQLite documentation says it's something that they want to implement. Having that would be very nice. We could do complex queries all at once without having to lock the DB during the whole transaction.
This is pretty neat. Now that they added `exp`, I'm tempted to implement an autojumper using [frecency] in SQL. I tried it once before, but gave up specifically because of this IIRC.
A very simplified example: if you call `j pro`, the command should take you to the folder that best matches the name based on some criteria (in my case, one of them would be frecency). In my case, that would be my projects folder in another drive.
The nifty thing about them is that you don't have to make those aliases manually -- the tool learns the directories you've visited over time.
Of course, the tool could take you to the wrong path, but with a well made set of matching/prioritization rules, I've found it more useful than not.
Returning and column deletes are a big deal. This will make me return back to SQLite.
One other huge issue is that sqllite doesn’t enforce schema. You can say that a column is an int but it will happily insert strings. That is a huge minefield.
I want SQLite but with strict enforced scheme that errors when it encounters an incorrect type.
https://sqlite.org/datatype3.html says "the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases"... but it doesn't expand on that point at all.
My best guess is that this is a language culture thing. SQLite has a strong connection to the Tcl community - it started life as a Tcl extension: https://www.sqlite.org/tclsqlite.html - and Tcl is a very dynamically typed language.
Whatever the reasons, it's not going to change now: SQLite has extremely strong backwards-compatibility guarantees, and this is very much baked into how it works.
As a dynamic language person this has never bothered me in the slightest, but it's definitely something that comes up a lot from people who are put off using SQLite because of it.
In many engines, row-tuples are materialized from rows by having the query planner turn the table’s metadata into a mapping function. With this approach, you get a bunch of things “for free”—the ability to reorder columns, rename columns, add new nullable all-NULL columns or default-constant all-default-valued columns, all without doing any writing. Rows instead get rewritten when the DB builds a new version of them for some other reason (e.g. during UPDATE) or during some DB-specific maintenance (e.g. during VACUUM, for Postgres.)
I don’t believe SQLite works this way. It gives you literally what’s in the encoded row, decoded. I believe this allows it to be either zero-copy or one-copy (not sure which), but it has the trade off of disallowing these fancy kinds of read-time mapping.
IMHO it’s a trade off that makes sense, on both sides. Client-server DBMS inherently need to eventually serialize the data and send it over the wire, so fewer copies doesn’t get you much, while remapping columns at read time might get you a lot. SQLite can hand pointers directly to the app it’s embedded in, so “direct” row reads are a great advantage, while—due to the small size of most SQLite DBs—the need for eager table rewrites on ALTER TABLE isn’t even very expensive.
postgresql does essentially nothing on a drop column, in part because it doesn't use fixed-size tuples:
> The DROP COLUMN form does not physically remove the column, but simply makes it invisible to SQL operations. Subsequent insert and update operations in the table will store a null value for the column. Thus, dropping a column is quick but it will not immediately reduce the on-disk size of your table, as the space occupied by the dropped column is not reclaimed. The space will be reclaimed over time as existing rows are updated.
but if you VACUUM FULL (or CLUSTER) it will immediately rewrite the entire table.
Also note that storing a null means forcing a null bitmap for every row (even if it's not otherwise used).
The DROP COLUMN form does not physically remove the column, but simply makes it invisible to SQL operations. Subsequent insert and update operations in the table will store a null value for the column. Thus, dropping a column is quick but it will not immediately reduce the on-disk size of your table, as the space occupied by the dropped column is not reclaimed. The space will be reclaimed over time as existing rows are updated.
I've been using pysqlite3 which is a drop-in replacement compiled against a more recent version. pysqlite3-binary even gives you a (Linux-only) pre-compiled wheel.
You can also read the release notes[0] which is pretty readable as always doesn't take the extra time to denigrate the work of this excellent and freely provided software
I think an independent implementation would help. Personally, I think SQLite is a unique project which is of such high quality that independence is unimportant, but others do not see it that way.
We now have three steaming piles of indexedDB garbage that are still (last I checked) implemented on top of sqlite anyway. I don't see how anyone could think the world became a better place after the change.
I run SQLite in serverless environments (Cloud Run, Vercel, Heroku) for dozens of projects... but the trick is that they all treat the database as a read-only asset.
If I want to deploy updated data, I build a brand new image and deploy the application bundled with the data. I tend to run the deploys for these (including the database build) in GitHub Actions workflows.
This works really well, but only for applications that don't need to apply updates more than a few times an hour! If you have a constant stream of updates I still think you're better off using a hosted database like Heroku PostgreSQL or Google Cloud SQL.
I've seen some folks use Litestream [1] to continuously replicate to S3 storage and restore it later after their container is killed. There are also other serverless platforms like Fly[2] that provide persistent disk and long-running (but cheap) container VMs.
If your app is in a Docker container, you need to have some way to mount a persistent volume for the database. AWS Fargate and fly.io both offer storage volumes.
I doubt you can have a writable filesystem with any server less offering.
Serverless offerings force you to have all your state network accessible so that they can offer you horizontal scalability by using load balancers - so no writable+persistable file system that SQLite needs.
What I do is use a normal VM on GCP, run my docker container on that, and write to a regionally replicated disk (so it is copied between zones on every write). Snapshots run hourly and are stored in GCP storage. This gets you high reliability (if you can have a few mins down time to reboot) but not horizontal scalability.
You can check out Dokku, which is the open source, single server Heroku equivalent. You can mount a storage volume to the container and access it from your app running in Docker. Dokku makes it stupid simple, so it might be worth reading the source code to see how they do that.
I use Dokku, but it also has a Postgres plugin which is basically just a few commands. That also gives you the niceties of dokku pg:backup etc. If you’re on Dokku anyway then that’s what I would recommend.
For me, SQLite remains the perfect file format for command line tools.
Anyone know how to get this version running on a Mac? Trying to brew upgrade it fails:
sqlite is keg-only, which means it was not symlinked into /usr/local,
because macOS already provides this software and installing another version in
parallel can cause all kinds of trouble.
And checking installed version:
$ type sqlite3
sqlite3 is /usr/bin/sqlite3
$ sqlite3
SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
That's not a direct quote from the maintainers - the author of the post seems to have made it up.
I imagine what changed is that the SQLite maintainers actively listen to their developer community, and decided it was finally time to prioritize that piece of work over other features.
If you can do a LEFT OUTER JOIN I don't see that there's any benefit in being able to do RIGHT OUTER JOIN - surely you can change the table order yourself?
my worries are more, if i use it, like everywhere, that at some point, some "ORM Layer" like for example hibernate will use the right outer join for something and then break since this is not implemented. and there are also a few other quirks where i am not sure, if some systems do not need them (grant/revoke is of course meaning less)
As superb a piece of software as SQLite is, it's definitely still missing features that many would use. No FULL OUTER JOINs is maybe the most notable that I've encountered.
I'm curious as to what you use FULL OUTER JOINS for - I've so far never felt the need for them in my own work but I'd love to understand what I'm missing out on.
The one use case I’ve found recently is joining sparse time series data. In other words, when the tables you’re joining are “equally important” as opposed to one depending on the other.
I think the changelog is a bit confusing on that point. I interpret it as meaning that CTE is a default optimization:
"The default behavior was formerly NOT MATERIALIZED, but is now changed to MATERIALIZED for CTEs that are used more than once"
The default was NOT MATERIALIZED only because previous versions of SQLite didn't have this feature at all.
"now changed to MATERIALIZED for CTEs that are used more than once" means that the query planner DOES use this optimization by default if you don't do anything - the optimization only makes sense for CTEs that are used more than once in the same query.
> The default was NOT MATERIALIZED only because previous versions of SQLite didn't have this feature at all.
The default was NOT MATERIALIZED because it's what the sqlite devs had selected whe they implemented CTEs.
Pg did the exact opposite before the feature was added (and exposed to end-users): CTEs would always be MATERIALIZED. And similarly, with the introduction of [NOT] MATERIALIZED they changed the default in some cases: for pg if the CTE is non-recursive, has no side-effects, and is only referenced once, then it defaults to NOT MATERIALIZED.
> so you have to declare the CTE as materialized to get this optimization
Not necessarily. You can get a materialized CTE if you request it regardless of its usage pattern, but you will also get one if the CTE is used more than once unless you suppress it.
Interesting thought; with the client/server database model, RETURNING isn't relevant for CDC, which rather extracts changes from the transaction log, not scoped to any particular client session. But in the case of SQLite and its embedded way of running, one could indeed envision to use it for implementing some basic CDC functionality at the application level. It'd still lack metadata though like transaction ids, log file offset, etc.
This is not a problem for "row stores" though, it's specific to SQLite.
As a matter of fact, any classical RDBMS can effectively store columns off-page and and most of them do just that in the case of large TEXT/JSON/BLOB columns.
Column stores like Redshift, Snowflake, etc. are optimized for a much narrower set of use cases. The more widely used databases could do the same optimizations internally but that would make them much less useful.
On the subject of "it's called 'lite' for a reason", my wishlist does include library functions for working with RFC3339 timestamps. SQLite already ships with a fairly large suite of JSON tools, which are optional to compile into the library, so there's precedent.
Datetimes are of those things which is incredibly annoying to get right, and really belongs inside the database. RFC3339 timestamps are already well designed, since if you stick to UTC (and if you don't store timezone data separately you deserve those problems), lexical order is temporal order, but queries which would be rendered in English as "return all accounts where last payment is ninety days prior to `now`" isn't really possible with string comparisons.
Also, with the JSON library, you can use a check constraint to fail if a string isn't valid JSON, another affordance I would love to have for datetimes.
Grateful for what we just got, though! Just daydreaming...