Custom Data Types

By now we've used Param and Row quite a bit. If you utilized Table, then you also used SqlType

However, as your plugin gets more advanced, you may find yourself needing to store data that does not have an equivalent built into the SQLite API. This guide will walk you through creating your own data type.

Defining a Custom Type

A custom type is simply an implementation of SqlType. You may store these implementations anywhere, but it's recommended to keep them in their own class for modularity's sake. Just like Table, it's also recommended to make these objects static, as they do not store state and are not instantiated.

To create a custom SqlType, first decide what type you want to implement. For this example, we'll create a custom type that handles Instant objects. Let's go ahead and create the entire type now, then break it down piece by piece. Here is the custom type for an Instant:

            public class CustomTypes {
                public static final SqlType<Instant> INSTANT = new SqlType<>() {
                    public void bind(PreparedStatement ps, int index, Instant value) throws SQLException {
                        if (value != null) ps.setLong(index, value.toEpochMilli());
                        else ps.setNull(index, 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;
                    }
                };
            }
        

Breaking It Down

A SqlType must contain two methods:

public void bind(PreparedStatement ps, int index, Instant value) throws SQLException;
public T read(ResultSet rs, String column) throws SQLException;

bind(): The bind() method is the method that Param calls under the hood, and is responsible for converting the Java object's value to the underlying SQLite data type and binding it to the placeholder at a given index.
read(): The read() method is the method that Row calls under the hood, and it does the exact opposite of bind(). It is responsible for reading the value of the object in the ResultSet in the given column, and converting it to the respective Java object.
This method is not normally annotated with @Nullable. The above SqlType has been written to support nullability, and therefore its methods have been written to support null values. This is another benefit of using custom types: being able to support fallback values.

Using a Custom Type

Now that we have our custom types created, we can use them wherever we need. Custom data types are supported with Row, Param, and Table.Column<T>

Use your custom data type in rows when you want to query data and convert a column to your custom type. A special helper method, Row#get() exists specifically for this purpose.

                    Instant instant = db.queryOne("""
                            SELECT updatedAt
                            FROM some_table
                            ORDER BY updatedAt DESC;
                        """,
                        row -> row.get("updatedAt", CustomTypes.INSTANT));
                

The SQLite API will handle calling CustomTypes.INSTANT.read() when it converts the column to an object

Use your custom data type in params when you want to query the database by a custom data type. A special helper method, Param#of() exists to fulfill this use case.

                    Instant instant = db.queryOne("""
                            SELECT updatedAt
                            FROM some_table
                            WHERE updatedAt < ?
                            ORDER BY updatedAt DESC;
                        """,
                        row -> row.get("updatedAt", CustomTypes.INSTANT),
                        Param.of(CustomTypes.INSTANT, Instant.now().minus(1, ChronoUnit.DAYS));
                

The SQLite API will handle calling CustomTypes.INSTANT.bind() when it converts the value provided in Param#of() to the respective SQLite datatype.

The last way to use a custom type is with Table Columns. This replaces the need to use Param#of(), but does not replace the need to use Row#get().

                    Table SOME_TABLE = Table.of("some_table");
                    Table.Column<Instant> updatedAt = SOME_TABLE.col("updatedAt", CustomTypes.INSTANT);
                    Instant instant = db.queryOne("""
                            SELECT updatedAt
                            FROM some_table
                            WHERE updatedAt < ?
                            ORDER BY updatedAt DESC;
                        """,
                        row -> row.get("updatedAt", CustomTypes.INSTANT),
                        updatedAt.param(Instant.now().minus(1, ChronoUnit.DAYS));
                

As previously discussed in the Convenience Methods guide, the Table.Column<T>#param() method will return the correct Param object for the column.

11 March 2026