Postgres Prepared

Almost all simple DB problems are resolvable by ORM, but in some cases DB architects produces a complex multiline strings, uncoverable by simple ORM logic. So usage of raw sql enters dart arena. In dart, backend written with extensive usage of raw-sql becomes unprotected against mistakes in syntax, occasional raw-sql changes, out-of-sync with DB, parameters types mismatches etc. Integrated tests deals with most of such problems, but scenarios can vary and test can have same kind of problems.

That is why @Prepared was created.

It allows to automatize SQL syntax checking of all requests and offers basic type safety for db-repository users.

postgres_prepared builder builds prepared statements and validates params at runtime.

@Prepared annotation

Wrap your sql with @Prepared class and specify input parameters. Class name should be unique in project.

Example

@Prepared(
  '''
      WITH inserted_account AS (
          INSERT INTO accounts(info, enabled)
          VALUES(
              @data::jsonb,
              @enabled
          )
          RETURNING *
      ),
      roles AS (
          INSERT INTO account_roles(account_id, role)
          SELECT id, UNNEST(@roles::VARCHAR[])
          FROM inserted_account
          RETURNING *
      )
          SELECT *
          FROM inserted_account
  ''',
  substitutionValues: {
    'data': ApiModelUser,
    'roles': Set<UserRole>,
    'enabled': Nullable<bool>,
  },
)
class AccountsAdd {}

or in case of no parameters:

@Prepared('''
  SELECT *
  FROM  service
  WHERE deleted = FALSE
''')
class SelectServiceQuery {}

Generate:

dart run build_runner build --delete-conflicting-outputs

or watch:

dart run build_runner watch --delete-conflicting-outputs

Your generated class will be accessible using PreparedStatement.accountsAdd method.

Usage of prepared statement:

final result = await pool.preparedQuery(
  PreparedStatement.accountsAdd(
        data: ApiModelUser(
          email: 'x@y.z',
          firstName: 'John',
          lastName: 'Smith',
          phoneNumber: '+79115550000',
        ),
        roles: {UserRole.manager, UserRole.owner},
      ),
);

Nullable type

Optional substitution variable can be declared as Nullable.

{
  'userId': Nullable<ApiUuid>
}

Support for custom substitution types

Custom type must be added to converters. Example:

  PgConverter.addConverter((value) {
    if (value is api.ApiModelUser) {
      return PgValue((value).toJson());
    } else if (value is ApiUuid) {
      return PgValue((value).encodeToString());
    }
    
    return null;
  });

Custom type must be converted to one of supported types enumerated in postgres/src/types.dart

For testing purposes two additional facilities must be implemented in test file. Value type used in PgSession.prepare must be mapped to one of supported postgres/src/types.dart

  PgConverter.addType((cls) {
    switch (cls) {
      case ApiModelUser:
        return PgDataType.jsonb;
      case ApiUuid:
        return PgDataType.uuid;
      default:
        return null;
    }
  });

Integration test

(See prepare_test.dart)

1. Setup DB.

2. Write integration test file. In integration test create db connection and loop prepare on PreparedQueryEnum.values enum. Every created PreparedStatement will have it's enum value. Mistakes and sql de-syncs will be caught by postgres engine.

for (final what in PreparedQueryEnum.values) {
  final statement = PreparedStatement.withDefaults(what);

  final converter =
      PgConverter(statement.query, statement.substitutionValues);

  test(
      'SQL: ${what.name}',
      () => expectLater(
            connection.prepare(
              Sql(converter.sql, types: converter.types),
            ),
            completion(isA<Statement>()),
          ));
}

Expected result: