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
@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.
@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 itsTRACE
logs enabled- Or,
BDatabaseConfig#queryLogThreshold
is configured, and the logger of the class that created the prepared statement has itsWARN
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¶
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¶
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¶
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¶
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();
});