Allow SQLite extensions

[lbe]

I am migrating a database where I would like to use sqlean's regexp extension. I have a field path that contains a file path. The file path has two embedded values that I want to break out into two new fields (user_id, filename). Without extensions, I am unable to do this is in sqlite. When I attempt to load an extension, I received "Error: no such function: load_extension".

In scanning the dbmate code, I see that the Makefile contains sqlite_omit_load_extension which explicitly prevents this behavior. I assume that this was added for some specific reason. I am requesting that this exclusion be reconsidered and possibly removed.

Thank you,

lbe

[dossy]

@lbe sqlite_omit_load_extension was added to the build 5 years ago on 2020-08-11 in commit 4581aca when @amacneil changed dbmate to build statically linked binaries, with the following comment:

# avoid building code that is incompatible with static linking

So, according to modern SQLite documentation on Run-Time Loadable Extensions, it sounds like a statically linked SQLite, linked to dbmate, should still be able to dynamically load extensions at runtime if the right steps are taken, and sqlite_omit_load_exension is not specified at build time.

However, the SQLite documentation does point out that disabling loading arbitrary executable code at run-time is a security precuation:

For security reasons, extension loading is turned off by default. [...]

@amacneil, how do you feel about enabling it in dbmate? Not the work to implement, but the consequence of the decision to support it?

[eduardosanzb]

Hello! Thank you for the work and for following up on this; it seems I encountered this issue because I'm trying to use dbmate to execute my migrations.

In one of my migrations, I would like to create a table to store vector embeddings using a plugin.
Sadly, I realized this is not possible with this current security measure from dbmate.

I ended up having a special separate migration strategy for this kind of table, tho sadly it makes the management of future migrations more complex.

Is there any other idea you could have for this kind of scenarios?

example of a migration that will fail; because the vec0 type is not loaded

-- migrate:up
-- Create new vector table with auxiliary columns for message embeddings
CREATE VIRTUAL TABLE IF NOT EXISTS message_vectors USING vec0(
  message_id INTEGER PRIMARY KEY,
  message_embedding FLOAT[384],
  +chat_id TEXT,
);

-- Migrate existing data to vector table (only the auxiliary columns, embedding will be populated later)
INSERT OR IGNORE INTO message_vectors(
  message_id,
  message_embedding,
  +chat_id,

)
SELECT 
  message_id,
  NULL, -- Will be populated in next step with embeddings
  chat_id,

FROM Messages;

-- Drop the message_embedding column from Messages table as it's now in the vector table
ALTER TABLE Messages DROP COLUMN IF EXISTS message_embedding;

-- migrate:down
-- Drop the vector table and restore the embedding column in Messages
DROP TABLE IF EXISTS message_vectors;

-- Restore the message_embedding column to Messages table
ALTER TABLE Messages ADD COLUMN IF NOT EXISTS message_embedding BLOB;