How to save a file to a database?

[Pingear]

Hello)
Can anyone tell me how to correctly save a file to a database using postgresql-dart?
I am creating a field in a table with type 'bytea' and saved data which is of type Uint8List.
Then I read it as Uint8List but the received bytes are different from the saved ones.
I tried the same thing with List, but the result is the same.

[isoos]

@Pingear: can you share the CREATE TABLE and the INSERT/SELECT + related code you are doing?

[Pingear]

Hello! Sure)
//==================IMAGE ENTITY=======================
class ImageEntity {
final int? id;
final String? name;
final String? description;
final Uint8List image;

ImageEntity({
this.id,
this.name,
this.description,
required this.image,
});
}
//==================CREATE TABLE IF NOT EXIST=======================
Future createTableIfNotExist() async {
final result = await _db.pgPool.query(
"SELECT to_regclass('public.images');",
);
if (result.first[0] == null) {
String createTableQuery = '''
CREATE TABLE images (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
image BYTEA NOT NULL
);
''';
await _db.pgPool.execute(createTableQuery);
}
}
//================== FETCH ONE =======================
Future<ImageEntity?> fetchOne(int id) async {
try {
final response = await _db.pgPool.query('''
SELECT id, name, description, image
FROM images
WHERE id = @id
''', substitutionValues: {
'id': id,
});
if (response.isEmpty) {
return null;
}
_logger.info('fetchOne SUCCESS! ImageEntity with PK: $id');
final row = response.first;
return ImageEntity(
id: row[0] as int?,
name: row[1] as String?,
description: row[2] as String?,
image: row[3] as Uint8List,
);
} catch (e, stackTrace) {
_logger.severe('fetchOne FAILD! ImageEntity with PK: $id', e, stackTrace);
throw DatabaseException(e.toString());
}
}
//================== INSERT ONE =======================
Future<int?> insertOne(ImageEntity imageEntity) async {
try {
final response = await _db.pgPool.query('''
INSERT INTO images (name, description, image)
VALUES (@name, @description, @image)
RETURNING id
''', substitutionValues: {
'name': imageEntity.name,
'description': imageEntity.description,
'image': imageEntity.image,
});
if (response.isEmpty || response.first.isEmpty) {
return null;
}
_logger.info('insertOne SUCCESS! ImageEntity with PK: $imageEntity');
return response[0][0] as int;
} catch (e, stackTrace) {
_logger.severe(
'insertOne FAILD! ImageEntity with PK: $imageEntity', e, stackTrace);
throw DatabaseException(e.toString());
}
}

[isoos]

@Pingear: Aside: you are using the old v2 API, I don't really recall anymore how it worked previously. However, I've created a small test that demonstrates how it should work: #289

Note: There is a :bytea type identifier after the named parameter. It seems to be important, because otherwise the encoder doesn't encode the bytes properly.

[Pingear]

Super! Thank you Very Much, adding @image:bytea to INSERT solved the problem.
//================== INSERT ONE =======================
Future<int?> insertOne(ImageEntity imageEntity) async {
try {
final response = await _db.pgPool.query('''
INSERT INTO images (name, description, image)
VALUES (@name, @description, @image:bytea)
RETURNING id
''', substitutionValues: {
'name': imageEntity.name,
'description': imageEntity.description,
'image': imageEntity.image,
});