sqlsqlitereact-native-sqlite-storage

how to avoid duplicate insertion sqlite


Hi i added array of data as rows in local database which is got from backend, now i got new data with existing data from backend, i'm trying to add this data to local database but duplicates are creating

"CREATE TABLE IF NOT EXISTS " +
this.GENRES_TABLE_NAME +
" " +
"( " +
"_id integer primary key autoincrement, " +
"genre_id integer, " +
"genre_name text, " +
" )";

i have this data in local database

    {
        "_id":1,
        "genre_id": 43,
        "genre_name": "*Born 1920 - 1925"
    },
    {
        "_id":2
        "genre_id": 44,
        "genre_name": "1920's and Before"
    },
    {
        "_id":3
        "genre_id": 45,
        "genre_name": "1930's"
    }

this is my new data

    {
        "genre_id": 43,
        "genre_name": "*Born 1920 - 1925"
    },
    {
        "genre_id": 44,
        "genre_name": "1920's and Before"
    },
    {
        "genre_id": 111,
        "genre_name": "new collection"
    }

i want to add genre_id:111 record to local database, but my query adding all records to local database, this is my query

   let insertSQL = `INSERT INTO ${this.GENRES_TABLE_NAME} (genre_id 
    ,genre_name) VALUES `;
      genres.forEach((genre, index) => {
        insertSQL += `(${genre.id},"${genre.name}")`;
        if (index < genres.length - 1) {
          insertSQL += ",";
        }
      });
      insertSQL += ";";
      db.executeSql(insertSQL)

please help me


Solution

  • Since genre_id already has a UNIQUE constraint, you should be able to use the SQLite-specific syntax INSERT OR IGNORE INTO <table>, silencing what would otherwise be a unique constraint violation error.

    Also, I noticed you're not using parameterized queries, which isn't safe. To protect your app from SQL injection attacks, use parameterized queries everywhere.