# How revit-mcp Uses SQLite Transactions for Data Integrity in Batch Room Imports

> Learn how revit-mcp ensures data integrity with SQLite transactions. Discover how batch room imports maintain ACID compliance through atomic commits or rollbacks.

- Repository: [MCP servers for Revit/revit-mcp](https://github.com/mcp-servers-for-revit/revit-mcp)
- Tags: how-to-guide
- Published: 2026-02-16

---

**The revit-mcp database service guarantees ACID compliance during bulk operations by wrapping batch inserts in SQLite transactions via the better-sqlite3 `db.transaction()` API, ensuring that all room records are committed atomically or fully rolled back on error.**

The revit-mcp project provides a Model Context Protocol (MCP) server for Autodesk Revit, managing project and room data in a local SQLite database. To prevent data corruption during high-volume imports, the service implements **SQLite transactions for data integrity**, leveraging the better-sqlite3 driver's transaction API to enforce atomic commits and automatic rollbacks.

## Atomic Batch Inserts with better-sqlite3 Transactions

When importing room data from Revit models, the system must handle hundreds of records without creating orphaned entries or partial datasets. The solution relies on better-sqlite3's transactional capabilities to treat the entire batch as a single atomic unit.

### Implementing storeRoomsBatch with db.transaction()

In [`src/database/service.ts`](https://github.com/mcp-servers-for-revit/revit-mcp/blob/main/src/database/service.ts), the `storeRoomsBatch` function defines a transaction wrapper that executes multiple `INSERT` or `UPDATE` statements as one logical operation:

```typescript
// src/database/service.ts – bulk-store rooms inside a transaction
export function storeRoomsBatch(projectId: number, rooms: RoomData[]): number {
  // `db.transaction` creates a function that runs all its statements
  // inside a single SQLite transaction.
  const insertMany = db.transaction((roomsData: RoomData[]) => {
    let count = 0;
    for (const room of roomsData) {
      // Each call uses the normal `storeRoom` logic (INSERT or UPDATE)
      storeRoom(projectId, room);
      count++;
    }
    return count; // returned after the transaction commits
  });

  // Execute the transaction; if any `storeRoom` fails, everything rolls back.
  return insertMany(rooms);
}

```

The `db.transaction()` method returns a new function (`insertMany`) that, when called, wraps all internal database operations in `BEGIN` and `COMMIT` statements. If any statement fails, better-sqlite3 automatically issues a `ROLLBACK`, leaving the database in its pre-transaction state.

### Automatic Rollback on Constraint Violations

The transaction mechanism provides **automatic rollback on failure** without requiring explicit error handling in the application code. If a `storeRoom` call encounters a constraint violation—such as a duplicate unique key or a foreign key mismatch—the better-sqlite3 driver catches the error, rolls back the entire transaction, and re-throws the exception. This prevents the "half-written" state where some rooms from a batch appear in the database while others are missing.

## Enforcing Referential Integrity via Foreign Keys

Beyond atomic transactions, the database layer maintains **referential integrity** between projects and rooms using SQLite's foreign key constraints.

### Enabling PRAGMA foreign_keys in db.ts

In [`src/database/db.ts`](https://github.com/mcp-servers-for-revit/revit-mcp/blob/main/src/database/db.ts), the connection initialization explicitly enables foreign key enforcement, which is disabled by default in SQLite for backward compatibility:

```typescript
// src/database/db.ts – enable foreign-key checks (required for cascade deletes)
import Database from 'better-sqlite3';
...
export const db = new Database(DB_PATH);

// Ensure foreign-key constraints are enforced for the whole connection.
db.pragma('foreign_keys = ON');

```

Without this pragma, foreign key constraints would be parsed but not enforced, allowing orphaned room records to exist without associated projects.

### Cascade Deletes for Project-Room Relationships

The schema defines the `rooms` table with a foreign key reference to `projects(id)` that includes `ON DELETE CASCADE`. When a project is deleted within a transaction, SQLite automatically removes all associated room records as part of the same atomic operation. This cascade behavior works in conjunction with the explicit transactions described earlier, ensuring that deleting a project and its rooms succeeds entirely or fails entirely without manual cleanup code.

## Summary

- **Atomic batch operations**: The `storeRoomsBatch` function in [`src/database/service.ts`](https://github.com/mcp-servers-for-revit/revit-mcp/blob/main/src/database/service.ts) uses `db.transaction()` to wrap multiple room inserts into a single atomic unit.
- **Automatic rollback**: better-sqlite3 automatically rolls back transactions when constraint violations or errors occur, preventing partial data writes.
- **Foreign key enforcement**: [`src/database/db.ts`](https://github.com/mcp-servers-for-revit/revit-mcp/blob/main/src/database/db.ts) enables `PRAGMA foreign_keys = ON` to ensure referential integrity between projects and rooms.
- **Cascade deletes**: The schema uses `ON DELETE CASCADE` to automatically clean up related room records when projects are deleted, all within the same transactional context.

## Frequently Asked Questions

### What happens if a single room fails during a batch import in revit-mcp?

If any individual room insert fails inside `storeRoomsBatch`—for example, due to a unique constraint violation or invalid foreign key—the better-sqlite3 driver automatically rolls back the entire transaction. This means none of the rooms from that batch are persisted to the database, maintaining consistency and preventing partial imports.

### Does revit-mcp support WAL mode for SQLite concurrency?

While the provided source analysis focuses on transaction integrity rather than concurrency modes, the better-sqlite3 driver supports Write-Ahead Logging (WAL) mode. To enable WAL mode for improved read concurrency, you would call `db.pragma('journal_mode = WAL')` in [`src/database/db.ts`](https://github.com/mcp-servers-for-revit/revit-mcp/blob/main/src/database/db.ts) alongside the existing `foreign_keys = ON` pragma.

### How does revit-mcp handle foreign key violations during transactions?

When `PRAGMA foreign_keys = ON` is enabled in [`src/database/db.ts`](https://github.com/mcp-servers-for-revit/revit-mcp/blob/main/src/database/db.ts), SQLite enforces referential integrity at the database level. If a transaction attempts to insert a room with a non-existent `projectId`, SQLite throws a foreign key constraint error. better-sqlite3 catches this error and rolls back the entire transaction, ensuring that referential integrity violations never result in orphaned records.

### Can I adjust the transaction batch size in revit-mcp's storeRoomsBatch?

The `storeRoomsBatch` function accepts an array of `RoomData` objects and processes the entire array within a single transaction. While the current implementation does not internally chunk large arrays into smaller sub-transactions, you can control the batch size by passing smaller slices of your data array to `storeRoomsBatch` from your application code. Each call creates a separate transaction boundary, allowing you to balance between transaction atomicity and memory usage.