Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

There are over one trillion SQLite databases in active use

sqlite.org

Post reply on HN
Print view View original

11–20 of 92 posts

Re: There are over one trillion SQLite databases in active use

#11

Post by williamdclt »

ccozan wrote: post #3

Ok tens of billions I believe, but trillion+ is likely an exaggeration

Tens of billions of installs of the database _engine_, trillions of databases (ie SQLite files).

The webpage is a bit unclear about the difference, I'll agree

Re: There are over one trillion SQLite databases in active use

#12

Post by acatton »

claytongulick wrote: post #2

They'd have one trillion + 1 if they had better JSON support. I'd be using it in a large healthcare project I'm working on now. Unfortunately, the JSON support is basically "serialize to a string". Postgres is miles ahead with jsonb. Shame, I <3 sqlite.

I love PostgreSQL, and would recommend it anytime. Even though jsonb is very powerful, in general if you store JSON in a SQL database you're very likely doing something wrong. (You're breaking first normal form)

But if you really wanted to use SQLite to store JSON à la jsonb in PostgreSQL you can use generated fields[1]

    sqlite> create table t(id integer primary key autoincrement, data text);
    sqlite> insert into t(data) values ('{"foo": "value", "bar": "other value"}'), ('{"foo": "baz", "bar": "qux"}');'
    […]
    sqlite> alter table t add column foo text generated always as (json_extract(data, '$.foo')) virtual;
    sqlite> select * from t;
    id  data                                    foo  
    --  --------------------------------------  -----
    1   {"foo": "value", "bar": "other value"}  value
    2   {"foo": "baz", "bar": "qux"}            baz
You can even use a "stored" (instead of 'virtual') generated field, and create an index on it for fast lookups.

It's not as powerful as Postgres, but it does a pretty good job.

[1] https://www.sqlite.org/gencol.html

Re: There are over one trillion SQLite databases in active use

#14

Post by wmanley »

claytongulick wrote: post #2

They'd have one trillion + 1 if they had better JSON support. I'd be using it in a large healthcare project I'm working on now. Unfortunately, the JSON support is basically "serialize to a string". Postgres is miles ahead with jsonb. Shame, I <3 sqlite.

Could you expand on what Postgres offers that SQLite doesn't in this regard? I'm curious to know what I'm missing out on.

Re: There are over one trillion SQLite databases in active use

#17

Post by ntumlin »

andkon wrote: post #8

Don’t forget to count all the cruise missiles, too!

Those tend to fall out of the “active use” category not long after entering it, so it’s probably not a big factor.

On a more serious note, had there been something special about SQLite supporting military use of their project that prompted this? Otherwise that’s the point of free software, people are free to do (mostly) whatever they want with it. Including building weapons. SQLite is so prevalent, it’s almost like pointing out you have to include cruise missiles in the count of active x86 chips, or $insert_bad_guy as an active user of roads and electricity.

Re: There are over one trillion SQLite databases in active use

#18

Post by acatton »

wmanley wrote: post #14
claytongulick wrote: post #2

They'd have one trillion + 1 if they had better JSON support. I'd be using it in a large healthcare project I'm working on now. Unfortunately, the JSON support is basically "serialize to a string". Postgres is miles ahead with jsonb. Shame, I <3 sqlite.

Could you expand on what Postgres offers that SQLite doesn't in this regard? I'm curious to know what I'm missing out on.

Not GP, but PostgreSQL offers jsonb fields[1]

Since PostgreSQL allows for functional indexes, you can query and index the data in a structured way.

For example you have a table t with "id" and "data" where data is a jsonb field like {foo: ..., bar: ...}. You can do

    SELECT id, data->'foo'
    FROM   t
    WHERE  data->'bar' > 5
Which will yield the value of "foo" (inside the json field) for rows where "bar" (inside the json field) is greater than 5.

[1] https://www.postgresql.org/docs/9.5/functions-json.html

Re: There are over one trillion SQLite databases in active use

#20

Post by nojito »

acatton wrote: post #12
claytongulick wrote: post #2

They'd have one trillion + 1 if they had better JSON support. I'd be using it in a large healthcare project I'm working on now. Unfortunately, the JSON support is basically "serialize to a string". Postgres is miles ahead with jsonb. Shame, I <3 sqlite.

I love PostgreSQL, and would recommend it anytime. Even though jsonb is very powerful, in general if you store JSON in a SQL database you're very likely doing something wrong. (You're breaking first normal form) But if you really wanted to use SQLite to store JSON à la jsonb in PostgreSQL you can use generated fields[1] sqlite> create table t(id integer primary key autoincrement, data text); sqlite> insert into t(dat…

Disagree storing json in databases is often the best thing to do. Clickhouse for example allows you to quickly pull out relevant data and feed materialized views.
Post reply on HN