Writes (update, insert, delete)

Select rows or invidiual columns from tables in Dart

Updates and deletes

You can use the generated classes to update individual fields of any row:

Future moveImportantTasksIntoCategory(Category target) {
  // for updates, we use the "companion" version of a generated class. This wraps the
  // fields in a "Value" type which can be set to be absent using "Value.absent()". This
  // allows us to separate between "SET category = NULL" (`category: Value(null)`) and not
  // updating the category at all: `category: Value.absent()`.
  return (update(todos)
      ..where((t) => t.title.like('%Important%'))
      category: Value(target.id),

Future updateTodo(Todo entry) {
  // using replace will update all fields from the entry that are not marked as a primary key.
  // it will also make sure that only the entry with the same primary key will be updated.
  // Here, this means that the row that has the same id as entry will be updated to reflect
  // the entry's title, content and category. As its where clause is set automatically, it
  // cannot be used together with where.
  return update(todos).replace(entry);

Future feelingLazy() {
  // delete the oldest nine tasks
  return (delete(todos)..where((t) => t.id.isSmallerThanValue(10))).go();

⚠️ Caution: If you don't explicitly add a where clause on updates or deletes, the statement will affect all rows in the table!


You can very easily insert any valid object into tables. As some values can be absent (like default values that we don't have to set explicitly), we again use the companion version.

// returns the generated id
Future<int> addTodo(TodosCompanion entry) {
  return into(todos).insert(entry);

All row classes generated will have a constructor that can be used to create objects:

    title: Value('Important task'),
    content: Value('Refactor persistence code'),

If a column is nullable or has a default value (this includes auto-increments), the field can be omitted. All other fields must be set and non-null. The insert method will throw otherwise.

Multiple insert statements can be run efficiently by using a batch. To do that, you can use the insertAll method inside a batch:

Future<void> insertMultipleEntries() async{
  await batch((batch) {
    // functions in a batch don't have to be awaited - just
    // await the whole batch afterwards.
    batch.insertAll(todos, [
        title: 'First entry',
        content: 'My content',
        title: 'Another entry',
        content: 'More content',
        // columns that aren't required for inserts are still wrapped in a Value:
        category: Value(3),
      // ...

Batches are similar to transactions in the sense that all updates are happening atomically, but they enable further optimizations to avoid preparing the same SQL statement twice. This makes them suitable for bulk insert or update operations.


Upserts are a feature from newer sqlite3 versions that allows an insert to behave like an update if a conflicting row already exists.

This allows us to create or override an existing row when its primary key is part of its data:

class Users extends Table {
  TextColumn get email => text()();
  TextColumn get name => text()();

  Set<Column> get primaryKey => {email};

Future<int> createOrUpdateUser(User user) {
  return into(users).insertOnConflictUpdate(user);

When calling createOrUpdateUser() with an email address that already exists, that user's name will be updated. Otherwise, a new user will be inserted into the database.

Inserts can also be used with more advanced queries. For instance, let's say we're building a dictionary and want to keep track of how many times we encountered a word. A table for that might look like

class Words extends Table {
  TextColumn get word => text()();
  IntColumn get usages => integer().withDefault(const Constant(1))();

  Set<Column> get primaryKey => {word};

By using a custom upserts, we can insert a new word or increment its usages counter if it already exists:

Future<void> trackWord(String word) {
  return into(words).insert(
    WordsCompanion.insert(word: word),
    onConflict: DoUpdate(
        (old) => WordsCompanion.custom(usages: old.usages + Constant(1))),

Note that this requires a fairly recent sqlite3 version (3.24.0) that might not be available on older Android devices when using drift_sqflite. NativeDatabases and sqlite3_flutter_libs includes the latest sqlite on Android, so consider using it if you want to support upserts.

Also note that the returned rowid may not be accurate when an upsert took place.


You can use insertReturning to insert a row or companion and immediately get the row it inserts. The returned row contains all the default values and incrementing ids that were generated.

Note: This uses the RETURNING syntax added in sqlite3 version 3.35, which is not available on most operating systems by default. When using this method, make sure that you have a recent sqlite3 version available. This is the case with sqlite3_flutter_libs.

For instance, consider this snippet using the tables from the getting started guide:

final row = await into(todos).insertReturning(TodosCompanion.insert(
  title: 'A todo entry',
  content: 'A description',

The row returned has the proper id set. If a table has further default values, including dynamic values like CURRENT_TIME, then those would also be set in a row returned by insertReturning.