Drift syntax to create an index

Question

Given this table:

class TodoCategories extends Table {
  IntColumn get age => integer()();
  TextColumn get name => text()();
}

how can I add an index to the name column? I see from the documentation there is an Index class, but I'm not clear how to use it.

Answer 1

I found a comment in the source code (index.dart) stating that indexes can be created only in .drift files. In my case, I had to move all of the sql into .drift files to get it to work. There is no mention in the documentation of whether it is possible to create Drift tables in both the dart files and the .drift files, but when I tried it, it failed silently.

So to answer the question, create a file like

tables.drift

CREATE TABLE TodoCategories(
   age INTEGER NOT NULL,
   name TEXT NOT NULL);

CREATE INDEX idx on TodoCategories (name);

In the dart source:

@DriftDatabase(
  include: {'tables.drift'},
)

Answer 2

Managed to make this work in 2 ways:

• using .drift file
• using createIndex() in migration

But, second way gave me an error (in debug mode) that DB schema did not match (because I have impl.validateDatabaseSchema(this) in beforeOpen taken from the official docs).

Note: code below not tested.

Using .drift file

The only difference that I see from accepted answer is that I had tables in @DriftDatabase annotation and import in tables.drift file.

tables.drift

import 'tables.dart';

CREATE UNIQUE INDEX "some_index_name"
ON "your_table_names" (
  "your_field",
  "another_field" DESC
);

tables.dart

@DataClassName('YourTableName')
class YourTableNames extends Table {
  TextColumn get yourField => text().nullable()();
  TextColumn get anotherField => text().nullable()();
}

database.dart

...
part 'database.g.dart';

@DriftDatabase(tables: [
  YourTableName,
], include: {
  'tables.drift'
})
class YourDatabase extends _$YourDatabase {
...
}

Using createIndex() in migration

tables.dart

@DataClassName('YourTableName')
class YourTableNames extends Table {
  TextColumn get yourField => text().nullable()();
  TextColumn get anotherField => text().nullable()();
}

// Note: This class is not created in database.g.dart after running `dart run build_runner build` and used as const-like class (see below).
// This way it can be put anywhere but I think some annotation is missing,
// because in case of .drift file and SQL above running build actually
// creates `late final Index` property in generated DB class.
class SomeIndexName extends Index {
  SomeIndexName()
      : super(
          'some_index_name', // actually not sure what should go in here
          'CREATE UNIQUE INDEX "some_index_name"'
              ' ON "your_table_names" ('
              ' "your_field",'
              ' "another_field" DESC'
              ');',
        );
}

...
part 'database.g.dart';

@DriftDatabase(tables: [
  YourTableName,
], include: {
  'tables.drift'
})
class YourDatabase extends _$YourDatabase {
...
  @override
  MigrationStrategy get migration {
    return MigrationStrategy(
      onCreate: (m) async {
         await m.createAll(); // Important! This is what gets called if `onCreate` not provided
         await m.createIndex(SomeIndexName());
      },
      onUpgrade: (Migrator m, int from, int to) async {
        if (from < some version) {
          await m.createIndex(SomeIndexName());
        }
      },
      beforeOpen: (details) async {
        // Here it throws error in debug mode if you followed the official doc.
        await impl.validateDatabaseSchema(this);
      },
    );
  }

Answer 3

You can now define your indexes when creating the table in dart. Please see here: https://drift.simonbinder.eu/docs/getting-started/advanced_dart_tables/#index

Shortly from Simon Binder:

" An index on columns in a table allows rows identified by these columns to be identified more easily. In drift, you can apply an index to a table with the @TableIndex annotation. More than one index can be applied to the same table by repeating the annotation:

@TableIndex(name: 'user_name', columns: {#name})
class Users extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text()();
}

Each index needs to have its own unique name. Typically, the name of the table is part of the index' name to ensure unique names. "