postgres

• Fix runTx throwing instead of waiting when called while another transaction is active on the same connection. Only a call from within the transaction's own callback fails fast now.

• Fix DateRange truncating pre-1970 bounds with a time component to the wrong day.
• Fix the pool reusing an idle connection without checking whether close() was already called, and Statement.dispose() throwing if called twice.
• Fix runTx deadlocking instead of failing fast when called again from within its own callback, and an SSL handshake that times out but succeeds later leaking its socket.
• SASL: compare the SCRAM server signature in constant time, and reject a server that doesn't offer SCRAM-SHA-256.
• Fix a malformed message frame with a length < 4 crashing the parser instead of failing gracefully, and remove a broken, unused bytesToMessageParser.
• Fix dollar-quoted strings with a self-overlapping tag sometimes missing their real closing delimiter, and a non-digit name after an indexed-mode marker silently producing malformed SQL instead of an error.
• Fix tsvector/tsquery throwing instead of degrading gracefully when decoded in text format.
• Use named TypeOid constants instead of raw literals in binary array encoders.

• Fix the hard-timeout fallback deadlocking the connection.
• Fix the Sql.indexed operator-tokenizing bug silently dropping the :type annotation.
• Fix port query parameter being ignored for comma-separated hosts in connection strings.
• Fix MD5 authentication hashing credentials as UTF-16 instead of UTF-8, breaking non-ASCII passwords.
• Fix list parameters silently mis-encoding null elements, and range decoding crashing on an unrecognized flag byte instead of failing gracefully.
• Fix RelationMessage.typeModifier and FieldDescription.typeSize decoding as unsigned instead of signed, and TruncateMessage (now .options) being unable to represent combined CASCADE/RESTART IDENTITY flags.
• Fix a socket leak when the SSL handshake fails (timeout, bad certificate, or the server doesn't support SSL).
• Fix dollar-quoted strings with a lettered tag (e.g. $body$...$body$) not being recognized in Sql.indexed queries.
• Fix runTx discarding the original exception when the rollback it triggers also fails, and add timeouts to the internal commit/rollback and to cancelPendingStatement() so a hung server can't deadlock the connection.
• Fix Pool.close() potentially missing a connection that was still being created, the pool's connectTimeout budget being applied twice, Pool.prepare() keeping a broken connection in circulation after a failure, and per-call onOpen closures silently defeating connection reuse.
• Fix text-format BC dates/timestamps encoding one year off and being unable to decode at all (DateTime.parse doesn't understand Postgres's BC suffix).
• Fix a pool connection leak: a connect attempt that timed out kept running in the background, and its socket was never closed if it succeeded afterwards.
• Fix text-format list/array parameters not supporting DateTime elements (e.g. an untyped List<DateTime> bind parameter), throwing Could not infer array type instead of encoding a Postgres array literal.
• Fix Pool.close() potentially returning before a connect attempt that timed out (but was still resolving in the background) had actually finished closing.

• Fix an opaque Bad state: StreamSink is closed crash when the socket closes in the narrow window between connecting and sending the first message (e.g. StartupMessage, a bound statement, or a simple query); this now surfaces as a catchable PgException.
• Fix connect() leaking the socket and its listener when startup (or onOpen) fails instead of closing the partially-opened connection.
• Fix connect() failures (e.g. wrong password, SSL/auth errors) reporting a stack trace made up of internal frames only; the caller's stack is now attached so the trace points back to the code that called connect()/Connection.open().
• Fix the same internal-frames-only stack trace issue for a failed LISTEN (Channels[channel]): the trace now points back to the code that subscribed to the channel.
• Fix a background UNLISTEN (sent when the last listener on a channel cancels) throwing when it raced a concurrent close(); this is now treated as a no-op instead of failing whatever cancelled the subscription.
• Fix a one-shot query being split across two transactions: parsing had its own Sync-terminated exchange, so a transaction-mode pooler could hand the connection to another client between the parse and the bind (26000/42P05). Parse, bind and execute now travel in one exchange under the unnamed statement.
• A failed one-shot query no longer sends a redundant Close for the unnamed statement it never named; failure and success now cost the same single exchange.
• Fix port connection-string query parameter being ignored whenever a host was already present in the URI (e.g. postgresql://host/db?port=9876).
• Fix pooled connections being reused across requests with different typeRegistry or securityContext settings.
• Fix Channels.notify() permanently failing after one transient error; a failed attempt to prepare the notify statement is no longer cached.
• Fix a malformed numeric field in a server error message, or a malformed SCRAM iteration count, crashing the connection instead of surfacing as a PgException.
• Remove an unconditional print() warning for the deprecated read type-substitution alias.
• Reduce date/timestamp binary encode and decode overhead by reusing the cached Y2K epoch instead of constructing a new DateTime on every call.
• Fix logical replication tuple values arriving undecoded (raw bytes) with typeOid always null; the live decode path now resolves types and decodes binary columns.
• Fix binary range decoding inferring a bound's byte length from the rest of the buffer instead of validating the explicit length prefix Postgres sends.
• TypeRegistry.decode() now returns FutureOr<Object?> instead of Future<Object?>, avoiding a Future allocation per column for the common synchronous codec case; this removes real overhead from the row-decoding hot path.
• Fix connectTimeout/queryTimeout: Duration.zero (or negative) meaning "wait forever" instead of failing fast; such values are now rejected at settings construction, matching the connection-string parser's existing validation.
• Connecting without a password now fails immediately with a clear PgException instead of silently authenticating with an empty one; also fixes a pending-operation error (e.g. this one) escaping as an unhandled exception instead of a catchable connection failure.

• Fix socket/listener leaks in cancelAll() and cancelPendingStatement(), and add a hard timeout fallback so a stuck query can't hang forever even if cancellation fails.
• Fix Pool.prepare() hanging when a connection can't be acquired, Pool.close() returning before in-use connections finish closing, and pooled connects ignoring time already spent waiting for a slot.
• Fix pooled connections not inheriting securityContext from pool-level settings.
• Fix connection-string parsing of bracketed IPv6 hosts, and accept the standard libpq sslmode=prefer/allow and replication=on/off/yes/no/1/0 values.
• Fix TypedValue(Type.unspecified, ...) silently discarding a parameter type known from a :type annotation or prepared statement.
• Fix Sql.indexed mis-tokenizing the @>, <@, @?, @@ operators as auto-incrementing variables.
• Fix Severity parsing to prefer PostgreSQL's non-localized error-severity field.
• Fix logical replication tuple decoding: unknown column type bytes no longer crash the stream, and binary-format columns are no longer mis-decoded as text.
• Fix off-by-one in DatabaseInfo column lookup causing a RangeError instead of returning null.
• Fix LSN.operator- performing addition instead of subtraction.
• Fix Range.flag misclassifying a valid single-point inclusive range (e.g. [x,x]) as empty.
• Fix numeric Infinity/-Infinity values silently decoding as 0.

• 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.