What's New in SQLite 3.35

https://nalgeon.github.io/sqlite-3-35/

by nalgeon • 5 years ago

496 167 5 years ago

nalgeon.github.io

gigatexal3• 5 years ago

with the returning clause it should be trivial to do cdc now, no?

paulryanrogers• 5 years ago

CDC?

dorfsmay• 5 years ago

https://en.wikipedia.org/wiki/Change_data_capture

gigatexal3• 5 years ago

yup, change data capture.

nalgeon7• 5 years ago

Isn't it done with triggers? SQLite supports them since long ago

gunnarmorling3• 5 years ago

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.

gigatexal3• 5 years ago

True that’s right. I was just highlighting the spin that SQLite puts on the returning idea. What a really cool feature and db

p4bl02• 5 years ago

I was already completely fan of SQLite, but some of these new features are the cherries on the cake =).

nalgeon7• 5 years ago

Yeah, like being able to remove a column! And math functions, can't believe they finally implemented those ツ

p4bl02• 5 years ago

Yeah! And RETURNING is awesome. No more second query with last_insert_rowid() hoping that no queries were done in between :D.

4ec0755f5522• 5 years ago

Oh that "returning" is nice; in current SQLite I'm doing a second query to get that:

  SELECT last_insert_rowid()

edoceo• 5 years ago

Yea, this one and column removal are my two favourites. And it makes it easier to switch this and PG

masklinn8• 5 years ago

Especially since last_insert_rowid() can never be free of race condition, and is basically useless whenever ON CONFLICT is involved.

scottlamb2• 5 years ago

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.

bob10293• 5 years ago

This sounds incredible.

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.

masklinn8• 5 years ago

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

fastball2• 5 years ago

I'm actually surprised it took SQLite this long to implement RETURNING, such a useful SQL language feature.

wnoise• 5 years ago

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.

https://www.sqlite.org/lang_returning.html

pimeys• 5 years ago

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

rphln2• 5 years ago

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.

[frecency]: https://wiki.mozilla.org/User:Jesse/NewFrecency?title=User:J...

marvel_boy• 5 years ago

What is an autojumper?

rphln2• 5 years ago

It's a shell command that allows you to `cd` to places without having to type the whole path. Some implementations are https://github.com/wting/autojump and https://github.com/rupa/z.

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.

throw_m239339• 5 years ago

Column removal

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

dmarlow2• 5 years ago

Probably the second most popular source of user suffering.

It was #1 for me! Finally indeed.

asveikau• 5 years ago

I would have guessed desire to be the #1 source of user suffering.

spaniard89277• 5 years ago

I had to implement an ETL

Buddy, google DuckDB, it's like OLAP SQLite. You'll thank me later.

bondant3• 5 years ago

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.

nalgeon7• 5 years ago

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.

abhgh• 5 years ago

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.

tinus_hn• 5 years ago

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.

rgacote• 5 years ago

Possibly the first step towards the ability to rename a column?

benasher44• 5 years ago

Rename is already here! Came in 3.25.0 (https://www.sqlite.org/changes.html)

u678u2• 5 years ago

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?

oblio• 5 years ago

You don't need to use an ORM, just use a query builder.

dmarlow2• 5 years ago

To delete a column, SQLite have to completely overwrite the table - so the operation is not fast. But it’s still nice.

Can someone with more knowledge/experience ELI5, please? Is this essentially how it's done in other db engines? TIA

awestroke• 5 years ago

In postgresql:

  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.

airstrike3• 5 years ago

The space will be reclaimed over time as existing rows are updated.

Or as you VACUUM, correct? I think it lets you specific a column name too

masklinn8• 5 years ago

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

derefr• 5 years ago

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.

esturk• 5 years ago

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?

WrtCdEvrydy2• 5 years ago

The issue is there's no database feature spec so you end up with each DB having it's own version of something.

dragonwriter• 5 years ago

The issue is there's no database feature spec

ISO/IEC 9075 disagrees.

nalgeon7• 5 years ago

Markus Winand is doing something like that at https://modern-sql.com/. But it's not nearly as extensive as caniuse.com

scrollaway• 5 years ago

That's a really wonderful idea.

foobar33333• 5 years ago

There is no standard spec so the same function name doing the same kind of thing will work in different ways or support different features.

The_rationalist• 5 years ago

It's such a shame that webSQL died, I hope they will reconsider the decision in the next few years.

slaymaker1907• 5 years ago

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.

hajile2• 5 years ago

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.

hajile2• 5 years ago

Even if they don't, I at least hope they'll provide an alternative. indexedDB is hardly usable let alone a replacement for anything.

Felk2• 5 years ago

SQLite is called ‘lite’ for a reason. If you need functions, add them yourself.

Most people I know, myself included, pronounce it that way. But Richard Hipp, the creator of SQLite, actually pronounces it SQL-ite, like a mineral.

airstrike3• 5 years ago

actually pronounces it SQL-ite, like a mineral.

Surprised you didn't say he pronounces it SQL-ite like an animal

samatman4• 5 years ago

"Esqueuellite, like a mineral" is D. Richard Hipp's phrasing.

u678u2• 5 years ago

It seems there is no universal agreement so you can pronounce which way you want. https://english.stackexchange.com/questions/431329/what-is-t...

euroderf• 5 years ago

Why not "sklite"? Monosyllabic - lite, ya know.

moonchild• 5 years ago

To delete a column, SQLite have to completely overwrite the table - so the operation is not fast. But it’s still nice.

Yet another reason why column stores are superior to row stores.

wayneftw• 5 years ago

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.

einnjo• 5 years ago

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.

[1] https://www.tendielist.com

[2] http://litereplica.io

[3] http://litesync.io

[4] https://bedrockdb.com

tyingq• 5 years ago

More than replication, but dqlite might be worth a look as well. It is supposedly stable now that it was rewritten in C.

https://dqlite.io/

deknos5• 5 years ago

also https://github.com/rqlite/rqlite

toddgruben• 5 years ago

You can add this one to the list https://litestream.io/

benbjohnson5• 5 years ago

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.

deknos5• 5 years ago

Do joins now work in every way (full, inner, outer)?

ketralnis2• 5 years ago

They always have, haven't they? https://www.sqlite.org/lang_select.html

deknos5• 5 years ago

not according to https://www.sqlite.org/omitted.html

krat0sprakhar• 5 years ago

What's the best way to run a service that uses SQLite in a serverless / container environment (e.g. Cloud Run).

I'd love to use this for my personal projects and I'm not sure how to set this up in a container given their ephemeral nature.

elamje• 5 years ago

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.

hectormalot• 5 years ago

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.

segf4ult• 5 years ago

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.

benbjohnson5• 5 years ago

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.

Disclaimer: I'm the author of Litestream.

[1] https://litestream.io/

[2] https://fly.io/

simonw14• 5 years ago

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.

One example of a site I deploy like that is https://datasette.io/ - it's built and deployed by this GitHub Actions workflow here: https://github.com/simonw/datasette.io/blob/main/.github/wor...

justsomeuser• 5 years ago

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.

swlkr• 5 years ago

This release changes everything!

My biggest complaints were no returning clause, and no drop column.

That's it, now I can run sqlite in production without hesitation!

deft• 5 years ago

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.

ketralnis2• 5 years ago

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

[0] https://sqlite.org/releaselog/3_35_0.html

samatman4• 5 years ago

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

nalgeon7• 5 years ago

SQLite has ISO-8601 compatible date functions, isn't that enough?

  sqlite> select datetime('now', '-90 days');
  2020-12-12 21:44:22

https://sqlite.org/lang_datefunc.html

rover0• 5 years ago

Without a timezone the time is meaningless for many purposes.

samatman4• 5 years ago

Beats a swift kick in the pants!

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.

porker2• 5 years ago

Yes native date and time handling would be awesome. I dislike dealing with them as strings.

And then temporal tables. And natively versioned tables. I can dream...

bob10293• 5 years ago

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.

siscia• 5 years ago

I am running zeeSQL.com a Redis module that embeds SQLite into Redis to provide SQL and secondary indexes (search by value) on top of Redis.

This release it is the first one I am really excited about .

As soon as I can allocate few minutes I will update the SQLite code of the software.

The RETURNING makes a lot of queries and use cases an order of magnitude simpler.

eigenvalue• 5 years ago

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.

zdkl• 5 years ago

There's a very similar project, https://github.com/rqlite/rqlite which has always felt more pleasant me.

IgorPartola• 5 years ago

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.

chrisweekly2• 5 years ago

Sounds like a job for lnav (https://lnav.org), an awesome little CLI "mini-ETL" powertool with SQLite embedded.

porker2• 5 years ago

Interesting, have you used it for more than log file analysis? A "mini-ETL" sounds exactly what I need each week.

pablobaz• 5 years ago

https://duckdb.org/ is good for these type of tasks

polyrand3• 5 years ago

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.

[0] https://github.com/litements/litequeue

lusbuab• 5 years ago

I guess you already know it, but: «SQLite is really easy to compile» by Julia Evans, see https://jvns.ca/blog/2019/10/28/sqlite-is-really-easy-to-com...

polyrand3• 5 years ago

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.

avinassh2• 5 years ago

I saw this on the homepage:

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?

nojvek• 5 years ago

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.

simonw14• 5 years ago

Have you tried using the CHECK constraint mechanism for this? https://www.sqlitetutorial.net/sqlite-check-constraint/

riyadparvez• 5 years ago

I am curious to know. Is there any reason why they don't support schema enforcement?

radus• 5 years ago

Awesome! SQLite, from my perspective at least is feature-complete! Anyone else have any notable missing features?

pgwhalen2• 5 years ago

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.

https://www.sqlite.org/omitted.html

simonw14• 5 years ago

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.

erezsh2• 5 years ago

That's awesome. Can't wait for these features to reach the Sqlite lib on Python in 5 years.

simonw14• 5 years ago

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.

https://github.com/coleifer/pysqlite3

erezsh2• 5 years ago

There is also this, which seems pretty good: https://github.com/rogerbinns/apsw

(haven't tried it yet)

systems2• 5 years ago

why isnt materialized CTE a default optimization in sql? why do we need to be explicit about it

any trade off

simonw14• 5 years ago

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.

systems2• 5 years ago

I think the change introduced this syntax

with history as materialized { .. }

so you have to declare the CTE as materialized to get this optimization

masklinn8• 5 years ago

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.

submeta2• 5 years ago

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.

re• 5 years ago

You can invoke it as `/usr/local/opt/sqlite/bin/sqlite3`. Or run `brew info sqlite3` to see how to add it to your path.

https://docs.brew.sh/FAQ#what-does-keg-only-mean

submeta2• 5 years ago

Ok, got it. Found out it was installed:

    $ mdfind -name sqlite3 | grep -E 'bin/sqlite3$'
    /usr/local/Cellar/sqlite/3.34.1/bin/sqlite3
    /usr/bin/sqlite3

Just need to symlink it or add it to path. Or `brew link sqlite`. Thanks!

bmn__• 5 years ago

Also see: "SQLite now allows multiple recursive SELECT statements in a single recursive CTE" https://news.ycombinator.com/item?id=24843643

polyrand3• 5 years ago

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.

[0] https://sqlite.org/draft/lang_returning.html#limitations_and...

junippor• 5 years ago

SQLite is called ‘lite’ for a reason. If you need functions, add them yourself.

Ok, lets say that SQLite developer believe this position has merit (they must've have, since they held it for 20 years, right?)

So what changed now which led to you implementing such functions?

Nothing changed, you just didn't _feel_ like doing it.

simonw14• 5 years ago

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.

What's New in SQLite 3.35

Related Stories