How to reset SQLite autoincrement while using Flyway in Unit test

Question

Consider the code below in a unit test, where I add a new Tag object in a pre-populated SQLite database.

@Test                                                     // Line 1
public void add() {                                       
    Tag tagToAdd = new Tag("Tall");                       
    Tag addedTag = this.tagDao.add(tagToAdd);
    assertNotNull(addedTag);
    assertEquals(3L, addedTag.getId());                   // Line 6
    assertEquals(tagToAdd.getTag(), addedTag.getTag());   
    List<Tag> tags = this.tagDao.get();
    assertEquals(3, tags.size());
}

On line 6, I expect the ID of the Tag to be 3, because the field is an AUTOINCREMENT and the test is initialized with a database already containing 2 Tags. This works fine every time I run the test and the ID is always 3.

Now, I am integrating flyway to the project. Every time I run the test, the AUTOINCREMENT starts from the value of the last run, so the Tag ID increments by 1 every run, and the test fails.

Any idea on how I can get flyway to always reset the database to a brand new state, and reset the AUTOINCREMENT value ? I could write a query to do it manually, but this is not maintainable.

What I have tried so far ?

Integrate @FlywayTest, as this executes flyway task clean
Defined a FlywayMigrationStrategy bean, which contains flyway.clean()
Set spring.flyway.clean-on-validation-error to true in my application.properties (that said, there was no change in my sql, so not sure if this changed anything)

-- Edit My 1st migration script contains the below.

DROP TABLE IF EXISTS Tag;

CREATE TABLE Tag(
    id INTEGER PRIMARY KEY AUTOINCREMENT, 
    tag VARCHAR(255) NOT NULL UNIQUE,
    createdDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    modifiedDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Recreating or cleaning the database before each test is a good practice to make sure the test always runs with the correct initial state. flyway.clean() drops all objects in the configured schemas so if you run it before each test execute, you should start from an empty database each time. You mentioned that you tried that already. Does that work for you? — Andrea Angella, Sep 23 '21 at 08:34

score 0 · Answer 1 · answered Sep 20 '21 at 21:37

If I understood everything correctly - you have a database and a table in this database which is created once and the same table is used for tests every time - you just delete rows from the table (without removing it) when tests are completed (or before starting next tests) and flyway just inserts two tags into this table every time you run the tests.

If that's right - you can just reset sequence in SQLite to set it back to 1 so next inserted row will be inserted with this id. You can do it by running the following query:

UPDATE `sqlite_sequence` SET `seq` = 1 WHERE `name` = 'tags_table_name';

Alternatively, you can set seq to 0 - this value is incorrect so SQLite will use next available correct value (if there are no rows in the table - it will be one, if there are some values - it will first available number).

Yet another possibility is just to delete your table after tests and recreate it before running next tests - as it is a database and table just for tests - it should work correctly. This way you have your sequence counter set back to value 1 each time. I would actually go this way until you have really good reason not to delete the table.

Yes, I would like to recreate and repopulate the db before each tests. But this is not happening when I add flyway. I have added the content of my 1st migration script to the question — Sandrew Cheru, Sep 22 '21 at 11:45
So have you tried the solution I provided? I know it isn't perfect solution here but it should do the trick (kind of workaround). Regarding proper solution - do you use Spring Boot or Spring (but not Sprint Boot)? Could you show your Configuration class with FlywayMigrationStrategy bean defined? — Pawel Woroniecki, Sep 23 '21 at 09:02

How to reset SQLite autoincrement while using Flyway in Unit test

1 Answers1