Usage
Schema Builder Reference
This is the complete API reference for Sarah's Schema Builder. All methods are available when defining migrations or building queries.
Column Types
uuid
Creates a UUID/VARCHAR(36) column.
// 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.
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 TINYINT(1)/BOOLEAN column.
// 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.
// 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");object
Stores any object (serialized).
table.object("settings", settingsObject);Auto Increment
autoIncrement
Creates an auto-incrementing INT primary key.
table.autoIncrement("id");autoIncrementBigInt
Creates an auto-incrementing BIGINT primary key.
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);Timestamp Helpers
timestamps
Adds both created_at and updated_at columns.
table.timestamps();createdAt
Adds only created_at column.
table.createdAt();updatedAt
Adds only updated_at column.
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 value is in a list.
// Varargs
table.whereIn("status", "active", "pending", "review");
// List
table.whereIn("name", Arrays.asList("Steve", "Alex"));
// With table prefix
table.whereIn("users", "status", statusList);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 = new JoinCondition(...);
table.leftJoin("users", "u", "uuid", "orders", "user_uuid", condition);SELECT Options
addSelect
Specify columns to select.
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");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 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);
});
}
}