Schema Builder Reference
This is the complete API reference for Sarah's Schema Builder. Use column definitions and modifiers in migrations; conditions and SELECT options apply to queries.
Column Types
Column types follow the configured dialect. PostgreSQL also maps raw SQL types DOUBLE to DOUBLE PRECISION, DATETIME to TIMESTAMP and TINYINT to SMALLINT.
uuid
Creates a VARCHAR(36) column for a UUID, including on PostgreSQL.
// Definition only
table.uuid("player_id");
// With value
table.uuid("player_id", playerUuid);string
Creates a VARCHAR column.
// Definition with length
table.string("name", 64);
// With value
table.string("name", playerName);text
Creates a TEXT column for longer strings.
table.text("description");longText
Creates a LONGTEXT column for very long content, or TEXT on PostgreSQL.
table.longText("content");integer
Creates an INT column.
table.integer("count");bigInt
Creates a BIGINT column.
// Definition only
table.bigInt("balance");
// With value
table.bigInt("balance", 1000L);decimal
Creates a DECIMAL column.
// Default precision
table.decimal("price");
// Custom precision (10 total digits, 2 after decimal)
table.decimal("price", 10, 2);
// With value
table.decimal("price", 99.99);bool
Creates a BOOLEAN column, including on PostgreSQL.
// Definition only
table.bool("is_active");
// With value
table.bool("is_active", true);json
Creates a JSON column.
table.json("metadata");blob
Creates a BLOB column for binary data, or BYTEA on PostgreSQL.
// Definition only
table.blob("data");
// With byte array
table.blob("data", byteArray);
// With object (auto-serialized)
table.blob("data", myObject);date
Creates a DATE column.
table.date("birth_date", new Date());timestamp
Creates a TIMESTAMP column.
table.timestamp("verified_at");enumValue
Stores a Java enum constant name in a VARCHAR(255) column.
table.enumValue("status");
table.enumValue("status", PlayerStatus.ACTIVE);enumType
Creates a native ENUM on MySQL/MariaDB, TEXT on SQLite and VARCHAR(255) on PostgreSQL. Fallback types do not restrict the allowed values.
table.enumType("status", "ACTIVE", "BANNED");
table.enumType("status", PlayerStatus.class);object
Stores any object (serialized).
table.object("settings", settingsObject);Auto Increment
autoIncrement
Creates an auto-incrementing INT primary key. PostgreSQL uses INTEGER GENERATED BY DEFAULT AS IDENTITY.
table.autoIncrement("id");autoIncrementBigInt
Creates an auto-incrementing BIGINT primary key. PostgreSQL uses BIGINT GENERATED BY DEFAULT AS IDENTITY.
table.autoIncrementBigInt("id");Column Modifiers
primary
Marks the column as a primary key.
table.uuid("uuid").primary();nullable
Allows NULL values.
table.string("nickname", 32).nullable();unique
Adds a UNIQUE constraint.
table.string("email", 255).unique();
// Or with explicit boolean
table.string("email", 255).unique(true);defaultValue
Sets a default value.
table.bigInt("balance").defaultValue(0);
table.string("status", 16).defaultValue("pending");
table.bool("active").defaultValue(true);defaultCurrentTimestamp
Sets default to CURRENT_TIMESTAMP.
table.timestamp("created_at").defaultCurrentTimestamp();foreignKey
Creates a foreign key relationship.
// Basic (references 'id' column)
table.uuid("user_id").foreignKey("users");
// With custom column and cascade delete
table.uuid("user_id").foreignKey("users", "uuid", true);increment
Adds an amount to a numeric column in one atomic write. Negative amounts subtract. INSERT writes the amount as is; UPSERT inserts it for a new row or adds it to the existing value.
requestHelper.update("players", table -> {
table.increment("balance", 10);
table.where("uuid", playerUuid);
});expression
Assigns a SQL expression with bound parameters. Quote column names with backticks so the dialect can translate them. In UPSERT, the expression only applies on conflict; the column is omitted for a new row and must therefore support being omitted.
requestHelper.update("players", table -> {
table.expression("balance", "`balance` + ?", 10);
table.expression("updated_at", "CURRENT_TIMESTAMP");
table.where("uuid", playerUuid);
});insertOnly
Marks the last added value: UPSERT inserts it for a new row and preserves the existing value on conflict. An UPSERT whose columns are all insert-only makes no change on conflict.
requestHelper.upsert("players", table -> {
table.uuid("uuid", playerUuid).primary();
table.date("created_at", new Date()).insertOnly();
table.increment("play_time", 60);
});Timestamp Helpers
timestamps
Adds both created_at and updated_at columns.
table.timestamps();On PostgreSQL, updated_at defaults to CURRENT_TIMESTAMP but does not change automatically on UPDATE. Assign it explicitly in your queries. The dialect is resolved when the schema executes; these helpers do not require MigrationManager.setDatabaseConfiguration().
createdAt
Adds only created_at column.
table.createdAt();updatedAt
Adds only the updated_at column, with the engine behavior described above for timestamps().
table.updatedAt();WHERE Conditions
where
Basic equality or comparison.
// Equality
table.where("name", "Steve");
table.where("uuid", playerUuid);
// Comparison
table.where("balance", ">", 1000);
table.where("level", ">=", 10);
table.where("score", "<", 50);
table.where("rank", "<=", 100);
table.where("status", "!=", "banned");whereNull
Checks for NULL values.
table.whereNull("deleted_at");whereNotNull
Checks for non-NULL values.
table.whereNotNull("verified_at");whereIn
Checks if a value is in a list. An empty list matches no rows. PostgreSQL binds numeric values with their type.
// Varargs
table.whereIn("status", "active", "pending", "review");
// List
table.whereIn("name", Arrays.asList("Steve", "Alex"));
// With table prefix
table.whereIn("users", "status", statusList);whereNotIn
Excludes the supplied values. With no values, every row matches. The table-prefix overload accepts a list.
table.whereNotIn("status", "banned", "deleted");
table.whereNotIn("users", "name", Arrays.asList("Bot", "Test"));whereBetween
Selects values between two inclusive bounds. Parameters retain their type.
table.whereBetween("level", 10, 20);whereNotBetween
Excludes the interval, including both bounds.
table.whereNotBetween("level", 10, 20);whereLike
Matches a SQL LIKE pattern: % matches a sequence of characters and _ matches one character.
table.whereLike("name", "Alex%");whereNotLike
Excludes values matching the LIKE pattern.
table.whereNotLike("name", "Bot%");orWhere
Joins a condition to the previous one with OR. Other WHERE calls use AND. AND takes precedence over OR; use a group to change that precedence.
table.where("status", "active").orWhere("status", "pending");
table.orWhere("balance", ">", 1000);
table.orWhere("users", "level", ">=", 10);orWhereNull
Adds an OR condition checking for NULL.
table.where("status", "pending").orWhereNull("verified_at");orWhereNotNull
Adds an OR condition checking for a non-NULL value.
table.where("status", "active").orWhereNotNull("verified_at");orWhereIn
Adds an OR condition with a list of values.
table.where("is_premium", true).orWhereIn("rank", "vip", "admin");orWhereBetween
Adds an OR condition with two inclusive bounds.
table.where("is_premium", true).orWhereBetween("level", 10, 20);where / orWhere (groups)
Puts the callback’s conditions in parentheses. where(group) joins them with AND, orWhere(group) with OR. An empty group adds no condition.
table.where("active", true);
table.where(group -> group.where("rank", "vip").orWhere("rank", "admin"));
table.orWhere(group -> group.where("level", ">=", 50).whereNotNull("verified_at"));JOIN Operations
leftJoin
table.leftJoin(
"users", // Primary table
"u", // Alias
"uuid", // Primary column
"orders", // Foreign table
"user_uuid" // Foreign column
);rightJoin
table.rightJoin("users", "u", "uuid", "orders", "user_uuid");innerJoin
table.innerJoin("users", "u", "uuid", "orders", "user_uuid");fullJoin
table.fullJoin("users", "u", "uuid", "orders", "user_uuid");Join with Additional Condition
JoinCondition condition = JoinCondition.and("u", "status", "active");
table.leftJoin("users", "u", "uuid", "orders", "user_uuid", condition);The extra condition compares u.status to the string value active. Sarah escapes this value for the configured engine, including backslashes on MySQL/MariaDB. When rendering a complete JoinCondition yourself, use getJoinClause(config.getDialect()) for engine-specific escaping.
SELECT Options
addSelect
Specify columns to select. Aliases are available as result-map keys, including on MySQL.
table.addSelect("name");
table.addSelect("users", "name"); // With table prefix
table.addSelect("users", "name", "userName"); // With alias
table.addSelect("users", "name", "userName", "Unknown"); // With defaultdistinct
Select only distinct rows.
table.distinct();orderBy
Sort results ascending.
table.orderBy("name");orderByDesc
Sort results descending.
table.orderByDesc("created_at");limit
Limits the number of rows returned by a select.
table.limit(20);offset
Skips the first rows of a select. Use with ordering and a limit for pagination; it also works without a limit.
table.orderBy("name");
table.limit(20);
table.offset(40);groupBy
Groups results by one or more columns. Select only grouped columns or aggregate expressions.
table.addSelect("rank");
table.groupBy("rank");having
Filters groups using a SQL expression and a bound value. Multiple calls are joined with AND. The expression must be developer-controlled.
table.addSelect("rank");
table.groupBy("rank");
table.having("COUNT(*)", ">=", 10);Schema Types
Sarah uses different schema types for different operations:
| Type | Description | Created By |
|---|---|---|
CREATE | Create new table | SchemaBuilder.create() |
ALTER | Modify existing table | SchemaBuilder.alter() |
DROP | Delete table | SchemaBuilder.drop() |
RENAME | Rename table | SchemaBuilder.rename() |
INSERT | Insert data | SchemaBuilder.insert() |
UPSERT | Insert or update | SchemaBuilder.upsert() |
UPDATE | Update data | SchemaBuilder.update() |
SELECT | Query data | SchemaBuilder.select() |
SELECT_COUNT | Count rows | SchemaBuilder.selectCount() |
DELETE | Delete data | SchemaBuilder.delete() |
CREATE_INDEX | Create index | SchemaBuilder.createIndex() |
SchemaBuilder Static Methods
create
Create a new table.
Schema schema = SchemaBuilder.create(migration, "users", table -> {
table.uuid("uuid").primary();
table.string("name", 64);
});create (from template)
Create table from class template.
Schema schema = SchemaBuilder.create(migration, "users", UserDTO.class);alter
Modify existing table.
Schema schema = SchemaBuilder.alter(migration, "users", table -> {
table.string("email", 255).nullable();
});drop
Delete a table.
Schema schema = SchemaBuilder.drop(migration, "old_table");rename
Rename a table.
Schema schema = SchemaBuilder.rename(migration, "old_name", "new_name");createIndex
Create an index.
Schema schema = SchemaBuilder.createIndex(migration, "users", "email");insert
Insert data.
Schema schema = SchemaBuilder.insert("users", table -> {
table.uuid("uuid", uuid);
table.string("name", name);
});upsert
Insert or update data.
Schema schema = SchemaBuilder.upsert("users", table -> {
table.uuid("uuid", uuid).primary();
table.string("name", name);
});update
Update data.
Schema schema = SchemaBuilder.update("users", table -> {
table.string("name", newName);
table.where("uuid", uuid);
});select
Query data.
Schema schema = SchemaBuilder.select("users");selectCount
Count rows.
Schema schema = SchemaBuilder.selectCount("users");delete
Delete data.
Schema schema = SchemaBuilder.delete("users");Execution Methods
execute
Execute the schema operation.
int affectedRows = schema.execute(databaseConnection, logger);executeSelect
Execute a SELECT query.
List<Map<String, Object>> results = schema.executeSelect(databaseConnection, logger);executeSelect (with mapping)
Execute SELECT and map to objects.
List<UserDTO> users = schema.executeSelect(UserDTO.class, databaseConnection, logger);executeSelectCount
Execute a COUNT query.
long count = schema.executeSelectCount(databaseConnection, logger);Complete Example
public class UserRepository {
private final RequestHelper helper;
public UserRepository(RequestHelper helper) {
this.helper = helper;
}
public UserRepository(RequestHelper helper) {
this.helper = helper;
}
public void createUser(User user) {
helper.upsert("users", schema -> {
schema.uuid("uuid", user.getUuid()).primary();
schema.string("name", user.getName());
schema.string("email", user.getEmail()).unique();
schema.bigInt("balance", user.getBalance()).defaultValue(0);
schema.bool("is_premium", user.isPremium()).defaultValue(false);
schema.text("bio").nullable();
schema.json("settings");
schema.timestamps();
});
}
public List<User> findPremiumUsers() {
return helper.select("users", User.class, table -> {
table.where("is_premium", true);
table.whereNotNull("email");
table.orderByDesc("balance");
});
}
public List<User> searchUsers(String query) {
return helper.select("users", User.class, table -> {
table.where("name", "LIKE", "%" + query + "%");
});
}
public void deleteInactiveUsers(Date threshold) {
helper.delete("users", table -> {
table.where("last_login", "<", threshold);
table.where("is_premium", false);
});
}
}