On: DuckDB – An embeddable SQL database like SQLite, b...

[torrance]

Strict typing has been the one big feature missing from SQLite, that this presumably brings.

[gralx → torrance]

Foreign keys enabled by default or by configuration setting would be another big feature missing from SQLite3, but I couldn't find any mention of foreign keys in DuckDB's documentation.

[mytherin → gralx]

Foreign keys are not supported yet in DuckDB currently, but we do plan to add support for them in the future. If you have a need for them, feel free to post a comment on the issue. We tend to prioritize implementing features that many people ask for :)

[joppy → gralx]

SQLite has foreign keys, but they are disabled by default for backwards-compatibility reasons. https://sqlite.org/foreignkeys.html

[gralx → joppy]

And it's not possible to enable them by default - there is no existing configuration setting to override that design choice. They hope to include the feature in SQLite4, as I understand it.

[ComputerGuru → gralx]

SQLite 4 was killed off in favor of just bringing a few changes to SQLite 3 iteratively.

https://www.sqlite.org/src4/doc/trunk/www/index.wiki

[gigatexal → gralx]

OLAP databases seem to omit ForeignKeys. Redshift does this for example. They don’t exist in BigQuery (but BQ pushes you towards a single table design anyway so moot point I guess).

[Master_Odin → gralx]

It doesn't look like it provides FK looking at the create table syntax. Just PKs, unique, and check on an expression similar to sqlite.

[cryptonector → Master_Odin]

What? SQLite3 absolutely has FKs.

[gralx → cryptonector]

You just have to write

    PRAGMA foreign_keys = on;

each and every single time you want to do an operation that requires an FK constraint, like `INSERT`.

[mceachen → gralx]

s/each and every single time/once when you instatiate a new database connection/

ftfy.

You really shouldn't be needing to create new connections often. Really only once for any given process.

There are some 20 PRAGMAs that PhotoStructure sets for library databases, but it's only at process startup, and it takes a couple millis to run them all. It's wonderful to be able to configure stuff so easily.

I also think the design decision for backward comparability (which meant this default is false) is absolutely defensible.

[gralx → mceachen]

I'll defer to anyone with expertise.

I'm modelling a program in Python on a work computer without administrative privileges, which means it has to stay in user space. Because I haven't decided on a final implementation language, I don't want to commit to any one language's memory model. Vanilla SQL is my solution there.

My specification limits procedures to SQL as much as possible, using the implementation language only for user interaction with and computations on the database not possible in standard SQL. It minimizes use of the heap, which requires opening connections to the database frequently, but this is practical given the low performance requirements of the application.

SQLite3 satisfies my spec except for the extra diligence required first to implement the foreign keys PRAGMA in order to maintain referential integrity, and second to remove it if an RDBMS closer to the SQL standard were eventually chosen.

In a nutshell, my constraints are user space, limiting operations to standard SQL as much as possible, referential integrity, and minimal implementation language dependencies besides having an available SQL API. Given those constraints, would you recommend a different RDBMS? Or would you agree SQLite3 is my least worst option?

[hfmuehleisen → torrance]

Yes, DuckDB has strict typing.