How to create custom column types?

[johannesstricker]

Hey 👋
I'm building a sync engine using drift and I'm looking for ways to customize the behavior of tables/columns.

One example: When a table has a unique constraint, I want to compute the tables primary key uuid based off of the unique constraint columns. That way I can make sure that it's deterministic for the unique constraint. Right now, I'm enforcing this in my app logic, but it would be very nice to be able to define this as part of the database schema / column definition.

Something like

@TableIndex(
    columns: {#columnA, #columnB},
    unique: true)
class MyTable extends Tables {
  TextColumn get id => text().uuidFor(() => [columnA, columnB])();
}

Of course, it would be even nicer, if I wouldn't have to redefine the columns in the uuidFor method and it would work with the decorator alone. Is there some way to achieve this? I assume I would have to hook into build_runner, but I have no experience with that whatsoever.

Any help appreciated :) Thanks.

[dickermoshe]

This is a limitation of sqlite. PRIMARY KEY cannot also be GENERATED.

Would it be ok if we did the following:

@TableIndex(
    columns: {area},
    unique: true)
class MyTable extends Tables {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get other_id => text().generatedAs(columnA + columnB, stored: true)();
  TextColumn get columnA=> text()();
  TextColumn get columnB=> text()();
}

Note

In most instances, your primary key should be a IntColumn get id => integer().autoIncrement()();, you should really try to use other columns to achieve your result

[simolus3]

Then I'm using that ID as a primary key. I guess using a dart function in the generatedAs is not possible, is it?

Drift doesn't let you set insert hooks to intercept data. It can't really do that because you're also allowed to insert rows based on complex expressions that aren't evaluated in Dart but rather by the SQL database when running the insert.

Something you can do with SQLite though is create a custom SQL function that is implemented in Dart. So you can use the GENERATED trick, but e.g. register a function called combine_uuids that folds multiple SQL values into a single UUID. With Drift, you can register these functions like this

NativeDatabase(
  ...,
  setup: (rawDb) {
    rawDb.createFunction(
      directOnly: false,
      functionName: 'combine_uuids',
      function: (args) =>
          const Uuid().v5(NAMESPACE, args.map((e) => e.toString()).join('-')),
      argumentCount: const AllowedArgumentCount.any(),
    );
  },
)

Then once that's function has been registered, you can call it from SQL.

[dickermoshe]

While the above could work, I would really not recommend it ( although you havent asked for my advice).

Deterministic primary keys usually get you into a place which will only make things more complicated for you in the long run.

The cool part of UUIDs is that clashes should not be worried about, here is a stat:

Note

Only after generating 1 billion UUIDs every second for the next 100 years, the probability of creating just one duplicate would be about 50%. Or, to put it another way, the probability of one duplicate would be about 50% if every person on earth owned 600 million UUIDs.

I'm not sure how popular your app will be, and I don't know the ramifications of a single users app not working, but there is a bigger chance of a nuke hitting the phone of your user (exactly on the slice of chip which hold the sqlite database), than a UUID clash.

Hope that helps put everything in perspective

[johannesstricker]

@simolus3 Thanks for clarifying. I'll see where that gets me, thanks :)

@dickermoshe Thank you for the advice (which I DO want to hear 😉).

I think I might not have explained the problem well enough, though. My fear is not the clash of uuids. I'll try to explain again:
I'm building a sync engine for an offline first app. That means, that all records will be created on the client (and therefore the client will create the uuids/primary keys) and then synced to the backend in the background. The backend will then do conflict resolution. Example: two clients write to the same record "simultaneously". Then the backend will use a last-write-wins strategy to determine which clients' change takes effect.

That works fine, until we have a record like the tags mentioned above. A tag will be uniquely identified by the name (meaning the name has a unique constraint). But a tag also has a primary key uuid. Now imagine the scenario above: two clients simultaneously create the Persian Food tag. Both will create their own uuid and now both clients push their changes to the server. The second push will result in a unique constraint error. To resolve that, I would have to

1. Acknowledge the error and send back the existing Persian Food tag to the client
2. The client would have to replace all associations to his Persian Food tag with the one he received from the server
3. Then the client would have to push again

This is extremely complicated. The much simpler solution is to just make sure that tags with the same name map to the same primary key. Cause in that case, the conflict can just be resolved with the last-write-wins and everything will keep working fine.

I hope this time it was more clear. Now if you still see a problem with that approach, I'd definitely appreciate to hear it.

[dickermoshe]

How are going to handle if someone renames Persian Food to Persian Cuisine and there is already a Persian Cuisine on another device?

The only solution is to block them from renaming, and once you're gonna do that, maybe make the name the primary key too.

[johannesstricker]

Yeah, good point. That's actually the responsibility of the client then. So renaming on the client is actually "creating a new tag". So the client logic is a bit more complicated there.
Using the name as the primary key instead is an interesting idea. That clashes with my generic logic around primary keys, though, which are expected to be uuids.

[simolus3]

At the moment, you can't really apply macros to how drift generates code itself. Technically the bits are all there (drift works internally by first creating a hidden JSON representation of the schema it analyzed and then later generating code based on that). So technically some kind of middleware is feasible, but requires a lot of customization with a custom builder (also, your users would have to disable parts from drift's default build setup, so it's complex to apply).

One thing you could do is write a custom TableInfo class that redefines the id column at runtime:

final class _WithCombinedUuid extends Table with TableInfo {
  @override
  final GeneratedDatabase attachedDatabase;

  final TableInfo _inner;

  late final GeneratedColumn _uuid = _computeUuidColumn();

  _WithCombinedUuid(this.attachedDatabase, this._inner);

  GeneratedColumn _computeUuidColumn() {
    // TODO: Find unique columns from indexes and individual columns as well.
    final uniqueColumns = _inner.uniqueKeys.expand((e) => e).toList();

    return GeneratedColumn<String>(
      'id',
      actualTableName,
      false,
      type: DriftSqlType.string,
      defaultConstraints: (ctx) {
        ctx.buffer.write(' GENERATED ALWAYS AS (');
        FunctionCallExpression('combine_uuids', uniqueColumns).writeInto(ctx);
        ctx.buffer.write(') STORED');
      },
    );
  }

  @override
  List<GeneratedColumn<Object>> get $columns => [
        _uuid,
        ..._inner.$columns.where((e) => e.name != 'id'),
      ];

  @override
  Set<Column<Object>> get primaryKey => {_uuid};

  @override
  String get actualTableName => _inner.actualTableName;

  @override
  TableInfo<Table, dynamic> createAlias(String alias) {
    throw UnsupportedError('Only used for CREATE TABLE statements');
  }

  @override
  FutureOr map(Map<String, dynamic> data, {String? tablePrefix}) {
    throw UnsupportedError('Only used for CREATE TABLE statements');
  }
}

You could use that in onCreate:

onCreate: (m) async {
  for (final entity in allSchemaEntities) {
    if (entity is TableInfo && entity is! VirtualTableInfo) {
      await m.create(_WithCombinedUuid(attachedDatabase, entity));
    } else {
      await m.create(entity);
    }
  }
}

[johannesstricker]

Thank you so much. This looks interesting and might be useful for other use-cases as well. I think I haven't fully understood how the pieces fit together though. Are there some more examples or docs on this, that I could look up?

Where does the combine_uuids call come from in your example? Is that also an SQLite function? Maybe you can have a look at my comment above, where I again tried to explain what I'm trying to achieve exactly.

[johannesstricker]

Another way to solve this might be to introduce some kind of lifecycle hooks, e.g. a beforeUpdate or beforeInsert on tables, where I could manually set the id? Is there a way to create such hooks?

[simolus3]

Are there some more examples or docs on this, that I could look up?

Not really except from this and the dartdocs. This is a fairly advanced concept touching drift internals.

Where does the combine_uuids call come from in your example

Yeah sorry I should have mentioned that - the idea is that you'd create a function like that and then rely on that to implement most of the logic because it can be called from SQL (such as through a GENERATED AS constraint on a column).

[johannesstricker]

Thanks! I guess I'll have to go back to the drawing board and see how your answers can help me achieve what I want 👍

By the way: thank you for the awesome work on this project and for the quick replies here!