Skip to content

SQLite API

Jump to bottom
Nate Reprogle edited this page Jan 20, 2026 · 4 revisions

Using ByteLib's SQLite API

ByteLib comes with an extremely opinionated yet extensible SQLite wrapper, abstracting away so much boilerplate it'd make your head spin! ByteLib was designed to look and feel like an ORM without taking away the power of raw SQL. All examples in here will be from the DimensionPause plugin.

To get started, in your plugin's wiring class, add SqliteModule() to your modules() function.

public final class DimensionPausePlugin extends ByteLibPlugin {

    @Inject
    public DimensionPausePlugin(Injector injector, PluginMeta meta, Path dataDir, ComponentLogger logger) {
        super(injector, meta, dataDir, logger);
    }

    @SuppressWarnings("unused")
    public static class Wiring implements PluginWiring {
        @Override
        public List<Module> modules(PluginMeta meta, Path dataDir, ComponentLogger logger) {
            return List.of(
                    new DimensionPauseModule(),
                    new SqliteModule("dimensionpause.db")
            );
        }
    }
}

new SqliteModule() takes between 1 and 2 parameters. The first is the name of your DB file, and the second is any options you want to provide. The second parameter is a SqliteConfig record. More info surrounding this will be documented later, but for now know that sane and safe defaults are used when omitted.

Once registered, you may inject a SqliteDatabase object where needed like so: @Inject SqliteDatabase db;

Utilizing SqliteDatabase

SqliteDatabase houses convenient helper functions for basic CRUD operations (insert, update, delete, selectAll, and selectWhere), but also exposes type-safe functions that allow raw SQL usage (execute, transaction, query, queryOne, executeAsync, and queryAsync).

You should first define a contract for the data you're storing. This is simply just a DTO

public record TrackedWorld(String world, World.Environment environment, boolean enabled, Instant expiresAt) {
}

Now that your object has been created, you may also optionally create a Table if you plan on just using the basic CRUD operations. These are nice as they abstract away raw SQL, but do not allow as much customization.

public final class Tables {
    public static final Table WORLDS = Table.of("worlds");
    public static final Table.Column<String> world = WORLDS.col("world", SqlType.TEXT);
    public static final Table.Column<World.Environment> dimension = WORLDS.col("dimension", PaperSqlTypes.ENV);
    public static final Table.Column<Boolean> enabled = WORLDS.col("enabled", SqlType.BOOLEAN);
    public static final Table.Column<String> updatedAt = WORLDS.col("updatedAt", SqlType.TEXT);
    public static final Table.Column<Instant> expiresAt = WORLDS.col("expiresAt", PaperSqlTypes.INSTANT);

    private Tables() {}
}

Notice how the WORLDS Table has an updatedAt column while the record does not. Your record does not necessarily need to contain all columns your table will have unless you plan on SELECTing these. Only columns that will be returned in ResultSet's will need to be listed in your record. For the CRUD operations, you must list all columns in your Table

Finally, let's do some querying! Let's do a basic query for if a world's dimension is enabled

WorldPauseStatus status = db.queryOne("""
                        SELECT enabled, expiresAt
                        FROM dimensionpause_worlds
                        WHERE world = ? AND dimension = ?;
                        """,
                row -> new WorldPauseStatus(
                        row.bool("enabled"),
                        row.get("expiresAt", PaperSqlTypes.INSTANT)
                ),
                Param.text(world), Tables.dimension.param(dimension));

Okay, so what is going on here? Well, we're not using CRUD operations because I don't want the entire table returned to me. So, I'm using the queryOne function. I pass in raw SQL in the first argument, and the second argument is a RowMapper. RowMapper's essentially tell the API how to convert the ResultSet to an object. row refers to Row, a super tiny lightweight wrapper around a ResultSet that tells SqliteDatabase how to parse a column.

You may have noticed a few things here:

  • row.get("expiresAt", PaperSqlTypes.INSTANT)
  • Param.text(world)
  • Tables.dimension.param(dimension)

Let's go over these:

  1. row.get() is a method that allows you to define a custom type when parsing a ResultSet column for a row. PaperSqlTypes is a custom type in DimensionPause that contains a bunch of static SqlType<T>'s. SqlType<T> is an interface that defines a bind method and a read method. The bind method writes to the DB, while the read method reads the ResultSet and converts it to the concrete type. There are already built-in SqlType<T>'s for int, long, double, String, byte[], UUID, and Boolean (Note that Boolean is an Integer in SQLite, so be sure to set up your boolean columns as INT) as well as associated methods for row (row.string, row.i32, row.i64, row.f64, etc.), but you can of course create your own
  2. Param.text(world) is similar to row.get(), except it works on write instead of on read. It's meant to explicitly bind parameters to a concrete type. If Param doesn't contain a type for your data, you can create your own by using SqlType<T> as well, and then calling Param.of(SqlType<T>, value). For example, to write an Instant instead of read one like in the previous example, you can do Param.of(PaperSqlTypes.INSTANT, expiresAt)
  3. Tables.dimension.param(dimension) is the exact equivalent of Param.of(PaperSqlTypes.ENV, dimension). Table.Column<T> contains a method param(T value) which returns a Param<T> of the column. This is useful for if you already have the type defined and you don't want to type Param.of everywhere, and is also necessary when using the CRUD operators (More on that in a sec)

Custom Data Types using SqlType<T>

If you want to create a custom type, create a variable that is typed as SqlType<T>, where T is your custom data type, then set it to a new SqlType<>() and implement the bind and read functions. Here is how DimensionPause implements the SqlType for Instant, while supporting nullability as well

public static final SqlType<Instant> INSTANT = new SqlType<>() {
        public void bind(PreparedStatement ps, int index, Instant value) throws SQLException {
            if (value != null) ps.setLong(4, value.toEpochMilli());
            else ps.setNull(4, Types.BIGINT);
        }

        public @Nullable Instant read(ResultSet rs, String column) throws SQLException {
            Object raw = rs.getObject(column);
            Long expiresAtMs = (raw instanceof Number n) ? n.longValue() : null;
            return expiresAtMs != null ? Instant.ofEpochMilli(expiresAtMs) : null;
        }
    };
}

If you couldn't yet tell, Instant is backed by the BIGINT type in SQLite. bind writes a long to the DB, and read converts it back to an Instant, or returns null if the column is null.

Using the basic CRUD methods

This is where Tables REALLY shine! Using the basic operators is extremely simple. DimensionPause is not using insert simply because it uses custom SQL to handle conflicts (essentially upsert), but if it was to insert a record using insert, it'd be as simple as doing this

db.insert(
    WORLDS,
    Map.of(
        WORLD, "world",
        DIMENSION, World.Environment.NORMAL,
        ENABLED, true,
        EXPIRES_AT, null
    )
);

Because we already defined the concrete types for the WORLDS table, SqliteDatabase can take the Map we provided, convert each of those values to their SQLite-compatible types, and write to the correct table. That's it, it's so stupidly easy! Let's say you want to query, it's just as easy

List<WorldPauseStatus> statuses = db.query(
    WORLDS,
    row -> new WorldPauseStatus(
        row.bool("enabled"),
        row.get("expiresAt", PaperSqlTypes.INSTANT)
    )
);

Need to filter your query? Use selectWhere

List<WorldPauseStatus> statuses = db.selectWhere(
    WORLDS,
    "world = ?",
    row -> new WorldPauseStatus(
        row.bool("enabled"),
        row.get("expiresAt", PaperSqlTypes.INSTANT)
    ),
    Param.text("world")
);

The API handles type conversion automatically, while still requiring you to write some manual SQL. It's not meant to be an ORM, but it is made to simplify your life a little bit

Migrations

The last big feature the SQLite API helps with is migrations. Migrations don't happen automatically, but the API provides methods for doing so, and it is extremely simple to wire this up. First, create a List of MigrationStep objects. Each object consists of the DB version and a transaction. Note that this example is NOT from DimensionPause, as that plugin does not have any migrations yet. Rather, this is just a conceptual migration

List<MigrationStep> steps = List.of(
    new MigrationStep(1, tx -> {
        tx.execute("""
            CREATE TABLE IF NOT EXISTS regions (
                id INTEGER PRIMARY KEY,
                name TEXT NOT NULL
            );
        """);
    }),
    new MigrationStep(2, tx -> {
        tx.execute("""
            CREATE VIRTUAL TABLE IF NOT EXISTS region_index
            USING rtree(id, minX, maxX, minZ, maxZ);
        """);
    })
);

This migration on version 1 would create a regions table, and on version 2 would create a regions index using rtree. Once you have this list of MigrationSteps created, initialize a UserVersionMigrator and call its migrate method, providing your instance of SqliteDatabase as its sole parameter

new UserVersionMigrator(steps).migrate(db);

The migrator will automatically look at your DB's version and run the migrations that need to be ran. The version is stored in the user_version PRAGMA, so it is recommended not to manually change this value ever.

Clone this wiki locally