SQLiteException: duplicate column name: Id

[MrTony1987]

 void Start()
    {
        // 1. Create a connection to the database.
        // The special ":memory:" in-memory database and
        // URIs like "file:///somefile" are also supported
        var db = new SQLiteConnection($"{Application.persistentDataPath}/MyDb.db");

        // 2. Once you have defined your entity, you can automatically
        // generate tables in your database by calling CreateTable
        db.CreateTable<Player>();

        // 3. You can insert rows in the database using Insert
        // The Insert call fills Id, which is marked with [AutoIncremented]
        var newPlayer = new Player
        {
            Name = "gilzoide",
        };
        Debug.Log("before insert Id=" + newPlayer.Id);
        db.Insert(newPlayer);
        Debug.Log("after insert Id=" + newPlayer.Id);
        Debug.Log($"Player new ID: {newPlayer.Id}");
        // Similar methods exist for Update and Delete.

        // 4.a The most straightforward way to query for data
        // is using the Table method. This can take predicates
        // for constraining via WHERE clauses and/or adding ORDER BY clauses
        var query = db.Table<Player>().Where(p => p.Name.StartsWith("g"));
        foreach (Player player in query)
        {
            Debug.Log($"Found player named {player.Name} with ID {player.Id}");
        }

        // 4.b You can also make queries at a low-level using the Query method
        var players = db.Query<Player>("SELECT * FROM Player WHERE Id = ?", 1);
        foreach (Player player in players)
        {
            Debug.Log($"Player with ID 1 is called {player.Name}");
        }

        // 5. You can perform low-level updates to the database using the Execute
        // method, for example for running PRAGMAs or VACUUM
        db.Execute("VACUUM");
    }

Hello, I found a problem. After building and running the above example on an Android device, starting from the second launch, an exception is thrown: “SQLiteException: duplicate column name: Id.”. Editor is OK. v1.1.0

[gilzoide]

Hey @MrTony1987, thanks for the report. That's really weird 🤔 How is your "Player" class defined?

[MrTony1987]

Hey @MrTony1987, thanks for the report. That's really weird 🤔 How is your "Player" class defined?

Hello, my code only contains the content in the Start method, with nothing else. You can freely create a MonoBehaviour script that includes this Start method and attach it to a GameObject. Then, print the log to the screen, and run it multiple times on an Android device to check the logs.

[gilzoide]

Well, you didn't really put the definition of Player in your code in this issue. I'm assuming you are using the same code that is present in this repository's README file.

public class Player
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    public string Name { get; set; }
}

I'll try to reproduce on an Android build here.

[gilzoide]

Also, tip for next time: use "```cs" to highlight code blocks with C# syntax =]

[gilzoide]

Ok, I've been able to reproduce this bug here. It's really weird, in my device the table is created only with the "Name" column, there's no "Id" column whatsoever 🫠 This is probably a bug in SQLite-net, as I've enabled the tracer function and the "CREATE TABLE" statement is missing the "Id" column 😔 In the subsequent sessions, where the database and the table exists, I get a "duplicate column name" just like you, although in my case the duplicated column is "Name". I added some Debug.Logs and found out that for whatever reason, SQLiteConnection.GetTableInfo is returning a list with null Columns, which causes SQLite-net to attempt to re-add the column and the bug to appear.

This needs more investigation, but it seems like a bug in the ORM code from SQLite-net.

Tested in Unity 2022.3.25.