updateOne/updateMany not working for v0.12.1

[mhmzdev]

Package Version

stormberry: 0.12.1

Error logs

ERROR - 2023-03-07 16:48:35.668546
Asynchronous error
PostgreSQLSeverity.error 42883: operator does not exist: bigint = text Hint: No operator matches the given name and argument types. You might need to add explicit type casts. 
package:postgres/src/connection.dart 514:18      _PostgreSQLExecutionContextMixin._query
package:postgres/src/connection.dart 475:7       _PostgreSQLExecutionContextMixin.query
package:stormberry/src/core/database.dart 91:34  Database.query

Error produced at

The statement with values.add(r.id), it doesn't actually giving the id in int type. I've print out the runTimeType it was String.

If I try to parse it via int.parse() or int.tryParse() it gives the radix exception i.e. the signed and unsigned format of integers.

Same issues for any kind of call I make here, any function involving id as int will cause trouble.

    @override
  Future<void> update(List<UserUpdateRequest> requests) async {
    if (requests.isEmpty) return;
    var values = QueryValues();
    await db.query(
      'UPDATE "users"\n'
      'SET "first_name" = COALESCE(UPDATED."first_name", "users"."first_name"), "followers" = COALESCE(UPDATED."followers", "users"."followers"), "following" = COALESCE(UPDATED."following", "users"."following"), "posts" = COALESCE(UPDATED."posts", "users"."posts"), "last_name" = COALESCE(UPDATED."last_name", "users"."last_name"), "username" = COALESCE(UPDATED."username", "users"."username"), "email" = COALESCE(UPDATED."email", "users"."email"), "password" = COALESCE(UPDATED."password", "users"."password"), "bio" = COALESCE(UPDATED."bio", "users"."bio"), "birthday" = COALESCE(UPDATED."birthday", "users"."birthday"), "image_url" = COALESCE(UPDATED."image_url", "users"."image_url"), "cover_url" = COALESCE(UPDATED."cover_url", "users"."cover_url")\n'
     --> 'FROM ( VALUES ${requests.map((r) => '( ${values.add(r.id)}:int8, ${values.add(r.firstName)}:text, ${values.add(r.followers)}:_int8, ${values.add(r.following)}:_int8, ${values.add(r.posts)}:_int8, ${values.add(r.lastName)}:text, ${values.add(r.username)}:text, ${values.add(r.email)}:text, ${values.add(r.password)}:text, ${values.add(r.bio)}:text, ${values.add(r.birthday)}:timestamp, ${values.add(r.imageURL)}:text, ${values.add(r.coverURL)}:text )').join(', ')} )\n'
      'AS UPDATED("id", "first_name", "followers", "following", "posts", "last_name", "username", "email", "password", "bio", "birthday", "image_url", "cover_url")\n'
      'WHERE "users"."id" = UPDATED."id"',
      values.values,
    );
  }

[mhmzdev]

Any updates on this, Herr?

[schultek]

I have to investigate when I got time.

[f3l1x98]

I am not 100% sure, because in my case the runtimeType was the correct one (int), but I got the same error while experimenting with this package and I fixed it by adjusting the toUpdateValue inside the UpdateGenerator and the toInsertValue inside the InsertGenerator like this:

// InsertGenerator
  String toInsertValue(NamedColumnElement c) {
      if (c.converter != null) {
        return '\${values.add(${c.converter!.toSource()}.tryEncode(r.${c.paramName}))}::${c.rawSqlType}'; // <- normal sql cast :: instead of :
      } else {
        return '\${values.add(r.${c.paramName}${c.converter != null ? ', ${c.converter!.toSource()}' : ''})}::${c.rawSqlType}'; // <- normal sql cast :: instead of :
      }
    }
// UpdateGenerator
  String toUpdateValue(NamedColumnElement c) {
      if (c.converter != null) {
        return '\${values.add(${c.converter!.toSource()}.tryEncode(r.${c.paramName}))}::${c.rawSqlType}'; // <- normal sql cast :: instead of :
      } else {
        return '\${values.add(r.${c.paramName})}::${c.rawSqlType}'; // <- normal sql cast :: instead of :
      }
    }

[mhmzdev]

These changes will be in new model.schema.g file Am i right?

But the insert function is working pretty fine for me, just the update isn't

[f3l1x98]

No my proposed changes are for the package generators which create the model.schema.dart files: https://github.com/schultek/stormberry/blob/main/lib/src/builder/generators/insert_generator.dart https://github.com/schultek/stormberry/blob/main/lib/src/builder/generators/update_generator.dart In other words they require a new release of this package.

Though I guess you could also manually adjust the generated methods in all model.schema.dart until this is fixed. That way you could at least find out if my suggestion would fix this issue,
Aka adjust all these file (my example is this https://github.com/schultek/stormberry/blob/main/example/lib/models/account.schema.dart) like this:

@override
  Future<void> update(List<AccountUpdateRequest> requests) async {
    if (requests.isEmpty) return;
    var values = QueryValues();
    await db.query(
      'UPDATE "accounts"\n'
      'SET "first_name" = COALESCE(UPDATED."first_name", "accounts"."first_name"), "last_name" = COALESCE(UPDATED."last_name", "accounts"."last_name"), "location" = COALESCE(UPDATED."location", "accounts"."location"), "company_id" = COALESCE(UPDATED."company_id", "accounts"."company_id")\n'
      // The following line needs to be adjusted (:: instead of :)
      'FROM ( VALUES ${requests.map((r) => '( ${values.add(r.id)}::int8, ${values.add(r.firstName)}::text, ${values.add(r.lastName)}::text, ${values.add(LatLngConverter().tryEncode(r.location))}::point, ${values.add(r.companyId)}::text )').join(', ')} )\n'
      'AS UPDATED("id", "first_name", "last_name", "location", "company_id")\n'
      'WHERE "accounts"."id" = UPDATED."id"',
      values.values,
    );
    await db.billingAddresses.updateMany(requests.where((r) => r.billingAddress != null).map((r) {
      return BillingAddressUpdateRequest(
          city: r.billingAddress!.city,
          postcode: r.billingAddress!.postcode,
          name: r.billingAddress!.name,
          street: r.billingAddress!.street,
          accountId: r.id);
    }).toList());
  }

Same change for the insert(...) method.

Keep in mind that this would have to be redone after each dart run build_runner build until this issue is fixed (given that this would fix your issue)!

[f3l1x98]

And concerning your working insert function:
Yes I know it was the same for me.
That is also why I said I am not 100% sure this would fix the issue because I was honestly a little bit surprised that this fixed my issue/that it worked previously (I just thought that the cast was only : not :: due to postgres package, afterall as far as I know the standard sql cast operator is ::)

[schultek]

Thanks for looking into it.

The single : comes from the postgres package, more specifically the query parameters. https://pub.dev/documentation/postgres/latest/postgres/PostgreSQLConnection/query.html

I'm also quite confused why the double :: work while the single doesn't. Maybe its a bug with the postgres package but I don't know for sure yet.

[schultek]

@mhmzdev Can you post your User model? Also can you double check that the database is properly migrated using the cli? Just to double check since I'm still searching for the issue and cannot reproduce it.

The one vs two : actually make a rather important difference but doesn't seem to be the root of the problem. Its just a workaround but not a solution.

[mhmzdev]

Sure, here's my user model, and I applied the migration command these are the logs

hamza@Muhammads-MacBook-Pro db % DB_SSL=false DB_SOCKET=false DB_NAME=postgres dart run stormberry migrate
Enter the database host address: 
Enter the database port: 
Enter the database username: 
Enter the database password: 
Database: connecting to postgres at 127.0.0.1...
Database: connected
Getting schema changes of null
=========================
NO CHANGES, ALL DONE

user.dart

import 'package:stormberry/stormberry.dart';
part 'user.schema.dart';

@Model()
abstract class User {
  @PrimaryKey()
  @AutoIncrement()
  int get id;

  String get firstName;
  String get lastName;
  String get username;
  String get email;
  String get password;
  String? get bio;
  DateTime? get birthday;
  String get imageURL;
  String get coverURL;
  List<int> get followers;
  List<int> get following;
  List<int> get posts;
}

user.scheme.g.dart

part of 'user.dart';

extension UserRepositories on Database {
  UserRepository get users => UserRepository._(this);
}

abstract class UserRepository
    implements
        ModelRepository,
        KeyedModelRepositoryInsert<UserInsertRequest>,
        ModelRepositoryUpdate<UserUpdateRequest>,
        ModelRepositoryDelete<int> {
  factory UserRepository._(Database db) = _UserRepository;

  Future<User?> queryUser(int id);
  Future<List<User>> queryUsers([QueryParams? params]);
}

class _UserRepository extends BaseRepository
    with
        KeyedRepositoryInsertMixin<UserInsertRequest>,
        RepositoryUpdateMixin<UserUpdateRequest>,
        RepositoryDeleteMixin<int>
    implements UserRepository {
  _UserRepository(super.db) : super(tableName: 'users', keyName: 'id');

  @override
  Future<User?> queryUser(int id) {
    return queryOne(id, UserQueryable());
  }

  @override
  Future<List<User>> queryUsers([QueryParams? params]) {
    return queryMany(UserQueryable(), params);
  }

  @override
  Future<List<int>> insert(List<UserInsertRequest> requests) async {
    if (requests.isEmpty) return [];
    var values = QueryValues();
    var rows = await db.query(
      'INSERT INTO "users" ( "first_name", "followers", "following", "posts", "last_name", "username", "email", "password", "bio", "birthday", "image_url", "cover_url" )\n'
      'VALUES ${requests.map((r) => '( ${values.add(r.firstName)}:text, ${values.add(r.followers)}:_int8, ${values.add(r.following)}:_int8, ${values.add(r.posts)}:_int8, ${values.add(r.lastName)}:text, ${values.add(r.username)}:text, ${values.add(r.email)}:text, ${values.add(r.password)}:text, ${values.add(r.bio)}:text, ${values.add(r.birthday)}:timestamp, ${values.add(r.imageURL)}:text, ${values.add(r.coverURL)}:text )').join(', ')}\n'
      'RETURNING "id"',
      values.values,
    );
    var result = rows.map<int>((r) => TextEncoder.i.decode(r.toColumnMap()['id'])).toList();

    return result;
  }

  @override
  Future<void> update(List<UserUpdateRequest> requests) async {
    if (requests.isEmpty) return;
    var values = QueryValues();
    await db.query(
      'UPDATE "users"\n'
      'SET "first_name" = COALESCE(UPDATED."first_name", "users"."first_name"), "followers" = COALESCE(UPDATED."followers", "users"."followers"), "following" = COALESCE(UPDATED."following", "users"."following"), "posts" = COALESCE(UPDATED."posts", "users"."posts"), "last_name" = COALESCE(UPDATED."last_name", "users"."last_name"), "username" = COALESCE(UPDATED."username", "users"."username"), "email" = COALESCE(UPDATED."email", "users"."email"), "password" = COALESCE(UPDATED."password", "users"."password"), "bio" = COALESCE(UPDATED."bio", "users"."bio"), "birthday" = COALESCE(UPDATED."birthday", "users"."birthday"), "image_url" = COALESCE(UPDATED."image_url", "users"."image_url"), "cover_url" = COALESCE(UPDATED."cover_url", "users"."cover_url")\n'
      'FROM ( VALUES ${requests.map((r) => '( ${values.add(r.id)}:int8, ${values.add(r.firstName)}:text, ${values.add(r.followers)}:_int8, ${values.add(r.following)}:_int8, ${values.add(r.posts)}:_int8, ${values.add(r.lastName)}:text, ${values.add(r.username)}:text, ${values.add(r.email)}:text, ${values.add(r.password)}:text, ${values.add(r.bio)}:text, ${values.add(r.birthday)}:timestamp, ${values.add(r.imageURL)}:text, ${values.add(r.coverURL)}:text )').join(', ')} )\n'
      'AS UPDATED("id", "first_name", "followers", "following", "posts", "last_name", "username", "email", "password", "bio", "birthday", "image_url", "cover_url")\n'
      'WHERE "users"."id" = UPDATED."id"',
      values.values,
    );
  }
}

class UserInsertRequest {
  UserInsertRequest({
    required this.firstName,
    required this.followers,
    required this.following,
    required this.posts,
    required this.lastName,
    required this.username,
    required this.email,
    required this.password,
    this.bio,
    this.birthday,
    required this.imageURL,
    required this.coverURL,
  });

  String firstName;
  List<int> followers;
  List<int> following;
  List<int> posts;
  String lastName;
  String username;
  String email;
  String password;
  String? bio;
  DateTime? birthday;
  String imageURL;
  String coverURL;
}

class UserUpdateRequest {
  UserUpdateRequest({
    required this.id,
    this.firstName,
    this.followers,
    this.following,
    this.posts,
    this.lastName,
    this.username,
    this.email,
    this.password,
    this.bio,
    this.birthday,
    this.imageURL,
    this.coverURL,
  });

  int id;
  String? firstName;
  List<int>? followers;
  List<int>? following;
  List<int>? posts;
  String? lastName;
  String? username;
  String? email;
  String? password;
  String? bio;
  DateTime? birthday;
  String? imageURL;
  String? coverURL;
}

class UserQueryable extends KeyedViewQueryable<User, int> {
  @override
  String get keyName => 'id';

  @override
  String encodeKey(int key) => TextEncoder.i.encode(key);

  @override
  String get query => 'SELECT "users".*'
      'FROM "users"';

  @override
  String get tableAlias => 'users';

  @override
  User decode(TypedMap map) => UserView(
      id: map.get('id'),
      firstName: map.get('first_name'),
      followers: map.getListOpt('followers') ?? const [],
      following: map.getListOpt('following') ?? const [],
      posts: map.getListOpt('posts') ?? const [],
      lastName: map.get('last_name'),
      username: map.get('username'),
      email: map.get('email'),
      password: map.get('password'),
      bio: map.getOpt('bio'),
      birthday: map.getOpt('birthday'),
      imageURL: map.get('image_url'),
      coverURL: map.get('cover_url'));
}

class UserView with User {
  UserView({
    required this.id,
    required this.firstName,
    required this.followers,
    required this.following,
    required this.posts,
    required this.lastName,
    required this.username,
    required this.email,
    required this.password,
    this.bio,
    this.birthday,
    required this.imageURL,
    required this.coverURL,
  });

  @override
  final int id;
  @override
  final String firstName;
  @override
  final List<int> followers;
  @override
  final List<int> following;
  @override
  final List<int> posts;
  @override
  final String lastName;
  @override
  final String username;
  @override
  final String email;
  @override
  final String password;
  @override
  final String? bio;
  @override
  final DateTime? birthday;
  @override
  final String imageURL;
  @override
  final String coverURL;
}

[mhmzdev]

Just an FYI, @schultek If I used :: instead of : it shows this error, for me the id is in String if I print(id.runTimeType). So, there are problems I'm facing first, the id is not in int and second the :: issue

ERROR - 2023-03-12 16:04:42.628045
PUT /api/v1/users/update
Error thrown by handler.
PostgreSQLSeverity.error 42804: COALESCE types text and bigint[] cannot be matched
package:postgres/src/connection.dart 514:18      _PostgreSQLExecutionContextMixin._query
package:postgres/src/connection.dart 475:7       _PostgreSQLExecutionContextMixin.query
package:stormberry/src/core/database.dart 91:34  Database.query

[stepanzalis]

I have the same issue.

[schultek]

@mhmzdev The id being a dart string is not the issue, since its just a sql statement containing a query parameter ('@0').

[mhmzdev]

So what should I do for now? Replace : with ::

Just to continue working?

[schultek]

Currently my best guess is to do both (':int8::int8) for complicated reasons. But I'm checking with the author if the postgres package to see if there is a better solution.

[mhmzdev]

Now the error has updated to

PostgreSQLSeverity.error 42804: COALESCE types text and bigint[] cannot be matched

If I add :int4::int8 and :_int4::_int8 for List, then the type cast is for other types e.g. timestamp

COALESCE types text and timestamp without time zone cannot be matched

Tried checking it out on google and stackoverflow, the solution was to convert entries to TEXT and then pass. But I'm not sure where this will be done to be exact

CC: @schultek

[schultek]

Its supposed to be the same type for both : and ::

[petrovyuri]

@schultek Hello everyone, I have the same problem. Is there a solution now?

[mhmzdev]

@petrovyuri as of now the work around was to put same int types where you are facing this issue e.g. :int8::int8. Just manage the types accordingly and it will work.

[petrovyuri]

@petrovyuri as of now the work around was to put same int types where you are facing this issue e.g. :int8::int8. Just manage the types accordingly and it will work.

Thank you very much for your help.

[mhmzdev]

Hey @schultek

PostgreSQLSeverity.error 42804: COALESCE types text and boolean cannot be matched

What would be the typecast for this? Although I've used boolean as per Postgres type. But still its somehow getting text to parse into bool.

The error is here:

@override
  Future<void> update(List<PostUpdateRequest> requests) async {
    if (requests.isEmpty) return;
    var values = QueryValues();
    await db.query(
      'UPDATE "posts"\n'
      'SET "uid" = COALESCE(UPDATED."uid", "posts"."uid"), "caption" = COALESCE(UPDATED."caption", "posts"."caption"), "has_image" = COALESCE(UPDATED."has_image", "posts"."has_image"), "image_url" = COALESCE(UPDATED."image_url", "posts"."image_url"), "has_video" = COALESCE(UPDATED."has_video", "posts"."has_video"), "video_url" = COALESCE(UPDATED."video_url", "posts"."video_url"), "likes" = COALESCE(UPDATED."likes", "posts"."likes"), "comments" = COALESCE(UPDATED."comments", "posts"."comments"), "created_at" = COALESCE(UPDATED."created_at", "posts"."created_at")\n'
      'FROM ( VALUES ${requests.map((r) => '( ${values.add(r.id)}:int8::int8, ${values.add(r.uid)}:int8::int8, ${values.add(r.caption)}:text, ${values.add(r.hasImage)}:boolean, ${values.add(r.imageUrl)}:text, ${values.add(r.hasVideo)}:boolean, ${values.add(r.videoUrl)}:text, ${values.add(r.likes)}:_int8::_int8, ${values.add(r.comments)}:_int8::_int8, ${values.add(r.createdAt)}::timestamp without time zone )').join(', ')} )\n'
      'AS UPDATED("id", "uid", "caption", "has_image", "image_url", "has_video", "video_url", "likes", "comments", "created_at")\n'
      'WHERE "posts"."id" = UPDATED."id"',
      values.values,
    );
  }

[schultek]

This is fixed now with v0.13.0.