SQLite Lands JSONB For Much Faster JSON Functions

[phoronix]

Phoronix: SQLite Lands JSONB For Much Faster JSON Functions

SQLite as the leading open-source embedded database solution has landed JSONB, a rewrite of the SQLite JSON functions that can be up to "several times faster" than the existing JSON functions...

https://www.phoronix.com/news/SQLite-Lands-JSONB

[uid313]

SQLite is okay, but it lacks support for the INFORMATION SCHEMA standard table.

It also only have a very limited set of data types so if you reverse engineer a database - generate code with classes from the database - then you end up with really poor classes, since SQLite only have INTEGER but not BOOL, BIGINT, SMALLINT, TINYINT, etc, and real but not any DATETIME data type.

[patrick1946]

SQLite is okay, but it lacks support for the INFORMATION SCHEMA standard table.

It also only have a very limited set of data types so if you reverse engineer a database - generate code with classes from the database - then you end up with really poor classes, since SQLite only have INTEGER but not BOOL, BIGINT, SMALLINT, TINYINT, etc, and real but not any DATETIME data type.

Can you clarify what is the advantages to support all that different types? Do you speak about ORMs? I think the usage of ORMs leads to really slow database queries especially with Sqlite.

I wrote a mapper which is mapping query results from Sqlite to constructors and the data types of Sqlite are fitting quite well.

[fitzie]

SQLite is okay, but it lacks support for the INFORMATION SCHEMA standard table.

It also only have a very limited set of data types so if you reverse engineer a database - generate code with classes from the database - then you end up with really poor classes, since SQLite only have INTEGER but not BOOL, BIGINT, SMALLINT, TINYINT, etc, and real but not any DATETIME data type.

i'm pretty sure we've had this discussion, but this isn't entirely true. while sqlite doesn't support the information schema standard, it does expose the metadata, and it does contain all these subtypes.

$ sqlite3 test.db
SQLite version 3.42.0 2023-05-16 12:36:15
Enter ".help" for usage hints.
sqlite> c reate table test ( a smallint, b datetime);
sqlite> select * from sqlite_schema;
table|test|test|2|CREATE TABLE test ( a smallint, b datetime)

( i had to put a space " " in create because michael has some sql injection filter protection running around)

what actually doesn't happen is _any_ type checking, unless you use strict tables (not the default), and only then, to the major type. If you wanted to, you can add all the check constraints you want. it has all the necessary datetime functions so that you could implement whatever you'd need. just because they don't bake this stuff into their storage layer doesn't really mean the capabilities are lacking. my biggest issue with sqlite is that they don't have a good async model that works with python well. soon enough we'll be gil free so we can use threads which is sqlite's preferred way to do async

[uid313]

Can you clarify what is the advantages to support all that different types? Do you speak about ORMs? I think the usage of ORMs leads to really slow database queries especially with Sqlite.

I wrote a mapper which is mapping query results from Sqlite to constructors and the data types of Sqlite are fitting quite well.

Yeah, I was thinking with a ORM.
With a ORM you can do two approaches, "code first" (and generate the database from the code) or "reverse engineer" (and generate the code from the database). Generating code from a SQLite database works really poorly due to limited set of data types available in SQLite since the ORM don't know more about the data and cannot map it to boolean, datetime, and such.

So yeah the advantages of a SQL dialect with support for all those different data types is that ORMs can know more about the database when reverse engineering and hence do a better job of scaffolding up classes with proper data types.

There may be other benefits too, such as being to able to run scripts to generate tables from other databases such as MySQL and PostgreSQL which supports more data types. Other benefits might include stricter data validation.

[uid313]

i'm pretty sure we've had this discussion, but this isn't entirely true. while sqlite doesn't support the information schema standard, it does expose the metadata, and it does contain all these subtypes.

$ sqlite3 test.db
SQLite version 3.42.0 2023-05-16 12:36:15
Enter ".help" for usage hints.
sqlite> c reate table test ( a smallint, b datetime);
sqlite> select * from sqlite_schema;
table|test|test|2|CREATE TABLE test ( a smallint, b datetime)

( i had to put a space " " in create because michael has some sql injection filter protection running around)

what actually doesn't happen is _any_ type checking, unless you use strict tables (not the default), and only then, to the major type. If you wanted to, you can add all the check constraints you want. it has all the necessary datetime functions so that you could implement whatever you'd need. just because they don't bake this stuff into their storage layer doesn't really mean the capabilities are lacking. my biggest issue with sqlite is that they don't have a good async model that works with python well. soon enough we'll be gil free so we can use threads which is sqlite's preferred way to do async

Yeah, but even though it exposes metadata, it doesn't support the INFORMATION SCHEME which is a standard used by tools, so then it is trickier to use tools with SQLite and many tools don't work.

I haven't used SQLite in a while, but last time I used it was with .NET with EF Core (which is an ORM) and I had some problems with the data types.

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

Yes, this SQL injection thing on this forum works really bad and is very annoying.

[fitzie]

Yeah, but even though it exposes metadata, it doesn't support the INFORMATION SCHEME which is a standard used by tools, so then it is trickier to use tools with SQLite and many tools don't work.

I haven't used SQLite in a while, but last time I used it was with .NET with EF Core (which is an ORM) and I had some problems with the data types.

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

Yes, this SQL injection thing on this forum works really bad and is very annoying.

i've yet to find an ORM that makes me happy. but it's hard to say this is EF Core's fault here.

[uid313]

i've yet to find an ORM that makes me happy. but it's hard to say this is EF Core's fault here.

I haven't had too much experience with different ORMs but I do have to say that I really like EF Core because it has great conventions which makes it easy to use and it allows much configuration (if you want to) which allows you use your own data types for strongly typed identifiers, use enums, have the framework use the constructor so properties can be private setters and be modified using setter methods that enforce business logic, etc.

[JEBjames]

Michael

typo

"that can read or written" should probably be "that can be read or written"

[timrichardson]

... It also only have a very limited set of data types


It is called 'sqlite' for a reason