
SQLite 3.33 - chmaynard
https://www.sqlite.org/releaselog/3_33_0.html
======
simonw
SQLite 3.33 also increases the maximum size of a SQLite database file from
140TB to 281TB:
[https://www.sqlite.org/releaselog/3_33_0.html](https://www.sqlite.org/releaselog/3_33_0.html)

I asked about this on the SQLite forum and D. Richard Hipp said "This change
was in response to a customer request. They still have a factor of 4 before
reaching the old upper limit, but asked for additional headroom."
[https://sqlite.org/forum/forumpost/8e40a7f588428077d7f073c00...](https://sqlite.org/forum/forumpost/8e40a7f588428077d7f073c004f704467e947c372ad507b7324c92ad7a118da8)

~~~
giancarlostoro
That is kind of entertaining to think someone can store a 281TB SQLite file
and I am still waiting for companies to sell 1PB hard drives.

Back in 2010 I thought yeah soon we will have 50TB hard drives then 500TB and
someday I can have 1PB it will be all I will need! Sadly no such change. I
would be happy to be in the 10s of TBs rage for under 200 dollars.

Games are getting stupid wasteful. Someone told me Fortnite is almost 90GB or
so and I flinched considering GTA5 is around that range and offers a loooot
more rich gameplay and features! What a wasteful game resource wise.

~~~
Stratoscope
Kids these days.

My first hard drive was an 8" 20MB drive. That's about 9.5 x 4.6 x 14.2 inches
and weighed about 20 pounds. Only cost me $6000, in 1981. And with that
massive capacity, I never did run out of room.

Now I have a 200GB MicroSD in my phone. It cost $75. Haven't run out of room
on that one either.

~~~
justinclift
Along those lines:

[https://youtu.be/hiLlNzxDfAg?t=714](https://youtu.be/hiLlNzxDfAg?t=714)

------
miohtama
Decimal type, finally. Now SQLite is 100x easier to use in finance.

~~~
fauigerzigerk
That's brilliant! I've been waiting for this for years.

------
chmaynard
If you appreciate SQL, the example (update inventory from daily sales) is a
thing of beauty.

~~~
dtf
Is “GROUP BY 2” a shorthand for “GROUP BY itemId”, the second column in the
FROM SELECT? Not sure I’ve seen that syntax before.

~~~
barrkel
You can mention columns from the select clause by ordinal in group by and
order by clauses, in lieu of restating the full expression. Very helpful with
complex expressions, but IMO not the greatest for readability, so I tend to
only use it in ad hoc queries. It's part of SQL-92, but I believe it is
deprecated.

Many interpreters let you use a column alias from the select clause in the
group and order clauses. This has better readability IMO but I'm not sure it's
in the SQL-92 standard, but I believe it is now standardized.

~~~
nicoburns
> Many interpreters let you use a column alias from the select clause in the
> group and order clauses. This has better readability IMO but I'm not sure
> it's in the SQL-92 standard, but I believe it is now standardized.

Not sure if it's standardised, but Postgres doesn't let you do this (rather
irritatingly).

~~~
benesch
No, PostgreSQL definitely lets you do this.

> An expression used inside a grouping_element can be an input column name, or
> the name or ordinal number of an output column (SELECT list item), or an
> arbitrary expression formed from input-column values. [0]

> Each expression can be the name or ordinal number of an output column
> (SELECT list item), or it can be an arbitrary expression formed from input-
> column values. [1]

Perhaps you are thinking of trying to use an output column name in a WHERE
clause?

[0]: [https://www.postgresql.org/docs/current/sql-
select.html#SQL-...](https://www.postgresql.org/docs/current/sql-
select.html#SQL-GROUPBY)

[1]: [https://www.postgresql.org/docs/current/sql-
select.html#SQL-...](https://www.postgresql.org/docs/current/sql-
select.html#SQL-ORDERBY)

~~~
nicoburns
> Perhaps you are thinking of trying to use an output column name in a WHERE
> clause?

Yeah, I think I am thinking about this.

Its pretty frustrating because the actual query engine is perfectly capable of
executing fairly complex SQL expressions efficiently (they're not really that
complex computationally, but they are syntactically because of SQL's
verbosity), but the code becomes quite unmaintainable if you use too many of
them.

For example, the following expression:

    
    
        ROUND((
          (EXTRACT(EPOCH FROM (s.end_time - s.start_time)) / 60) -- Shift length in minutes
            - (FLOOR((EXTRACT(EPOCH FROM (s.end_time - s.start_time)) / 60) / 380) * 20) -- Break length in minutes
        )::numeric / 60 , 2) as hours_planned,
    

It repeats the sub-expression `EXTRACT(EPOCH FROM (s.end_time - s.start_time))
/ 60)`. If I could name that sub expression and reference it multiple times
then the overall expression would be a _lot_ more readable.

------
lazulicurio
As someone who uses SQL Server at work, I was surprised to find that this
wasn't a standard feature in other DB engines. I use it all the time! Glad to
see it spreading.

~~~
isoprophlex
Postgres has this too afaik?

Yeah![1] with some caveats, that also apply to the sqlite implementation if im
not mistaken:

 _When a FROM clause is present, what essentially happens is that the target
table is joined to the tables mentioned in the from_item list, and each output
row of the join represents an update operation for the target table. When
using FROM you should ensure that the join produces at most one output row for
each row to be modified. In other words, a target row shouldn 't join to more
than one row from the other table(s). If it does, then only one of the join
rows will be used to update the target row, but which one will be used is not
readily predictable._

[https://www.postgresql.org/docs/current/sql-
update.html](https://www.postgresql.org/docs/current/sql-update.html)

~~~
anarazel
The release notes are directly referencing that they copied us / PG: "Support
for UPDATE FROM following the PostgreSQL syntax. The sqlite folks have done
that a few times, and I think that's good. No need to unnecessarily
proliferate more variants of doing the same.

------
SloopJon
I'm intrigued by the decimal extension. I thought SQLite was IEEE double only,
but it handles 64-bit integers, and now decimals. They don't seem to be fully
incorporated into the documentation yet, and decimal.c leaves me with
questions, so I guess I'll just have to play around with it.

~~~
nabla9
Decimals are represented as strings.

They have add, sub, mul and sum functions for text strings as well as
comparison.

------
mamcx
Oh, decimal support is landing!

I have a lot of stuff dancing around the lacking of decimals in sqlite, so
this is so welcoming!

------
stef-13013
This software is a piece of magic...

------
oars
Is SQLite capable of supporting multiple writes to the database from different
processes at the same time?

This question is purely around INSERTS - no UPDATES.

Anything I should be aware of? Looking to write data from multiple services to
an SQLite file.

~~~
itroot
I think that this applies to all modifying operations - INSERTs, UPDATEs,
DELETEs.

It's perfectly ok to write to sqlite from different processes in the same
time, but to achieve good results it's better to:

* use WAL mode - so the readers and writers do not block (you can turn on it with `PRAGMA journal_mode=WAL;` in CLI, and it's better to add `PRAGMA main.synchronous=NORMAL;` also).

* all concurrent writes will be queued by sqlite3 lib and done in sequential manner, and if any write attempt will wait longer that BUSY_TIMEOUT ( see [https://www.sqlite.org/pragma.html#pragma_busy_timeout](https://www.sqlite.org/pragma.html#pragma_busy_timeout) ) , it will return error.

Snippet

    
    
      # setting things up...
      itroot@l7490:/tmp$ grep -i pragma ~/.sqliterc
      PRAGMA journal_mode=WAL;
      PRAGMA main.synchronous=NORMAL;
      PRAGMA busy_timeout=1000;
      itroot@l7490:/tmp$ sqlite3 test.sqlite 'CREATE TABLE records (id INTEGER PRIMARY KEY, record TEXT);' > /dev/null 2>&1
    
      # running 10 parallel processes that inserts numbers from 1 to 1000...
      itroot@l7490:/tmp$ echo {1..1000} | xargs -n1 -d' ' -P 10 -i% sqlite3 test.sqlite 'INSERT INTO records (record) VALUES (%);' >/dev/null 2>&1
    
      # getting number of records
      itroot@l7490:/tmp$ sqlite3 test.sqlite 'SELECT count(*) FROM records;'
      count(*) = 1000

------
wfriesen
UPDATE FROM is a nice extension. In Oracle I've done similar things with an
UPSERT/MERGE statement. The linked example of:

    
    
      UPDATE inventory
         SET quantity = quantity - daily.amt
        FROM (SELECT sum(quantity) AS amt, itemId FROM sales GROUP BY 2) AS daily
       WHERE inventory.itemId = daily.itemId;
    

Could be done with MERGE using:

    
    
      MERGE INTO inventory i
      USING (
        SELECT
          SUM(quantity) AS amt
        , itemId
        FROM sales
        GROUP BY 2
      ) daily
      ON (
        i.itemId = daily.itemId
      )
      WHEN MATCHED THEN UPDATE
        SET i.quantity = i.quantity - daily.amt;

------
justinclift
As a data point, the nightly builds of DB Browser for SQLite now include
SQLite 3.33:

[https://nightlies.sqlitebrowser.org/latest/](https://nightlies.sqlitebrowser.org/latest/)

------
ZnZirconium
SQLite is great because I don't want to run a database server if I don't need
to and whenever it seems like I might have to give up and run a database
server instead I find a configuration option in SQLite that solves my scaling
problem or performance problem or concurrency problem. I don't like to
recompile SQLite to set compile time options but I'm perfectly capable of
doing it though.

I wish WAL mode were the default. I wish read only WAL mode were easier to do
without putting the database file in a sticky directory. Oh well.

~~~
tmp538394722
Why do you need read only WAL mode? Or do you just mean some processes are
read only, while others can write?

~~~
ZnZirconium
The real reason I need WAL mode is so I can read the database while vacuum is
running.

The web server runs unprivileged and only needs read permission. Insert and
vacuum are done as a user which has write permission.

The documentation says read only WAL mode is possible if there is write
permission on the directory. So I put the database file in its own directory
and set the mode of the database file to 0664 and set the mode of the
directory to 3777. So an unprivileged process has permission to create WAL
files which become group writable.

~~~
tmp538394722
Thank you for explaining.

~~~
anitil
My understanding is that this is because a temporary file needs to be created
next to the db in WAL mode so write permissions are necessary on on directory.
([https://www.sqlite.org/tempfiles.html](https://www.sqlite.org/tempfiles.html),
search for 'WAL')

------
thom
This is useful but the clunkyness of using FROM/WHERE vs JOIN ... USING has
always annoyed me in Postgres, which they've based this on. The MySQL version
is sort of what my brain expects.

~~~
zeroimpl
I wonder if they could just make this syntax work in Postgres:

    
    
        UPDATE inventory 
        SET quantity = inventory.quantity - daily.amt
        FROM
            (SELECT sum(quantity) AS amt, itemId FROM sales GROUP BY 2) daily USING( itemId );
    

Basically just allowing a ON/USING clause after the first FROM entry as if it
was joining to the table being updated.

Otherwise it's kind of annoying when you end up joining to several tables in
the update, but have to use the WHERE clause to join back to the main table.

~~~
Izkata
Personally I prefer UPDATE JOIN as I've used it in mysql:

    
    
      UPDATE inventory
      INNER JOIN (SELECT sum(quantity) AS amt, itemId FROM sales GROUP BY 2) AS daily USING (itemId)
      SET quantity = quantity - daily.amt;
    

I had thought postgres could do it this way without the extra FROM syntax, but
apparently not.

~~~
zeroimpl
The idea of being able to update multiple tables in a single query this way is
intriguing to me! But I like the fact that the Postgresql syntax makes it
crystal clear which table is being updated.

~~~
funny_falcon
In fact, MySQL allows update of multiple tables.
[https://stackoverflow.com/questions/4361774/mysql-update-
mul...](https://stackoverflow.com/questions/4361774/mysql-update-multiple-
tables-with-one-query)

------
yread
Nice feature, I hate having to work around it with subqueries or worse

~~~
chmaynard
Agreed. It would be interesting to compare the UPDATE FROM query plan with the
equivalent subquery-based update.

------
emddudley
Bummer, Python 3.9.0 RC1 just missed it! It bundles SQLite 3.32.3. Ah well, I
guess we can look forward to it in 3.9.1 in a few months.

------
ufo
Is UPDATE FROM part of ISO SQL or is it just a very popular extension?

~~~
josefx
Going by the SQLite documentation it isn't.

[https://www.sqlite.org/lang_update.html](https://www.sqlite.org/lang_update.html)

------
pella
[https://www.sqlite.org/releaselog/3_33_0.html](https://www.sqlite.org/releaselog/3_33_0.html)

------
leeoniya
Fossil also got a new release today: [https://fossil-
scm.org/home/uv/download.html](https://fossil-scm.org/home/uv/download.html)

------
gigatexal
a better link to the changes in this ver than what the entry provides:
[https://www.sqlite.org/releaselog/3_33_0.html](https://www.sqlite.org/releaselog/3_33_0.html)

------
Communitivity
Interesting. Great for Command Query Responsibility Segregation (CQRS), but I
could see this leading to a reduction in data normalization for a lot of teams
not using CQRS too. CQRS is a great tool - but the complexity is high so you
only want to use it where you need it.

CQRS overused a lot though, like using a $75k surveillance robotic dog from
Boston Dynamics [[https://spectrum.ieee.org/automaton/robotics/industrial-
robo...](https://spectrum.ieee.org/automaton/robotics/industrial-
robots/boston-dynamics-spot-robot-dog-now-available)] (Massive Dynamics?) to
see who's at the door, when you could have just looked through the peephole.

I could see this feature becoming the CQRS of the SQL world for a while, used
in many places where it should be considered harmful, in addition to the
places where it is helpful.

~~~
stingraycharles
I don’t understand why you focus on CQRS; I don’t think it’s specifically
related? At least, not any more related than say, materialized views are
useful for CQRS.

I’d expect a CQRS system on top of SQL to be implemented using a single event
table and a lot of triggers, each triggering a different aggregate.

~~~
Communitivity
Not sure why I got downvoted. Also, Domain Driven Design (which is where I've
usually seen aggregates discussed), Event Sourcing, and CQRS are three
different things that might be used together in an implementation but don't
have to be.

The reason I noted how it's great for CQRS is that I've usually seen CQRS
implemented with two data models. One is queried, and one is modified via
commands. The one that's queried is updated according to some strategy,
sometimes on an event triggered by the command model being modified. This this
you could trigger and update on your query model when your command model is
changed, all within SQL.

~~~
stingraycharles
I think you're mostly downvoted because:

* It's only about CQRS, which in no way was mentioned in the article;

* You're not properly qualifying _why_ this feature is so important for CQRS;

* In the same breath, you're also claiming it's overused and complex, while in fact you're the one who's "promoting" it.

I agree that in my reply, I was actually thinking about ES + CQRS rather than
pure CQRS, but the gist of my comment still stands: I would expect a CQRS
implementation to work with triggers (for incremental updates) _or_
materialized views (one table insert-only, the "view" is kept in sync
automatically by the database, which is what users query). Could you elaborate
on why you think this new feature is better than those two approaches?

