title: Flutter - sqflite: How to change the order (index) of the persisted data?
body: <p>I have searched for this question everywhere. Maybe I didn’t use the correct search terms. If it’s been asked already, my apologies. I’ll quickly delete this if it turns out to be a duplicate.</p>
  <p>I’m a rather inexperienced coder, so this code will definitely not be the best looking one out there. Hope it’s understandable though.</p>
  <p>The Question:</p>
  <ul>
  <li>I have a list of custom ListTiles and its data is persisted via the sqflite package.</li>
  <li>Everything you do to it (change info on it) is already being persisted</li>
  <li>The problem is, I have implemented the reorderables 0.3.2 package so that the list could be reorderable via drag and drop</li>
  <li>How can I make the order changes apply to the database? How can I change the order of the bar tiles in the database?</li>
  <li>The way it works right now, you add a bar and it goes to the DB. That’s the order forever it never changes.</li>
  <li>How can I make the index changes from dragging and dropping a bar to a different position/index apply to my sqflite database?</li>
  </ul>
  <p>I will leave the code from where the list is rendered, the fetchAndSetBars method, as well as the DB methods for creating and updating the DB. If I’m missing any info that anyone might need let me know. I will put it up here as soon as possible</p>
  <p><a href="https://i.stack.imgur.com/2DWWw.png" rel="nofollow noreferrer">Image of the list ordered by DB</a></p>
  <p><a href="https://i.stack.imgur.com/tyLjw.png" rel="nofollow noreferrer">Image of altered list order. If you restart app it goes back to image 1</a></p>
  <p>This is the code for the list that is the problem:</p>
  <pre><code>import 'package:flutter/material.dart';
  import 'package:provider/provider.dart';
  import 'package:budget_mentor/models/bar_tile_data.dart';
  import 'package:reorderables/reorderables.dart';
  
  class BarTilesList extends StatelessWidget {
   @override
   Widget build(BuildContext context) {
     return FutureBuilder(
       future:
           Provider.of&lt;BarTileData&gt;(context, listen: false).fetchAndSetBars(),
       builder: (ctx, snapshot) =&gt; snapshot.connectionState ==
               ConnectionState.waiting
           ? Center(
               child: CircularProgressIndicator(),
             )
           : Consumer&lt;BarTileData&gt;(
               builder: (context, barTileData, child) {
                 ScrollController _scrollController =
                     PrimaryScrollController.of(context) ?? ScrollController();
  
                 void _onReorder(int oldIndex, int newIndex) {
                   Widget row = barTileData.barTiles.removeAt(oldIndex);
                   barTileData.barTiles.insert(newIndex, row);
                 }
  
                 return Column(
                   children: &lt;Widget&gt;[
                     Expanded(
                       child: CustomScrollView(
                         controller: _scrollController,
                         slivers: &lt;Widget&gt;[
                           ReorderableSliverList(
                             delegate: ReorderableSliverChildBuilderDelegate(
                                 (BuildContext context, int index) =&gt;
                                     barTileData.barTiles[index],
                                 childCount: barTileData.barTiles.length),
                             onReorder: _onReorder,
                           )
                         ],
                       ),
                     ),
                     Text('Placeholder'),
                   ],
                 );
               },
             ),
     );
   }
  }
  </code></pre>
  <p>FetchAndSetBars method:</p>
  <pre><code>Future&lt;void&gt; fetchAndSetBars() async {
   final dataList = await DBHelper.getBarTileDBData('user_bars');
   barTiles = dataList.map((bar) {
     final int colorHex = int.tryParse(bar['barColor']);
     final String valueKey = bar['barKey'];
     return BarTile(
       barTitle: bar['barTitle'],
       barColor: Color(colorHex),
       barWidth: bar['barWidth'],
       id: bar['barID'],
       key: ValueKey(valueKey),
     );
   }).toList();
  
   notifyListeners();
  }
  </code></pre>
  <p>DB Creation Code:</p>
  <pre><code>    static Future&lt;Database&gt; barTilesDatabase() async {
   final dbPath = await sql.getDatabasesPath();
   return sql.openDatabase(path.join(dbPath, 'bars_tile_list.db'),
       onCreate: (db, version) {
     return db.execute(
         'CREATE TABLE user_bars($barTitle TEXT PRIMARY KEY, $barColor TEXT, $barWidth REAL, $barID TEXT, $barKey TEXT)');
   }, version: 5);
  }
  </code></pre>
  <p>(Codes for insert and delete are standard)
  I‘m guessing the only other relevant DB piece of code to add would be my updateDB method:</p>
  <pre><code>static updateUserBarsDB(
     String barTitle,
     Color barColor,
     double barWidth,
     String barID,
     String barKey) async {
  
   Database db = await DBHelper.barTilesDatabase();
   final String colorValueAsString = barColor.value.toString();
  
   Map&lt;String, dynamic&gt; updatedBarTile = {
     DBHelper.barTitle: barTitle,
     DBHelper.barColor: colorValueAsString,
     DBHelper.barWidth: barWidth,
     DBHelper.barID: barID,
     DBHelper.barKey: barKey,
   };
  
   String id = barID;
   await db.update('user_bars', updatedBarTile,
       where: '${DBHelper.barID} = ?', whereArgs: [id]);
   print(await db.query('user_bars'));
  }
  </code></pre>