[FEATURE]: SQLite: Support blob SQL type in relational queries

[Colonel-Sandvich]

Describe what you want

This issue has been made a few times and the conclusion is store json in text not blob type. However, my use case is storing UUIDs in blob instead of text to cut down on storage. (Maybe there's perf gains for column comparisons too, who knows)

Proposed solution:

If the column is of blob type instead of sending an invalid query, wrap the field in hex and return it as a string then convert that back into a Buffer in drizzle.

The first part seems easy,

field.getSQLType() === 'blob' ? sql.raw(`hex("${field.name}")`)

If this could be done better, let me know.

Git Diff

diff --git a/drizzle-orm/src/sqlite-core/dialect.ts b/drizzle-orm/src/sqlite-core/dialect.ts
index aa229d23..ec485a31 100644
--- a/drizzle-orm/src/sqlite-core/dialect.ts
+++ b/drizzle-orm/src/sqlite-core/dialect.ts
@@ -638,7 +638,13 @@ export abstract class SQLiteDialect {
                        let field = sql`json_array(${
                                sql.join(
                                        selection.map(({ field }) =>
-                                               is(field, SQLiteColumn) ? sql.identifier(field.name) : is(field, SQL.Aliased) ? field.sql : field
+                                               is(field, SQLiteColumn)
+                                                       ? field.getSQLType() === 'blob'
+                                                               ? sql.raw(`hex("${field.name}")`)
+                                                               : sql.identifier(field.name)
+                                                       : is(field, SQL.Aliased)
+                                                       ? field.sql
+                                                       : field
                                        ),
                                        sql`, `,
                                )

Converting this string back to a buffer seems less easy to me.

Maybe we can add:

  mapFromDriverValue(value: Buffer | string) {
    if (typeof value === "string") {
      value = hexStringToBuffer(value);
    }
    return value;
  }

To SQLiteBlobBuffer. And then value.toString() -> typeof value === "string" ? value : value.toString() in the other two blob classes.

That just leaves custom column types:

override mapFromDriverValue(value: T['driverParam']): T['data'] {
+  if (typeof value === 'string' && this.getSQLType() === 'blob') {
+    value = hexStringToBuffer(value);
+  }
  return typeof this.mapFrom === 'function' ? this.mapFrom(value) : value as T['data'];
}

Cons:
Extra overhead of converting hex strings to Buffer. This isn't done for BlobJSON or BlobBigInt but it is always done for custom blob columns and regular BlobBuffer even if the consumer then converts these to strings. So there's some compute wasted there.
Pros:
We can fetch blob type columns in relational queries!

Many thanks for reading ❤️