Attaching A Core Data SQLite Database To A Failed Test

September 2, 2021

In the previous articles we have learned about the following:

Now let's look at how we can attach a Core Data SQLite database to a failing test.

We'll cover these four topics:

  1. Capture when a test fails.
  2. Understand a bit about SQLite WAL and checkpointing.
  3. Force Core Data to perform a SQLite checkpoint.
  4. Attach an NSPersistentStore's SQLite database to the failed test.

Quick Recap of CoreDataTestCase

Let's start with the complete CoreDataTestCase from the previous article.

import CoreData
import XCTest

open class CoreDataTestCase<P: NSPersistentContainer>: XCTestCase {

    public private(set) var persistentContainer: P!

    public var viewContext: NSManagedObjectContext {
        return persistentContainer.viewContext

    open func targetBundle() -> Bundle {
        return .testTargetBundle

    open override func setUpWithError() throws {
        try super.setUpWithError()

        continueAfterFailure = false
        persistentContainer = try .testCasePersistentContainer(
            forModelInBundle: targetBundle()

    open override func tearDownWithError() throws {
        try persistentContainer?.destroyPersistentStore()
        persistentContainer = nil

        try super.tearDownWithError()

Capturing When A Test Fails

Let's add a boolean property named didRecordFailure to capture when a test method fails. The didRecordFailure property is set to true when the test runner executes the record(_:) method.

open class CoreDataTestCase<P: NSPersistentContainer>: XCTestCase {

    . . . 

    private var didRecordFailure: Bool = false

    . . .
    open override func record(_ issue: XCTIssue) {
        didRecordFailure = true

We could attempt to attach the database in the record(_:) method, but we would then be forced to deal with a thrown error, which is just awkward. The didRecordFailure flag allows the tearDownWithError() to try attaching the database and re-throwing any error to the test runner.

Attaching The SQLite Database To A Failed Test

Now let's read the didRecordFailure flag in the tearDownWithError() method. If a test failure is recorded, then the SQLite database is attached to the failed test method using an XCTAttachment.

     // CoreDataTestCase

    open override func tearDownWithError() throws {
        if didRecordFailure {
            try attachSQLiteDatabaseToFailedTest()

        try persistentContainer?.destroyPersistentStore()
        persistentContainer = nil

        try super.tearDownWithError()

    private func attachSQLiteDatabaseToFailedTest() throws {
        if let persistentContainer = persistentContainer {
            let sqliteFileURL = try persistentContainer.checkpointPersistentStore()
            let attachment = XCTAttachment(contentsOfFile: sqliteFileURL) 

Another option is to compress the contents of the SQLite database directory using XCTAttachment/init(compressedContentsOfDirectory:). However, that method, as of Xcode 13, is only available on macOS. Therefore, if you're targeting iOS, you'll need to use the checkpoint technique described in this section.

The attachSQLiteDatabaseToFailedTest() method creates an XCTAttachment with the contents of the NSPersistentStore SQLite database file. However, you'll notice that there's a call to a method on NSPersistentContainer named checkpointPersistentStore(). The checkpointPersistentStore() is a test case only extension used to force SQLite to move all transactions in the SQLite WAL file back to the main SQLite database file. The process of moving WAL file transactions into the the database is known as checkpointing.

Let's Talk About WAL and Checkpointing

Starting way back in iOS 7 and OS X Mavericks, Core Data started using the SQLite WAL (write-ahead-log) journaling mode. This means that transactions are first written to the WAL file, and then later moved to the main database file. Normally, as users of Core Data, we don't care about this detail. However, in order to correctly capture the SQLite database file for debugging a failed test, we need a way to tell SQLite to checkpoint the database.

If the database is not checkpointed, then you'll end up with missing or unexpected records in the captured database.

Here's what your Core Data SQLite directory may look like:


<persistent-container-name> ls -la
drwxr-xr-x  6 <your_user_name>      192 Sep  1 20:29 .
drwxr-xr-x  4 <your_user_name>      128 Sep  1 20:30 ..
drwxr-xr-x  3 <your_user_name>       96 Sep  1 20:29 .Station_Station_SUPPORT
-rw-r--r--  1 <your_user_name>   319488 Sep  1 20:29 Station_Station.sqlite
-rw-r--r--  1 <your_user_name>    32768 Sep  1 20:29 Station_Station.sqlite-shm
-rw-r--r--  1 <your_user_name>  3254832 Sep  1 20:29 Station_Station.sqlite-wal

The goal is to flush the WAL file by checkpointing the database prior to attaching the database to a failed test.

According to the SQLite WAL documentation, a checkpoint happens when a COMMIT statement executes that causes the WAL file to be 1000 pages or more in size, or when the last database connection on a database file closes.

Let's lean on closing the database connection to perform the checkpoint since that is a predictable operation.

Checkpointing the SQLite Database

import XCTest

extension NSPersistentContainer {

    func checkpointPersistentStore() throws -> URL  {
        try persistentStoreCoordinator.performAndWait {
            let persistentStore = try unwrapPersistentStore()
            try persistentStoreCoordinator.remove(persistentStore)
            try loadPersistentStoreOrFail()
            return try unwrapPersistentStoreURL()

We can close the database connection by removing the NSPersistentStore from the NSPersistentStoreCoordinator. If you step through this code using the debugger, then you'll notice the WAL file size drop to zero and SQLite database increase in size when the persistent store is removed.

According to this Apple Tech Note, the preferred way to force Core Data to checkpoint a SQLite database is to add the SQLite database to another NSPersistentStoreCoordinator using journal_mode=DELETE. I was never able to get this to work. Perhaps I am missing something. Either way, removing the persistent store, which closes the database connection, works well to force a checkpoint for the purposes of capturing and attaching the database to a failed test.

Debugging the SQLite Database

Here's an example showing an attached SQLite database on a failed test.

Xcode Test Result

You can now save off the SQLite database and open it using your favorite database tool.

We'll use sqlite3 to show how to open the database. For more advanced debugging there is DataGrip from JetBrains.

➜ sqlite3 Station_Station_1_F1D0102A-0809-4F9D-8FC8-F06B734ECBA7.sqlite 
SQLite version 3.32.3 2020-06-18 14:16:19
Enter ".help" for usage hints.

You now have full access to explore the contents of the database.

Wrapping Up

The past four articles have focused on helping you prepare to write unit tests that exercise production code that uses Core Data and a SQLite NSPersistentStore.