Prompt and context
Node.js 24's node:sqlite exposes synchronous APIs. DatabaseSync represents one SQLite connection; createSession() tracks changes after the session starts; session.changeset() returns a binary changeset; and the target can call applyChangeset() with a conflict handler that omits, replaces, or aborts. Design an incremental synchronization protocol between an offline client and a server.
The problem is safe propagation of database changes: primary keys, old-value checks, conflict policy, idempotency, transaction boundaries, and permissions. A changeset is not executable SQL and is not automatic cross-database merging.
What the interviewer is testing
A strong answer separates source generation, reliable delivery, atomic target application, and business conflict decisions. Expect questions about Session lifetime, changeset versus patchset, SQLITE_CHANGESET_DATA and related conflict types, duplicate application, schema evolution, and how synchronous DatabaseSync work affects the Node.js event loop.
“Serialize and overwrite” loses independent target updates. Always returning SQLITE_CHANGESET_REPLACE silently overwrites business data and provides no correctness argument.
Questions to clarify first
Topology and authority
Determine whether synchronization is one-way, bidirectional, or multi-client aggregation, and which copy is authoritative for each entity. If both sides edit a row, use a version, device ID, or business event to arbitrate; SQLite's default callback is not a business rule.
Version and schema lifecycle
Confirm that source and target share schema, primary keys, and column types. Changesets depend on table structure. Migrations must complete and carry a protocol version before old changesets are replayed.
Latency and security boundary
Clarify payload size, retry window, offline duration, and sensitive fields. Binary payloads need integrity, authentication, replay protection, and encryption; they are not trusted SQL.
30-second answer
“I assign every batch an ID and a source-version cursor. The source creates a Session, commits a local transaction, and exports a changeset. The server validates schema, signature, ordering, and idempotency, then calls applyChangeset inside a target transaction. The conflict handler aborts by default and records row, column, and reason; omit or replace is allowed only by explicit business policy. A successful batch advances durable state, failures retry with bounded backoff, and duplicate batches return their prior result. Large synchronous SQLite work runs behind a worker or queue so it does not block Node's event loop.”
Step-by-step solution
Step 1: Create traceable batches
After creating a Session, include one explicit local transaction scope in a batch. Record batchId, source device, starting cursor, schema version, changeset hash, and creation time. Export the Uint8Array from session.changeset(), then close or reuse the Session so an unbounded history cannot accumulate.
Step 2: Choose changeset or patchset
A changeset includes old-value information useful for checking whether the target row still matches expectations. A patchset is smaller but exposes less old-value context. Choose from bandwidth and audit requirements; payload size alone is not a sufficient reason to lose diagnostics.
Step 3: Validate and deliver idempotently
Use TLS, device identity, and a payload signature. Validate size, hash, schema version, and origin. Store a unique record keyed by batchId and source cursor; a duplicate of an already applied batch returns the recorded result. Apply later batches in cursor order, placing missing batches in a wait queue instead of skipping them.
Step 4: Apply in a transaction and classify conflicts
Open a target transaction and call applyChangeset. The conflict handler collects table, primary key, conflict type, and target value in an audit buffer. Return SQLITE_CHANGESET_ABORT by default so the batch rolls back atomically. Only policy-approved fields may return OMIT or REPLACE, and the decision belongs in a replayable log.
Step 5: Define business merges
Field-level mergeable data can use timestamps, monotonic versions, or set union. Money, inventory, and permissions are not safe for blind merging; send them to a human queue or compensation event. Do not mutate the original changeset after a decision. Generate a child batch containing the parent batch and decision reason to preserve the audit chain.
Step 6: Handle types and integer precision
Node.js and SQLite support different type sets. If a SQLite INTEGER exceeds JavaScript's safe integer range and readBigInts is disabled, reading it can throw ERR_OUT_OF_RANGE. Standardize on BigInt, strings, or an explicit range. Use Uint8Array for BLOB values and reject arbitrary objects.
Step 7: Control synchronous cost
DatabaseSync calls execute synchronously. Long transactions, huge changesets, or frequent applyChangeset calls can block the event loop. Move synchronization to a worker, separate process, or controlled queue; cap payload and rows per batch; monitor apply duration, conflicts, rollbacks, and queue age.
High-quality sample answer
I treat each synchronization batch as an immutable event. The device creates an ID, cursor, schema version, and hash; the Session covers only a committed local transaction; and the changeset travels over an authenticated, replay-protected channel. The server validates structure and order, deduplicates by batch ID, and calls applyChangeset in a target SQLite transaction.
The conflict callback aborts by default, atomically rolling back and recording the primary key, conflict type, and target value. Inventory, money, and permissions go to business merge or compensation instead of generic replacement; only explicitly safe fields may be omitted or replaced. Durable batch state advances the cursor only after commit. Because DatabaseSync blocks synchronously, I run large batches in a worker and test duplicate delivery, missing batches, schema migration, integer overflow, and process-crash recovery.
Common mistakes
- Mistake: Overwrite the target database on every sync. → Why it fails: Independent target updates disappear. → Fix: Send a changeset, compare old values, and record the decision.
- Mistake: Return
REPLACEfor every conflict. → Why it fails: Business data is silently overwritten. → Fix: Abort by default and authorize replacement per field and invariant. - Mistake: Generate a new batch for every retry without an idempotency key. → Why it fails: Duplicate application or cursor jumps create repeated side effects. → Fix: Deduplicate with batch ID, source cursor, and application state.
- Mistake: Apply a huge changeset on the main event loop. → Why it fails: Synchronous
DatabaseSyncwork blocks HTTP and scheduled work. → Fix: Isolate it in a worker or queue and cap batch size.
Follow-up questions and responses
Follow-up 1: When would you use a patchset?
Use a patchset when bandwidth is constrained and the target has enough context, with modest conflict-audit needs. Use a changeset when old values are needed to explain conflicts or perform auditable merges, accepting a larger payload.
Follow-up 2: What if the target schema is missing a column?
Reject the batch with a compatibility error; do not let the application guess a mapping. Run the target migration, verify schema version and primary keys, then replay. If multiple versions must coexist, convert by protocol version while retaining the original payload.
Follow-up 3: How do you prevent side effects in the conflict callback?
The callback only collects structured conflict data and returns a constant decision. It does not call external services or mutate other tables. Write audit records or notifications after commit so rollback cannot leave external state inconsistent.
Follow-up 4: Why not send SQL directly?
SQL lacks source old values, schema context, and batch boundaries. Retries cannot reliably determine whether it was applied, and unauthorized statements may reach the target. SQLite-generated changesets can process each conflict at the target, making them a better controlled-sync primitive.
Follow-up 5: How do you verify integer and BLOB protocol parity?
Create cross-language fixtures for the largest safe integer, an out-of-range integer, negatives, NULL, UTF-8 text, and binary payloads. Record SQLite type, Node read/write options, and serialized representation for each, then assert bytes and values match before and after replay.