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.

[longnvgeargames]

Is there any update on this? I also face the same problem.

[gilzoide]

No updates from me, at least. In my latest test, I got the "Id" column back in the "CREATE TABLE" statement by lowering the managed code stripping level. It's possible that the attributes are being stripped from the builds, we can try to preserve them or something like that to see if it fixes the problem (reference documentation on managed code strippping).
Unfortunately I won't have time to look into this for a couple of weeks.

[ZezhongWang]

I had the same issue with the iOS platform also.

SQLiteException: duplicate column name: LevelName 
  at SQLite.SQLite3.Prepare2 (System.IntPtr db, System.String query) [0x00000] in <00000000000000000000000000000000>:0  
  at SQLite.SQLiteCommand.ExecuteNonQuery () [0x00000] in <00000000000000000000000000000000>:0  
  at SQLite.SQLiteConnection.Execute (System.String query, System.Object[] args) [0x00000] in <00000000000000000000000000000000>:0  
  at SQLite.SQLiteConnection.MigrateTable (SQLite.TableMapping map, System.Collections.Generic.List`1[T] existingCols) [0x00000] in <00000000000000000000000000000000>:0  
  at SQLite.SQLiteConnection.CreateTable (System.Type ty, SQLite.CreateFlags createFlags) [0x00000] in <00000000000000000000000000000000>:0  
  at GameDataManager.LoadDataModel () [0x00000] in <00000000000000000000000000000000>:0  
  at GameDataManager.Awake () [0x00000] in <00000000000000000000000000000000>:0  

The table structure like this:

public class WaveDataModel
{
    // Remove the static dbPath and replace it with a method
    private static string dbFileName = "EnemyWave.db";

    public static string GetDbPath()
    {
        string dbPath = Path.Combine(Application.streamingAssetsPath, dbFileName);
        return dbPath;
    }

    public string LevelName { get; set; }

    public int WaveNumber { get; set; }
}

Loading logic:

    private void LoadDataModel()
    {
        // Open a connection to the SQLite database
        using (SQLiteConnection db = new SQLiteConnection(WaveDataModel.GetDbPath()))
        {
            var result = db.CreateTable<WaveDataModel>();
            WaveDataArray = db.Table<WaveDataModel>().ToArray();

            string LevelName = SceneManager.GetActiveScene().name;
            WaveDataArray = WaveDataArray.Where(wave => wave.LevelName == LevelName).ToArray();
            Debug.LogFormat("Load enemy wave done, {0} waves in total.", WaveCount);
        }
    }