Inspecting an Umbraco SQLite database on Windows

SQLite makes an Umbraco development site pleasantly self-contained: the database can live in a file alongside the application. When you need to inspect that file on Windows, DB Browser for SQLite provides a useful graphical interface for browsing tables and running SQL.

The important detail is getting a consistent copy before opening it. A running SQLite database can involve more than the file ending in .db.

Find the database the site actually uses

Check the effective Umbraco connection string and its SQLite provider. Configuration may come from environment variables, user secrets or hosting settings as well as appsettings.json.

A typical local filename is Umbraco.sqlite.db, often under umbraco/Data, but do not rely on a filename search alone. A connection string using |DataDirectory| needs resolving against the application’s data directory. You may otherwise spend time examining a stale copy while the site uses a different file.

Keep connection settings and database copies private. An Umbraco database can contain member information, user details, unpublished content and integration settings.

Take a consistent copy

For a local development site, the straightforward approach is to stop all processes that can write to the database, then take a backup. Close the application cleanly and check that another development server or worker is not still using it.

SQLite’s write-ahead logging mode can create companion files ending in -wal and -shm. Committed changes may still be in the WAL file rather than the main database. Copying just the main file while the application is writing can omit those changes.

SQLite’s WAL documentation explains this relationship. Never delete the companion files as a way to “unlock” an active database. If a WAL remains after stopping writers, do not discard it when making your copy. For a live system, use a backup mechanism based on SQLite’s online backup API, or an equivalent consistent snapshot, rather than assuming a normal file copy is enough.

Work on the backup in a separate directory. Keep its original untouched so you can start again if an investigation changes the working copy.

Open it read-only

Install DB Browser from its official website and use File → Open Database Read Only to select the copied database. The main areas you need are:

• Database Structure to inspect tables, columns and indexes.
• Browse Data to view rows and apply filters.
• Execute SQL for focused queries.

For a first look, ask SQLite which tables and views exist:

SELECT name, type
FROM sqlite_schema
WHERE type IN ('table', 'view')
ORDER BY type, name;

That avoids relying on a table list remembered from a different Umbraco version. Start with a small result set rather than selecting every row from a large content table.

Inspect migration state without changing it

One useful diagnostic is seeing which migration states have been recorded:

SELECT "key", "value"
FROM umbracoKeyValue
WHERE "key" LIKE 'Umbraco.Core.Upgrader.State+%'
ORDER BY "key";

Treat the result as evidence of recorded state. It does not prove that every custom behaviour is working, and deleting a row to force a rerun can replay operations against data that has already changed.

For a package migration problem, compare the state with the plan in the package source and read the startup logs. My migration article explains how plan names and transition states fit together.

You can also run a read-only integrity check on the copy:

PRAGMA quick_check;

An ok result concerns SQLite’s structural checks. It does not validate Umbraco’s content relationships, permissions or application-specific rules. On a large database, allow time for the check to complete.

Browse first; change through the application

It is tempting to fix a value directly after finding the relevant row. Umbraco’s service layer handles relationships, validation, notifications and cache effects that a hand-written update bypasses.

Use a database browser to understand the problem, then implement a controlled change through an appropriate Umbraco service or a tested migration. Avoid treating core table layouts as a permanent public API, and do not paste an SQL Server repair script into SQLite without understanding the differences.

If you deliberately experiment with SQL updates, do so on a disposable copy and record the query. Never make a casual change to a live database simply because a GUI makes it easy.

Another option: God Mode 17

For inspection within Umbraco, God Mode 17 includes a database browser for exploring schema information, columns, relationships and row counts. It is useful when you want database structure alongside the other developer information in the backoffice.

DB Browser is useful for examining a separate SQLite backup and writing focused SQL. God Mode gives you another route to understanding the running application’s structure. Neither removes the need for a proper backup and a controlled process for changing data.