Using a database

The framework provides several JDBC abstractions, statement logging, reporting long transactions and officially supports PostgreSQL and H2 (in PostgreSQL compatibility mode).

Info

Some features requires a database, such as components and paginators.

Creating a ConnectionSupplier

A ConnectionSupplier is responsible for providing connections to your database, as well as some metadata.

Requesting the connection supplier service is not recommended, you should instead use Database (Kotlin) and BlockingDatabase (Java).

If you wish not to use these abstractions, you can use their fetchConnection methods, to at least take advantage of statement logging and long transaction reports.

We'll also use HikariCP, a connection pool that will reduce latency a lot when often reacquiring connections.

Creating a datasource requires implementing HikariSourceSupplier, where you can directly give the connection details, that's it.

Example

PostgreSQLH2

KotlinJava

@BService
class DatabaseSource(config: Config) : HikariSourceSupplier {
    override val source = HikariDataSource(HikariConfig().apply {
        // Suppose those are your config values
        jdbcUrl = config.databaseConfig.url
        username = config.databaseConfig.user
        password = config.databaseConfig.password

        // At most 2 JDBC connections, suspends the coroutine if all connections are used
        maximumPoolSize = 2
        // Emits a warning and does a thread/coroutine dump after the duration
        leakDetectionThreshold = 10.seconds.inWholeMilliseconds
    })
}

@BService
public class DatabaseSource implements HikariSourceSupplier {
    private final HikariDataSource source;

    public DatabaseSource(Config config) {
        final var hikariConfig = new HikariConfig();
        // Suppose those are your config values
        hikariConfig.setJdbcUrl(config.getDatabaseConfig().getUrl());
        hikariConfig.setUsername(config.getDatabaseConfig().getUser());
        hikariConfig.setPassword(config.getDatabaseConfig().getPassword());

        // At most 2 JDBC connections, the database will block if all connections are used
        hikariConfig.setMaximumPoolSize(2);

        // Emits a warning and does a thread/coroutine dump after the duration (in milliseconds)
        hikariConfig.setLeakDetectionThreshold(10000);

        source = new HikariDataSource(hikariConfig);
    }

    @NotNull
    @Override
    public HikariDataSource getSource() {
        return source;
    }
}

PostgreSQL connection URL

The URL should be similar to jdbc:postgresql://[HOST]:[PORT]/[DB_NAME], by default, the port is 5432.

KotlinJava

@BService
class DatabaseSource : HikariSourceSupplier {
    override val source = HikariDataSource(HikariConfig().apply {
        // Create an in-file database with the PostgreSQL compatibility mode
        jdbcUrl = "jdbc:h2:./MyBotDatabase;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;DEFAULT_NULL_ORDERING=HIGH"

        // At most 2 JDBC connections, suspends the coroutine if all connections are used
        maximumPoolSize = 2
        // Emits a warning and does a thread/coroutine dump after the duration
        leakDetectionThreshold = 10.seconds.inWholeMilliseconds
    })
}

@BService
public class DatabaseSource implements HikariSourceSupplier {
    private final HikariDataSource source;

    public DatabaseSource() {
        final var hikariConfig = new HikariConfig();
        // Create an in-file database with the PostgreSQL compatibility mode
        hikariConfig.setJdbcUrl("jdbc:h2:./MyBotDatabase;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;DEFAULT_NULL_ORDERING=HIGH");

        // At most 2 JDBC connections, the database will block if all connections are used
        hikariConfig.setMaximumPoolSize(2);

        // Emits a warning and does a thread/coroutine dump after the duration (in milliseconds)
        hikariConfig.setLeakDetectionThreshold(10000);

        source = new HikariDataSource(hikariConfig);
    }

    @NotNull
    @Override
    public HikariDataSource getSource() {
        return source;
    }
}

This will create a database stored in a file called MyBotDatabase, in the current working directory, see more details on the H2 website.

Using migration

The framework's tables may be automatically created and migrated on updates, while the migration scripts uses a naming scheme compatible with Flyway, it may work with other migration libraries.

Using ServiceLoader-based libraries

Libraries like Flyway, PostgreSQL and SLF4J, uses a ServiceLoader which requires having files in your JAR to load these services.

While creating your JAR, your build tools needs to be configured to merge those files properly, as it would overwrite those files when multiple libraries target a common interface, causing missing services.

You can see how it's done in this Setup section.

Migration using Flyway

You can run this after creating your database:

Flyway.configure()
    .dataSource(source) // Your already existing data source
    .schemas("bc") // The name of the internal schema
    .locations("bc_database_scripts") // Where the migration scripts are located
    .validateMigrationNaming(true)
    .loggers("slf4j") // Both JDA and BC logs using SLF4J
    .load()
    .migrate() // Create or update existing schema

Tip

You can also use the same code to migrate your own database, using similar migration scripts.

Configuration

Logging statements

SQL Statements can be logged if:

• BDatabaseConfig#logQueries is enabled, and the logger of the class that created the prepared statement has its TRACE logs enabled
• Or, BDatabaseConfig#queryLogThreshold is configured, and the logger of the class that created the prepared statement has its WARN logs enabled

Ignoring utility classes creating prepared statements

If you are creating a prepared statement in a class unrelated to what actually uses the statement, the queries will be logged using the unrelated class.

The logger used is the first class in the call stack that's not annotated with @IgnoreStackFrame, you may use this annotation on your class, making the logger use the caller class.

You can alternatively use withLogger to manually set the logger of a prepared statement.

Report long transactions

A thread/coroutine dump can be created when the connection has a longer lifetime than expected, refer to BDatabaseConfig#dumpLongTransactions for more details.

Sample usages

Running a statement, returning a value

KotlinJava

        val tagCount: Long = database.preparedStatement("SELECT count(*) FROM tag") {
            val dbResult = executeQuery().read() //Read a single row
            dbResult[1] // Indexes start at 1
        }

        final long tagCount = database.withStatement("SELECT count(*) FROM tag", statement -> {
            return statement.executeQuery().read() //Read a single row
                    .getLong(1); // Indexes start at 1
        });

Running a statement, returning rows

KotlinJava

        val tagNames: List<String> = database.preparedStatement("SELECT name FROM tag") {
            // Reads all rows and convert them to strings (type inference with List<String>)
            executeQuery().map { it["name"] }
        }

        final List<String> tagNames = database.withStatement("SELECT name FROM tag", statement -> {
            // Reads all rows and convert them to strings
            return statement.executeQuery()
                    .stream()
                    .map(result -> result.getString("name"))
                    .toList();
        });

Running multiple statements in a transaction statement

KotlinJava

            database.transactional {
                // This should not be in the database since the next query will fail, thus reverting the transaction
                preparedStatement("INSERT INTO tag (name, content) VALUES ('should_not_be_here', 'should not be here')") {
                    executeUpdate()
                }
                // This will raise an exception as the name has a constraint matching ^[\w-]+$ (spaces aren't allowed, for example)
                preparedStatement("INSERT INTO tag (name, content) VALUES ('invalid name', 'foo')") {
                    executeUpdate()
                }
            }

            database.withTransaction(transaction -> {
                // This should not be in the database since the next query will fail, thus reverting the transaction
                transaction.withStatement("INSERT INTO tag (name, content) VALUES ('should_not_be_here', 'should not be here')", statement -> {
                    statement.executeUpdate();
                    return null;
                });
                // This will raise an exception as the name has a constraint matching ^[\w-]+$ (spaces aren't allowed, for example)
                transaction.withStatement("INSERT INTO tag (name, content) VALUES ('invalid name', 'foo')", statement -> {
                    statement.executeUpdate();
                    return null;
                });
                return null;
            });

Running a single statement, returning generated keys

KotlinJava

        val createdAt: Instant = database.preparedStatement(
            "INSERT INTO tag (name, content) VALUES ('new_tag', 'new content')",
            columnNames = arrayOf("created_at") // This is required as this is a generated column
        ) {
            executeReturningUpdate() // executeUpdate() + getGeneratedKeys()
                .read() //Read a single row
                .getTimestamp("created_at").toInstant()
        }

        final Instant createdAt = database.withStatement(
                "INSERT INTO tag (name, content) VALUES ('new_tag', 'new content')",
                new String[]{"created_at"}, // This is required as this is a generated column
                statement -> {
                    return statement.executeReturningUpdate() // executeUpdate() + getGeneratedKeys()
                            .read() //Read a single row
                            .getTimestamp("created_at").toInstant();
                });