-
Notifications
You must be signed in to change notification settings - Fork 351
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Support storing arbitrary dart types as JSONB BLOBs with configuration support within .drift files #2841
Comments
I agree that we should have For instance, if a user writes (of course, we could cheat and open an in-memory database that just does |
Agree, this does not look like a trivial problem. It likely requires architectural changes.
|
For those using import 'dart:convert';
import 'package:drift/drift.dart';
import 'package:sqlite3/sqlite3.dart' as sqlite3;
class JsonbConverter<T> implements TypeConverter<T, Uint8List> {
final T Function(Object?) _fromJson;
JsonbConverter(this._fromJson);
final sqlite3.Database _database = sqlite3.sqlite3.openInMemory();
late final sqlite3.PreparedStatement _toText =
_database.prepare('select json(?)');
late final sqlite3.PreparedStatement _toBinary =
_database.prepare('select jsonb(?)');
@override
T fromSql(Uint8List fromDb) {
final result = _toText.select([fromDb]).single;
final asText = result.values[0] as String;
return _fromJson(json.decode(asText));
}
@override
Uint8List toSql(T value) {
final asText = json.encode(value);
final result = _toBinary.select([asText]).single;
return result.values[0] as Uint8List;
}
} It can be used in a column definition like this: BlobColumn myJson => blob().map(JsonbConverter((json) => MyClass.fromJson(json)))(); |
Hi @simolus3, is this feature in the roadmap or is hanged? |
You can already get pretty far with the I've tried approaching this a few times, but I always backed out due to the complexity - this feature requires us to rewrite queries based on the result type, so if you do |
Thank you so much for the fast update! 😀 |
Problem
I have the following column definition in .drift file:
It allows to store dart List as json TEXT in the sqlite database.
Sqlite 3.45.0 added support for serializing internal json parse tree (JSONB) as BLOB to avoid parsing it.
I want to be able to store the dart list as JSONB BLOB.
Suggested solution
Introduce a new
JSONB
type that could be used within .drift files so that example above is translated toJSONB
type should ensure that appropriate json functions (json()
/jsonb()
) are added to the SQL queries to translate between text and JSONB BLOB.MAPPED BY
converted should still receive TEXT payload from database because JSONB BLOB format is not intended to be used from the application side.We could also take it a step further and implement support similarly to enums:
In this case
JSONB
signifies the JSONB BLOB on-disk format andList<int>
specifies the dart field type. With such a solution we also avoid the need to write a customTypeConverter
.Other suggestions are welcome.
The text was updated successfully, but these errors were encountered: