PgOutput2Json.Sqlite
2.1.0
dotnet add package PgOutput2Json.Sqlite --version 2.1.0
NuGet\Install-Package PgOutput2Json.Sqlite -Version 2.1.0
<PackageReference Include="PgOutput2Json.Sqlite" Version="2.1.0" />
<PackageVersion Include="PgOutput2Json.Sqlite" Version="2.1.0" />
<PackageReference Include="PgOutput2Json.Sqlite" />
paket add PgOutput2Json.Sqlite --version 2.1.0
#r "nuget: PgOutput2Json.Sqlite, 2.1.0"
#:package PgOutput2Json.Sqlite@2.1.0
#addin nuget:?package=PgOutput2Json.Sqlite&version=2.1.0
#tool nuget:?package=PgOutput2Json.Sqlite&version=2.1.0
PgOutput2Json
PgOutput2Json is a .NET library that uses PostgreSQL logical replication to stream JSON-encoded change events from your database to a .NET application or a message broker like RabbitMQ, Kafka, or Redis.
π Documentation: pg2json.com
π Use Cases
Use PgOutput2Json when you need to react to DB changes in near real-time:
- π Background tasks triggered by inserts/updates
- β‘ Low-latency application cache updates
- π Asynchronous logging (to file or another DB)
- π Real-time data aggregation
- π οΈ Lightweight ETL for DWH solutions
All with minimal latency β events are dispatched shortly after a transaction is committed, though large transactions may introduce additional delay due to processing and transmission overhead.
π Supported Outputs
- β .NET application via a simple delegate handler
- β Kafka (used by PgHook.Kafka)
- β RabbitMQ (Streams + Classic Queues - used by PgHook.RabbitMqStreams)
- β Redis (Streams + Pub/Sub Channels - used by PgHook.Redis)
- β SQLite (used by PgFreshCache)
- β MongoDB
- β Amazon Kinesis
- β Amazon DynamoDB
- β ClickHouse
- β Azure Event Hubs
- β Webhooks (used by PgHook)
Plug-and-play adapters handle the heavy lifting β or handle messages directly in your app for maximum control.
π§ How It Works
PostgreSQL 10+ ships with a built-in logical decoding plugin called pgoutput, which emits row-level changes (INSERT/UPDATE/DELETE) over a replication slot.
PgOutput2Json:
- Connects to a replication slot via
pgoutput - Converts change events into clean JSON
- Sends them to your application or a supported message broker
No extra plugins needed β pgoutput comes with PostgreSQL by default.
PgOutput2Json needs PostgreSQL 14 or newer, and is tested with versions 14 to 18. Row filters and column lists in publications need PostgreSQL 15.
The change events JSON format:
{
"c": "U", // Change type: I (insert), U (update), D (delete), C (row of the initial data copy)
"w": 2485645760, // Transaction final LSN β part of the deduplication key
"n": 1, // Message number within the transaction β part of the deduplication key
"t": "schema.table", // Table name (if enabled in JSON options)
"k": { ... }, // Key values β included for deletes, and for updates if the key changed,
// or old row values, if the table uses REPLICA IDENTITY FULL
"r": { ... } // New row values (not present for deletes)
}
Message Key
Besides the JSON, every message has a key: the values of the table's key columns, e.g. [1]. Depending on the output, it is the message key (Kafka), the routing or partition key (RabbitMQ, Redis, Kinesis, and Kafka without partition-key columns), or the message id (Azure Event Hubs, RabbitMQ Streams).
The key columns are the replica identity columns PostgreSQL sends with the change. The replication protocol does not carry the primary key, and the library does not query the database while streaming, so:
REPLICA IDENTITY DEFAULT: the primary key, stable for all changes to a row.REPLICA IDENTITY USING INDEX: the index columns.REPLICA IDENTITY FULL: all columns, so the key changes with every update. Kafka log compaction, and routing by key, then treat each version of a row as a different row. Partition-key columns (WithPartitionKeyFields) keep the routing stable, but the Kafka message key still changes.REPLICA IDENTITY NOTHING: empty.
If the message key matters, use DEFAULT, or USING INDEX on a stable unique key. The rows of the initial data copy have the same key columns as the streamed changes.
Values
| PostgreSQL type | JSON |
|---|---|
smallint, integer, bigint, oid, numeric, real, double precision |
A number, with every digit PostgreSQL sent. NaN, Infinity and -Infinity are null, because JSON has no such numbers. |
boolean |
true or false |
bytea |
A string of hex digits, e.g. "deadbeef" |
Everything else, including json, jsonb, uuid, dates and timestamps |
A string, in PostgreSQL's text format. timestamptz is in UTC. |
| Arrays of the types above | A JSON array, nested for more dimensions. A NULL element is null. |
| Arrays of other types (enums, user-defined types) | A string with the PostgreSQL array literal, e.g. "{sad,happy}" |
A large value that an update did not change is not sent by PostgreSQL. It is written as "__TOAST__".
Compact Mode
Compact mode is off by default. Your message handler, the message brokers and the webhooks get "k" and "r" as objects with the column names, as shown above, unless you turn compact mode on.
With .WithJsonOptions(o => o.WriteMode = JsonWriteMode.Compact), "k" and "r" are arrays of values without column names, which makes the messages smaller. The column names come in a schema property:
"s": ["schema.table", ["id", 1, 23], ["name", 0, 1043, 104], ["moods", 0, 16384, -1, "_mood"], ...]
// column name, part of the key (1/0), type OID, type modifier (only if present),
// type name (only for types that are not built in)
The schema is not repeated in every message. It comes with the first row of a table after the listener connects, and again after the table changes. After a reconnect it is not always repeated for every table. Consumers must remember the last schema per table and decode rows with it, as the SQLite, MongoDB and DynamoDB adapters do. A consumer that starts reading in the middle of a stream must already know the schema, for example from its own store.
Partitioned outputs (Kafka topics with more than one partition, RabbitMQ super streams, and Azure Event Hubs with partition-key columns) spread the rows of a table across partitions, so the schema lands only in the partition of the row it comes with. Consumers must therefore share the last schema per table across all partitions, for example in a common store.
The outputs that store the rows (SQLite, MongoDB, DynamoDB, ClickHouse) work with compact rows internally, whatever WriteMode is set to. You never see that JSON, and there is nothing to configure for them.
π Delivery and Deduplication
Delivery is at-least-once. A batch of changes is confirmed to PostgreSQL only after the output has acknowledged it. After a failure or a restart, PostgreSQL replays everything that was not confirmed, and those changes are published again, in their original order.
A replay is usually one batch, but it can be more:
- A transaction that was interrupted part-way is replayed from its start.
- The position of a replication slot is persisted only at checkpoints, so after a PostgreSQL crash the slot can go back further.
Every message carries a deduplication key: the transaction final LSN ("w") plus the message number within that transaction ("n"). Consumers use it in one of two ways:
- Apply changes idempotently. Upsert and delete by key. Replayed changes arrive in order, so the result is the same.
- Keep a watermark. Remember the highest
(w, n)pair processed and skip anything at or below it. Keep one watermark per table, in each partition or stream you read: the order is guaranteed only there. On Kafka and Kinesis, a message the output rejects can arrive late and out of order; see those outputs.
The rows of the initial data copy have change type "C" and no position: "w" and "n" are 0. Upsert them by key and leave them out of the watermark. A copy that failed and started again publishes its rows again. While a table is being copied and synced, its changes are published behind the changes of the other tables, which is one more reason to keep the watermark per table.
The outputs that store data instead of forwarding messages (SQLite, MongoDB, DynamoDB) save their position with the data, and the changes replayed after a reconnect are skipped for them. .WithDeduplication(false) turns that off; it has no effect on the other outputs. The ClickHouse output saves no position: every row it writes carries the position as its version, so a replayed change changes nothing.
π§ͺ Testing
Every output is covered by integration tests, which run on each change against PostgreSQL 14 to 18.
To measure the throughput on your own machine, run the benchmark: it copies and streams rows to a null output or to ClickHouse, and reports rows per second, CPU and allocations.
π€ AI-assisted Development
For transparency: as of recently, we use AI tools (e.g. Claude Code) for some development tasks β writing and reviewing code, tests, and documentation, and analyzing issues. AI-assisted changes go through the same review and testing as everything else before they are merged into a release.
Quick Start
Configure postgresql.conf
To enable logical replication, add the following setting in your postgresql.conf:
wal_level = logical
# If needed increase the number of WAL senders, replication slots.
# The default is 10 for both.
max_wal_senders = 10
max_replication_slots = 10
Other necessary settings usually have appropriate default values for a basic setup.
Note: PostgreSQL must be restarted after modifying this setting.
Create a Replication User
Next, create a user in PostgreSQL that will be used for replication. Run the following SQL command:
CREATE USER pgoutput2json WITH
PASSWORD '_your_password_here_'
REPLICATION;
If you are connecting from a remote machine, don't forget to modify pg_hba.conf to allow non-local connections for this user. After modifying pg_hba.conf, apply the changes by either:
- Sending a SIGHUP to the PostgreSQL process
- Running
pg_ctl reload - Or executing:
SELECT pg_reload_conf();
Create a Publication
Now, connect to the target database and create a publication to specify which tables and actions should be replicated:
CREATE PUBLICATION my_publication
FOR TABLE my_table1, my_table2
WITH (publish = 'insert, update, delete');
In the example code below, we'll assume the database name is my_database.
Using SQLite
PgOutput2Json supports copying modified PostgreSQL rows to SQLite. By default, rows are copied only when they change, using logical replication.
Optionally, initial data copy can be enabled with WithInitialDataCopy(true) when configuring the builder.
The PgOutput2Json library will create the SQLite database if it does not already exist, along with any table included in logical replication. A table is created the first time a row belonging to that table is changed. If a table already exists, it is not modified, but new columns will be created automatically, if a new column is added to the source PostgreSQL table.
The output writes through a connection of its own, opened without pooling, so it neither gets nor leaves connection settings in the pool your application uses (for example PRAGMA query_only). On that connection it turns off the foreign key checks, like PostgreSQL's own logical replication: the rows arrive in the order of the table syncs and the batches, not in the order of the foreign keys, so a child row can come before its parent. Your application's connections still check them. To check them on the output's connection too, run PRAGMA foreign_keys = ON in options.PostConnectionSetup.
Triggers on the tables fire for the replicated rows too: SQLite cannot turn them off for one connection, as PostgreSQL's replication does. A trigger can tell the output's writes apart by the foreign key setting of the connection that fires it:
CREATE TRIGGER items_audit AFTER UPDATE ON items
WHEN (SELECT foreign_keys FROM pragma_foreign_keys) = 1 -- not the replication
BEGIN
INSERT INTO audit (item_id) VALUES (new.id);
END;
This tells connections with foreign key checks off from those with them on, so it works as long as your application's connections check them (the default of Microsoft.Data.Sqlite and EF Core), and you do not turn them on in PostConnectionSetup.
Batch size
Every batch of changes is one transaction. UseSqlite sets the batch size to 1,000 changes (the default of the library is 100). In our benchmark with batches of 1,000 and the database file on the server's disk, the initial copy ran at about 55,000 rows per second and the stream at about 50,000, or about 68,000 and 61,000 with options.UseWal = true (the WAL journal mode of SQLite), since a commit then waits less for the disk. With the file in memory (tmpfs), where a commit does not wait for a disk, they ran at about 85,000 and 75,000 (with or without UseWal), and at about 72,000 and 68,000 with batches of 100 (measured on a Hetzner CX33, a cost-optimized cloud server with 4 shared vCPUs, 8 GB RAM and its local disk with ext4, with PostgreSQL in Docker on the same machine). Larger batches add little. To use another size, call WithBatchSize after UseSqlite. A batch that is not full is still written after the batch wait time (100 ms by default).
Create a .NET Worker Service
Set up a new .NET Worker Service and add the following package reference:
dotnet add package PgOutput2Json.Sqlite
In your Worker.cs, use the following code to configure change propagation to SQLite:
using PgOutput2Json;
using PgOutput2Json.SQLite;
public class Worker : BackgroundService
{
private readonly ILoggerFactory _loggerFactory;
public Worker(ILoggerFactory loggerFactory)
{
_loggerFactory = loggerFactory;
}
protected override async Task ExecuteAsync(CancellationToken stoppingToken)
{
// This code assumes PostgreSQL is running on localhost
using var pgOutput2Json = PgOutput2JsonBuilder.Create()
.WithLoggerFactory(_loggerFactory)
.WithPgConnectionString("server=localhost;database=my_database;username=pgoutput2json;password=_your_password_here_")
.WithPgPublications("my_publication")
.UseSqlite(options =>
{
options.ConnectionStringBuilder.DataSource = "my_database.s3db";
})
.Build();
await pgOutput2Json.StartAsync(stoppingToken);
}
}
Note: This example uses a temporary replication slot, meaning it wonβt capture changes made while the worker was stopped.
Working with Permanent Replication Slots
If you want to capture changes made while the worker is stopped, you need a permanent replication slot.
In the context of logical replication, a slot represents a stream of changes that can be replayed to a client in the order they were made on the origin server. Each slot streams a sequence of changes from a single database.
To create a replication slot, call the pg_create_logical_replication_slot function in the same database that holds the tables being tracked:
SELECT * FROM pg_create_logical_replication_slot('my_slot', 'pgoutput');
The first parameter is the name of the slot, that must be unique across all databases in a PostgreSQL cluster. Make sure you specify pgoutput as the second parameter - that specifies the correct logical decoding plugin.
The current position of each slot is persisted only at checkpoint, so in the case of a crash, the slot may return to an earlier log sequence number (LSN), which will then cause recent changes to be sent again when the server restarts.
Note: It is the responsibility of the receiving client to avoid the ill effects from handling the same message more than once.
β οΈ Important: Replication slots persist across crashes and know nothing about the state of their consumer(s). They will prevent removal of required resources even when there is no connection using them. This consumes storage because neither required WAL nor required rows from the system catalogs can be removed by VACUUM as long as they are required by a replication slot.
In extreme cases, this could cause the database to shut down to prevent transaction ID wraparound.
So, if a slot is no longer required, it should be dropped. To drop a replication slot, use:
SELECT * FROM pg_drop_replication_slot('my_slot');
Once the replication slot is created, to use it, simply specify the name of the slot in the PgOutput2JsonBuilder:
// ...
using var pgOutput2Json = PgOutput2JsonBuilder.Create()
.WithLoggerFactory(_loggerFactory)
.WithPgConnectionString("server=localhost;database=my_database;username=pgoutput2json;password=_your_password_here_")
.WithPgPublications("my_publication")
.WithPgReplicationSlot("my_slot") // <-- slot specified here
//...
| Product | Versions Compatible and additional computed target framework versions. |
|---|---|
| .NET | net8.0 is compatible. net8.0-android was computed. net8.0-browser was computed. net8.0-ios was computed. net8.0-maccatalyst was computed. net8.0-macos was computed. net8.0-tvos was computed. net8.0-windows was computed. net9.0 is compatible. net9.0-android was computed. net9.0-browser was computed. net9.0-ios was computed. net9.0-maccatalyst was computed. net9.0-macos was computed. net9.0-tvos was computed. net9.0-windows was computed. net10.0 is compatible. net10.0-android was computed. net10.0-browser was computed. net10.0-ios was computed. net10.0-maccatalyst was computed. net10.0-macos was computed. net10.0-tvos was computed. net10.0-windows was computed. |
-
net10.0
- Microsoft.Data.Sqlite (>= 10.0.10)
- Microsoft.Extensions.Logging.Abstractions (>= 10.0.0)
- PgOutput2Json (>= 2.1.0)
- SQLitePCLRaw.bundle_e_sqlite3 (>= 2.1.12)
-
net8.0
- Microsoft.Data.Sqlite (>= 10.0.10)
- Microsoft.Extensions.Logging.Abstractions (>= 8.0.0)
- PgOutput2Json (>= 2.1.0)
- SQLitePCLRaw.bundle_e_sqlite3 (>= 2.1.12)
-
net9.0
- Microsoft.Data.Sqlite (>= 10.0.10)
- Microsoft.Extensions.Logging.Abstractions (>= 9.0.0)
- PgOutput2Json (>= 2.1.0)
- SQLitePCLRaw.bundle_e_sqlite3 (>= 2.1.12)
NuGet packages (1)
Showing the top 1 NuGet packages that depend on PgOutput2Json.Sqlite:
| Package | Downloads |
|---|---|
|
PgFreshCache.Lite
PgFreshCache is a caching layer for PostgreSQL that mirrors selected tables into a local SQLite database using logical replication. |
GitHub repositories
This package is not used by any popular GitHub repositories.
| Version | Downloads | Last Updated |
|---|---|---|
| 2.1.0 | 0 | 11/16/2026 |
| 2.0.0 | 56 | 11/14/2026 |
| 2.0.0-rc.1 | 89 | 9/15/2026 |
| 1.5.0 | 191 | 3/29/2026 |
| 1.4.0 | 275 | 11/23/2025 |
| 1.3.0 | 231 | 10/12/2025 |
| 1.2.1 | 178 | 10/11/2025 |
| 1.2.0 | 240 | 8/17/2025 |
| 1.1.2 | 176 | 8/16/2025 |
| 1.1.1 | 248 | 8/11/2025 |
| 1.1.0 | 264 | 8/11/2025 |
| 1.0.6 | 274 | 8/10/2025 |
| 1.0.3 | 240 | 8/10/2025 |
| 1.0.2 | 251 | 8/10/2025 |
| 1.0.1 | 220 | 8/9/2025 |
| 1.0.0 | 314 | 8/5/2025 |
| 0.9.20 | 234 | 5/19/2025 |
| 0.9.19 | 242 | 5/18/2025 |
| 0.9.18 | 227 | 5/18/2025 |
| 0.9.17 | 227 | 5/18/2025 |