So... what exactly _is_ it doing?

[IngwiePhoenix]

Describe the bug
I honestly wish I could give you one...

root@7644ac4961cd:/workspace# surrealdb-migrations apply --dry-run
Error: 
   0: The query was not executed due to a failed transaction
   1: The query was not executed due to a failed transaction

Location:
   /root/.cargo/registry/src/index.crates.io-6f17d22bba15001f/surrealdb-migrations-2.1.0/src/surrealdb.rs:153

Backtrace omitted. Run with RUST_BACKTRACE=1 environment variable to display it.
Run with RUST_BACKTRACE=full to include source snippets.
root@7644ac4961cd:/workspace# surrealdb-migrations apply          
Executing migration ChangeDataKindGroupSingleToArray...
Error: 
   0: The query was not executed due to a failed transaction
      The field 'title' already exists

Location:
   /root/.cargo/registry/src/index.crates.io-6f17d22bba15001f/surrealdb-migrations-2.1.0/src/surrealdb.rs:185

Backtrace omitted. Run with RUST_BACKTRACE=1 environment variable to display it.
Run with RUST_BACKTRACE=full to include source snippets.

And the migration itself?

REMOVE FIELD kinds on data_kind_group;
DEFINE FIELD kinds ON data_kind_group TYPE array<record<data_kind>>;

What. o.o

To Reproduce
Scheme:

DEFINE TABLE IF NOT EXISTS data_kind
    SCHEMAFULL;

DEFINE FIELD description ON data_kind TYPE string;

And then add the above snippet as migration - and apply.

Expected behavior
I expected my migration to work...

Information

• SurrealDB version: SurrealDB command-line interface and server 2.1.4 for linux on x86_64
• surrealdb-migrations version: surrealdb-migrations 2.1.0

Additional context
I have a few tables (10ish or so) in my schemas folder and this is just my second migration. In fact, the first one passes just fine:

root@7644ac4961cd:/workspace# surrealdb-migrations list     
 Name                     | Executed at | File name                                      
--------------------------+-------------+------------------------------------------------
 ChangeAddressDeclaration | just now    | 20241211_144616_ChangeAddressDeclaration.surql 

But the new one... does not. And the error message is super confusing...

[Odonno]

Hello @IngwiePhoenix

It happens because the schema definition is replayable. Meaning it will happen many times and with SurrealDB v2, a simple DEFINE FIELD will break the replayable pattern. The easiest way to fix this is to use the new OVERWRITE keyword so that a schema file would look like this:

DEFINE TABLE OVERWRITE post SCHEMALESS;

DEFINE FIELD OVERWRITE title ON post TYPE string;
DEFINE FIELD OVERWRITE content ON post TYPE string;
DEFINE FIELD OVERWRITE author ON post TYPE record<user>;
DEFINE FIELD OVERWRITE created_at ON post TYPE datetime DEFAULT time::now();
DEFINE FIELD OVERWRITE status ON post TYPE string DEFAULT 'DRAFT' ASSERT $value IN ['DRAFT', 'PUBLISHED'];

You should use OVERWRITE on any kind of db object (table, field, index, etc..).

[IngwiePhoenix]

Good morning! Writing to you from home today :)

Is there any particular reason for this behaviour? I'd have thought a schema is static while migrations are the dynamic part?

[Odonno]

Yeah, I suppose it can feel a little disturbing. surrealdb-migrations does not really act exactly like a traditional migration tool. You are correct to say that the schema is static, meaning the schema is valid at any point in time, when the migrations are "dynamic" meaning they are applied only a particular time. But this also missing key points:

• schema isn't really static. You only see the last version of it in your file system while there could have been some changes during the time of migrations
• migrations can be applied up or down at any time (by future developers or yourself)

That's the main reason why the schema files must be written in a certain and defining everything with DEFINE OVERWRITE is the simplest manageable way.

I assume and I know myself this isn't the best approach. Note that I have some ideas to improve this tool in the future versions.