Apache NiFi JDBC database integration services

How NiFi moves relational data with JDBC

The supported way to move operational tables out of a relational database and into a lakehouse, warehouse or search index is JDBC through NiFi's record-based processors, not custom scripts. For incremental pulls, QueryDatabaseTableRecord tracks a maximum-value column for each table and fetches only rows beyond the last value it stored. For writes, PutDatabaseRecord inserts, updates or upserts records into a target table. Both processors rely on a Database Connection Pooling Service for connections and on record readers and writers to translate between the database and NiFi's record model. This section explains the build decisions behind that pattern. It is not a product overview, and it assumes you already run NiFi 2 or are planning the platform around it.

The decisions that matter most come before the first processor is placed. You need to choose the maximum-value columns, and confirm that they are monotonic and indexed, because a column that is updated in place or is not always populated will silently skip rows. You also need to decide where pulls run. QueryDatabaseTableRecord is designed to be scheduled on the primary node in a cluster so that only one node polls a given table, and the state it stores determines where the next pull resumes. Plan how that state is kept, how a reset is handled, and who approves it. Size the connection pool against the database's own connection limits, and confirm with your database team how many concurrent sessions the source and target can accept from NiFi during peak windows.

The write side carries a caveat that teams often find late. PutDatabaseRecord needs a key to decide which rows to update, and if the key columns are missing, mis-specified or not unique on the target, updates can fail or affect the wrong rows. Verify the key definition for every target table in a non-production copy before go-live, and test how the flow behaves when a record does not match the schema. Confirm the exact driver version and database features your target supports against the documentation for that database and the NiFi component documentation for your NiFi version, since behavior differs between engines.

What you need before building a JDBC flow

A JDBC flow touches the NiFi runtime, the database driver, the database account and the network path between them. Each item below is a decision or check to close before the first processor is placed, because a gap in any one of them tends to surface as a failed query or a silent data gap later.

NiFi version and Java runtime

Confirm which NiFi line you run and that the hosts meet the minimum runtime in the administration guide, which lists Java 21 as the minimum. The 1.x line is no longer maintained, so a new JDBC flow should target NiFi 2 unless a migration is already planned. Check the version on every cluster node, since mixed versions in one cluster cause confusing processor behavior. Record the chosen version in the design document so later upgrades start from a known baseline.

JDBC driver jar from the vendor

Obtain the JDBC driver for your database directly from the database vendor and confirm its version is supported by that database release. Place the jar in a directory every node can read, then reference that path from the database connection pool controller service. Avoid copying drivers between nodes by hand, because a node that misses the jar will fail only when it becomes primary. Record the driver version, checksum and source so driver upgrades go through the same change control as flows.

Read-only database account

Create a dedicated account that can read only the source tables and the columns the flow needs. Grant SELECT on those objects and nothing else, with no write, DDL or administrative privileges. For incremental pulls, confirm the account can read the maximum-value column and any index that supports the filter. Store the credentials in a sensitive property of the controller service rather than in flow parameters that are visible to every editor, and rotate them through the same process you use for other service accounts.

Network path and TLS

Map the route from every NiFi node to the database host, including firewalls, load balancers and any proxy in between. Decide whether the connection must be encrypted and confirm the database server presents a certificate that the NiFi truststore trusts. Test the path from each node separately, because a node in a different subnet may fail while the primary succeeds. If you use PostgreSQL, its client authentication docs list methods such as password, certificate and GSSAPI that your security team may need to approve.

Clustered NiFi with a primary node

Incremental pulls that track state must run on one node, so confirm the NiFi cluster is formed and that one node is elected primary. Check the coordination service the cluster uses, whether embedded ZooKeeper, external ZooKeeper or Kubernetes leases, and confirm it survives a node restart. Decide which node runs primary-only processors and what happens during failover, since a new primary reads the stored state and resumes. Plan for PostgreSQL as the target if you need a standby, and confirm failover tooling separately because PostgreSQL does not detect primary failure on its own.

PostgreSQL version and support window

If PostgreSQL is the source or target, confirm the major version is still within its support window before you build against it. Check the versioning policy for the end-of-support date of your release, because a flow built on a version nearing retirement will need a database upgrade mid-project. Confirm the licence terms fit your use, since PostgreSQL is released under a permissive licence similar to BSD or MIT. Ask your DBA which upgrade method they plan, because driver and SQL behavior can shift across major versions.

How to configure the pull and write sequence

This sequence turns a relational source and target into a working NiFi pair: a pull that tracks new rows by maximum-value columns and a write that lands records through a shared connection pool. Each step closes with a check you can run before moving on.

Install the JDBC driver on every node
Place the vendor JDBC driver for your database in a directory that every NiFi node can read, then reference that directory from the Database Connection Pooling Service so the driver loads at runtime. An engineer with access to the NiFi host and the database vendor's driver package does this, and the same driver version should be installed on each cluster node. You know the step is done when the connection pool service shows as enabled in the controller services list without a driver-class error in the NiFi application log.
Create the database connection pooling service
Create a DBCPConnectionPool controller service that holds the JDBC URL, driver class name and a pool sizing setting that fits the database's connection limits. Store the username and password in NiFi parameters backed by a sensitive parameter context, not as literal values in the service, so the credentials stay out of exported flow definitions. Run the service's built-in validation or a test connection from the service configuration, and confirm it reports a valid connection before any processor references it.
Configure QueryDatabaseTableRecord with state and columns
Add QueryDatabaseTableRecord to the canvas and point it at the connection pool, the database type, the table name and the maximum-value columns, typically a monotonically increasing key or a last-modified timestamp that the source actually populates. Attach a record writer such as JSON or Avro so the output is typed records instead of raw result sets. Run the processor once against a test table, then inspect its state in the processor view and confirm the stored maximum value matches the largest value in the source table.
Schedule the pull on the primary node
Set the processor's execution to run only on the primary node so a multi-node cluster does not query the same table from several nodes and emit duplicate batches. Choose a run schedule that matches how quickly the source changes, and confirm the processor's scheduling strategy displays the primary node restriction. The check is to start the processor, let it run for a few cycles, and compare the number of FlowFiles produced with the number of new rows added to the source during that window.
Add PutDatabaseRecord with a record reader
Add PutDatabaseRecord and connect the success relationship from the pull, then set the same or a compatible connection pool, the target table, a record reader that matches the writer format from the pull, and the statement type, typically INSERT for append-only loads. Review the processor's documentation for the other statement types before choosing UPDATE, because it requires that the key fields are identified correctly in the processor configuration. Verify by sending one test FlowFile and querying the target table to confirm the row count and a sample of field values.
Route failures and confirm end-to-end counts
Connect the failure and retry relationships from PutDatabaseRecord to a visible path, such as a queue with a back-pressure threshold or a dead-letter location, so rejected records are kept and counted rather than dropped. Run the full flow for a defined test window, then compare rows in the source with rows in the target and check that the pull's stored state advanced to the newest value. Record the counts and the state value in your runbook, since they become the baseline for later troubleshooting.

Why maximum-value columns decide what gets loaded

The maximum-value column setting decides what counts as new. QueryDatabaseTableRecord stores the highest value it has seen in each configured maximum-value column as processor state, and each run asks the database only for rows above that stored point. Rows below the point are never revisited. An auto-increment key fits this model because newer rows normally receive larger values. A timestamp fits only if every writer sets it on insert and on every update. A column that rows can change in place without touching the chosen column will never trigger a reload, so those updates are silently missed. For each source table, confirm which writers set the column and whether any update path leaves it unchanged.

The main risk is late-committed rows. If a transaction takes several seconds to commit after it has been assigned a larger key or timestamp, a later row can be committed and picked up first. The slower row then sits below the stored maximum and is skipped on every later run. Clock skew creates the same gap with timestamps: when application servers stamp rows from clocks that disagree, a server running behind writes values beneath the high-water mark. Test this on your own workload by inserting rows from several writers and comparing source and target counts. Ask the application team which clock writes the column and how long transactions can stay open.

Resetting state is the other lever, and it needs a written procedure. Clearing processor state, or changing the table name, the maximum-value column list or the connection pool, makes NiFi start from the beginning or from an unexpected point, which can duplicate large volumes or skip data. Before the build starts, ask the DBA for the data type of each candidate column, whether it can be NULL (rows with NULL values need a documented decision), whether it is indexed, and whether rows are ever deleted, since this processor does not propagate deletes. Agree who may reset state, and how the team reconciles the target after a reset.

How source columns map to target records and keys

This table compares how common source column types travel through the record reader and writer, which target key PutDatabaseRecord uses for UPDATE statements, and which column QueryDatabaseTableRecord tracks as its maximum-value state. Read each row as a checkpoint to confirm in a test flow before production. The main takeaway is that type and key behavior depends on the driver and the schema, so verify it with real sample rows.

Source element	How it maps	What to confirm	Common failure
Timestamps and dates	Read as temporal values and written back through the record writer's date format	Time zone handling and precision on both the source and target column types with your driver	Shifted times or truncated fractional seconds after a round trip
Decimals and numerics	Mapped to decimal or numeric record fields that keep declared precision and scale	Precision and scale of the target column match the source, and the writer does not coerce to floating point	Rounded currency values when a column is written as a double
Nulls	Carried as null values in the record so the writer can send SQL NULL	The writer's null handling and whether the target column accepts NULL in update statements	Empty strings written in place of NULL, or required columns rejecting a null in an UPDATE
JSON columns	Typically passed as text or string fields unless your driver exposes a dedicated type	Driver support for the JSON type and whether the writer sends it as a string or a typed object	Escaped or double-encoded JSON in the target table
Binary and BLOB columns	Handled as byte arrays or encoded strings depending on the reader and writer settings	Encoding setting used in the reader and writer and the size limits of your driver	Corrupted bytes when content passes through a character encoding
Target primary key for UPDATE	PutDatabaseRecord uses the key fields you configure to build WHERE clauses for updates	Configured key fields exist in the record and uniquely identify each target row	Updates that match many rows or none, because the key is missing or not unique
Maximum-value column for incremental state	QueryDatabaseTableRecord stores the largest value seen and only fetches rows above it	The column is monotonic, indexed and non-null, and its type compares correctly as a string or number	Missed rows when late commits carry smaller values, or repeated rows after a state reset

The UPDATE key caveat in PutDatabaseRecord

When PutDatabaseRecord runs an UPDATE, the key fields you configure decide which rows change. The processor builds its WHERE clause from the fields named in its key property, so a missing key field, a key that does not match the table's primary or unique key, or a key that is not unique in practice can produce no updates at all, or updates to more rows than you intended. Because the statement type is set on the processor and the database enforces only what the table defines, a flow can run without errors while the target drifts from the source. Before you go live, confirm in the processor's component documentation in your NiFi version how each statement type (INSERT, UPDATE, UPSERT and any others it lists) uses the key fields, and whether a row that matches nothing is counted as a failure or silently skipped.

Check the target table's constraints against the statement you chose. An UPDATE keyed on a column without a unique index can match several rows, and an INSERT into a table with a unique constraint will fail for records that already exist, which pushes FlowFiles to the failure relationship. If the database offers a native upsert form, its conflict target must name the same columns as your key, or the write will not behave as the flow expects. Ask the database owner to confirm the constraints and indexes for each target table, and record the key decision alongside the flow so that a later schema change does not quietly invalidate it.

Test every statement type on a copy of the target table before any production run. Load a representative sample that includes new rows, changed rows, duplicates in the source and rows deleted upstream, then compare row counts and checksums against the expected result. Keep the copy's schema identical to production, including indexes and constraints, because a test table without the unique index will hide the exact failure you are looking for. Repeat the test whenever the statement type, key fields or the source extraction changes.

Choosing between a single node and a cluster for JDBC flows

A single NiFi node is the simplest place to prove a JDBC flow. There is one scheduler, one copy of the flow and one state store for the incremental maximum-value column, so the query runs once per schedule and duplicate pulls come only from the flow itself, such as a reset of state or a manual restart. The trade-off is that there is no failover. If the node stops, ingestion stops until someone restores it, and any backlog builds up in the source table until the node returns. A pilot is useful for validating column choices, record readers and writers, and the UPDATE key behavior before anything is scaled.

Duplicate pulls: Usually limited to state resets, manual reruns or changes to the maximum-value column.
Failover: None built in. Plan a recovery procedure and a restart window.
Connection pool: One pool with a small maximum size is normally enough for a pilot.
Promotion risk: Pilot configuration does not carry over automatically. Re-check paths, credentials and scheduling when moving to a cluster.
Recovery: Confirm how the state is stored and restored before the pilot is treated as reliable.

Before you rely on a single node, confirm with your NiFi administration guide how node-local state is persisted and backed up.

Securing database credentials and connections

A JDBC flow is only as safe as the account it logs in with and the screens that expose it. The cards below cover where credentials live, how the database accounts are scoped, how the connection is protected in transit, and who inside NiFi can see or change the settings.

Keep credentials in sensitive parameters

Store the database password as a sensitive parameter in a parameter context, then reference it from the DBCPConnectionPool controller service. Sensitive values are encrypted in the flow definition and hidden in the UI, so they do not appear in exported flow files. Check whether your team will source these values from an external parameter provider, such as a secrets manager, and confirm which provider your NiFi version supports before you commit to that design. Avoid typing the password directly into the controller service properties, where it can be copied by anyone with view rights.

Use a dedicated read-only account

Create one database user for QueryDatabaseTableRecord that holds only SELECT on the source tables, and a separate user for PutDatabaseRecord. A read-only account cannot alter source data even if a flow is misconfigured or a processor is pointed at the wrong table. Ask the database owner to confirm that the read account can also read the maximum-value columns and any metadata the processor needs to discover column types.

Validate the server certificate

Enable TLS on the JDBC connection and set the driver to verify the database server certificate against a trusted CA, not just encrypt the channel. Without validation, an attacker on the network path can present their own certificate and capture the credentials. The exact connection string parameters depend on the database and JDBC driver version, so confirm them with the driver documentation and test with a deliberately wrong certificate to prove the connection is refused.

Grant write rights only where needed

Limit the write account to INSERT and UPDATE on the specific target tables, and grant no DDL, no DELETE and no rights on unrelated schemas. PutDatabaseRecord with an UPDATE statement type needs the key columns to be correct, so give the account only the columns it writes. Review grants whenever a flow is added, and record who approved each change so the access model stays traceable.

Authenticate users to NiFi

Limit access to the NiFi UI with one of the supported authentication providers: single-user, LDAP, OpenID Connect, SAML 2.0 or X.509 client certificates. The Kerberos provider is deprecated, so do not choose it for a new deployment. Connect the provider to your identity source, confirm group membership maps to NiFi policies, and remove any default or shared accounts before the flow goes live.

Restrict views of processor state

Set NiFi policies so that only operators who need them can view processor state, connection pool settings and the controller service configuration. Viewing a pool often reveals the JDBC URL, user name and pool size, and that information helps an attacker plan access. Separate the policy to view from the policy to modify, and audit who holds each one at least quarterly.

Common JDBC failures and how to fix them

This table lists the failures teams most often hit when polling relational databases with QueryDatabaseTableRecord and PutDatabaseRecord, with the likely cause and the fix for each. Read it as a checklist when a flow runs but delivers the wrong rows, too many rows or none at all.

Failure	Likely cause	Fix
Driver class not found	The JDBC driver JAR is not on the classpath of the connection pool service, or the driver class name in the controller service does not match the JAR	Place the vendor JDBC driver JAR where the Database Connection Pooling Service can load it, set the driver class name exactly, and restart the service on every node
Connection pool exhaustion	Concurrent tasks, run schedules and long queries hold more connections than the pool or the database allows	Keep the query processor to one concurrent task, size the pool to the database limit, shorten the run schedule window, and confirm the database maximum connections setting with your DBA
State reset causing full reloads	Processor state was cleared, the processor was recreated or copied to a new process group, or the maximum-value column was not configured as expected	Restore the stored maximum-value state, avoid recreating the processor during changes, and document the initial value so a deliberate full load is never mistaken for a fault
Missed rows from late commits	A row with a higher maximum-value column commits after a newer row, so the watermark moves past it and the late row is never picked up	Use a maximum-value column set by the database at commit time where possible, or add a lag window that re-reads recent rows and deduplicates downstream
Duplicate loads after primary node change	State is per node in a cluster, and the new primary node may start from an older or empty watermark while the previous node already delivered rows	Schedule the query on the primary node only, confirm the state is cluster-scoped in your NiFi version, and make the target write idempotent with a stable key
Type conversion errors	Source column types such as timestamps, decimals or vendor-specific types do not map to the record schema or the target column type	Define an explicit Avro or JSON schema in the record reader and writer, cast problem columns in the SQL, and test with representative values including nulls
Updates that match no rows	PutDatabaseRecord uses an update key that is missing from the incoming records, has different casing or type, or is not unique in the target table	Set Update Keys to the real primary key columns, match case and type with the target table, and route zero-match results to a failure relationship for review

Questions buyers ask before building JDBC flows

What drives the cost of a NiFi database integration build?

The main cost drivers are the number of source databases and tables, how clean the schema is, which write semantics the target needs, and how much testing the flows require. Each table with a reliable maximum-value column is cheap to pull incrementally, while tables without one, or with inconsistent types and timestamps, need design work before any flow is built. Write semantics add cost when the target needs updates or upserts, because the UPDATE key must be designed and tested against real duplicates. Ask any provider for a quote that lists each source, each target, each table, the write mode per table, and the test cases that will be run. One-time costs cover design, build, testing and handover. Recurring costs cover the database side, your hosting, and any support you retain after go-live.

How long does a JDBC flow build take?

Duration depends on the same factors as cost: the number of sources and tables, schema quality, the number of write patterns, and how much validation the business requires. A single table with a stable key and an append-only pattern is a much smaller task than a set of tables that must be kept in sync with updates and deletes. Ask for a timeline that is tied to a written scope, with milestones for connection setup, incremental load design, write testing and the change window for go-live. Be wary of any timeline that is not connected to a list of tables and test cases, because it cannot be checked against the work.

What does the client need to provide for a database integration?

The work needs read access to the source tables, write access to the target only where records are written, and a dedicated database account with the least privileges that the flow needs. It also needs named schema owners who can explain column meanings, nullability, primary keys and how rows are deleted or changed. Change windows matter when the first full load or a schema change could affect production traffic. A named reviewer on your side should approve the design, the test results and the go-live plan. Confirm these items before the engagement starts, because missing schema knowledge is one of the most common reasons a build slows down.

Can our internal team build JDBC flows in NiFi without a partner?

Yes, a team with NiFi experience, database skills and a clear owner for the schema can build these flows in house. The skills that matter most are understanding how QueryDatabaseTableRecord tracks its maximum-value columns, how connection pools are configured, how primary node scheduling works in a cluster, and how PutDatabaseRecord handles inserts and updates. Teams that lack one of these areas usually benefit from a short design review or a second pair of eyes before go-live. The decision depends on how many flows you need, how often they change, and whether you have someone to own them after handover.

Should we use CDC or polling with QueryDatabaseTableRecord?

Polling with QueryDatabaseTableRecord is simpler to set up and works well when the table has a reliable column that only increases, such as an updated timestamp or a sequence, and when latency of a few schedule intervals is acceptable. Change data capture reads the database log and captures inserts, updates and deletes with the order of transactions, but it needs database-level prerequisites and more operational care. Polling generally cannot see hard deletes, and it can miss changes if the maximum-value column is not monotonic. Choose CDC when you need deletes, transaction ordering or lower latency. Confirm your database platform and the change capture options with your database team before deciding.

What does ongoing maintenance look like after handover?

After handover, the flows need someone to watch the processor status, queue depths, connection pool errors and the state of maximum-value tracking. Schema changes in the source or target are the most common cause of breakage, so they should go through the same change process as the flows. Database credentials need rotation, and NiFi and database versions need patching on their own schedules. Recurring work includes reviewing failures, testing after each change, and updating the flows when a table is added. Decide before go-live who owns each task, and whether you will keep a support retainer from DeployOpen or another provider, based on your team's capacity.

What happens if the UPDATE key is wrong?

PutDatabaseRecord uses the key fields you configure to decide which rows to update, so a key that is not unique can change more rows than intended or fail silently for some records. Teams should confirm that the key matches a unique constraint on the target table, test with duplicate and out-of-order records, and check the affected row count in a non-production copy before go-live. Ask the database owner to confirm that the key is indexed, because an unindexed key can slow large updates. Treat this as a design decision reviewed by the schema owner, not a setting to adjust after an incident.

Which official sources to read alongside this page

Start with the NiFi component documentation for QueryDatabaseTableRecord and PutDatabaseRecord, because the processor pages define the settings this design depends on: the maximum-value columns, the table and database dialect properties, the statement type and the update keys. Read them against the exact NiFi 2 release you plan to run, since property names and defaults can change between releases, and confirm any change that affects your flows in the migration guidance. Then read the System Administrator's Guide for the parts that govern where these processors run: the Java runtime you must provide, how the cluster coordinates through ZooKeeper or Kubernetes state, and how primary node scheduling behaves when a node changes role. Those answers decide whether polling from a single node is safe on your cluster, so settle them before you configure a connection pool.

Then go to the vendor documentation for your database and its JDBC driver. NiFi loads the driver jar through the database connection pool controller service, so the driver version must support your database major version and the TLS and authentication options you intend to use. Confirm which authentication method your environment requires, such as password, client certificate, Kerberos or a token-based option, and whether the driver expects those parameters in the connection URL or as separate properties. For PostgreSQL, the client authentication methods page is the natural starting point, and it should be read alongside the server's pg_hba.conf rules so the database accepts the account NiFi uses. Record the driver version and the account's grants in your change record, because a driver upgrade can change type mappings that affect the records your readers produce.

How DeployOpen scopes and delivers NiFi JDBC integrations

DeployOpen treats each Apache NiFi database integration as a fixed-scope engagement covering the source and target databases, incremental keys, credentials and the cluster that runs the flows. The work is built inside your own infrastructure and handed over with flows, runbooks and test evidence your team can operate.

How we scope

Written scope lists each source and target database, its engine and version, and the tables or views in play.
Scope records the maximum-value columns, expected update behavior and whether deletes must be captured.
Scope names the NiFi cluster, primary node placement, connection pool sizing and the credential store used.

How we build

Flows are built with QueryDatabaseTableRecord and PutDatabaseRecord, with record readers and writers chosen for each target.
Connection pools, driver JARs and controller services are configured per environment using parameters and sensitive values.
Each flow is tested against sample data for duplicates, late-arriving rows, failed batches and UPDATE key mismatches before promotion.

How we hand over

Handover includes exported flow versions, parameter definitions and a record of the maximum-value state for every query.
Runbooks describe how to reset state, rerun a window, rotate database credentials and move flows between environments.
Test evidence and a list of known limits, such as the lack of delete capture in polling, are delivered in writing.

How we support

Ongoing support, upgrades and continuous testing are available as a retainer after handover.
Support covers monitoring of query failures, backlog growth and database version changes that affect drivers.
Support scope is agreed in writing, including how database changes are reviewed before flows are updated.

Apache NiFi database integration with JDBC