postgres

• Fix runTx silently rolling back after ROLLBACK TO SAVEPOINT recovery: clear stale _transactionException when PostgreSQL confirms a healthy transaction state.
• Fix connection permanently blocked when BEGIN fails inside runTx (stale _activeTransaction state).
• Fix connection left in undefined PostgreSQL state when ROLLBACK fails after a transaction error.
• Fix untyped Uint8List parameters being encoded as an integer array literal: they are now encoded as a bytea hex literal.

• Adding JsonbListView with isSqlNull(int index) method to check if a JSONB array has SQL or JSON null value.
• Fix double-timezone encoding bug.
• Fix statement scope bug.

• Supporting BYTEA[] built-in type.
• Fix TypedValue parameter propagation.

• Supporting multiple hosts in connection strings via comma-separated hosts or multiple host query parameters.
• Unix socket connections via host query parameter (automatically detected when path contains /).
• Supporting user/username, password, database, and port as query parameters in connection strings (override URL components).

• Upgraded SDK constraints and lints.
• New SASL authenticator (reduce dependencies that haven't been updated for a while).
• Supporting more URL-based connection-string parameters (mostly for pool).
• Optimized StackTrace capture #432 by gmpassos.

• Supporting URL-based connection-string specification in Connection.openFromUrl and Pool.withUrl. (Note: feature and supported settings is considered experimental.)

• Accept null values as part of the binary List encodings.

• Better exception message when SSL is not supported.
• Fix: typo in type specification (read -> real, also keeping read for compatibility).

Warning: This version fixes the inconsistency of 3.5.3 by:

• UniqueViolationException and ForeignKeyViolationException extends ServerException instead of PgException, and
• they no longer have public constructor (please open an issue if this is something you'd rely on).
• The exception from cancelling the statement is also extends ServerException instead of PgException.

• New typed exceptions: UniqueViolationException, ForeignKeyViolationException. #416 by hurrba
• Fix: don't reuse pool connections that are closed/closing. #417 by davidmartos96

• Fix: Pool connection timeout does not rely on package:pool's timeout, instead uses a separate Timer.

• Fix: ARRAY_AGG (and other arrays) may return [null].

• Better exception stacktraces (in some cases) using package:stack_trace.
• With that dependency, upgraded minimum SDK to 3.4.

• Fix: Don't ignore de pool resource close call when using foce. #406 by davidmartos96.

• Implemented Pool.close(force: true) (using davidmartos96's #397 as baseline).

• Fix: do not close connection after an empty statement.
• Fix: close(force: true) does not cause uncaught exception.

• close(force: true) to indicate intent to force-close pending queries and resources. #396 by davidmartos96
• Fix: multiple message processing overlap bug when SSL mode was disabled.

• Fix: explicit closing of prepared statement portals in transactions to release table locks. #393

• Fix: prevent hanging state by forwarding protocol-level parsing errors into the message stream.

• Fix: When a transaction is rolled back, do not expose the exception on rollback, rather the original exception from the transaction.

• Do not allow exceptions escape when closing broken connection. #384 by pulyaevskiy.

• Connection.info (through ConnectionInfo class) exposes read-only connection-level information, e.g. acessing access server-provided parameter status values.
• Support for binary pgoutput replication by wolframm.
• Allowing custom type codecs:
  • Codec interface is used for encoding/decoding value by type OIDs or Dart values.
  • Codec.encode and Codec.decode gets a reference to CodecContext which provides access to encoding, observed runtime parameters and the TypeRegistry.
  • EncoderFn value converter for generic Dart object -> Postgres-encoded bytes (for values where type is not specified).
  • DatabaseInfo tracks information about relations and oids (currently limited to RelationMessage caching).
• Timeout-related behaviour changes, may be breaking in some cases:
  • Preparing/executing a stamement on the main connection while in a runTx callback will throw an exception.
  • Setting timeout will try to actively cancel the current statement using a new connection.
  • ServerException may be transformed into _PgQueryCancelledException which is both PgException and TimeoutException (but no longer ServerException).
• API deprecations:
  • Deprecated TupleDataColumn.data, use .value instead (for binary protocol messages).
  • Deprecated some logical replication message parsing method.
  • Removed @internal-annotated methods from the public API of ServerException and Severity.

Removed legacy v2 APIs. These APIs were meant to be removed in 3.1.0, but were kept for a bit longer, as they didn't have any drawback to be around. However, a planned redesign of the type customization would require to touch the v2 codebase and it is not worth the effort to keep it much longer.

• Added or fixed decoders support for QueryMode.simple: double, real, timestampWithTimezone, timestampWithoutTimezone, date, numeric, json, jsonb #338 by pst9354.

• Support for tsvector and tsquery types.
• ResultRow.isSqlNull(int) returns true if the column's value was SQL NULL.
• TypedValue.isSqlNull indicates SQL NULL (vs. JSON null).

• ConnectionSettings.onOpen callback to initialize a new connection and set connection-level variables.

• Use ':' (colon) for substitution and cast operator together #309 by xvrh.

Added the following PostgreSQL builtin types:

• Geometric types line, lseg, path, polygon, box, circle
• Range types int4range, int8range, daterange, tsrange, tstzrange
• Time type time
• Array types _int2, _date, _time, _timestamp, _timestamptz, _uuid

Huge thanks for #294 by wolframm.

• Properly react to connection losses by reporting the database connection as closed.

• Allow cleartext passwords when secure connection is used. (#283 by simolus3)

• Allow passing a SecurityContext when opening postgres connections.

• Support for type char/character/bpchar.

• Fix: SSL connection problem handler.

• Using const for ConnectionSettings, SessionSettings and PoolSettings classes. (#267 by Gerrel)
• Parsing of Sql.indexed and Sql.named happens when the Connection starts to interpret it. Errors with unknown type names are thrown in this later step.

• Fix: Dispose disconnected pool Connections. (#260 by nehzata).
• Deprecated ParseMessage constructor's types argument, use typeOids instead. (As most users don't access this directly, it will be removed in 3.1.0).

• Fix: do not allow execute after closing the Connection.
• Session.runTx() supports rolling back the transaction through TxSession.rollback() (otherwise any exception has the same effect, but callers need to catch it).
• Supporting more type aliases, including serial4, serial8, integer...
• Deprecated all of v2 API, legacy fallback will be removed in next minor version (3.1.0).

New features:

• New API (better names and consistency).
• New SQL parsing and configurable query substitutions.
• Integrated connection pooling.
• A somewhat-compatible legacy API support to help migrations (will be removed in 3.1.0).

BREAKING CHANGES

The package had a partial rewrite affecting public client API and internal behaviour, keeping most of the wire protocol handling from the old version.

Users of this package must rewrite and test their application when upgrading.

Notable breaking behaviour changes:

• Simple query protocol allows sending queries to the server without awaiting on the result. The new implementation queues these request on the client instead.
• Table name OIDs are not fetched or cached, this information from the result schema is absent, also causing mappedResultsQuery to be removed from the new API.
• Queries are not cached implicitly, explicit prepared statements can be used instead.
• interval values are returned as Interval type instead of Duration.
• A newly added UndecodedBytes instances are returned when the package does not know or has not implemented the appropriate type decoding yet. Previously these values were auto-encoded to String and if that failed the Uint8List were used.
• Types, fields and parameter names may have been renamed to be more consistent or more aligned with the Dart naming guides.

Legacy compatibility layer

package:postgres/legacy.dart provides a somewhat backwards-compatible API via the PostgreSQLConnection.withV3() constructor. Many features, including the ones mentioned above are missing and/or throw UnimplementedError when called.

Migration

• You may use the legacy compatibility layer to check if your code relies on any of the above mentioned feature, rewrite if needed.
• Start using the new API, incrementally, when possible.
• For most queries, you may use Sql.named('SELECT ...') to keep the default name- and Map-based query @variable`` substitution, or you may use the raw $1` version (with 1-based indexes).
• Always write tests.

If you have any issues with migration or with the new behavior, please open an issue on the package's GitHub issue tracker or discussions.

Thanks

The rewrite happened because of many contributions (including code, comments or criticism) on the new direction and design. I'd like to call out especially to simolus3 and osaxma, who helped to push forward.

see the latest 3.0.0 (pre)release

see the latest 3.0.0 (pre)release

see the latest 3.0.0 (pre)release

• Allow encoding to be specified for connections. The setting will be used for all connection-related string conversions.
• Allow generic List type as substitution values in binary encoding (as long as the inner type matches).
• Refactor: replaced UTF8BackedString with generic encoding (not complete).
• Breaking change in package:postgres/messages.dart: default constructors made internal, parsing is done with more efficient reader.

• Improved (experimental) v3 implementation. #98 and #102 by simolus3.

• Added support for bigInt (int8) arrays. #41 by schultek.

• Updated to package:lints.
• Adding lowerCase values to AuthenticationScheme.
• Add new package:postgres/postgres_v3_experimental.dart library as a preview. It exposes the postgres client under a new API that will replace the current one in version 3.0.0 of this package. At the moment, the new library is experimental and not fully implemented. Until the actual 3.0.0 release, the new APIs might change without a breaking version.

• Connecting without a password for non-trusted users throws an exception instead of timing out #68 by osaxma.

• Use substitutionValues with useSimpleQueryProtocol #62 by osaxma

• Added Support for Streaming Replication Protocol which included the following changes:
  • Replication Mode Messages Handling. #58 by osaxma
  • Add new message types for replication. #57 by osaxma
  • Add connection configuration for Streaming Replication Protocol. #56 by osaxma
  • Raise the min sdk version to support enhanced enums. #55 by osaxma
  • Add LSN type and time conversion to and from ms-since-Y2K. #53 by osaxma
  • Fix affected rows parsing in CommandCompleteMessage. #52 by osaxma
  • Introduced new APIs to PostgreSQLConnection: addMessage to send client messages, messages stream to listen to server messages & useSimpleQueryProtocol option in query method. #51 by osaxma

• Fix crash when manually issuing a transaction statement like BEGIN without using the high-level transaction APIs. #47 by simolus3.

• Added support for boolean arrays. #41 by slightfoot.

• Added support for varchar arrays. #39 by paschalisp.

• Support for clear text passwords using a boolean parameter in connection as 'allowClearTextPassword' to activate / deactivate the feature. #20.

• Include original stacktrace when query fails. (#15 by davidmartos96)

• Support for type interval, #10.

• Fix error when passing allowReuse: null into query() #8

• Fix error when sending json data with execute() #11

• Support for type numeric / decimal (#7, #9).
• Support SASL / SCRAM-SHA-256 Authentication, #6.

• Expose ColumnDescription.typeId.