Runtime schema introspection

Thanks to the type-safe table classes generated by drift, writing SQL queries in Dart is simple and safe. However, these queries are usually written against a specific table. And while drift supports inheritance for tables, sometimes it is easier to access tables reflectively. Luckily, code generated by drift implements interfaces which can be used to do just that.

Since this is a topic that most drift users will not need, this page mostly gives motivating examples and links to the documentation for relevant drift classes. For instance, you might have multiple independent tables that have an id column. And you might want to filter rows by their id column. When writing this query against a single table, like the Todos table as seen in the getting started page, that's pretty straightforward:

Selectable<Todo> findTodoEntryById(int id) {
  return select(todos)..where((row) => row.id.equals(id));
}

But let's say we want to generalize this query to every database table, how could that look like? This following snippet shows how this can be done (note that the links in the snippet point directly towards the relevant documentation):

extension FindById<Table extends HasResultSet, Row>
    on ResultSetImplementation<Table, Row> {
  Selectable<Row> findById(int id) {
    return select()..where((row) {
      final idColumn = columnsByName['id'];

      if (idColumn == null) {
        throw ArgumentError.value(
          this,
          'this',
          'Must be a table with an id column',
        );
      }

      if (idColumn.type != DriftSqlType.int) {
        throw ArgumentError('Column `id` is not an integer');
      }

      return idColumn.equals(id);
    });
  }
}

Since that is much more complicated than the query that only works for a single table, let's take a look at each interesting line in detail:

• FindById is an extension on ResultSetImplementation. This class is the superclass for every table or view generated by drift. It defines useful methods to inspect the schema, or to translate a raw Map representing a database row into the generated data class.
  • ResultSetImplementation is instantiated with two type arguments: The original table class and the generated row class. For instance, if you define a table class Todos extends Table, drift would generate a class that extends Todos while also implementing. ResultSetImplementation<Todos, Todo> (with Todo being the generated data class).
  • ResultSetImplementation has two subclasses: TableInfo and ViewInfo which are mixed in to generated table and view classes, respectively.
  • HasResultSet is the superclass for Table and View, the two classes used to declare tables and views in drift.
• Selectable<Row> represents a query, you can use methods like get(), watch(), getSingle() and watchSingle() on it to run the query.
• The select() extension used in findById can be used to start a select statement without a reference to a database class - all you need is the table instance.
• We can use columnsByName to find a column by its name in SQL. Here, we expect an int column to exist.
• The GeneratedColumn class represents a column in a database. Things like column constraints, the type or default values can be read from the column instance.
  • In particular, we use this to assert that the table indeed has an IntColumn named id.

To call this extension, await myDatabase.todos.findById(3).getSingle() could be used. A nice thing about defining the method as an extension is that type inference works really well - calling findById on todos returns a Todo instance, the generated data class for this table.

Updates and inserts#

The same approach also works to construct update, delete and insert statements (although those require a TableInfo instead of a ResultSetImplementation as views are read-only). Also, updates and inserts use an Insertable object which represents a partial row of updated or inserted columns, respectively. With a known table, one would use the generated typed Companion objects for that. But this can also be done with schema introspection thanks to the RawValuesInsertable, which can be used as a generic Insertable backed by a map of column names to values.

This example builds on the previous one to update the title column of a generic table based on a filter of the id column:

extension UpdateTitle on DatabaseConnectionUser {
  Future<Row?> updateTitle<T extends TableInfo<Table, Row>, Row>(
    T table,
    int id,
    String newTitle,
  ) async {
    final columnsByName = table.columnsByName;
    final stmt = update(table)
      ..where((tbl) {
        final idColumn = columnsByName['id'];

        if (idColumn == null) {
          throw ArgumentError.value(
            this,
            'this',
            'Must be a table with an id column',
          );
        }

        if (idColumn.type != DriftSqlType.int) {
          throw ArgumentError('Column `id` is not an integer');
        }

        return idColumn.equals(id);
      });

    final rows = await stmt.writeReturning(
      RawValuesInsertable({'title': Variable<String>(newTitle)}),
    );

    return rows.singleOrNull;
  }
}

In a database or database accessor class, the method can then be called like this:

Future<Todo?> updateTodoTitle(int id, String newTitle) {
  return updateTitle(todos, id, newTitle);
}

Hopefully, this page gives you some pointers to start reflectively inspecting your drift databases. The linked Dart documentation also expains the concepts in more detail. If you have questions about this, or have a suggestion for more examples to include on this page, feel free to start a discussion about this.