How to store DateTime in IsoFormat without T and Z (for SQL Alchemy compatibility)

[albe-jj]

I'm building a flutter application that should be able to write and retrieve data on a database where also a python application which uses sqlAlchemy writes and read the database. Unfortunatly SQL alchemy default rendering is modified ISO-8601 rendering (i.e. ISO-8601 with the T converted to a space) https://docs.sqlalchemy.org/en/20/changelog/whatsnew_20.html which makes date handling complicated.

Is there a way to have drift database to write the date in the database as "ISO-8601 without T" but while keeping the type of the column DateTime (changing it to text will make it harder to do queries on the date, say for instance I want to do a select distinct on the Date only without time using drift and not custom queries .)

[simolus3]

The format used by drift can't be changed.

(changing it to text will make it harder to do queries on the date, say for instance I want to do a select distinct on the Date only without time using drift and not custom queries .)

As long as you're still using the date times as text option, you could use a text type with a converter using the SQL Alchemy format. The trick is to use dartCast to reinterpret the text as a date time expression - that will make the builtin date time APIs available. It's still a little more verbose than the default option, but it doesn't require any custom queries.

[simolus3]

Wait, there is actually a way. You can use custom SQL types for that:

class SqlAlchemyCompatibleDateTime implements CustomSqlType<DateTime> {
  const SqlAlchemyCompatibleDateTime();

  @override
  String mapToSqlLiteral(DateTime dartValue) {
    return "'${mapToSqlParameter(dartValue)}'";
  }

  @override
  String mapToSqlParameter(DateTime dartValue) {
    // todo: Read date time from text representation
  }

  @override
  DateTime read(Object fromSql) {
    // todo: Read date time from text representation
  }

  @override
  String sqlTypeName(GenerationContext context) {
    return 'TEXT';
  }
}

And then, in a table:

  DateTimeColumn get date => customType(const SqlAlchemyCompatibleDateTime())();

[albe-jj]

Thanks for the quick reply! The way posted above I am able to write and read from databse as desired, but the queries on DateTime columns are giving unexpected results, for example:

SELECT "datasets"."id" AS "datasets.id" FROM "datasets"
WHERE "datasets"."collected" > ? ORDER BY "datasets"."collected" 
DESC LIMIT 1000 OFFSET 0; 
with args [1701948180]

as you see the date in the query is sent in timestamp format and sql treats it as a string I believe.

Here the converter I used

  const SqlAlchemyCompatibleDateTime();

  @override
  String mapToSqlLiteral(DateTime dartValue) {
    return "'${mapToSqlParameter(dartValue)}'";
  }

  @override
  String mapToSqlParameter(DateTime dartValue) {
    // Format the DateTime to the desired string format
    return "${dartValue.toUtc().toIso8601String().replaceAll('T', ' ').replaceAll('Z', '')}";
  }

  @override
  DateTime read(Object fromSql) {
    // Assuming the date is stored in UTC and should be parsed as such
    return DateTime.parse(fromSql as String).toUtc();
  }

  @override
  String sqlTypeName(GenerationContext context) {
    return 'TEXT';
  }
}

Setting store_date_time_values_as_text: true gives the same issue.

[albe-jj]

I get a similar behavior in queries with a where condition on a column with a TypeConverter

for instance to store UUID v4 without the hyphen in the database I made a type converter

class UUIDTypeConverter extends TypeConverter<String, String> {
  const UUIDTypeConverter();

  @override
  String fromSql(String value) {
    print('converting from sql $value => ${UuidValue.withValidation(formatUuid(value)).toString()}');
    return UuidValue.withValidation(formatUuid(value)).toString();
  }

  @override
  String toSql(String value) {
    print('converting to sql $value => ${UuidValue.withValidation(value).toString().replaceAll('-', '')}');
    return UuidValue.withValidation(value).toString().replaceAll('-', '');
  }
}

But when I send a select query with a WHERE condition on the uuid column, the method toSql is not called and the uuid with hyphen is used as query argument.
Sent SELECT * FROM "scopes" WHERE "uuid" = ?; with args [3fa89f64-5717-4562-b3fc-2c963f68afa6]

[simolus3]

That might be a generator bug fixed in drift_dev version 2.14.1 - could you try upgrading and see if that fixes the issue?

[albe-jj]

I upgraded to drift: ^2.14.1 and drift_dev: ^2.14.1 but I still get the same issue.
Both using a TypeConverter and a CustomSqlType types are correctly converted when writing and retrieving data, but it does not perform any conversion on the arguments of queries.

[simolus3]

But when I send a select query with a WHERE condition on the uuid column, the method toSql is not called and the uuid with hyphen is used as query argument.

Could you show an example query for that? Drift can't apply type converters or custom types if you create Variable instances manually, but with other uses of the API this should work.

[albe-jj]

here an example that does not convert date to my custom type:

    final query = selectOnly(db.datasets)
      ..addColumns([db.datasets.id]); 

    if (startDate != null) {
      query.where(db.datasets.collected.isBiggerThanValue(startDate)); 
    }

with startDate being of type DateTime,
so I have to use a custom expression to have it working as desired:

     query.where(CustomExpression('collected > ${SqlAlchemyCompatibleDateTime().mapToSqlLiteral(startDate)}'));

Here an example with uuid which does not convert the argument of the query to my desired type:

    final query = select(scopes)..where((tbl) => tbl.uuid.equals(uuid));

so I need to convert the uuid manually

    final query = select(scopes)..where((tbl) => tbl.uuid.equals(UUIDTypeConverter().toSql(uuid)).

[North101]

Use .equalsValue(uuid) instead of equals to apply the type converter. The following functions will automatically apply the type converter:

• equalsValue
• isInValues
• isNotInValues

[simolus3]

As of 615a1d7, which will be part of the next drift version, we will apply custom types more consistently.

[albe-jj]

Thanks for the support, it worked!
Perhaps would be helpful to add a note on this in the documentation https://drift.simonbinder.eu/docs/advanced-features/type_converters/ to point out that to apply the converter on a query one needs to use equalsValue isInValues isNotInValues