Npgsql 4.0 is out and available at nuget.org. This is a major version with significant changes, upgrade with care, consult the breaking changes section below and test well before deploying to production.
A special thanks goes out to @YohDeadfall for his many contributions and reviews. Thanks also (alphabetically) to @austindrenski, @Brar, @kspeakman, @rwasef1830, @shortspider, @StillLearnin, @uhayat for their valuable contributions.
A concentrated effort has substantially increased Npgsql performance, especially in highly concurrent, low-latency scenarios. Improvements include:
- Rewriting of the connection pool to be lock-free, since contention started to be an issue in highly concurrent, short-lived connection scenarios (#1839).
- Significant reduction of allocations through more recycling and other techniques.
- New API for generically providing parameters, avoiding boxing of value types (#1639).
- Avoiding numerous internal async calls where they weren't needed.
- ... many others
In round 16 of the TechEmpower benchmark, .NET Core/ASP.NET Core came in 7th place running with Npgsql, making it one of the fastest mainstream web stacks available - see this blog post for more info. Please let us know how the new version works for you - both positive and negative comments are welcome.
If you're interested in Npgsql performance and haven't yet seen the performance page, it's a good opportunity to check it out (it's valid also for 3.2 users).
Improved spatial support (PostGIS)
Previous versions have allowed basic usage of PostGIS's spatial types via built-in Npgsql types, which were limited in many ways. Thanks to a new plugin infrastructure, you can now use the Npgsql.NetTopologySuite plugin, which maps PostGIS types to the NetTopologySuite spatial library's types. NetTopologySuite's types are more complete, and support a variety of spatial operations and conversions you can perform after loading your spatial data from PostgreSQL.
Finally, if you prefer to use the previous Npgsql types (e.g.
PostgisPoint), these are available via the Npgsql.LegacyPostgis plugin.
Thanks to @YohDeadfall for implementing both the NetTopologySuite and GeoJSON plugins.
NodaTime date/time support
NodaTime is a powerful alternative to .NET's built-in date/time types, such as
DateTime. The built-in types are flawed in many ways: they have problematic support for timezones, don't have a date-only or time-only types, and promote problematic programming but not making the right distinctions. If your application handles dates and times in anything but the most basic way, you should seriously consider using NodaTime. To learn more read this blog post by Jon Skeet.
You can now use the new Npgsql.NodaTime to have Npgsql map PostgreSQL date/time types to NodaTime types.
Another plugin, Npgsql.Json.NET, works with Newtonsoft Json.NET to automatically serialize and deserialize PostgreSQL's
json types to your objects, providing a seamless database JSON programming experience. Instead of working with strings which you have to serialize and deserialize, Npgsql does it for you.
- Fix the binary COPY API to make it interact better with exceptions (#1646).
- Npgsql better supports working with enums and composites, even without mapping them, and better supports new types introduced via plugins (#1792).
- Better "reflection" capabilities. Continuing work from 3.2, Npgsql now exposes more information about PostgreSQL types, allowing you to dynamically reflect on columns types returned by queries, or required as parameters (#1276, #1779).
- Derive parameters for queries. You can now also use
NpgsqlCommandBuilderto dynamically understand which parameters and types are required for arbitrary queries (previously supported only for functions) (#1698, thanks @Brar!).
- Allow reading a single character from a PostgreSQL text column (#1188).
- Decimals read from PostgreSQL will now have the correct scale (#1925). Thanks @StillLearnin and @YohDeadfall.
In addition to more documentation, several blog posts are planned to explain the above in more details (to be announced on @shayrojansky).
Breaking changes from 3.2
The date/time behavior has changed in the following ways:
DateTimeis always sent as
timestampby default, regardless of its kind. You can still specify
NpgsqlDbType.TimestampTz, in which case local
DateTimegets converted to UTC before sending.
- When reading
DateTimeOffset, the machine local offset will be used. Previously a
DateTimeOffsetin UTC was returned.
- It is no longer possible to read or write
timestamp, only as
The API for binary import (COPY IN) has changed substantially in a breaking way, and code from 3.2 will not work as-is on 4.0.
You must now call
NpgsqlBinaryImporter.Complete() to save your imported data; not doing so will roll the operation back.
NpgsqlBinaryImporter.Cancel() has been removed - simply closing/disposing the importer will implicitly cancel the import. This is similar to how
TransactionScope works and is necessary to prevent accidental commit of data on exception. See #1646.
If you're using decimal/numeric numbers (not floating-point), there's a chance your data needs to be fixed (previous versions incorrectly inserted a scale larger than 28, which is the maximum allowed by .NET
decimal). If you're having trouble reading data previously inserted by Npgsql, consider running this fixup code. If your data really does contain more than 28/29 fractional digits and you need to keep that precision, see the workarounds proposed in this comment for loading these values.
- .NET Standard 1.3 is no longer supported. .NET Standard 2.0 is the lowest supported version.
- Npgsql used to use its own internal TLS/SSL due to issues with some server. As these issues have been resolved, the standard .NET SslStream is now used by default (#1482), but you can still set
Use SSL Stream=falseto keep using the internal implementation (please report why you need this, as it's likely the internal implementation will be removed in a future release).
- The reader instances returned by
NpgsqlCommand.ExecuteReader()are now recycled, to reduce memory allocations (#1649). You should not keep a reference or interact with a reader after its command has been disposed (such interaction was limited in any case).
Min Pool Sizeparameter will no longer make the pool create new connections internally - it will only have an effect on how many connections are pruned. Previously, in various points the pool would check if the current number of connections was below
Min Pool Size, and if so, automatically created new ones - this no longer happens.
- Parameter types have become more strict. Previous versions allowed to you pass arbitrary value types, such as writing CLR string to int columns, or anything that implemented IConvertible. Although some implicit conversions are still supported (e.g. long -> int, short -> int), some have been removed.
- Data type names returned from
NpgsqlDataReader.GetDataTypeName()and other APIs are now more standards-conforming (e.g.
_int4), and properly include type modifiers (e.g.
character varying(10)) (#1919).
NpgsqlParameter.SpecificTypehave been removed. See Composites and Enums for more details.
- Parameter names are no longer trimmed, set your names to the exact parameter name specified in your SQL.
- If a parameter's name isn't set, it will no longer default to Parameter1, Parameter2, etc.
- The following APIs "connection capability" APIs have been removed from NpgsqlConnection:
- The default name translator,
NpgsqlSnakeCaseNameTranslator, has been changed to handle acronyms better. Given the property name
IsJSON, the old translator algorithm would output
is_j_s_o_n, while the new outputs
is_json. To revert back to the old algorithm, create a
legacyMode: trueand pass it when calling the
- If you are reading tables as composites (#990), you will have to add the new
Load Table Compositesto your connection string.
NpgsqlConnection.GetSchema()will no longer return system tables (i.e. tables in schemas
- You may no longer have multiple streams or text readers open on a reader (this was previously supported with non-sequential readers). Accessing a new column closes any open stream or text reader.
DateTimeOffsetinstances returned for PostgreSQL
timetznow have their date set to 0001-01-02 instead of the previous 0001-01-01 (#1924).
Thank you very much to the following people who have contributed to the individual 4.0.x. releases.