Bug: The table X already exists

[dancixx]

Describe the bug
I had an old version of Surrealdb < 2.0.0. I have updated to 2.0.1 by dropping the old rocksdb and migrating to surrealkv. I have run the github action as always. At first run everything worked well, after that I have inserted my data using surrealql in the surrealist app. After that when I want to deploy my app using github action which also includes the surrealdb-migrations github action I always get the following error:

Run Odonno/[email protected]
  
[surrealdb-migrations] downloading surrealdb-migrations from https://github.com/Odonno/surrealdb-migrations/releases/download/v2.0.0-preview.1/surrealdb-migrations_v2.0.0-preview.1_x86_64-unknown-linux-musl.tar.gz
/usr/bin/tar xz --warning=no-unknown-keyword --overwrite -C /home/runner/work/_temp/7e9fb0f6-f80a-4e63-b41a-045c3a57fdbb -f /home/runner/work/_temp/45bd46f1-d77c-45dc-ae31-0e5ae62dfeb1
[surrealdb-migrations] checking if everything is right
/home/runner/work/_temp/7e9fb0f6-f80a-4e63-b41a-045c3a57fdbb/surrealdb-migrations apply --dry-run --validate-version-order --address *** --ns *** --db *** --username *** --password ***
[surrealdb-migrations] applying migrations
/home/runner/work/_temp/7e9fb0f6-f80a-4e63-b41a-045c3a57fdbb/surrealdb-migrations apply --address *** --ns *** --db *** --username *** --password ***
Error: 
   0: The query was not executed due to a failed transaction
      The table 'author' already exists
Location:
   src/surrealdb.rs:[18](https://github.com/rust-dd/blog/actions/runs/10973232669/job/30470393177#step:8:19)5
Backtrace omitted. Run with RUST_BACKTRACE=1 environment variable to display it.
Run with RUST_BACKTRACE=full to include source snippets.
Error: The process '/home/runner/work/_temp/7e9fb0f6-f80a-4e63-b41a-045c3a57fdbb/surrealdb-migrations' failed with exit code 1

Expected behavior
When I start a deploy the migration should run without any error message.

Information
My surrealdb-migrations related files:

events:
-- event1.surql
-- event2.surql
migrations:
-- definitions:
----- _initial.json
----- timestamp_name.json
-- timestamp_name.surql
schemas:
-- author.surql
-- post.surql
--script_migration.surql

• SurrealDB version: 2@latest
• surrealdb-migrations version:

 with:
    address: ***
    ns: ***
    db: ***
    username: ***
    password: ***
    version: v2.0.0-preview.1
    skip-untracked-files: true
  env:
    CARGO_TERM_COLOR: always
    CARGO_HOME: /home/runner/.cargo
    CARGO_INCREMENTAL: 0

[Odonno]

Due to breaking changes in surrealdb v2, you need to use the new OVERWRITE keyword in all your DEFINE statements.

Here is a link that explain this new keywork: https://surrealdb.com/docs/surrealql/statements/define/table#using-overwrite-clause

[dancixx]

@Odonno Thanks, this solved that, but can you explain in more detail why this is necessary? I looked through the docs but not totally clear.

[Odonno]

Well, the change was made to avoid accidental change in the schema, using the extra keyword when you are sure about what you are doing.

[dancixx]

@Odonno so that means that I have to add in any kind of schema files OVERWRITE keyword in any DEFINE statement? So that means when the GitHub actions run every DEFINE statement will be overwritten?

As I understood how surrealdb-migrations is working:
schema and event files are applied, and migrations are only for data manipulations of a new field that was added, I can fill in the missing data with migrations.

What happens if I use IF NOT EXISTS statement, in the future I want the update one of my table fields. I can add the OVERWRITE and it will just work, or just use OVERWRITE everywhere?

[Odonno]

You are correct, this is exactly how surrealdb-migrations works.
I would recommend adding OVERWRITE to all your DEFINE statements to have the same behavior as with surrealdb v1.

I would also not recommend the use of IF NOT EXISTS statement as it will prevent you from updating a table/field/etc... in the future. But if you know exactly how you or your team will use it, then go for it.

In any case, I would prefer to use OVERWRITE by default, as you can see in the predefined templates.