SQLiteLib

SQLiteLib
Simple, safe and fast SQLite storage for your plugins. Ditch those YML files!

GitHub | JitPack | Discord | Report a bug

What is SQLiteLib?
Many of you used my tutorial How to: SQLite, and plenty of people found the JDBC code hard to get right. SQLiteLib does all of that for you. Your plugin gets a real database in a few lines, without the boilerplate, connection bugs or SQL injection.

Features

• Parameterized queries: no string building, no SQL injection.
• Typed results: getInt, getUUID, getBoolean, getEnum, or map rows straight to your own objects.
• Async API: every database has its own worker thread, so the main thread never waits on disk.
• Transactions & batch inserts: atomic changes and fast bulk writes.
• Schema migrations: versioned table changes that only run once.
• Key/value stores: a drop-in replacement for data.yml files, with atomic counters.
• Live backups: safe while the server is running.
• Fast defaults: WAL mode, foreign keys, busy timeout.
• Admin commands: inspect, query, back up and vacuum databases in game or from the console.
• Lightweight: no extra downloads. Spigot and Paper already include the SQLite driver.

Requirements

• Java 17+
• Spigot or Paper 1.18+

Commands
Base command: /sqlite (alias /sl)

• /sl list: list loaded databases with size and owner
• /sl info <db>: file, size, schema version, tables and row counts
• /sl query <db> <sql>: run a read-only query and show the rows
• /sl queryvalue <db> <column> <sql>: show a single value
• /sl queryrow <db> <column> <sql>: show one column for every row
• /sl execute <db> <sql>: run any statement
• /sl init <db> [sql]: create/load a database in SQLiteLib's folder
• /sl backup <db>: live backup to plugins/SQLiteLib/backups
• /sl vacuum <db>: compact the database file

Permissions

• sqlite.use: all commands
• sqlite.command.read: list, info, query, queryvalue, queryrow
• sqlite.command.write: execute
• sqlite.command.admin: init, backup, vacuum

All permissions default to op.

Storage
Each plugin's databases are stored in that plugin's own folder (for example plugins/MyPlugin/data.db). Databases created with /sl init are stored in plugins/SQLiteLib. You can open any of them with tools like DB Browser for SQLite.

For developers

Adding the dependency
Add SQLiteLib via JitPack, and add depend: [SQLiteLib] to your plugin.yml.

Maven:

<repository>
    <id>jitpack.io</id>
    <url>https://jitpack.io</url>
</repository>

<dependency>
    <groupId>com.github.pablo67340</groupId>
    <artifactId>SQLiteLib</artifactId>
    <version>2.0.0</version>
    <scope>provided</scope>
</dependency>

Gradle:

repositories { maven("https://jitpack.io") }
dependencies { compileOnly("com.github.pablo67340:SQLiteLib:2.0.0") }

Quick start

private Database db;

@Override
public void onEnable() {
    SQLiteLib sqlite = SQLiteLib.hookSQLiteLib();
    db = sqlite.open(this, "data"); // plugins/MyPlugin/data.db

    db.migrate(
        // version 1
        "CREATE TABLE homes (owner TEXT NOT NULL, name TEXT NOT NULL, world TEXT NOT NULL, "
            + "x REAL, y REAL, z REAL, PRIMARY KEY (owner, name))",
        // version 2 - added in a later update, only runs on servers that don't have it yet
        "ALTER TABLE homes ADD COLUMN created INTEGER NOT NULL DEFAULT 0"
    );
}

SQLiteLib closes every database cleanly when the server shuts down.

Queries
Always pass values as ? parameters. UUIDs, enums and booleans are converted for you.

db.update("INSERT OR REPLACE INTO homes (owner, name, world, x, y, z) VALUES (?, ?, ?, ?, ?, ?)",
        player.getUniqueId(), "base", loc.getWorld().getName(), loc.getX(), loc.getY(), loc.getZ());

for (Row row : db.query("SELECT * FROM homes WHERE owner = ?", player.getUniqueId())) {
    String name = row.getString("name");
    double x = row.getDouble("x");
}

Optional<Row> home = db.queryFirst("SELECT * FROM homes WHERE owner = ? AND name = ?", uuid, "base");
long count = db.queryScalar("SELECT COUNT(*) FROM homes WHERE owner = ?", Long.class, uuid).orElse(0L);

Async

db.queryAsync("SELECT name FROM homes WHERE owner = ?", player.getUniqueId())
  .thenAcceptAsync(rows -> player.sendMessage("You have " + rows.size() + " homes"),
          SQLiteLib.mainThread(this));

Transactions

db.transaction(tx -> {
    tx.update("UPDATE balances SET coins = coins - ? WHERE uuid = ?", 100, from);
    tx.update("UPDATE balances SET coins = coins + ? WHERE uuid = ?", 100, to);
}); // both happen, or neither does

Key/value stores

KeyValueStore coins = db.keyValue("coins");
String key = player.getUniqueId().toString();

long balance = coins.increment(key, 50); // atomic, returns the new value
coins.set("motd", "Welcome!");
String motd = coins.getString("motd", "");

Backups & maintenance

db.backup(new File(getDataFolder(), "backups/data.db")); // safe while running
db.vacuum();
db.getTables();
db.columnExists("homes", "created");

The full documentation is in the README on GitHub.

Upgrading from 1.x
Plugins built against 1.x keep working without changes. The old methods (initializeDatabase, getDatabase, executeStatement, queryValue, queryRow, queryMultipleRows) are still there and have been fixed. The query methods are deprecated in favour of the safer parameterized ones.

Suggestions & Support
Found a bug or have an idea? Post in the discussion tab, open an issue, or join the Discord:
https://discord.gg/wm6zMzdBwf