Working with temporary tables

[JetA2]

First, thank you for this excellent library! It has made database operations very easy to use in my apps.

I need to make a SELECT statement with what potentially may be a very large WHERE..IN clause.
My plan is to make a temporary table, insert the items from the IN clause, and operate on the temporary table and the table I want to query.

I can easily create a temporary table with a customStatement and manipulate it with more custom statements, but I really want to keep using all the nice features of Drift that makes writing SQL statements unnecessary.

Is there a way to operate on a table created with a customStatement using Drift classes?

[simolus3]

Hi, glad you're enjoying drift!

Is there a way to operate on a table created with a customStatement using Drift classes?

Because drift does not support the TEMPORARY keyword in CREATE TABLE statements, you would have to create the table yourself, using custom statements.
However, one trick you could use is to mixin TableInfo to define your own custom tables. Let's say you had a CREATE TEMPORARY TABLE pending_values (value TEXT NOT NULL);. You could define a Dart class modelling that table with a custom name:

final class TemporaryTable extends Table
    with TableInfo<TemporaryTable, Map<String, dynamic>> {
  @override
  final String actualTableName;
  final String? alias;
  @override
  final DatabaseConnectionUser attachedDatabase;

  TemporaryTable(this.attachedDatabase, this.actualTableName, [this.alias]);

  late final GeneratedColumn<String> value = GeneratedColumn(
      'value', actualTableName, false,
      type: DriftSqlType.string);

  @override
  List<GeneratedColumn<Object>> get $columns => [value];

  @override
  TableInfo<TemporaryTable, Map<String, dynamic>> createAlias(String alias) {
    return TemporaryTable(attachedDatabase, actualTableName, alias);
  }

  @override
  FutureOr<Map<String, dynamic>> map(Map<String, dynamic> data,
      {String? tablePrefix}) {
    return data;
  }
}

After creating the table, you can use something like this to insert into it:

final table = TemporaryTable(db, 'pending_values');
await db.batch((b) {
  b.insert(table, RawValuesInsertable({'value': Variable('...')}));
});

Similarly, you could join the table with the other parts of the query builder API to select the pending values.

[JetA2]

Great, this looks like just what I need!

I do get a runtime error for the batch insert though:

Unhandled Exception: type 'RawValuesInsertable<dynamic>' is not a subtype of type 'Insertable<Map<String, dynamic>>' of 'instance'
#0      TableInfo.validateIntegrity (package:drift/src/runtime/query_builder/schema/table_info.dart:60:55)
table_info.dart:60
#1      InsertStatement._validateIntegrity (package:drift/src/runtime/query_builder/statements/insert.dart:383:11)
insert.dart:383
#2      InsertStatement.createContext (package:drift/src/runtime/query_builder/statements/insert.dart:214:5)
insert.dart:214
#3      Batch.insert (package:drift/src/runtime/api/batch.dart:52:10)
batch.dart:52
#4      Batch.insertAll (package:drift/src/runtime/api/batch.dart:72:7)
batch.dart:72

My code (path is a String):

      await batch((batch) {
        batch.insertAll(
          temporaryTable,
          filePaths.map(
            (path) => RawValuesInsertable({columnName: Variable(path)}),
          ),
        );
      });

[simolus3]

Does it work if you add an explicit RawValuesInsertable<Map<String, dynamic>>() type parameter?

[JetA2]

Yes, that was it. Many thanks!

[JetA2]

I expanded Simon's class a bit to create a general purpose single-value temporary table if anyone else needs a solution for this use case.
Disclaimer: not extensively tested!

/// Allows Drift query builder access for temporary tables created
/// with [customStatement]. Does not support nullable values.
///
final class TemporaryTable<T extends Object> extends Table
    with TableInfo<TemporaryTable, Map<String, dynamic>> {
  final DriftSqlType<T> type;
  final GeneratedColumn<T> value;

  @override
  final String actualTableName;
  final String? alias;

  @override
  final DatabaseConnectionUser attachedDatabase;

  TemporaryTable(
    this.attachedDatabase,
    this.actualTableName, {
    this.alias,
    required this.type,
  }) : value = GeneratedColumn(
          'value',
          actualTableName,
          false,
          type: type,
        );

  @override
  List<GeneratedColumn<Object>> get $columns => [value];

  @override
  TableInfo<TemporaryTable, Map<String, dynamic>> createAlias(String alias) {
    return TemporaryTable(
      attachedDatabase,
      actualTableName,
      alias: alias,
      type: type,
    );
  }

  @override
  FutureOr<Map<String, dynamic>> map(Map<String, dynamic> data,
      {String? tablePrefix}) {
    return data;
  }

  Future<void> create() {
    final context = GenerationContext.fromDb(attachedDatabase);

    return attachedDatabase.customStatement(
      'CREATE TEMPORARY TABLE $actualTableName '
      '(${value.name} ${value.type.sqlTypeName(context)} NOT NULL)',
    );
  }

  Future<void> drop() {
    return attachedDatabase.customStatement(
      'DROP TABLE IF EXISTS $actualTableName',
    );
  }

  Insertable<Map<String, dynamic>> insertable(T value) =>
      RawValuesInsertable<Map<String, dynamic>>(
        {this.value.name: Variable(value)},
      );
}