Stream SQLite Changes with Turso

Build a restart-safe consumer for SQLite database changes.

Introduction

30 Second Summary

When one app keeps a second copy of its data, every edit must arrive in the right order. A restart can leave that copy behind.

In this project, you will use Turso Database's built-in change data capture on a local SQLite file. You will build a consumer that reads every change in order, saves its position and removes processed history.

What You'll Build

You will watch a restart-safe consumer keep pace with every committed database change.

By the end of this project, you'll have:

  • A captured change history where every insert, update and delete appears in order.
  • A readable transaction stream that prints each committed group as decoded JSON.
  • A restart-safe consumer that resumes from its saved watermark without missing changes.
  • Secret Mission: An optional challenge to push your skills further.

Are there any prerequisites?

You need Node.js with npm plus basic SQL knowledge.

Everything runs locally, so you do not need a Turso account.

Before We Start

Your writer and consumer need a runtime before they can read changes from a local SQLite database. This step closes that setup gap before you create any project files.

Node.js runs both scripts. npm installs the database package you use later.

A terminal gives you one place to run both tools. Turso Database works with a local file in this project. You do not need a Turso account.

In this step, get ready to:
  • Open a terminal for running project commands.
  • Confirm that Node.js is installed.
  • Confirm that npm is installed.
Open your terminal

The terminal is where you send commands to Node.js and npm. A command prompt confirms that it is waiting for your input.

  • Press Cmd+Space on macOS or the Windows key on Windows to open your search bar.
  • Search for Terminal on macOS or Windows PowerShell on Windows.
  • Press Return on macOS or Enter on Windows.

You will see a window with a command prompt. That prompt shows that your terminal is ready to accept commands.

Check Node.js and npm

Node.js runs JavaScript files on your computer. npm manages the packages those files depend on.

Before you run the checks, do you expect both tools to report a version number?

  • Ask both tools to report their installed versions by running these commands:
node --version
npm --version

What Do These Checks Prove?

  • The node --version command confirms that the Node.js runtime can answer commands.
  • The npm --version command confirms that the package manager is available.

✔️ I see two version numbers

That is the setup hurdle cleared. Both tools are responding in your terminal.

ⓧ I do not see both versions

The official Node.js installer includes npm. Installing Node.js prepares both tools at once.

A computer permission request can feel sensitive. It only authorizes this local software installation.

  • Visit the official Node.js download page.
  • Choose an LTS release.
  • Download the installer offered for your operating system.
  • Open the downloaded installer.
  • Continue through the installer using its default options.
  • Approve the computer permission request if one appears.
  • Complete the installation.

Why Reopen the Terminal?

An open terminal can hold an older command search path. Reopening it lets the new Node.js installation appear.

  • Close the terminal you opened earlier.
  • Repeat the operating system search steps above to reopen your terminal.
  • Confirm that both tools now respond by running these commands:
node --version
npm --version

What Does This Check Confirm?

  • The first command checks that your new Node.js installation is available.
  • The second command checks that the included npm installation is available.

There you go. Your terminal now shows one version number for each tool.

Still Missing a Version Number?

  • Close every open terminal window before opening a fresh one.
  • Run the Node.js installer again if it did not reach its completion screen.

Ask for help: Why are my Node.js or npm version checks failing after installation?

Your local toolchain is ready. Node.js and npm both respond without any project files or cloud resources.

Your local tools are ready. Next up, you will create the project and connect its first SQLite database.

Initialize the Database Project

Every change data capture stream needs a table whose edits can be recorded. Your project starts with that source database.

You will prepare a local Node.js workspace with Turso Database. You will create app.db with a projects table.

In this step, get ready to:
  • Create a local Node.js project folder on your Desktop.
  • Install @tursodatabase/database version 0.8.3.
  • Create an app.db database containing a listed projects table.
Prepare the local Node.js project

A package.json manifest records the packages that your project depends on. Keeping the database package at one version gives every later script the same API behavior.

  • Move to your Desktop by running this command:
cd ~/Desktop

What does this command do?

This switches your terminal to the Desktop. Your new project folder will be easy to find.

  • Create the sqlite-cdc folder by running these commands:
mkdir sqlite-cdc
cd sqlite-cdc
pwd

What do these commands do?

  • The first command creates the sqlite-cdc folder.
  • The second command makes sqlite-cdc your terminal's current location.
  • The final command prints that location as a quick check.

Your printed path should end with sqlite-cdc.

  • Create the default project manifest by running this command:
npm init --yes

What does this command do?

The npm init command creates package.json in the current folder. The --yes option accepts the default manifest values without opening a questionnaire.

You should see a message showing that package.json was written inside your sqlite-cdc folder.

The package download can take a minute while npm retrieves native files. A quiet pause during the install is normal.

  • Install the pinned database package by running this command:
npm install --save-exact @tursodatabase/database@0.8.3

Why pin this package?

The package provides the local SQLite-compatible engine that opens app.db inside your Node.js process. The exact version keeps the CDC behavior consistent throughout this project.

  • Confirm the installed package version by running this command:
npm ls @tursodatabase/database

What does this command do?

The npm ls command prints the matching installed dependency. It verifies the package recorded by your project.

You should see @tursodatabase/database@0.8.3 in the dependency tree. Good progress. Your pinned database engine is ready for local queries.

Package missing from the dependency tree?

Check that your printed path ends with sqlite-cdc. Run the install command again if the earlier download stopped before completion.

If the installation still fails, ask for help with the package installation.

Create and list the projects table

An embedded database stores its data in a local file while the database engine runs inside your Node.js process. Connecting to app.db creates that file without a cloud account.

Before you run the setup, which table name do you expect the final query to print?

  • Create the local database table by running this command:
node --input-type=module --eval '
import { connect } from "@tursodatabase/database";

// Open the local database file through Turso Database.
const db = await connect("app.db");

// Create the source table whose row changes will be captured later.
await db.exec(`
  CREATE TABLE IF NOT EXISTS projects (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    status TEXT NOT NULL
  )
`);

// Query the schema so the terminal proves that the table exists.
const tables = await db
  .prepare("SELECT name FROM sqlite_schema WHERE type = ? AND name = ?")
  .all("table", "projects");
console.log(tables);
'

What does this command do?

  • The connect() call opens the local app.db file through the installed package.
  • The table definition gives each project an ID. It also stores a name plus a status.
  • The schema query looks for the projects table. The final line prints the matching row.

You should see [ { name: 'projects' } ] in your terminal. That result proves the package opened app.db successfully.

Projects table not listed?

Check that the package import matches @tursodatabase/database. Make sure every quote plus parenthesis in the command was copied.

If the command still fails, ask for help checking the database setup.

Your local database now has the source table that every later change will target. Next, you will enable capture on the writer connection and watch those changes enter the CDC stream.

Enable Capture and Write Changes

The projects table is ready inside your local Turso Database file. The next job is to make each write leave an ordered record.

Capture belongs to the connection that performs the writes. This step enables full change data capture before that connection changes any project rows.

In this step, get ready to:
  • Enable full capture on the writer connection.
  • Write a sample sequence of project changes.
  • Confirm that data changes appear beside their commit rows.
Build the captured write path

The capture setting applies only to the connection that runs PRAGMA capture_data_changes_conn('full'). Your script therefore enables capture before its first write.

  • Add writer.js to the local project directory by pasting the code below into a new file:
async function main() {
  // Load the local database package and open the existing project database.
  const { connect } = await import("@tursodatabase/database");
  const writer = await connect("app.db");

  // Capture complete row changes from this writer connection.
  await writer.exec("PRAGMA capture_data_changes_conn('full')");

  // Reuse one prepared statement for the two sample inserts.
  const insertProject = writer.prepare(
    "INSERT INTO projects (name, status) VALUES (?, ?)"
  );
  await insertProject.run("Search refresh", "planned");
  await insertProject.run("Dashboard cache", "planned");

  // Produce one update and one delete after the inserts.
  await writer.prepare("UPDATE projects SET status = ? WHERE name = ?").run("active", "Search refresh");
  await writer.prepare("DELETE FROM projects WHERE name = ?").run("Dashboard cache");

  // Read the stream metadata without decoding the row images yet.
  const changes = await writer.prepare(`
    SELECT change_id, change_txn_id, change_type, table_name, id
    FROM turso_cdc
    ORDER BY change_id
  `).all();
  console.table(changes);
}

main().catch((error) => { console.error(error); process.exitCode = 1; });

What does this code do?

  • The writer connection opens the existing app.db file.
  • The capture PRAGMA tells that connection to record complete row changes in turso_cdc.
  • The prepared insert adds two sample projects.
  • The remaining statements update one project and delete the other.
  • The final query selects stream metadata in change_id order.
  • Save writer.js.
  • Confirm that writer.js is visible beside app.db in your editor's file panel.

✔️ Awesome, I've got everything!

Your writer enables capture before issuing any sample writes.

ⓧ I'd like to double check the full code

async function main() {
  // Load the local database package and open the existing project database.
  const { connect } = await import("@tursodatabase/database");
  const writer = await connect("app.db");

  // Capture complete row changes from this writer connection.
  await writer.exec("PRAGMA capture_data_changes_conn('full')");

  // Reuse one prepared statement for the two sample inserts.
  const insertProject = writer.prepare(
    "INSERT INTO projects (name, status) VALUES (?, ?)"
  );
  await insertProject.run("Search refresh", "planned");
  await insertProject.run("Dashboard cache", "planned");

  // Produce one update and one delete after the inserts.
  await writer.prepare("UPDATE projects SET status = ? WHERE name = ?").run("active", "Search refresh");
  await writer.prepare("DELETE FROM projects WHERE name = ?").run("Dashboard cache");

  // Read the stream metadata without decoding the row images yet.
  const changes = await writer.prepare(`
    SELECT change_id, change_txn_id, change_type, table_name, id
    FROM turso_cdc
    ORDER BY change_id
  `).all();
  console.table(changes);
}

main().catch((error) => { console.error(error); process.exitCode = 1; });
Run the writer and inspect the stream

Each sample write commits separately. The stream therefore records row changes alongside transaction boundaries.

Before you run the writer, consider which rows will prove that each transaction finished.

  • Execute writer.js from the terminal in your local project directory by running this command:
node writer.js

What should you see?

  • You should see two insert rows with change_type set to 1.
  • You should see one update row with change_type set to 0.
  • You should see one delete row with change_type set to -1.
  • You should see commit rows with change_type set to 2.
  • Each commit row should share a change_txn_id with its corresponding data change.

Writer did not print the CDC rows?

  • Check that writer.js sits in the same local project directory as app.db.
  • Confirm that @tursodatabase/database remains listed in package.json.
  • Compare the projects column names in the script with the table created earlier.

Help me debug my CDC writer.

You have the stream working. Every sample write now has an ordered record plus a transaction boundary.

Your raw CDC stream is ready. Next, you will decode each captured after-state into readable JSON.

Decode Captured Changes

Your writer now captures every sample edit in turso_cdc. The change rows prove that your change data capture stream is working.

The after column stores each resulting row as a binary image. You need readable data before those changes can become useful events.

In this step, get ready to:
  • Decode applicable after values with bin_record_json_object.
  • Inspect the captured project changes as readable JSON.
Decode the after-state

Turso Database can decode a binary row image using the table's current column list. This turns captured values into named fields that you can inspect.

  • Return to writer.js in the code editor you used earlier.
  • Find the final sample delete operation.
  • Add the decoding query below that operation by pasting this code:
// Decode captured row images with the current projects schema.
const decodedChanges = await writer.prepare(`
  SELECT change_id, id,
    bin_record_json_object(table_columns_json_array('projects'), after) AS after_state
  FROM turso_cdc
  WHERE table_name = 'projects' AND change_type != 2
  ORDER BY change_id
`).all();

// Display each captured project change in commit order.
console.table(decodedChanges);

What does this code do?

  • The table_columns_json_array('projects') helper supplies the current column names for the projects table.
  • The bin_record_json_object helper maps each binary after image to those column names.
  • The change_type != 2 filter leaves transaction commit markers out of the decoded row list.
  • The ORDER BY change_id clause preserves the captured change order.
  • Save writer.js.
Inspect the decoded changes

Before you run the writer again, which captured changes do you expect to have a readable after_state value?

  • Switch back to the terminal from earlier.
  • Run the updated writer by entering this command:
node writer.js

Near the end of the output, you should see one table row for each non-commit project change. Applicable after_state values appear as readable objects with named project fields.

Why can some after-states be empty?

An insert or update has a resulting row that can appear in after_state. A delete removes that resulting row, so its after-state can be empty.

Not seeing decoded project data?

  • Confirm that the decoding query appears after the sample edits in writer.js.
  • Check that the query uses projects in both the schema helper and the table filter.
  • Check that the query reads from the existing turso_cdc table.

Help me debug why my decoded CDC rows are missing or unreadable.

✔️ Awesome, I've got everything!

Your writer.js now queries the captured project changes in order. It displays every applicable after value as readable data.

ⓧ I'd like to double check the full code

Compare the decoding section at the end of writer.js with this reference.

// Decode captured row images with the current projects schema.
const decodedChanges = await writer.prepare(`
  SELECT change_id, id,
    bin_record_json_object(table_columns_json_array('projects'), after) AS after_state
  FROM turso_cdc
  WHERE table_name = 'projects' AND change_type != 2
  ORDER BY change_id
`).all();

// Display each captured project change in commit order.
console.table(decodedChanges);

This section belongs below the existing sample insert, update, and delete operations. It uses the same writer connection that enabled capture.

You have turned binary change records into data you can inspect. Next up, you will process committed transactions as ordered groups.

Consume Committed Transactions

Your writer has exposed readable changes from Turso Database. The next challenge is processing those changes without splitting a database transaction into separate pieces.

A reliable change data capture consumer waits for each commit row. It also saves a watermark so the next poll starts after the latest fully processed transaction.

In this step, get ready to:
  • Create a checkpoint table for the consumer's saved watermark.
  • Group captured changes until each transaction reaches its commit row.
  • Print committed transactions and save the latest processed change ID.
Create the consumer checkpoint

A watermark records the last change the consumer finished processing. The consumer stores that position in cdc_offsets inside app.db.

  • Use your code editor's new-file control to create consumer.js beside writer.js.
  • Initialize the consumer connection and its checkpoint table by adding this code:
import { connect } from "@tursodatabase/database"

// Keep consumer bookkeeping outside the captured writer connection
const reader = await connect("app.db")
await reader.exec(
  "CREATE TABLE IF NOT EXISTS cdc_offsets (consumer TEXT PRIMARY KEY, last_id INTEGER)"
)
console.log("Offset table ready")

What does this code do?

  • The reader connection opens the existing app.db file without enabling capture on itself.
  • The cdc_offsets table keeps one saved last_id for each named consumer.
  • Keeping bookkeeping on this connection prevents offset updates from entering the CDC stream.
  • Save consumer.js.
  • Confirm the offset table can be prepared by running:
node consumer.js

What should I see?

You should see Offset table ready. This confirms that app.db now contains the consumer's checkpoint table.

Offset table not ready?

Check that consumer.js sits beside app.db. Confirm that the import matches the package used by writer.js.

Ask for help with preparing the consumer checkpoint table.

Print each committed transaction

A transaction can contain several row changes. Printing the complete group together makes that commit boundary visible.

  • Add the transaction printer below the existing code in consumer.js by pasting:
async function applyTransaction(changes) {
  // Label the complete group with its shared transaction ID
  const transactionId = changes[0]?.change_txn_id ?? "empty"
  console.log(`\nTransaction ${transactionId}`)

  for (const change of changes) {
    // Turn the decoded JSON text into a printable JavaScript value
    const after = change.after ? JSON.parse(change.after) : null
    console.log({
      change_id: change.change_id,
      change_type: change.change_type,
      table_name: change.table_name,
      id: change.id,
      after
    })
  }
}
console.log("Transaction printer ready")

How does the printer work?

  • The shared change_txn_id labels every row from the same committed transaction.
  • The after value becomes a JavaScript value when a captured after-state exists.
  • Deletes print null because a deleted row has no after-state.
  • Save consumer.js.
  • Check that the transaction printer loads by running:
node consumer.js

What should I see?

You should see Offset table ready followed by Transaction printer ready. The printer is now ready to receive a complete group.

Printer not loading?

Check the closing braces around the for loop. Make sure applyTransaction appears after the checkpoint setup.

Ask for help with finding the syntax problem.

Poll from the saved watermark

The polling query reads changes in change_id order. It also decodes project after-states before the rows reach the transaction printer.

  • Add the ordered batch reader below the transaction printer in consumer.js by pasting:
const BATCH = 500

async function readChanges(cursor) {
  // Read the next ordered page after the current cursor
  return reader.prepare(`
    SELECT change_id, change_txn_id, change_type, table_name, id,
      CASE WHEN table_name = 'projects'
        THEN bin_record_json_object(table_columns_json_array('projects'), after)
      END AS after
    FROM turso_cdc
    WHERE change_id > ?
    ORDER BY change_id
    LIMIT ${BATCH}`).all(cursor)
}
console.log("Change reader ready")

How does the batch query work?

  • The cursor excludes every change at or below the saved watermark.
  • The sort keeps row changes and commit rows in their captured order.
  • The decoding expression turns each applicable project after-state into JSON text.
  • The batch limit lets the consumer catch up without loading the full stream at once.
  • Save consumer.js.
  • Confirm that the batch reader loads by running:
node consumer.js

What should I see?

You should now see Change reader ready after the first two readiness messages. The ordered reader is ready to poll turso_cdc.

Change reader not ready?

Check that the query includes the closing template literal after LIMIT ${BATCH}. Confirm that BATCH is declared above readChanges.

Ask for help with checking the batch query.

The batch reader can return row changes before their matching commit row. The poller buffers those rows by transaction ID until the commit boundary arrives.

  • Add the commit-aware poller below readChanges in consumer.js by pasting:
async function poll() {
  // Resume after the latest fully processed commit
  const offset = await reader.prepare("SELECT last_id FROM cdc_offsets WHERE consumer = 'search'").get()
  let cursor = offset?.last_id ?? 0
  const open = new Map()

  while (true) {
    const changes = await readChanges(cursor)

    // Hold each group until its commit row arrives
    for (const change of changes) {
      cursor = change.change_id
      const group = open.get(change.change_txn_id) ?? []
      if (change.change_type !== 2) {
        group.push(change)
        open.set(change.change_txn_id, group)
        continue
      }
      await applyTransaction(group)
      open.delete(change.change_txn_id)
      if (open.size === 0) {
        await reader.prepare("INSERT OR REPLACE INTO cdc_offsets VALUES ('search', ?)").run(change.change_id)
        console.log(`Watermark saved at change_id ${change.change_id}`)
      }
    }
    if (changes.length < BATCH) return
  }
}
console.log("Poller ready")

How does the poller protect transaction boundaries?

  • The saved last_id becomes the starting cursor.
  • The open map holds changes under their shared transaction ID.
  • A change type of 2 closes its group and sends the complete transaction to applyTransaction.
  • The watermark advances only when no transaction groups remain open.
  • Save consumer.js.
  • Confirm that the poller loads by running:
node consumer.js

What should I see?

You should see Poller ready as the fourth readiness message. The poller now has every part it needs to process complete transactions.

Poller not ready?

Check that poll appears after readChanges. Confirm that each opening brace has a matching closing brace.

Ask for help with debugging the commit-aware poller.

One poll catches up to the current stream. A short loop repeats that poll so the consumer remains ready for future changes.

  • Add the continuous polling loop at the bottom of consumer.js by pasting:
while (true) {
  // Poll again after a short pause so new commits are picked up
  await poll()
  await new Promise((resolve) => setTimeout(resolve, 1000))
}

What does the loop do?

The loop catches up through poll. It waits one second before checking for another committed transaction.

  • Save consumer.js.

Before you run the finished consumer, how many transaction groups do you expect from the writer's sample edits?

  • Start the finished consumer by running:
node consumer.js

What should I see?

You should see each committed transaction printed as its own group. Every group contains decoded project changes followed by a saved watermark message.

The final watermark uses the latest fully processed commit's change_id. The process stays running so it can poll for later commits.

No transactions printed?

Confirm that writer.js previously created captured changes in this same app.db file. Check that the polling query uses change_id > ?.

Ask for help with tracing an empty CDC poll.

✔️ Awesome, I've got everything!

Your consumer is printing complete transactions and saving its progress. Keep the process running for the next restart test.

ⓧ I'd like to double check the full code

Compare your saved consumer.js with this complete version.

import { connect } from "@tursodatabase/database"

// Keep consumer bookkeeping outside the captured writer connection
const reader = await connect("app.db")
await reader.exec(
  "CREATE TABLE IF NOT EXISTS cdc_offsets (consumer TEXT PRIMARY KEY, last_id INTEGER)"
)
console.log("Offset table ready")

async function applyTransaction(changes) {
  // Label the complete group with its shared transaction ID
  const transactionId = changes[0]?.change_txn_id ?? "empty"
  console.log(`\nTransaction ${transactionId}`)

  for (const change of changes) {
    // Turn the decoded JSON text into a printable JavaScript value
    const after = change.after ? JSON.parse(change.after) : null
    console.log({
      change_id: change.change_id,
      change_type: change.change_type,
      table_name: change.table_name,
      id: change.id,
      after
    })
  }
}
console.log("Transaction printer ready")

const BATCH = 500

async function readChanges(cursor) {
  // Read the next ordered page after the current cursor
  return reader.prepare(`
    SELECT change_id, change_txn_id, change_type, table_name, id,
      CASE WHEN table_name = 'projects'
        THEN bin_record_json_object(table_columns_json_array('projects'), after)
      END AS after
    FROM turso_cdc
    WHERE change_id > ?
    ORDER BY change_id
    LIMIT ${BATCH}`).all(cursor)
}
console.log("Change reader ready")

async function poll() {
  // Resume after the latest fully processed commit
  const offset = await reader.prepare("SELECT last_id FROM cdc_offsets WHERE consumer = 'search'").get()
  let cursor = offset?.last_id ?? 0
  const open = new Map()

  while (true) {
    const changes = await readChanges(cursor)

    // Hold each group until its commit row arrives
    for (const change of changes) {
      cursor = change.change_id
      const group = open.get(change.change_txn_id) ?? []
      if (change.change_type !== 2) {
        group.push(change)
        open.set(change.change_txn_id, group)
        continue
      }
      await applyTransaction(group)
      open.delete(change.change_txn_id)
      if (open.size === 0) {
        await reader.prepare("INSERT OR REPLACE INTO cdc_offsets VALUES ('search', ?)").run(change.change_id)
        console.log(`Watermark saved at change_id ${change.change_id}`)
      }
    }
    if (changes.length < BATCH) return
  }
}
console.log("Poller ready")

while (true) {
  // Poll again after a short pause so new commits are picked up
  await poll()
  await new Promise((resolve) => setTimeout(resolve, 1000))
}

How to use this reference

Check the order of the checkpoint setup, transaction printer, batch reader, poller, and continuous loop. This complete file matches the consumer you assembled above.

You have the core CDC consumer working. It now respects commit boundaries and remembers exactly how far it has processed.

Your consumer has a durable checkpoint. Next, you will restart it and prove that the saved watermark prevents any committed change from being missed.

Resume From the Saved Watermark

Your Turso Database consumer now prints each committed transaction as a group. It also stores the latest fully processed change_id in cdc_offsets.

A restart-safe change data capture consumer uses that saved position after its process stops. This step tests whether your consumer can resume without missing or replaying completed changes.

In this step, get ready to:
  • Stop the consumer after it saves its latest watermark.
  • Run writer.js again while the consumer is offline.
  • Prove that the restarted consumer processes each new transaction once.
Stop at a clean commit boundary

The watermark is a durable boundary between completed work and pending work. It advances only after the consumer processes a transaction's commit row.

  • Return to the terminal session running consumer.js from the previous step.
  • Watch the output until the consumer finishes printing its latest committed transaction.
  • Record the latest saved change_id from the previous consumer run here: your previous watermark.
  • Press Ctrl+C in the terminal to stop consumer.js.

You now have a clean restart boundary. The consumer is offline while its checkpoint remains stored in app.db.

Create changes while the consumer is offline

Capture is enabled separately for each writer connection. Every fresh run of writer.js must enable full capture before it edits projects.

Why enable capture on every writer connection?

Turso Database applies CDC mode to one connection at a time. When writer.js opens app.db again, its new connection needs PRAGMA capture_data_changes_conn('full').

Your writer already runs this before its edits. Reusing the script keeps the offline changes visible to the consumer.

  • Use the terminal history to find the earlier command that runs writer.js.
  • Run the selected writer command.

You should see fresh decoded CDC rows from the additional project edits. Each new row should carry a change_id greater than your previous watermark.

No fresh CDC rows?

  • Check that the command selected from terminal history targets writer.js.
  • Confirm that the writer completed its project edits before returning control to the terminal.

A fresh connection captures changes only after full capture is enabled. Help me diagnose the missing CDC rows.

Restart from the saved watermark

When consumer.js starts, it reads its position from cdc_offsets. Its next poll begins after that saved change_id.

  • Use the terminal history to find the earlier command that runs consumer.js.

Before you run it, do you expect the original transaction groups to appear a second time?

  • Run the selected consumer command.

You will see only the transaction groups created while the consumer was offline. The transaction groups from the first run remain absent.

Each newly committed transaction appears in order. After the final new group is processed, cdc_offsets holds its commit change_id as the new watermark.

Missing or repeated transactions?

  • Confirm that the writer completed its run after you stopped the consumer.
  • Confirm that both scripts use the same local app.db file.

If the output still looks wrong, help me trace the consumer restart.

Your consumer now survives a stop without losing its place. Next up, you will prune the CDC rows that every consumer has finished processing.

Prune Processed CDC Rows

Your restarted consumer has caught up from its saved watermark. It processed only the new committed transactions.

Built-in change data capture (CDC) in Turso Database keeps adding rows to turso_cdc until you prune them. This step gives that stream a safe retention boundary.

In this step, get ready to:
  • Find the slowest saved consumer watermark.
  • Remove CDC rows through that watermark.
  • Verify that only newer CDC rows remain.
Measure the retention boundary

The cdc_offsets table may eventually track several consumers. Its smallest last_id is the safe cutoff because every consumer has processed through that point.

  • Count the CDC rows on each side of the safe cutoff by running this command:
node --input-type=module --eval "import { connect } from '@tursodatabase/database'; const db = await connect('app.db'); const { watermark } = await db.prepare('SELECT MIN(last_id) AS watermark FROM cdc_offsets').get(); const counts = await db.prepare('SELECT COUNT(*) AS total_rows, SUM(change_id <= ?) AS prunable_rows FROM turso_cdc').get(watermark); console.log({ watermark, ...counts });"

What does this check do?

  • The MIN(last_id) calculation selects the slowest saved consumer watermark.
  • The total_rows value counts every row currently stored in turso_cdc.
  • The prunable_rows value counts rows that every saved consumer has already processed.

You will see numeric values for watermark and total_rows. The prunable_rows value shows how many rows the next command can safely remove.

Missing watermark values?

  • Check that the terminal prompt still shows the local Node.js project directory from earlier.
  • Return to the restart step if watermark is empty. The consumer must save a completed commit before pruning.

Help me diagnose why my CDC watermark query is missing values.

Apply the retention rule

A saved watermark represents a fully processed commit. Rows at or below the slowest watermark can now be removed without creating a gap for another saved consumer.

Before you run this, do you expect the row count to fall while the saved watermark stays unchanged?

  • Delete the eligible rows and measure the result by running this command:
node --input-type=module --eval "import { connect } from '@tursodatabase/database'; const db = await connect('app.db'); const { watermark } = await db.prepare('SELECT MIN(last_id) AS watermark FROM cdc_offsets').get(); const { before_count } = await db.prepare('SELECT COUNT(*) AS before_count FROM turso_cdc').get(); await db.prepare('DELETE FROM turso_cdc WHERE change_id <= ?').run(watermark); const { after_count } = await db.prepare('SELECT COUNT(*) AS after_count FROM turso_cdc').get(); const { rows_at_or_before_watermark } = await db.prepare('SELECT COUNT(*) AS rows_at_or_before_watermark FROM turso_cdc WHERE change_id <= ?').get(watermark); console.log({ watermark, before_count, after_count, rows_at_or_before_watermark });"

How does pruning stay safe?

  • The first query reads the smallest saved last_id.
  • The second query records the starting number of CDC rows.
  • The DELETE FROM turso_cdc WHERE change_id <= ? query removes only rows through the slowest watermark.
  • The final queries prove that the row count fell without leaving an old CDC row behind.

You will see an after_count lower than before_count. You will also see rows_at_or_before_watermark set to 0.

Did the row count stay the same?

  • Check the earlier output for a numeric watermark.
  • Confirm that prunable_rows was greater than 0 before retrying the prune command.

Help me debug why my Turso CDC prune query did not reduce the row count.

You have closed the retention loop. Your CDC stream now keeps only rows beyond the slowest saved consumer watermark.

Secret mission

Design Replay-Safe Consumer Logic

A crash can cause a committed transaction to be delivered again before the watermark advances. Design an idempotency contract for a search index. Prove that processing the same transaction twice leaves one correct result.

Clean Up Your Resources

Clean Up Your Resources

Your restart-safe CDC consumer now works on your computer. Decide whether to keep the files, pause an active process, or delete the local project files.

Resources you used:

  • The local database in app.db.
  • The reusable consumer in consumer.js.
  • The sample writer in writer.js.
  • The project manifest in package.json.

Keep everything running

Leave the files in place if you want to keep testing the change stream. Local files have no ongoing service cost.

  • Keep the listed project files together in the same local project directory.
  • Reuse consumer.js as a template for keeping another system in step with a local database.
  • Run further local experiments whenever you want to explore new transactions.

Pause - I'll come back to this later

Pause any active consumer process to free local memory. Your database remains ready for later.

  • Return to the terminal from this project.
  • Stop consumer.js if it is still polling for changes.
  • Leave the listed project files in their current local directory.

Delete - I don't want to use this again

Choose this option when you no longer need the local database or its supporting scripts. Permanent deletion creates a fresh starting point.

Save the Consumer Template

Deleting consumer.js removes the restart-safe consumer you built. A separate copy preserves that template for future search index or cache projects.

  • Copy consumer.js to another local folder if you want to preserve the template.
  • Choose your operating system below to delete the project files.

macOS

  • In Finder, navigate to the local project directory that contains consumer.js.
  • Move app.db to Trash.
  • Move consumer.js to Trash.
  • Move writer.js to Trash.
  • Move package.json to Trash.
  • Permanently delete only these project files from Trash.

You should no longer see any of the four project files in the local project directory.

Cannot Delete the Database?

A running local script can keep the database in use.

  • Return to the terminal from this project.
  • Stop any running consumer.js process.
  • Stop any running writer.js process.
  • Retry deleting app.db.

Help me find which local process is using app.db.

Windows

  • In File Explorer, navigate to the local project directory that contains consumer.js.
  • Move app.db to the Recycle Bin.
  • Move consumer.js to the Recycle Bin.
  • Move writer.js to the Recycle Bin.
  • Move package.json to the Recycle Bin.
  • Permanently delete only these project files from the Recycle Bin.

You should no longer see any of the four project files in the local project directory.

Cannot Delete the Database?

A running local script can keep the database in use.

  • Return to the terminal from this project.
  • Stop any running consumer.js process.
  • Stop any running writer.js process.
  • Retry deleting app.db.

Help me find which local process is using app.db.

Nice Work!

Nice Work!

You did it! You built a restart-safe change data capture pipeline around a local SQLite database.

Your consumer now prints committed transactions in order. It remembers its position across restarts.

You've learned how to:

  • Enable full data-change capture on the writer connection. You verified each database operation plus its commit in turso_cdc.
  • Decode binary after-states into readable JSON with bin_record_json_object.
  • Run a restart-safe consumer that saves a watermark in cdc_offsets. You also pruned processed rows using the slowest saved consumer watermark.
  • Secret Mission: Push your CDC skills further through an optional challenge.

Ready to quiz yourself?