Custom data types topic

While package:typed_sql has a limited number of data-types that can be used in columns when defining tables in a database schema, it is possible to create custom types that serialize to a database column. This is done by creating a class that implements CustomDataType<T>, where T is one of:

• bool,
• int,
• double,
• String,
• DateTime, or,
• Uint8List.

Note

A custom types does not give rise to a User Defined Type (UDT) in SQL. Implementations of CustomDataType serves exclusively to facilitate convenient serialization and deserialization of custom types.

Implementing a CustomDataType

Implemeting a CustomDataType<T> is straight forward, you pick a T which must be one of the existing columns types supported by package:typed_sql. Then you write a class that implements CustomDataType<T> and can be deserialized from T using a fromDatabase factory constructor.

Below we have an example of how to implement a Color as a custom type that can be stored in a table with package:typed_sql.

@immutable
final class Color implements CustomDataType<int> {
  final int red;
  final int green;
  final int blue;

  Color(this.red, this.green, this.blue);
  Color.red() : this(255, 0, 0);
  Color.green() : this(0, 255, 0);
  Color.blue() : this(0, 0, 255);

  /// Factory constructor `fromDatabase(T value)` is required by code-generator!
  factory Color.fromDatabase(int value) => Color(
        (value >> 16) & 0xFF,
        (value >> 8) & 0xFF,
        value & 0xFF,
      );

  /// `toDatabase` serialization method is also required!
  @override
  int toDatabase() => (red << 16) | (green << 8) | blue;
}

The Color class above implements CustomDataType<int>, which means it is stored as an int in the database. Thus, the toDatabase method must return an int, and the fromDatabase constructor must take an int.

Note

The fromDatabase constructor must be defined, and it must be a factory constructor taking a single parameter of type T.

You may implement additional methods on your custom type. For example, it's often useful to implement == and hashCode for immutable data types, for details see Effective Dart.

Tip

You custom type is not required to be immutable, but using an immutable type is strongly recommended. The analyzer can help with you this if you use the @immutable annotation from package:meta.

Finally, it's important to note that if your custom type implements Comparable<T> from dart:core, then the serialized value should satisfy this ordering! As Expr<Comparable> can be used for .min and .max aggregate functions in .groupBy.

Database schema using a CustomDataType

With Color implementing CustomDataType<int> as in the example above we can use Color as column type in a database schema. The following example shows how to use Color in a database schema:

abstract final class Dealership extends Schema {
  Table<Car> get cars;
}

@PrimaryKey(['id'])
abstract final class Car extends Row {
  @AutoIncrement()
  int get id;

  String get model;

  @Unique()
  String get licensePlate;

  // We can use our custom type as column type
  Color get color;
}

The custom type Color does not need be define in the same library as the database schema.

Inserting a row with a CustomDataType

We can insert a row into the cars table with a Color as follows:

await db.cars
    .insert(
      model: toExpr('Beetle'),
      licensePlate: toExpr('ABC-001'),
      color: Color.red().asExpr,
    )
    .execute();

To insert a Color we must constructor an Expr<Color> object wrapping the value we want to insert. We can obtain such an object using the .asExpr extension method. This extension method will be created by the code-generator when it sees a field with the type Color.

Fetching a rows with a CustomDataType

We can fetch rows from the cars table with a Color as follows:

final List<Car> cars = await db.cars.fetch();

This gives us a list of Car objects, where the color field is deserialized using Color.fromDatabase. We can also use .select to fetch only a subset of fields from the database. The following example shows how to fetch distinct model and color combinations:

final List<(String, Color)> modelAndColor = await db.cars
    .select((car) => (
          car.model,
          car.color,
        ))
    .distinct()
    .fetch();

Expressions with a Expr<CustomDataType>

In the previous section we saw that we can use Car.color as any other field. When using extension methods like .select or .where we can access Expr<Color> using the .color property on Expr<Car>. However, since the Color type is purely a Dart concept, package:typed_sql doesn't have many extension methods.

The only extension method on Expr<Color> is asEncoded(), which casts to the underlying serialized expression type. Thus, asEncoded() on Expr<Color> will return an Expr<int>. We can use this to make our own extension methods for Expr<Color> as follows:

extension ColorExprExt on Expr<Color> {
  // We know black is encoded as zero
  Expr<bool> get isBlack => asEncoded().equalsValue(0);

  // We can make `.equals` and `.equalsValue` for `Color` if we want
  Expr<bool> equals(Expr<Color> other) => asEncoded().equals(other.asEncoded());
  Expr<bool> equalsValue(Color other) => equals(other.asExpr);

  // We can make our own utility methods too
  Expr<bool> get isRed => equalsValue(Color.red());
  Expr<bool> get isGreen => equalsValue(Color.green());
  Expr<bool> get isBlue => equalsValue(Color.blue());
}

These extension methods operate on the serialized Color, as serialized by Color.toDatabase. Implementing a .equals might not make sense, if your custom type doesn't have a canonical encoding. When writing queries we can use these extension methods in methods like

If we loaded the following rows into the database:

final initialCars = [
  (model: 'Beetle', licensePlate: 'ABC-123', color: Color.red()),
  (model: 'Cooper', licensePlate: 'DEF-456', color: Color.blue()),
  (model: 'Beetle', licensePlate: 'GHI-789', color: Color.blue()),
];

Then we could query for blue cars as follows:

final modelsAndLicense = await db.cars
    .where((car) => car.color.isBlue)
    .select((car) => (
          car.model,
          car.licensePlate,
        ))
    .fetch();

check(modelsAndLicense).unorderedEquals([
  ('Beetle', 'GHI-789'),
  ('Cooper', 'DEF-456'),
]);

Classes

CustomDataType<T extends Object?>

Interface to be implemented by custom types that can be stored in a Row for automatic (de)-serialization.