Clarify conditions for a integer/UUID primary key column

[LQR471814]

It would be great if the conditions for this error were clarified a bit more (in the actual error or documentation).

Error: Config(Invalid("Table for api '...' is missing valid integer/UUID primary key column."))

Like:

Error: Config(Invalid("Table for api '...' is missing valid integer/UUID primary key column. You should specify
a primary key like `<field> int primary key` or `<field> blob primary key check(is_uuid_v4|v7(<field>))`"))

I have not been using SQLite for long so this could totally just be a result of skill issues. However, it is rather counter-intuitive that this error will trigger unless you specifically use blob and a is_uuid_* check is present when creating the table, when it is possible to store UUIDs with TEXT or BLOB fields without checks.

[ignatz]

Hi @LQR471814

thanks for the input. The error could and absolutely should be more useful.

None of this is a skill issue, none of this is related to SQLite but all is utterly opaque and historical. Let me provide some context before improving the error to understand what would be most useful...

SQLite lets you do whatever you want. It's more a question of what TB will accept as a TABLE for exposing an API on top. Historically, TB would only allow BLOB UUIDv7 as the most reasonable compromise between efficiency, allowing cursors and randomness (as opposed to predictable IDs). In many ways, I'd argue it's still the most reasonable default. Over time, this requirement has loosened to also support UUIDv4 for true randomness (not leaking timestamps) and INT for peak efficiency/compactness.

We could loosen requirements further to allow:

1. arbitrary blobs
2. more SQLite types (i.e. TEXT and REAL)
3. composite primary keys

A few thoughts. REALs would make for terrible PKs. TEXT and arbitrary BLOBs could be fine. I could see value for importing pre-existing data with FK relations but I'm not sure if it's worth it. Composite keys might be nice and have been on my bucket list for a while.

I'm not sure if the "loosening requirements" part was interesting, you may try to accomplish something that's not supported at the moment. What do you think?

[LQR471814]

I see. I think loosening requirements would definitely be a more intuitive default from a user's perspective though I can understand the benefit behind making sure the user does the most reasonable thing by default.

I am not familiar with what looser PK requirements would imply for aspects like efficiency and cursors/subscription but I can definitely see where UUID requirements can create friction. (for instance, the is_uuid_* function is only available in trailbase itself, so to use other tools to access the SQLite database would require the user to disable checks globally)

Perhaps trailbase could do some kind of lightweight detection (like random sampling) on startup of record APIs using TEXT to store UUIDs or some other suboptimal PK and show a "not-recommended" message to the user?

[ignatz]

for instance, the is_uuid_* function is only available in trailbase itself, so to use other tools to access the SQLite database would require the user to disable checks globally

You're touching on a point that's very dear to my heart, letting folks easily migrate in and out. At very least, I should offer a standalone SQLite extension (a shared object) that folks could load (though arguably that may still be an inconvenience). FWIW, writing a migration that merely strips the constraint also isn't to hard (adding constraints is always harder :)).

For context, why I've been requiring the constraints is from a strict typing, type-safety point of view similar to custom JSON schemas. Otherwise, if you imported data with a blob PK, TrailBase wouldn't know what format it is. Is it random, is it ordered, ...?

I am not familiar with what looser PK requirements would imply for aspects like efficiency and cursors/subscription but I can definitely see where UUID requirements can create friction.

There's two dimensions:

• Yes, we could allow storing UUIDs as text, it simply would be a waste of CPU and even more so disk. One could argue that this should be the users choice.
• The question would then still be, should we enforce a constraint on the text column to be a valid UUIDvX string from a strict-typing perspective?

Perhaps trailbase could do some kind of lightweight detection (like random sampling) on startup of record APIs using TEXT to store UUIDs or some other suboptimal PK and show a "not-recommended" message to the user?

Making performance critical decisions in a production environment based on looking at 5 records, feels maybe a bit too magic :). We could simply fall back, if the relevant constraints are missing in the schema.

To summarize. I guess nobody wants REAL primary keys. The question is only if one should support arbitrary BLOB and TEXT primary keys that are not explicitly constraint? Just to assign a priority to it, is this something you need or something we think may be a nice generalization? Both are valid.

Thanks!

[LQR471814]

Making performance critical decisions in a production environment

Sorry, I had meant simply a suggestion log message for the user (kind of like the Supabase "assistant" thing), but then again it could also be docs. Indeed, magical automatic migrations in production sounds awful.

is this something you need or something we think may be a nice generalization

Oh no, this is not something I need in my current use-case. I am perfectly fine with doing it the proper way but I think allowing for unconstrained PK formats would be a nice generalization.

[ignatz]

I spent a little bit of time to do the easy and obvious: brush up on the errors both for server-side validation and in the UI - Thanks!

This also gave me the chance to page in some more of the historic context. One detail we haven't touched on that is relevant to the discussion: non-PK related constraints. Specifically, while loosening PK requirements would allow more pre-existing TABLE/VIEWs to qualify we would still require all columns to be STRICTly typed. This may still require somebody who's onboarding to migrate and potentially clean up their not so strict data 🤷‍♀️. To be clear, I'm not bringing this up to brush of the loosened PK requirements, just trying to better understand the limitations.

[LQR471814]

Yeah, I suppose that if you are going to migrate you might as well migrate everything and do things the proper way while keeping trailbase simple and predictable. Perhaps a migration tool could be made to ease the process of migrating existing data with unsupported ids and a lot of FKs? They could even keep the original custom ID and make that a unique/index or something, and add a new one that is the actual PK for trailbase.

[ignatz]

Yeah, I suppose that if you are going to migrate you might as well migrate everything and do things the proper way while keeping trailbase simple and predictable.

Maybe. The one point that certainly still sticks with me is that is_uuid_vX may feel like lock-in (arguably you can just drop the constraint and everything will just keep working, your ID's will still be blobs just their format will no longer be enforced) 🤔

Perhaps a migration tool could be made to ease the process of migrating existing data with unsupported ids and a lot of FKs?

To some of extent we have that (though probably not sufficient). The table explorer in trailase is unopinionated. If you import external data, you can use it to migrate tables (I don't think it let's you alter the PK and table strictness right now - the SQL shell however won't stop you). The biggest challenge is probably, if your data set isn't clean and I'm not sure how much a tool can help you, e.g. your primary key column is a mix integers, strings, booleans (SQLite really doesn't care about types unless your table is STRICT). So you would have to fix-up your data first, e.g. decide how you want to normalize those ids.

Anyway, maybe for the happy path where the data is clean enough it would be enough to be able to alter the strictness and PK in the table explorer... i like that. In fact, you should already be able to use it to remove column constraints in preparation of moving off TrailBase.

[LQR471814]

is_uuid_vX may feel like lock-in

Yeah, I think your idea of a shared-object containing all the "SQLite extensions" this project uses would be helpful in that regard. It doesn't seem to me that you can achieve type-safety any way otherwise (other than, perhaps asking the SQLite devs to add a is_uuid_* built-in?) It definitely feels like better DX to have a thing you setup once for your external tools and then everything just works, rather than having to remove and re-add the checks every time you import.

I am not sure if there is already this functionality, but perhaps another folder under traildepot/ like traildepot/sqlite-extensions could be added that allows TrailBase to use custom SQLite extensions (including the bulit-in ones) would keep the separation between SQLite and TrailBase clean? (alongside a check to make sure the user doesn't remove the built-in TrailBase extension) That way, the user could simply keep all the extensions they use for SQLite in one place (including the built-in ones) and load them with whatever external tool they are using.

if your data set isn't clean and I'm not sure how much a tool can help you

Yeah, data-cleaning is definitely out of scope here.

maybe for the happy path where the data is clean enough it would be enough to be able to alter the strictness and PK in the table explorer

This sounds good 👍 .

[ignatz]

👍 I'll look into adding the extension to the binary releases.

It definitely feels like better DX to have a thing you setup once for your external tools and then everything just works, rather than having to remove and re-add the checks every time you import.

I hope you're not changing your opinion on whether to leave or stick with TrailBase on a daily basis :). Adding is_uuid_* to SQLite is probably going to be tricky.

I am not sure if there is already this functionality, but perhaps another folder under traildepot/ like traildepot/sqlite-extensions could be added that allows TrailBase to use custom SQLite extensions (including the bulit-in ones) would keep the separation between SQLite and TrailBase clean? (alongside a check to make sure the user doesn't remove the built-in TrailBase extension) That way, the user could simply keep all the extensions they use for SQLite in one place (including the built-in ones) and load them with whatever external tool they are using.

I do like the idea, however shared-objects are always a bit finicky. I would certainly look more into the limitations of loading shared objects (which are likely built against GLIBC at least on linux) from static musl binaries. I've been looking for ways to conveniently support spatialite, maybe this could be a path.

[ignatz]

I did a quick PoC, loading a GLIBC with a MUSL binary is probably UB, specifically i got

1277458 segmentation fault  cargo run --target=x86_64-unknown-linux-musl

[LQR471814]

Yeah that makes sense. Would it be feasible to compile shared-objects with MUSL?

[ignatz]

Yeah that makes sense. Would it be feasible to compile shared-objects with MUSL?

That is not an option :/. https://www.musl-libc.org/faq.html doesn't talk about this point specifically but talks a bit more about compatibility.