Skip to content

SimonCropp/LocalDb

BranchesTags

Repository files navigation

LocalDb

Build status NuGet Status NuGet Status NuGet Status NuGet Status NuGet Status NuGet Status NuGet Status

Provides a wrapper around SqlLocalDB to simplify running tests against Entity Framework or a raw SQL Database.

See Milestones for release notes.

SqlLocalDB is only supported on Windows

Sponsors

Entity Framework Extensions

Entity Framework Extensions is a major sponsor and is proud to contribute to the development this project.

Entity Framework Extensions

Developed using JetBrains IDEs

JetBrains logo.

Contents

NuGet packages

Why

Goals

  • Have a isolated SQL Server Database for each unit test method.
  • Does not overly impact performance.
  • Results in a running SQL Server Database that can be accessed via SQL Server Management Studio (or other tooling) to diagnose issues when a test fails.

Why not SQLite

Why not SQL Express or full SQL Server

  • Control over file location. SqlLocalDB connections support AttachDbFileName property, which allows developers to specify a database file location. SqlLocalDB will attach the specified database file and the connection will be made to it. This allows database files to be stored in a temporary location, and cleaned up, as required by tests.
  • No installed service is required. Processes are started and stopped automatically when needed.
  • Automatic cleanup. A few minutes after the last connection to this process is closed the process shuts down.
  • Full control of instances using the Command-Line Management Tool: SqlLocalDB.exe.

Why not EntityFramework InMemory

  • Difficult to debug the state. When debugging a test, or looking at the resultant state, it is helpful to be able to interrogate the Database using tooling
  • InMemory is implemented with shared mutable state between instance. This results in strange behaviors when running tests in parallel, for example when creating keys.
  • InMemory is not intended to be an alternative to SqlServer, and as such it does not support the full suite of SqlServer features. For example:

See the official guidance: InMemory is not a relational database.

References

Usage

This project supports several approaches.

Raw SqlConnection

Interactions with SqlLocalDB via a SqlConnection.

Full Usage

EntityFramework Classic

Interactions with SqlLocalDB via Entity Framework Classic.

Full Usage

EntityFramework Core

Interactions with SqlLocalDB via Entity Framework Core.

Full Usage

EntityFramework Core NUnit

NUnit test base class wrapping EfLocalDb with Arrange-Act-Assert phase enforcement.

Full Usage

EntityFramework Core xunit.v3

xunit.v3 test base class wrapping EfLocalDb with Arrange-Act-Assert phase enforcement.

Full Usage

EntityFramework Core MSTest

MSTest test base class wrapping EfLocalDb with Arrange-Act-Assert phase enforcement.

Full Usage

EntityFramework Core TUnit

TUnit test base class wrapping EfLocalDb with Arrange-Act-Assert phase enforcement.

Full Usage

LocalDB

How MS SqlServer LocalDB is structured

graph TB
    subgraph PM["Physical Machine (Windows)"]
        subgraph SQLLocalDB["SqlServer LocalDB"]
            subgraph Instance1["LocalDB Instance: MSSQLLocalDB"]
                DB1A[(DB: MyApp)]
                DB1B[(DB: TestDB)]
                DB1C[(DB: master)]
            end
            
            subgraph Instance2["LocalDB Instance: ProjectsV15"]
                DB2A[(DB: WebApi)]
                DB2B[(DB: master)]
            end
            
            subgraph Instance3["LocalDB Instance: MyCustomInstance"]
                DB3A[(DB: Analytics)]
                DB3B[(DB: Staging)]
                DB3C[(DB: master)]
            end
        end
        
        UserData["Default User Data Folder<br/>%LOCALAPPDATA%\Microsoft\Microsoft SQL Server Local DB\Instances\"]
    end
    
    Instance1 --> |"Stores mdf/ldf files in"| UserData
    Instance2 --> |"Stores mdf/ldf files in"| UserData
    Instance3 --> |"Stores mdf/ldf files in"| UserData
Loading

Key relationships:

  • Physical Machine → One Windows machine can have one LocalDB engine installed
  • LocalDB Engine → Can host multiple isolated instances (each is like a mini SQL Server)
  • Instance → Each contains multiple databases (always includes system DBs like master)
  • Storage → Each instance stores its .mdf and .ldf files in a subfolder under %LOCALAPPDATA%\Microsoft\Microsoft SQL Server Local DB\Instances\

How this project works

Inputs

buildTemplate

A delegate that builds the template database schema. Called zero or once based on the current state of the underlying LocalDB:

  • Not called if a valid template already exists (timestamp matches)
  • Called once if the template needs to be created or rebuilt

The delegate receives a connected DbContext (EF) or SqlConnection (raw) to create schema and seed initial data.

timestamp

A timestamp used to determine if the template database needs to be rebuilt:

  • If the timestamp is newer than the existing template, the template is recreated
  • Defaults to the last modified time of buildTemplate delegate's assembly, or the TDbContext assembly if buildTemplate is null

callback

A delegate executed after the template database has been created or mounted:

  • Guaranteed to be called exactly once per SqlInstance at startup
  • Receives a SqlConnection and DbContext (EF) for seeding reference data or post-creation setup
  • Called regardless of whether buildTemplate ran (useful for setup that must always occur)

SqlInstance Startup Flow

This flow happens once per SqlInstance, usually once before any tests run.

flowchart TD

    start[Start]
    checkExists{Instance<br>Exists?}
    checkRunning{Instance<br>Running?}
    startInstance[Start Instance]
    checkDataFile{Data File<br>Exists?}
    stopAndDelete[Stop & Delete<br>Instance]
    cleanDir[Clean Directory]
    checkTimestamp{Timestamp<br>Match?}
    checkCallback{Callback<br>Exists?}
    createInstance[Create Instance]

    subgraph openMasterForNewBox[Open Master Connection]
        optimizeModel[Optimize Model DB]
        deleteFiles[Delete Template Files]
        createTemplateDb[Create Template DB]
        subgraph openTemplateForNewBox[Open Template Connection]
            runBuildTemplate[Run buildTemplate]
            checkCallbackAfterBuild{Callback<br>Exists?}
            runCallbackAfterBuild[Run Callback]
        end
        detachShrink[Shrink & Detach Template]
    end
    setTimestamp[Set Creation Timestamp]

    subgraph openMasterForExistingBox[Open Master Connection]
        attachTemplate[Attach Template DB]
        subgraph openTemplateForExistingBox[Open Template Connection]
            runCallback[Run Callback]
        end
        detachTemplate[Detach Template DB]
    end


    done[Done]

    start --> checkExists

    checkExists -->|No| cleanDir
    checkExists -->|Yes| checkRunning

    checkRunning -->|No| startInstance
    startInstance --> checkDataFile

    checkRunning -->|Yes| checkDataFile

    checkDataFile -->|No| stopAndDelete
    stopAndDelete --> cleanDir

    checkDataFile -->|Yes| checkTimestamp

    checkTimestamp -->|No| deleteFiles

    checkTimestamp -->|Yes| checkCallback
    cleanDir --> createInstance
    createInstance --> optimizeModel
    optimizeModel --> deleteFiles
    deleteFiles --> createTemplateDb
    createTemplateDb --> runBuildTemplate
    runBuildTemplate --> checkCallbackAfterBuild
    checkCallbackAfterBuild -->|Yes| runCallbackAfterBuild
    checkCallbackAfterBuild -->|No| detachShrink
    runCallbackAfterBuild --> detachShrink
    detachShrink --> setTimestamp

    checkCallback -->|Yes| attachTemplate
    attachTemplate --> runCallback
    runCallback --> detachTemplate

    checkCallback -->|No| done
    detachTemplate --> done
    setTimestamp --> done
Loading

Clean Directory Flow

On first access, the library scans the data root directory (%TEMP%\LocalDb by default) and cleans up stale database files. This prevents unbounded disk growth from old test runs. The directory LocalDB keeps for each instance is swept separately, see Clean Instance Root Flow.

flowchart TD
    start[Start: CleanRoot]
    checkRootExists{Root Directory<br>Exists?}
    iterateDirs[Iterate Instance<br>Directories]
    collectFiles[Collect All DB Files<br>.mdf .ldf]
    checkHasFiles{Has Files?}
    checkDirAge{Directory Older<br>Than 6 Hours?}
    deleteDirEmpty[Delete Directory]
    findNewest[Find Newest<br>File Write Time]
    checkNewest{Newest File<br>Older Than<br>6 Hours?}
    stopInstance[Remove Instance:<br>Stop, Delete, and Remove<br>Its LocalDB Directory]
    deleteAllFiles[Delete All Files]
    checkEmptyAfter{Directory<br>Empty?}
    deleteDirAfter[Delete Directory]
    done[Done]

    start --> checkRootExists
    checkRootExists -->|No| done
    checkRootExists -->|Yes| iterateDirs
    iterateDirs --> collectFiles
    collectFiles --> checkHasFiles
    checkHasFiles -->|No| checkDirAge
    checkDirAge -->|Yes| deleteDirEmpty
    checkDirAge -->|No| done
    deleteDirEmpty --> done
    checkHasFiles -->|Yes| findNewest
    findNewest --> checkNewest
    checkNewest -->|No| done
    checkNewest -->|Yes| stopInstance
    stopInstance --> deleteAllFiles
    deleteAllFiles --> checkEmptyAfter
    checkEmptyAfter -->|Yes| deleteDirAfter
    checkEmptyAfter -->|No| done
    deleteDirAfter --> done
Loading

Key behaviors:

  • Triggered once during DirectoryFinder static initialization, before any SqlInstance starts.
  • All-or-nothing cleanup: The newest file write time in a directory determines whether the entire instance is stale. If any file is recent, nothing is touched. This avoids partially cleaning an active instance.
  • 6-hour cutoff: An instance directory is only cleaned if all its files have a last-write time older than 6 hours.
  • Removes the instance, not only the files: If stale files belong to a LocalDB instance, the instance is stopped (via KillProcess), deleted, and the directory LocalDB keeps for it removed. Stopping first prevents UnauthorizedAccessException from locked .mdf/.ldf files. Removing the instance along with its data means it never becomes an orphan that nothing under the data root points at.
  • Empty directory cleanup: Empty directories older than 6 hours are removed.
  • The instance name is derived from the directory name (e.g. %TEMP%\LocalDb\MyTestInstance → instance name MyTestInstance).
  • The instance that is about to be started is treated differently: only its stale data files are removed, and the instance is left in place to be reused, since recreating one is significantly slower than restarting it.

Clean Instance Root Flow

LocalDB keeps a directory of its own for every instance at %LocalAppData%\Microsoft\Microsoft SQL Server Local DB\Instances\InstanceName, holding the system databases (master, model, msdb, tempdb), the error logs, and the extended event files. That location is owned by LocalDB and cannot be moved.

The flow above only sees instances that still have a data directory. Once that directory is gone, cleared with the temp directory or by the flow above, nothing under the data root points at the instance and it is invisible there. Those orphans, and the directories left behind by instances that were already deleted, are reclaimed by this second sweep.

LocalDB instances are shared by everything on the machine that uses LocalDB, and nothing in an instance records what created it. So a marker file is written into the directory of each instance this library starts, and the sweep only reclaims instances carrying that marker.

flowchart TD
    start[Start: CleanInstanceRoot]
    checkEnabled{Cleanup Enabled?<br>threshold not zero}
    checkRootExists{Instance Root<br>Exists?}
    iterateDirs[Iterate Instance<br>Directories]
    checkDefault{LocalDB Managed?<br>MSSQLLocalDB, v11.0}
    checkRegistered{Backed By A<br>Registered Instance?}

    checkResidueOwn{Marked, Or<br>Backlog Pass?}
    checkResidueAge{Untouched For<br>5 Minutes?}
    deleteResidue[Delete Directory]

    checkAge{Untouched For<br>Threshold?<br>default 30 days}
    checkRunning{Running?}
    checkDataDir{Has A Data<br>Directory?}
    checkMarked{Marked As Created<br>By This Library?}
    checkBacklog{Backlog Pass?}
    checkModel{model.mdf<br>Shrunk?}
    removeInstance[Remove Instance:<br>Stop, Delete, and Remove<br>Its LocalDB Directory]

    leave[Leave Alone]
    done[Done]

    start --> checkEnabled
    checkEnabled -->|No| done
    checkEnabled -->|Yes| checkRootExists
    checkRootExists -->|No| done
    checkRootExists -->|Yes| iterateDirs
    iterateDirs --> checkDefault
    checkDefault -->|Yes| leave
    checkDefault -->|No| checkRegistered

    checkRegistered -->|No: residue| checkResidueOwn
    checkResidueOwn -->|No| leave
    checkResidueOwn -->|Yes| checkResidueAge
    checkResidueAge -->|No| leave
    checkResidueAge -->|Yes| deleteResidue
    deleteResidue --> done

    checkRegistered -->|Yes| checkAge
    checkAge -->|No| leave
    checkAge -->|Yes| checkRunning
    checkRunning -->|Yes| leave
    checkRunning -->|No| checkDataDir
    checkDataDir -->|Yes: handled above| leave
    checkDataDir -->|No: orphan| checkMarked
    checkMarked -->|Yes| removeInstance
    checkMarked -->|No| checkBacklog
    checkBacklog -->|No| leave
    checkBacklog -->|Yes| checkModel
    checkModel -->|No| leave
    checkModel -->|Yes| removeInstance

    removeInstance --> done
    leave --> done
Loading

Key behaviors:

  • Triggered once during DirectoryFinder static initialization, immediately after the data root cleanup above.
  • Only reclaims what it created: an instance with no marker is never removed, so an instance belonging to anything else on the machine is left alone. The default instances LocalDB manages itself are always skipped.
  • Marked on start as well as on create: instances that predate marking are picked up the next time they are used.
  • An instance is only an orphan if it has no data directory: while one exists, the data root cleanup above governs when it is reclaimed, so the two never contend.
  • Running instances are skipped: they are likely in use by another process.
  • 30-day threshold for instances, configurable via the LocalDBInstanceCleanupDays environment variable or LocalDbSettings.InstanceCleanupThreshold. Set it to zero to disable the sweep.
  • Residue uses a 5-minute threshold instead. A directory with no registered instance behind it has no instance to race, only the window where LocalDB has created the directory but not yet registered the instance. Using the full threshold would strand any residue not old enough when the backlog pass runs, since that pass only happens once.
  • The backlog pass runs once per machine: instances that predate marking cannot be told apart from instances belonging to anything else, so they get a single best effort pass which takes unmarked residue and unmarked orphans that still carry the shrunk model.mdf this library leaves behind. A fresh instance leaves model.mdf at 8MB, so an instance created by anything else is never matched. That the pass has run is recorded under %LocalAppData%\LocalDb, and only once a sweep actually completes.
  • Never fails a test run: every directory is handled independently and any failure is logged and skipped.

Create SqlDatabase Flow

This happens once per SqlInstance.Build, usually once per test method.

flowchart TD
    entry[Start]

    subgraph openMaster[Open Master Connection]
        checkDbExists{DB Exists?}
        takeOffline[Take DB Offline]
        copyFilesExisting[Copy Data & Log Files]
        setOnline[Set DB Online]
        copyFilesNew[Copy Data & Log Files]
        attachDb[Attach DB]
    end
    openNewConn[Open New Connection]
    returnConn[Return Connection]

    entry
    entry --> checkDbExists

    checkDbExists -->|Yes| takeOffline
    takeOffline --> copyFilesExisting
    copyFilesExisting --> setOnline
    setOnline --> openNewConn

    checkDbExists -->|No| copyFilesNew
    copyFilesNew --> attachDb
    attachDb --> openNewConn

    openNewConn --> returnConn
Loading

Performance

Benchmarks measuring SqlInstance startup performance under different LocalDB states. Results collected using BenchmarkDotNet.

Hardware

  • BenchmarkDotNet v0.15.8, Windows 11 (10.0.26200.7623)
  • AMD Ryzen 9 5900X 3.70GHz, 1 CPU, 24 logical and 12 physical cores
  • .NET SDK 10.0.102

Scenarios

Scenario Description When It Occurs
Cold LocalDB instance does not exist. Full startup from scratch. First run on a machine, or after sqllocaldb delete. Rare in practice.
Stopped LocalDB instance exists but is stopped. Instance is started and existing template files are reused. After LocalDB auto-shutdown (default: 5 min idle) or system restart. Performance similar to Warm/Rebuild. More info
Rebuild LocalDB running, but template timestamp changed. After code changes that modify the buildTemplate delegate's assembly. Common during development. More info
Warm LocalDB running with valid template. Typical test runs when instance is already warm. Most common scenario.

Results

All times in milliseconds.

DBs Cold total Cold per DB Stopped total Stopped per DB Rebuild total Rebuild per DB Warm total Warm per DB
0 6396 - 472 - 85 - 3 -
1 6395 6395 514 514 120 120 40 40
5 6542 1308 641 128 267 53 173 35
10 6705 671 834 83 421 42 402 40
100 10284 103 3900 39 3436 34 3328 33

Key Insights

  • Stopped ≈ Warm/Rebuild: When a stopped instance is detected, the library starts it and reuses the existing template files. This provides ~500ms performance instead of Cold (~6.4s). More info
  • Warm is 2000x faster than Cold: With 0 databases, warm start takes ~3ms vs ~6.4s for cold start. This is the primary optimization the library provides.
  • Rebuild is 75x faster than Cold: When only the template needs rebuilding (code changed), startup is ~85ms vs ~6.4s.
  • Marginal cost per database converges to ~35ms: Regardless of startup scenario, each additional database adds approximately 35ms once the instance is running.
  • At scale, database creation dominates: With 100 databases, Warm/Rebuild/Stopped scenarios converge to similar total times (~3.3-3.9s) because database creation time dominates. Cold remains slower (~10s).
  • Tests re-run after system restart will now benefit from stopped instance reconstitution, avoiding cold start times. More info
  • Minimize databases per test when possible, as each database adds ~35ms overhead

Debugging

To connect to a SqlLocalDB instance using SQL Server Management Studio use a server name with the following convention (LocalDb)\INSTANCENAME.

So for a instance named MyDb the server name would be (LocalDb)\MyDb. Note that the name will be different if a name or instanceSuffix have been defined for SqlInstance.

The server name will be written to Trace.WriteLine when a SqlInstance is constructed. It can be accessed programmatically from SqlInstance.ServerName. See Logging.

SqlLocalDb

The SqlLocalDb Utility (SqlLocalDB.exe) is a command line tool to enable users and developers to create and manage an instance of SqlLocalDB.

Useful commands:

  • sqllocaldb info: list all instances
  • sqllocaldb create InstanceName: create a new instance
  • sqllocaldb start InstanceName: start an instance
  • sqllocaldb stop InstanceName: stop an instance
  • sqllocaldb delete InstanceName: delete an instance (this does not delete the file system data for the instance)

ReSharper Test Runner

The ReSharper Test Runner has a feature that detects spawned processes, and prompts if they do not shut down when a test ends. This is problematic when using SqlLocalDB since the Sql Server process continues to run:

To avoid this error spawned processes can be ignored:

Credits

SqlLocalDB API code sourced from https://github.com/skyguy94/Simple.LocalDb

Icon

Robot designed by Creaticca Creative Agency from The Noun Project.

About

Provides a wrapper around SqlLocalDB to simplify running tests or samples that require a SQL Server Database

Resources

Readme

License

MIT license

Code of conduct

Code of conduct
Activity

Stars

205 stars

Watchers

5 watching

Forks

13 forks
Report repository

Releases 37

24.1.5 Latest
Jul 20, 2026
+ 36 releases

Sponsor this project

 
Learn more about GitHub Sponsors

Contributors

Languages