How Apollo Save Tool Leverages libSQLite for PS4 Save Database Operations

Apollo Save Tool uses a custom in-memory VFS extension called memvfs to load SQLite databases entirely into RAM, allowing safe read-write operations on PS4 save data before atomically flushing changes back to disk via sqlite3_memvfs_dump.

Apollo Save Tool is an open-source PS4 homebrew application that manages save-game files, trophies, and application metadata. To handle the complex relational data stored by the PS4 system, the tool leverages the libSQLite library through a specialized wrapper implemented in source/sqlite_db.c. This implementation uses a custom virtual file system to enable safe in-memory editing of database files before committing changes to the console's storage.

The memvfs Extension for In-Memory Database Editing

At the core of Apollo's database handling is a custom SQLite VFS (Virtual File System) named orbis_rw. This VFS is provided by the memvfs extension, which is initialized at runtime before any database operations occur.

In source/sqlite_db.c, the initialization code registers the VFS:

if (sqlite3_memvfs_init("orbis_rw") != SQLITE_OK_LOAD_PERMANENTLY) {
    LOG("Error loading extension: %s", "memvfs");
    return NULL;
}

The memvfs extension allows SQLite to treat a block of memory as a database file. This is critical for the PS4 environment because it enables the tool to load an entire database into RAM, perform multiple read and write operations safely, and only write the final result back to the filesystem once. This approach prevents corruption of the original database file if an operation is interrupted.

Opening Databases with open_sqlite_db

The open_sqlite_db function in source/sqlite_db.c serves as the primary entry point for loading PS4 database files. This function orchestrates the process of reading the file from disk, registering it with the memvfs VFS, and opening it as an in-memory SQLite database.

The process follows these steps:

1. Read the file into memory – The function first loads the raw database file into a dynamically allocated buffer using read_buffer:

// Read file into db_buf, get db_size
if (!read_buffer(db_path, &db_buf, &db_size)) {
    LOG("Error reading database file: %s", db_path);
    return NULL;
}

2. Construct the memvfs URI – A special URI is formatted to tell SQLite where the database lives in memory:

char *memuri = sqlite3_mprintf(
    "file:memdb?ptr=0x%p&sz=%lld&freeonclose=1",
    db_buf, db_size);

The ptr parameter points to the buffer, sz specifies the size, and freeonclose=1 ensures the buffer is freed when the database connection closes.

3. Open the database – The connection is opened using the memvfs VFS:

sqlite3_open_v2(memuri, &db,
                SQLITE_OPEN_READWRITE | SQLITE_OPEN_URI,
                "memvfs");

4. Optimize for memory – Since the entire database resides in RAM and will be saved atomically later, journaling is disabled to improve performance:

sqlite3_exec(db, "PRAGMA journal_mode = OFF;", NULL, NULL, NULL);

Querying and Modifying Save Data

Once the database is open in memory, Apollo Save Tool uses standard SQLite C API functions to query and update PS4 metadata. The tool handles application information, trophy data, and downloadable content records.

Reading Application Metadata

To retrieve human-readable game titles from the Appinfo database, the tool uses complex prepared statements. The get_appdb_title function constructs a query that checks multiple conditions to find the correct title:

char* query = sqlite3_mprintf(
    "SELECT titleId, val FROM tbl_appinfo WHERE key='TITLE' AND (titleId = %Q "
    "OR titleId = (SELECT titleId FROM tbl_appinfo WHERE key='INSTALL_DIR_SAVEDATA' AND val = %Q))",
    titleid, titleid);
sqlite3_prepare_v2(db, query, -1, &res, NULL);
if (sqlite3_step(res) == SQLITE_ROW) {
    strncpy(name, (const char*)sqlite3_column_text(res, 1), ORBIS_SAVE_DATA_TITLE_MAXSIZE);
}

This query handles cases where the title might be stored under different keys or referenced through install directory mappings.

Inserting New Records

For adding new entries to the Appinfo database, the tool uses sqlite3_mprintf to safely format SQL strings and sqlite3_exec for execution. The insert_appinfo_row helper demonstrates this pattern:

char* query = sqlite3_mprintf(
    "INSERT OR IGNORE INTO tbl_appinfo(titleId, key, val) VALUES(%Q, %Q, %Q)",
    titleId, key, value);
sqlite3_exec(db, query, NULL, NULL, NULL);
sqlite3_free(query);

The INSERT OR IGNORE clause prevents duplicate entry errors when rebuilding the database.

Updating Trophy States

Trophy manipulation requires atomic updates across multiple tables. The trophy_unlock and trophy_lock functions construct multi-statement strings that update the flag status, timestamps, and progress statistics in a single sqlite3_exec call:

char* query = sqlite3_mprintf(
    "UPDATE tbl_trophy_flag SET (visible, unlocked, time_unlocked, ... ) = ..."
    "UPDATE tbl_trophy_title SET (progress, unlocked_trophy_num, %s) = ..."
    "UPDATE tbl_trophy_group SET (progress, unlocked_trophy_num, %s) = ...",
    …);
sqlite3_exec(db, query, NULL, NULL, NULL);

These statements use SQLite's strftime function to generate ISO-8601 timestamps for trophy unlock times.

Committing Changes to Disk

After all modifications are complete in memory, the tool must persist the changes back to the PS4's filesystem. This is handled by save_sqlite_db and the sqlite3_memvfs_dump function.

The process is straightforward:

if (sqlite3_memvfs_dump(db, NULL, db_path) != SQLITE_OK) {
    LOG("Error saving database: %s", sqlite3_errmsg(db));
    return 0;
}

The NULL schema argument instructs the function to dump the entire database. This atomic write operation ensures that the on-disk file is only updated once all transactions are complete, preventing database corruption from partial writes or power failures during the editing process.

Practical Implementation Examples

Opening a Database and Retrieving a Game Title

This example demonstrates the complete workflow for loading an Appinfo database and fetching a human-readable title:

void *db = open_sqlite_db("/user/appmeta/APP00/app.db");
char title[64];
if (get_appdb_title(db, "CUSA12345", title)) {
    LOG("Game title: %s", title);
}
sqlite3_close((sqlite3 *)db);

This pattern uses open_sqlite_db to initialize the memvfs-backed connection, get_appdb_title for the prepared-statement execution, and standard sqlite3_close for cleanup.

Adding a New DLC Entry

When registering new downloadable content, the tool follows a consistent pattern of formatting, execution, and atomic saving:

sqlite3 *db = (sqlite3 *)open_sqlite_db("/user/addcont/addcont.db");
char *query = sqlite3_mprintf(
    "INSERT OR IGNORE INTO addcont(title_id, dir_name, content_id, title, version, attribute, status) "
    "VALUES(%Q, %Q, %Q, %Q, 536870912, '01.00', 0)",
    "CUSA12345", "DLCDIR5678", "CONTENT123456", "Awesome DLC");
sqlite3_exec(db, query, NULL, NULL, NULL);
sqlite3_free(query);
sqlite3_memvfs_dump(db, NULL, "/user/addcont/addcont.db");
sqlite3_close(db);

This demonstrates the typical sqlite3_mprintf → sqlite3_exec → sqlite3_memvfs_dump workflow used throughout Apollo Save Tool for persistent database modifications.

Summary

• Apollo Save Tool leverages libSQLite through a custom wrapper in source/sqlite_db.c to manage PS4 save-game metadata, trophy data, and application information.
• The tool uses the memvfs virtual file system extension to load entire databases into RAM, enabling safe in-memory editing without risking corruption of the original on-disk files.
• Database connections are opened using sqlite3_open_v2 with the memvfs VFS, after formatting a special URI that points to the memory buffer containing the database file.
• All queries utilize standard SQLite C API functions: sqlite3_prepare_v2 for SELECT statements, sqlite3_mprintf for safe SQL string formatting, and sqlite3_exec for INSERT/UPDATE operations.
• Changes are persisted atomically using sqlite3_memvfs_dump, which writes the in-memory database back to the PS4 filesystem only after all modifications are complete.

Frequently Asked Questions

How does Apollo Save Tool prevent database corruption during editing?

Apollo Save Tool prevents corruption by using the memvfs virtual file system to load the entire database into RAM before opening it with SQLite. All read and write operations occur in memory, and the original on-disk file remains untouched until sqlite3_memvfs_dump is called to atomically write the complete modified database back to storage. This ensures that partial writes or power failures during editing do not corrupt the original database file.

What is the purpose of the orbis_rw VFS in sqlite_db.c?

The orbis_rw VFS is a custom SQLite virtual file system registered by the memvfs extension specifically for the PS4 (Orbis) platform. It enables SQLite to treat a block of memory as a database file, allowing Apollo Save Tool to perform high-speed in-memory database operations. The VFS is initialized via sqlite3_memvfs_init("orbis_rw") before any database connections are established.

How does the tool handle complex queries like retrieving game titles?

For complex queries, Apollo Save Tool uses sqlite3_mprintf to safely format SQL strings with proper escaping, followed by sqlite3_prepare_v2 to create prepared statements. For example, when retrieving a game title from tbl_appinfo, the tool constructs a query that checks multiple key types and title ID mappings, then steps through the results with sqlite3_step and extracts text using sqlite3_column_text.

Why does Apollo Save Tool disable SQLite journaling?

The tool disables journaling by executing PRAGMA journal_mode = OFF; immediately after opening the database. Since the entire database operates in memory via the memvfs VFS, traditional disk-based journaling is unnecessary and would add overhead. The atomic write performed by sqlite3_memvfs_dump at the end of the session serves as the durability mechanism, ensuring data integrity without the need for rollback journals.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}