storm

High-performance lightweight SQL ORM with strongly-typed SQL DSL surfacing compile-time errors, automated entity mapping, bundled SQLite drivers, expressive querying supporting joins, aggregates, transactions.

#sqlite
#sql
#ksp
#jetpack
#gradle-plugin
#dsl
#database
#code-generation

Suggest an edit

Android JVMJVMKotlin/Native

GitHub stars7

Authorskaus-io

Dependents0

LicenseMIT License

Creation date2 months ago

Last activity1 day ago

Latest release1.0.0-beta9 (5 days ago)

Homepage GitHub repository GitHub pages

介绍

Storm 是一个直接基于纯 Kotlin 编写的高效简洁的轻量级 Kotlin Multiplatform 与 Android SQL ORM 框架，提供了强类型的 SQL DSL，直接将低级 bug 暴露在编译期。

英文版请参见 README_EN.md。

安装

dependencies {
    implementation("com.zxhhyj:storm-core:<version>")
    implementation("com.zxhhyj:storm-driver-androidx-sqlite:<version>")
    // 这里以 androidx-sqlite-bundled 为例
    implementation("androidx.sqlite:sqlite-bundled:<version>")
    // implementation("androidx.sqlite:sqlite-framework:<version>")
}

以下是支持的驱动列表：

模块	适用平台
`storm-driver-androidx-sqlite`	KMP 与 Android（`androidx.sqlite`，可选 `sqlite-bundled` 或 `sqlite-framework`）
`storm-driver-android-sqlite`	Android（直接封装 `android.database.sqlite`）

快速上手

1. 定义 `UserInfo` 表

import com.zxhhyj.storm.schema.*
import com.zxhhyj.storm.schema.Table

object UserInfoTable : Table("t_user_info") {
    val id = int("id").primaryKey().autoincrement()
    val name = varchar("name").notNull()
    val info = varchar("info").nullable()
    val email = varchar("email").unique()
    val age = int("age")
}

CREATE TABLE IF NOT EXISTS "t_user_info" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "name" TEXT NOT NULL,
    "info" TEXT,
    "email" TEXT UNIQUE,
    "age" INTEGER
);

Table 用于声明 schema 与行级 CRUD。列 helper（int、varchar 等）定义在 com.zxhhyj.storm.schema 包中；在表 object 体内调用时，各列类型为 ColumnBuilder<具体表类型, …>，写入 DSL（RowBuilder、 returning、onConflict 等）会在编译期拒绝跨表混用列。

2. 定义带外键的 `User` 表

import com.zxhhyj.storm.schema.*
import com.zxhhyj.storm.schema.ForeignKeyAction
import com.zxhhyj.storm.schema.Table

object UserTable : Table("t_user") {
    val id = int("id").primaryKey().autoincrement()
    val email = varchar("email").notNull()
    val userInfoId =
        int("user_info_id").references(UserInfoTable, onDelete = ForeignKeyAction.CASCADE)
    val createDateTime = datetime("create_date_time").notNull()
}

CREATE TABLE IF NOT EXISTS "t_user" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "email" TEXT NOT NULL,
    "user_info_id" INTEGER REFERENCES "t_user_info"("id") ON DELETE CASCADE,
    "create_date_time" TEXT NOT NULL
);

references(otherTable, onDelete = …) 的第一个参数是目标 Table，默认引用其 id 列。

3. 打开数据库并创建 schema

ReadWriteDatabase 通过工厂函数创建，提供参数式与 DSL 式两种风格。把 onDatabaseCreate / onCreate / onClose 作为参数传入即可声明表结构与生命周期逻辑。

参数式：

import androidx.sqlite.driver.bundled.BundledSQLiteDriver
import com.zxhhyj.storm.database.ReadWriteDatabase
import com.zxhhyj.storm.database.createTable
import com.zxhhyj.storm.AndroidXSQLiteConnection
import com.zxhhyj.storm.crud.insert

val appDatabase = ReadWriteDatabase(
    connection = AndroidXSQLiteConnection(BundledSQLiteDriver().open("app.db")),
    version = 1,
    onDatabaseCreate = {
        createTable(UserInfoTable, ifNotExists = true)
        createTable(UserTable, ifNotExists = true)
    },
    onCreate = {},
    onClose = {},
)

DSL 式（等价写法）：

val appDatabase = ReadWriteDatabase {
    connection = AndroidXSQLiteConnection(BundledSQLiteDriver().open("app.db"))
    version = 1
    onDatabaseCreate {
        createTable(UserInfoTable, ifNotExists = true)
        createTable(UserTable, ifNotExists = true)
    }
    onCreate {}
    onClose {}
}

PRAGMA foreign_keys = ON;
CREATE TABLE IF NOT EXISTS "storm_meta" ("key" TEXT PRIMARY KEY, "value" INTEGER NOT NULL);
CREATE TABLE IF NOT EXISTS "t_user_info" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "name" TEXT NOT NULL, "info" TEXT, "email" TEXT UNIQUE, "age" INTEGER);
CREATE TABLE IF NOT EXISTS "t_user" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "email" TEXT NOT NULL, "user_info_id" INTEGER REFERENCES "t_user_info"("id") ON DELETE CASCADE, "create_date_time" TEXT NOT NULL);

onDatabaseCreate 在首次建库时执行；onCreate 在每次打开数据库后执行；onClose 在 close() 时执行。三者中的 onDatabaseCreate / onCreate 以及 migrate { } 均为 suspend 回调，在 ioContext 上运行，可直接调用 insert、transaction 等写 API 做种子数据初始化，无需 runBlocking。

3.1 只读场景

只读场景用 ReadOnlyDatabase，写 API 仅存在于 ReadWriteDatabase 上。

import com.zxhhyj.storm.database.ReadOnlyDatabase
import com.zxhhyj.storm.crud.form
import com.zxhhyj.storm.crud.querySingle

val readOnly = ReadOnlyDatabase(
    AndroidXSQLiteConnection(BundledSQLiteDriver().open("app.db"))
)

val count = readOnly.form(UserInfoTable).selectAll().count()
val alice = readOnly.querySingle(UserInfoTable) { UserInfoTable.name eq "Hao" }!!

// readOnly.insert(...)            // 编译期就不行
// readOnly.execSQL("DELETE …")   // 编译期就不行

4. 列类型与修饰符

import com.zxhhyj.storm.schema.*

enum class Status { ACTIVE, BANNED }

object ArticleTable : Table("t_article") {
    val id = int("id").primaryKey().autoincrement()
    val title = varchar("title").notNull()
    val rating = real("rating").nullable()
    val published = boolean("published").notNull().default(0L)
    val createdAt = datetime("created_at").notNull().defaultCurrentTimestamp()
    val birth = date("birth").nullable()
    val status = enum("status", Status::class).notNull()
    val wordCount = long("word_count").notNull().default(0L)
}

CREATE TABLE "t_article" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "title" TEXT NOT NULL,
    "rating" REAL,
    "published" INTEGER NOT NULL DEFAULT 0,
    "created_at" TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "birth" TEXT,
    "status" TEXT NOT NULL,
    "word_count" INTEGER NOT NULL DEFAULT 0
);

工厂	SQLite 存储	Kotlin 类型
`int`	`INTEGER`	`Int`
`long`	`INTEGER`	`Long`
`real`	`REAL`	`Float`
`varchar`	`TEXT`	`String`
`blob`	`BLOB`	`ByteArray`
`boolean`	`INTEGER` (0/1)	`Boolean`
`date`	`TEXT` (ISO date)	`LocalDate`
`datetime`	`TEXT` (ISO dt)	`LocalDateTime`
`enum(field, E::class)`	`TEXT` (常量名)	`E`

所有修饰符都可链式调用，按书写顺序生效：

import com.zxhhyj.storm.schema.Collation
import com.zxhhyj.storm.schema.ForeignKeyAction

val id = int("id").primaryKey().autoincrement()
val email = varchar("email").notNull().unique()
val nickname = varchar("nick").nullable()
val parentId = int("parent_id").references(UserInfoTable, onDelete = ForeignKeyAction.SET_NULL)
val archived = boolean("archived").notNull().default(0L)
val score = real("score").nullable().default(0f)
val refreshedAt = datetime("refreshed_at").defaultCurrentTimestamp()
val birthTime = date("birth_time").defaultCurrentDate()
val locale = varchar("locale").collate(Collation.NOCASE)
val token = varchar("token").check { neq("") }

val tags = varchar("tags").transform(
    save = { list: List<String> -> list.joinToString(",") },
    restore = { csv: String -> csv.split(",").filter(String::isNotEmpty) },
)

4.1 索引

import com.zxhhyj.storm.schema.*
import com.zxhhyj.storm.schema.IndexOrder

object UserTable : Table("t_user") {
    val id = int("id").primaryKey().autoincrement()
    val email = varchar("email").notNull()
    val dept = varchar("dept").notNull()

    @Suppress("unused")
    private val idxDeptEmail = index("idx_t_user_dept_email")
        .column(dept, IndexOrder.ASC)
        .column(email)
        .unique()
}

CREATE INDEX IF NOT EXISTS "idx_t_user_dept_email" ON "t_user" ("dept" ASC, "email");

5. 行级 CRUD

5.1 插入

import com.zxhhyj.storm.crud.insert

appDatabase.transaction {
    insert(UserInfoTable) {
        this[UserInfoTable.name] = "Hao"
        this[UserInfoTable.info] = "keep going"
    }
    insert(UserInfoTable) {
        this[UserInfoTable.name] = "Ada"
        this[UserInfoTable.info] = null
    }
}

INSERT INTO "t_user_info" ("name", "info") VALUES (?, ?);
INSERT INTO "t_user_info" ("name", "info") VALUES (?, NULL);

import com.zxhhyj.storm.crud.batchInsert

appDatabase.batchInsert(
    UserInfoTable,
    listOf(
        mapOf("name" to "Lin", "info" to "x", "email" to "lin@example.com"),
        mapOf("name" to "Grace", "info" to "y", "email" to "grace@example.com"),
    ),
)

INSERT INTO "t_user_info" ("name", "info", "email") VALUES (?, ?, ?), (?, ?, ?);

import com.zxhhyj.storm.crud.upsert

appDatabase.upsert(UserInfoTable).set {
    this[UserInfoTable.name] = "Dennis"
    this[UserInfoTable.info] = "C"
    this[UserInfoTable.email] = "dennis@example.com"
}.onConflict(UserInfoTable.email).execute()

INSERT INTO "t_user_info" ("name", "info") VALUES (?, ?)
    ON CONFLICT ("email") DO UPDATE SET "name" = excluded."name", "info" = excluded."info";

5.2 更新与删除

import com.zxhhyj.storm.crud.update

appDatabase.update(UserInfoTable).set {
    this[UserInfoTable.name] = "updated"
}.where { UserInfoTable.name eq "Hao" }.execute()

UPDATE "t_user_info" SET "name" = ? WHERE "name" = ?;

import com.zxhhyj.storm.crud.updateAll

appDatabase.updateAll(UserInfoTable) { this[UserInfoTable.info] = "bulk" }

UPDATE "t_user_info" SET "info" = ?;

import com.zxhhyj.storm.crud.delete

appDatabase.delete(UserInfoTable).where { UserInfoTable.name eq "Hao" }.execute()

DELETE FROM "t_user_info" WHERE "name" = ?;

import com.zxhhyj.storm.crud.deleteAll

appDatabase.transaction { deleteAll(UserInfoTable) }

DELETE FROM "t_user_info";

5.3 查询

import com.zxhhyj.storm.crud.querySingle

val user: QueryBuilder.ResultSet? =
    appDatabase.querySingle(UserInfoTable) { UserInfoTable.name eq "Hao" }

SELECT * FROM "t_user_info" WHERE "name" = ? LIMIT 1;

import com.zxhhyj.storm.crud.form

val users: List<QueryBuilder.ResultSet> = appDatabase.form(UserInfoTable).selectAll().toList()

SELECT * FROM "t_user_info";

val count: Long = appDatabase.form(UserInfoTable).selectAll().count()

SELECT COUNT(*) FROM "t_user_info";

5.4 `RETURNING`

val deleted = appDatabase.delete(UserInfoTable)
    .where { UserInfoTable.name eq "Hao" }
    .returning(UserInfoTable.name, UserInfoTable.email)
    .executeReturning()

6. 条件 / 谓词 DSL

import com.zxhhyj.storm.dsl.ConditionalExpression.Companion.between
import com.zxhhyj.storm.dsl.ConditionalExpression.Companion.eq
import com.zxhhyj.storm.dsl.ConditionalExpression.Companion.eqDistinct
import com.zxhhyj.storm.dsl.ConditionalExpression.Companion.gte
import com.zxhhyj.storm.dsl.ConditionalExpression.Companion.isNotNull
import com.zxhhyj.storm.dsl.ConditionalExpression.Companion.isNull
import com.zxhhyj.storm.dsl.ConditionalExpression.Companion.like
import com.zxhhyj.storm.dsl.ConditionalExpression.Companion.lt
import com.zxhhyj.storm.dsl.ConditionalExpression.Companion.neqDistinct
import com.zxhhyj.storm.dsl.ConditionalExpression.Companion.notBetween

where { UserInfoTable.name eq "Hao" }
where { UserInfoTable.age gte 18 }
where { UserInfoTable.age lt 30 }
where { UserInfoTable.name like "%a%" }
where { UserInfoTable.info.isNull() }
where { UserInfoTable.info.isNotNull() }
where { UserInfoTable.age between (18..65) }
where { UserInfoTable.age notBetween (18..65) }
where { UserInfoTable.name eqDistinct null }
where { UserInfoTable.name neqDistinct null }

where { (UserInfoTable.age gte 18) and (UserInfoTable.name like "%a%") }

ColumnRef 的运算符也接受其他 ColumnRef —— 在 JOIN 谓词里很有用：

where { JoinRightTable.leftId eq JoinLeftTable.id }

运算符	SQL 渲染
`a eq b` / `a == b`	`a = b`
`a neq b` / `a != b`	`a <> b`
`a gt b` / `a > b`	`a > b`
`a gte b` / `a >= b`	`a >= b`
`a lt b` / `a < b`	`a < b`
`a lte b` / `a <= b`	`a <= b`
`a like pattern`	`a LIKE pattern`
`a.between(range)` / `notBetween(r)`	`a BETWEEN x AND y`
`a.in(list)` / `notIn(list)`	`a IN (?, ?, …)`
`a.in(qb)`	`a IN (SELECT …)` (infix)
`a.isNull()` / `isNotNull()`	`a IS [NOT] NULL`
`a eqDistinct b` / `neqDistinct b`	`a IS b` / `IS NOT b`
`cond and cond` / `or` / `not(cond)`	`AND` / `OR` / `NOT`

7. 查询构造器 —— `FormBuilder` / `QueryBuilder`

import com.zxhhyj.storm.dsl.SortOrder

val top10: List<QueryBuilder.ResultSet> = appDatabase.form(UserTable)
    .select(UserTable.id, UserTable.email)
    .innerJoin(UserInfoTable) { UserTable.userInfoId eq UserInfoTable.id }
    .where { UserTable.email like "%@example.com%" }
    .groupBy(UserInfoTable.id)
    .having { UserInfoTable.id gte 1L }
    .orderBy(UserTable.id, SortOrder.DESC)
    .limit(10)
    .offset(20)
    .toList()

SELECT "id", "email"
FROM "t_user"
INNER JOIN "t_user_info" ON "t_user"."user_info_id" = "t_user_info"."id"
WHERE "t_user"."email" LIKE ?
GROUP BY "t_user_info"."id"
HAVING "t_user_info"."id" >= ?
ORDER BY "t_user"."id" DESC
LIMIT 10
OFFSET 20;

聚合：

val sumAge = appDatabase.form(ScoresTable).selectAll().sum(ScoresTable.age)
val avgAge = appDatabase.form(ScoresTable).selectAll().avg(ScoresTable.age)
val minAge = appDatabase.form(ScoresTable).selectAll().min(ScoresTable.age)
val maxAge = appDatabase.form(ScoresTable).selectAll().max(ScoresTable.age)
val names = appDatabase.form(ScoresTable).selectAll().groupConcat(ScoresTable.name, ", ")

7.1 CTE 与窗口函数

import com.zxhhyj.storm.dsl.SortOrder
import com.zxhhyj.storm.dsl.rowNumber
import com.zxhhyj.storm.dsl.over

val ranked = appDatabase.form(ScoresTable)
    .withCte("high", appDatabase.form(ScoresTable).selectAll().where { ScoresTable.age gte 18 })
    .selectExpr(
        (rowNumber().over(
            partitionBy = ScoresTable.name,
            orderBy = ScoresTable.age to SortOrder.ASC,
        )) `as` "rn"
    )
    .orderBy(ScoresTable.age, SortOrder.DESC)
    .toList()

7.2 标量表达式

import com.zxhhyj.storm.dsl.caseWhen
import com.zxhhyj.storm.dsl.coalesce

where { coalesce(UserInfoTable.info, "unknown") eq "x" }

caseWhen {
    branch(UserInfoTable.age gte 18) thenValue "adult"
    otherwise "minor"
}

8. DDL

import com.zxhhyj.storm.database.createTable

appDatabase.createTable(UserTable, ifNotExists = true)

CREATE TABLE IF NOT EXISTS "t_user" (...);

import com.zxhhyj.storm.database.dropTableIfExists

appDatabase.dropTableIfExists(UserTable)

DROP TABLE IF EXISTS "t_user";

import com.zxhhyj.storm.database.dropIndex

appDatabase.dropIndex("idx_user_email", ifExists = true)

DROP INDEX IF EXISTS "idx_user_email";

import com.zxhhyj.storm.database.createTableAndIndex
import com.zxhhyj.storm.database.createIndex
import com.zxhhyj.storm.database.addColumn

appDatabase.createTableAndIndex(UserTable)
appDatabase.createIndex(UserTable, ifNotExists = true)

val nickname = UserTable.varchar("nickname").nullable()
appDatabase.addColumn(UserTable, nickname)

val createSql: String = UserTable.createTable(ifNotExists = true)
val dropSql: String = UserTable.dropTable(ifExists = true)
val nickname = UserTable.varchar("nickname").nullable()
val addColumnSql: String = UserTable addColumn nickname

CREATE TABLE IF NOT EXISTS "t_user" (...);
DROP TABLE IF EXISTS "t_user";
ALTER TABLE "t_user" ADD COLUMN ...;

9. 观察数据

import com.zxhhyj.storm.observe.observe

val count: Flow<Long> = appDatabase.observe(UserInfoTable) { ops ->
    ops.form(UserInfoTable).selectAll().count()
}

val filtered: Flow<List<QueryBuilder.ResultSet>> =
    appDatabase.form(UserInfoTable).selectAll()
        .where { UserInfoTable.age gte 18 }
        .observe()

filtered.collect { rows -> println("got ${rows.size} adults") }

10. 事务与 Savepoint

import com.zxhhyj.storm.crud.insert

appDatabase.transaction {
    insert(UserInfoTable) {
        this[UserInfoTable.name] = "Hao"
        this[UserInfoTable.info] = "x"
    }
}

appDatabase.transaction {
    appDatabase.transaction {
        println(depth)
        println(savepointName)
    }
}

11. 迁移

用 migrations 注册 migrate(from, to) { … } 步骤：

import com.zxhhyj.storm.database.ReadWriteDatabase
import com.zxhhyj.storm.database.addColumn
import com.zxhhyj.storm.database.createTable

val password = UserTable.varchar("password").notNull().default("")

val appDatabase = ReadWriteDatabase(
    connection = AndroidXSQLiteConnection(BundledSQLiteDriver().open("app.db")),
    version = 2,
    migrations = {
        migrate(1, 2) {
            addColumn(UserTable, password)
        }
    },
    onDatabaseCreate = {
        createTable(UserInfoTable, ifNotExists = true)
        createTable(UserTable, ifNotExists = true)
    },
    onCreate = {},
    onClose = {},
)

ALTER TABLE "t_user" ADD COLUMN password TEXT NOT NULL DEFAULT '';

12. 杂项 PRAGMA

appDatabase.enableCaseSensitiveLike()
appDatabase.disableCaseSensitiveLike()

println(appDatabase.foreignKeysEnabled)

@OptIn(ExperimentalStormApi::class)
appDatabase.execSQL("VACUUM")

升级指南

从旧版升级时请注意以下破坏性变更（详见 CHANGELOG.md Unreleased）：

Table 去泛型：object X : Table<X>("…") → object X : Table("…")。列 helper（int、varchar、 index 等）定义在 com.zxhhyj.storm.schema 包；在表 object 所在文件添加 import com.zxhhyj.storm.schema.*。各列仍为 ColumnBuilder<具体表类型, …>，写入 DSL 继续拒绝跨表混用列。
移除 storm-orm：不再发布 storm-orm、storm-ksp、storm-gradle-plugin。请使用 Table + form() / RowBuilder 行级 CRUD，或在应用层自行把查询结果映射为 data class。
枚举列：enum<Status>("status") → enum("status", Status::class)。

参与贡献

欢迎提 Issues 和 PR，你的 Issues 就是我最大的动力，你的 PR 是对我最大的支持。

开源协议

本项目基于 Apache License 2.0。

交流

如有疑问或建议，请提 Issues 或者写一封邮件发送到 zxhhyj@qq.com。

Android JVMJVMKotlin/Native

GitHub stars7

Authorskaus-io

Dependents0

LicenseMIT License

Creation date2 months ago

Last activity1 day ago

Latest release1.0.0-beta9 (5 days ago)

Homepage GitHub repository GitHub pages

介绍

Storm 是一个直接基于纯 Kotlin 编写的高效简洁的轻量级 Kotlin Multiplatform 与 Android SQL ORM 框架，提供了强类型的 SQL DSL，直接将低级 bug 暴露在编译期。

英文版请参见 README_EN.md。

安装

dependencies {
    implementation("com.zxhhyj:storm-core:<version>")
    implementation("com.zxhhyj:storm-driver-androidx-sqlite:<version>")
    // 这里以 androidx-sqlite-bundled 为例
    implementation("androidx.sqlite:sqlite-bundled:<version>")
    // implementation("androidx.sqlite:sqlite-framework:<version>")
}

以下是支持的驱动列表：

模块	适用平台
`storm-driver-androidx-sqlite`	KMP 与 Android（`androidx.sqlite`，可选 `sqlite-bundled` 或 `sqlite-framework`）
`storm-driver-android-sqlite`	Android（直接封装 `android.database.sqlite`）

快速上手

1. 定义 `UserInfo` 表

import com.zxhhyj.storm.schema.*
import com.zxhhyj.storm.schema.Table

object UserInfoTable : Table("t_user_info") {
    val id = int("id").primaryKey().autoincrement()
    val name = varchar("name").notNull()
    val info = varchar("info").nullable()
    val email = varchar("email").unique()
    val age = int("age")
}

CREATE TABLE IF NOT EXISTS "t_user_info" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "name" TEXT NOT NULL,
    "info" TEXT,
    "email" TEXT UNIQUE,
    "age" INTEGER
);

2. 定义带外键的 `User` 表

import com.zxhhyj.storm.schema.*
import com.zxhhyj.storm.schema.ForeignKeyAction
import com.zxhhyj.storm.schema.Table

object UserTable : Table("t_user") {
    val id = int("id").primaryKey().autoincrement()
    val email = varchar("email").notNull()
    val userInfoId =
        int("user_info_id").references(UserInfoTable, onDelete = ForeignKeyAction.CASCADE)
    val createDateTime = datetime("create_date_time").notNull()
}

CREATE TABLE IF NOT EXISTS "t_user" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "email" TEXT NOT NULL,
    "user_info_id" INTEGER REFERENCES "t_user_info"("id") ON DELETE CASCADE,
    "create_date_time" TEXT NOT NULL
);

references(otherTable, onDelete = …) 的第一个参数是目标 Table，默认引用其 id 列。

3. 打开数据库并创建 schema

参数式：

import androidx.sqlite.driver.bundled.BundledSQLiteDriver
import com.zxhhyj.storm.database.ReadWriteDatabase
import com.zxhhyj.storm.database.createTable
import com.zxhhyj.storm.AndroidXSQLiteConnection
import com.zxhhyj.storm.crud.insert

val appDatabase = ReadWriteDatabase(
    connection = AndroidXSQLiteConnection(BundledSQLiteDriver().open("app.db")),
    version = 1,
    onDatabaseCreate = {
        createTable(UserInfoTable, ifNotExists = true)
        createTable(UserTable, ifNotExists = true)
    },
    onCreate = {},
    onClose = {},
)

DSL 式（等价写法）：

val appDatabase = ReadWriteDatabase {
    connection = AndroidXSQLiteConnection(BundledSQLiteDriver().open("app.db"))
    version = 1
    onDatabaseCreate {
        createTable(UserInfoTable, ifNotExists = true)
        createTable(UserTable, ifNotExists = true)
    }
    onCreate {}
    onClose {}
}

PRAGMA foreign_keys = ON;
CREATE TABLE IF NOT EXISTS "storm_meta" ("key" TEXT PRIMARY KEY, "value" INTEGER NOT NULL);
CREATE TABLE IF NOT EXISTS "t_user_info" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "name" TEXT NOT NULL, "info" TEXT, "email" TEXT UNIQUE, "age" INTEGER);
CREATE TABLE IF NOT EXISTS "t_user" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "email" TEXT NOT NULL, "user_info_id" INTEGER REFERENCES "t_user_info"("id") ON DELETE CASCADE, "create_date_time" TEXT NOT NULL);

3.1 只读场景

只读场景用 ReadOnlyDatabase，写 API 仅存在于 ReadWriteDatabase 上。

import com.zxhhyj.storm.database.ReadOnlyDatabase
import com.zxhhyj.storm.crud.form
import com.zxhhyj.storm.crud.querySingle

val readOnly = ReadOnlyDatabase(
    AndroidXSQLiteConnection(BundledSQLiteDriver().open("app.db"))
)

val count = readOnly.form(UserInfoTable).selectAll().count()
val alice = readOnly.querySingle(UserInfoTable) { UserInfoTable.name eq "Hao" }!!

// readOnly.insert(...)            // 编译期就不行
// readOnly.execSQL("DELETE …")   // 编译期就不行

4. 列类型与修饰符

import com.zxhhyj.storm.schema.*

enum class Status { ACTIVE, BANNED }

object ArticleTable : Table("t_article") {
    val id = int("id").primaryKey().autoincrement()
    val title = varchar("title").notNull()
    val rating = real("rating").nullable()
    val published = boolean("published").notNull().default(0L)
    val createdAt = datetime("created_at").notNull().defaultCurrentTimestamp()
    val birth = date("birth").nullable()
    val status = enum("status", Status::class).notNull()
    val wordCount = long("word_count").notNull().default(0L)
}

CREATE TABLE "t_article" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "title" TEXT NOT NULL,
    "rating" REAL,
    "published" INTEGER NOT NULL DEFAULT 0,
    "created_at" TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "birth" TEXT,
    "status" TEXT NOT NULL,
    "word_count" INTEGER NOT NULL DEFAULT 0
);

工厂	SQLite 存储	Kotlin 类型
`int`	`INTEGER`	`Int`
`long`	`INTEGER`	`Long`
`real`	`REAL`	`Float`
`varchar`	`TEXT`	`String`
`blob`	`BLOB`	`ByteArray`
`boolean`	`INTEGER` (0/1)	`Boolean`
`date`	`TEXT` (ISO date)	`LocalDate`
`datetime`	`TEXT` (ISO dt)	`LocalDateTime`
`enum(field, E::class)`	`TEXT` (常量名)	`E`

所有修饰符都可链式调用，按书写顺序生效：

import com.zxhhyj.storm.schema.Collation
import com.zxhhyj.storm.schema.ForeignKeyAction

val id = int("id").primaryKey().autoincrement()
val email = varchar("email").notNull().unique()
val nickname = varchar("nick").nullable()
val parentId = int("parent_id").references(UserInfoTable, onDelete = ForeignKeyAction.SET_NULL)
val archived = boolean("archived").notNull().default(0L)
val score = real("score").nullable().default(0f)
val refreshedAt = datetime("refreshed_at").defaultCurrentTimestamp()
val birthTime = date("birth_time").defaultCurrentDate()
val locale = varchar("locale").collate(Collation.NOCASE)
val token = varchar("token").check { neq("") }

val tags = varchar("tags").transform(
    save = { list: List<String> -> list.joinToString(",") },
    restore = { csv: String -> csv.split(",").filter(String::isNotEmpty) },
)

4.1 索引

import com.zxhhyj.storm.schema.*
import com.zxhhyj.storm.schema.IndexOrder

object UserTable : Table("t_user") {
    val id = int("id").primaryKey().autoincrement()
    val email = varchar("email").notNull()
    val dept = varchar("dept").notNull()

    @Suppress("unused")
    private val idxDeptEmail = index("idx_t_user_dept_email")
        .column(dept, IndexOrder.ASC)
        .column(email)
        .unique()
}

CREATE INDEX IF NOT EXISTS "idx_t_user_dept_email" ON "t_user" ("dept" ASC, "email");

5. 行级 CRUD

5.1 插入

import com.zxhhyj.storm.crud.insert

appDatabase.transaction {
    insert(UserInfoTable) {
        this[UserInfoTable.name] = "Hao"
        this[UserInfoTable.info] = "keep going"
    }
    insert(UserInfoTable) {
        this[UserInfoTable.name] = "Ada"
        this[UserInfoTable.info] = null
    }
}

INSERT INTO "t_user_info" ("name", "info") VALUES (?, ?);
INSERT INTO "t_user_info" ("name", "info") VALUES (?, NULL);

import com.zxhhyj.storm.crud.batchInsert

appDatabase.batchInsert(
    UserInfoTable,
    listOf(
        mapOf("name" to "Lin", "info" to "x", "email" to "lin@example.com"),
        mapOf("name" to "Grace", "info" to "y", "email" to "grace@example.com"),
    ),
)

INSERT INTO "t_user_info" ("name", "info", "email") VALUES (?, ?, ?), (?, ?, ?);

import com.zxhhyj.storm.crud.upsert

appDatabase.upsert(UserInfoTable).set {
    this[UserInfoTable.name] = "Dennis"
    this[UserInfoTable.info] = "C"
    this[UserInfoTable.email] = "dennis@example.com"
}.onConflict(UserInfoTable.email).execute()

INSERT INTO "t_user_info" ("name", "info") VALUES (?, ?)
    ON CONFLICT ("email") DO UPDATE SET "name" = excluded."name", "info" = excluded."info";

5.2 更新与删除

import com.zxhhyj.storm.crud.update

appDatabase.update(UserInfoTable).set {
    this[UserInfoTable.name] = "updated"
}.where { UserInfoTable.name eq "Hao" }.execute()

UPDATE "t_user_info" SET "name" = ? WHERE "name" = ?;

import com.zxhhyj.storm.crud.updateAll

appDatabase.updateAll(UserInfoTable) { this[UserInfoTable.info] = "bulk" }

UPDATE "t_user_info" SET "info" = ?;

import com.zxhhyj.storm.crud.delete

appDatabase.delete(UserInfoTable).where { UserInfoTable.name eq "Hao" }.execute()

DELETE FROM "t_user_info" WHERE "name" = ?;

import com.zxhhyj.storm.crud.deleteAll

appDatabase.transaction { deleteAll(UserInfoTable) }

DELETE FROM "t_user_info";

5.3 查询

import com.zxhhyj.storm.crud.querySingle

val user: QueryBuilder.ResultSet? =
    appDatabase.querySingle(UserInfoTable) { UserInfoTable.name eq "Hao" }

SELECT * FROM "t_user_info" WHERE "name" = ? LIMIT 1;

import com.zxhhyj.storm.crud.form

val users: List<QueryBuilder.ResultSet> = appDatabase.form(UserInfoTable).selectAll().toList()

SELECT * FROM "t_user_info";

val count: Long = appDatabase.form(UserInfoTable).selectAll().count()

SELECT COUNT(*) FROM "t_user_info";

5.4 `RETURNING`

val deleted = appDatabase.delete(UserInfoTable)
    .where { UserInfoTable.name eq "Hao" }
    .returning(UserInfoTable.name, UserInfoTable.email)
    .executeReturning()

6. 条件 / 谓词 DSL

import com.zxhhyj.storm.dsl.ConditionalExpression.Companion.between
import com.zxhhyj.storm.dsl.ConditionalExpression.Companion.eq
import com.zxhhyj.storm.dsl.ConditionalExpression.Companion.eqDistinct
import com.zxhhyj.storm.dsl.ConditionalExpression.Companion.gte
import com.zxhhyj.storm.dsl.ConditionalExpression.Companion.isNotNull
import com.zxhhyj.storm.dsl.ConditionalExpression.Companion.isNull
import com.zxhhyj.storm.dsl.ConditionalExpression.Companion.like
import com.zxhhyj.storm.dsl.ConditionalExpression.Companion.lt
import com.zxhhyj.storm.dsl.ConditionalExpression.Companion.neqDistinct
import com.zxhhyj.storm.dsl.ConditionalExpression.Companion.notBetween

where { UserInfoTable.name eq "Hao" }
where { UserInfoTable.age gte 18 }
where { UserInfoTable.age lt 30 }
where { UserInfoTable.name like "%a%" }
where { UserInfoTable.info.isNull() }
where { UserInfoTable.info.isNotNull() }
where { UserInfoTable.age between (18..65) }
where { UserInfoTable.age notBetween (18..65) }
where { UserInfoTable.name eqDistinct null }
where { UserInfoTable.name neqDistinct null }

where { (UserInfoTable.age gte 18) and (UserInfoTable.name like "%a%") }

ColumnRef 的运算符也接受其他 ColumnRef —— 在 JOIN 谓词里很有用：

where { JoinRightTable.leftId eq JoinLeftTable.id }

运算符	SQL 渲染
`a eq b` / `a == b`	`a = b`
`a neq b` / `a != b`	`a <> b`
`a gt b` / `a > b`	`a > b`
`a gte b` / `a >= b`	`a >= b`
`a lt b` / `a < b`	`a < b`
`a lte b` / `a <= b`	`a <= b`
`a like pattern`	`a LIKE pattern`
`a.between(range)` / `notBetween(r)`	`a BETWEEN x AND y`
`a.in(list)` / `notIn(list)`	`a IN (?, ?, …)`
`a.in(qb)`	`a IN (SELECT …)` (infix)
`a.isNull()` / `isNotNull()`	`a IS [NOT] NULL`
`a eqDistinct b` / `neqDistinct b`	`a IS b` / `IS NOT b`
`cond and cond` / `or` / `not(cond)`	`AND` / `OR` / `NOT`

7. 查询构造器 —— `FormBuilder` / `QueryBuilder`

import com.zxhhyj.storm.dsl.SortOrder

val top10: List<QueryBuilder.ResultSet> = appDatabase.form(UserTable)
    .select(UserTable.id, UserTable.email)
    .innerJoin(UserInfoTable) { UserTable.userInfoId eq UserInfoTable.id }
    .where { UserTable.email like "%@example.com%" }
    .groupBy(UserInfoTable.id)
    .having { UserInfoTable.id gte 1L }
    .orderBy(UserTable.id, SortOrder.DESC)
    .limit(10)
    .offset(20)
    .toList()

SELECT "id", "email"
FROM "t_user"
INNER JOIN "t_user_info" ON "t_user"."user_info_id" = "t_user_info"."id"
WHERE "t_user"."email" LIKE ?
GROUP BY "t_user_info"."id"
HAVING "t_user_info"."id" >= ?
ORDER BY "t_user"."id" DESC
LIMIT 10
OFFSET 20;

聚合：

val sumAge = appDatabase.form(ScoresTable).selectAll().sum(ScoresTable.age)
val avgAge = appDatabase.form(ScoresTable).selectAll().avg(ScoresTable.age)
val minAge = appDatabase.form(ScoresTable).selectAll().min(ScoresTable.age)
val maxAge = appDatabase.form(ScoresTable).selectAll().max(ScoresTable.age)
val names = appDatabase.form(ScoresTable).selectAll().groupConcat(ScoresTable.name, ", ")

7.1 CTE 与窗口函数

import com.zxhhyj.storm.dsl.SortOrder
import com.zxhhyj.storm.dsl.rowNumber
import com.zxhhyj.storm.dsl.over

val ranked = appDatabase.form(ScoresTable)
    .withCte("high", appDatabase.form(ScoresTable).selectAll().where { ScoresTable.age gte 18 })
    .selectExpr(
        (rowNumber().over(
            partitionBy = ScoresTable.name,
            orderBy = ScoresTable.age to SortOrder.ASC,
        )) `as` "rn"
    )
    .orderBy(ScoresTable.age, SortOrder.DESC)
    .toList()

7.2 标量表达式

import com.zxhhyj.storm.dsl.caseWhen
import com.zxhhyj.storm.dsl.coalesce

where { coalesce(UserInfoTable.info, "unknown") eq "x" }

caseWhen {
    branch(UserInfoTable.age gte 18) thenValue "adult"
    otherwise "minor"
}

8. DDL

import com.zxhhyj.storm.database.createTable

appDatabase.createTable(UserTable, ifNotExists = true)

CREATE TABLE IF NOT EXISTS "t_user" (...);

import com.zxhhyj.storm.database.dropTableIfExists

appDatabase.dropTableIfExists(UserTable)

DROP TABLE IF EXISTS "t_user";

import com.zxhhyj.storm.database.dropIndex

appDatabase.dropIndex("idx_user_email", ifExists = true)

DROP INDEX IF EXISTS "idx_user_email";

import com.zxhhyj.storm.database.createTableAndIndex
import com.zxhhyj.storm.database.createIndex
import com.zxhhyj.storm.database.addColumn

appDatabase.createTableAndIndex(UserTable)
appDatabase.createIndex(UserTable, ifNotExists = true)

val nickname = UserTable.varchar("nickname").nullable()
appDatabase.addColumn(UserTable, nickname)

val createSql: String = UserTable.createTable(ifNotExists = true)
val dropSql: String = UserTable.dropTable(ifExists = true)
val nickname = UserTable.varchar("nickname").nullable()
val addColumnSql: String = UserTable addColumn nickname

CREATE TABLE IF NOT EXISTS "t_user" (...);
DROP TABLE IF EXISTS "t_user";
ALTER TABLE "t_user" ADD COLUMN ...;

9. 观察数据

import com.zxhhyj.storm.observe.observe

val count: Flow<Long> = appDatabase.observe(UserInfoTable) { ops ->
    ops.form(UserInfoTable).selectAll().count()
}

val filtered: Flow<List<QueryBuilder.ResultSet>> =
    appDatabase.form(UserInfoTable).selectAll()
        .where { UserInfoTable.age gte 18 }
        .observe()

filtered.collect { rows -> println("got ${rows.size} adults") }

10. 事务与 Savepoint

import com.zxhhyj.storm.crud.insert

appDatabase.transaction {
    insert(UserInfoTable) {
        this[UserInfoTable.name] = "Hao"
        this[UserInfoTable.info] = "x"
    }
}

appDatabase.transaction {
    appDatabase.transaction {
        println(depth)
        println(savepointName)
    }
}

11. 迁移

用 migrations 注册 migrate(from, to) { … } 步骤：

import com.zxhhyj.storm.database.ReadWriteDatabase
import com.zxhhyj.storm.database.addColumn
import com.zxhhyj.storm.database.createTable

val password = UserTable.varchar("password").notNull().default("")

val appDatabase = ReadWriteDatabase(
    connection = AndroidXSQLiteConnection(BundledSQLiteDriver().open("app.db")),
    version = 2,
    migrations = {
        migrate(1, 2) {
            addColumn(UserTable, password)
        }
    },
    onDatabaseCreate = {
        createTable(UserInfoTable, ifNotExists = true)
        createTable(UserTable, ifNotExists = true)
    },
    onCreate = {},
    onClose = {},
)

ALTER TABLE "t_user" ADD COLUMN password TEXT NOT NULL DEFAULT '';

12. 杂项 PRAGMA

appDatabase.enableCaseSensitiveLike()
appDatabase.disableCaseSensitiveLike()

println(appDatabase.foreignKeysEnabled)

@OptIn(ExperimentalStormApi::class)
appDatabase.execSQL("VACUUM")

升级指南

从旧版升级时请注意以下破坏性变更（详见 CHANGELOG.md Unreleased）：

Table 去泛型：object X : Table<X>("…") → object X : Table("…")。列 helper（int、varchar、 index 等）定义在 com.zxhhyj.storm.schema 包；在表 object 所在文件添加 import com.zxhhyj.storm.schema.*。各列仍为 ColumnBuilder<具体表类型, …>，写入 DSL 继续拒绝跨表混用列。
移除 storm-orm：不再发布 storm-orm、storm-ksp、storm-gradle-plugin。请使用 Table + form() / RowBuilder 行级 CRUD，或在应用层自行把查询结果映射为 data class。
枚举列：enum<Status>("status") → enum("status", Status::class)。

参与贡献

欢迎提 Issues 和 PR，你的 Issues 就是我最大的动力，你的 PR 是对我最大的支持。

开源协议

本项目基于 Apache License 2.0。

交流

如有疑问或建议，请提 Issues 或者写一封邮件发送到 zxhhyj@qq.com。

storm

介绍

安装

快速上手

1. 定义 UserInfo 表

2. 定义带外键的 User 表

3. 打开数据库并创建 schema

3.1 只读场景

4. 列类型与修饰符

4.1 索引

5. 行级 CRUD

5.1 插入

5.2 更新与删除

5.3 查询

5.4 RETURNING

6. 条件 / 谓词 DSL

7. 查询构造器 —— FormBuilder / QueryBuilder

7.1 CTE 与窗口函数

7.2 标量表达式

8. DDL

9. 观察数据

10. 事务与 Savepoint

11. 迁移

12. 杂项 PRAGMA

升级指南

参与贡献

开源协议

交流

介绍

安装

快速上手

1. 定义 UserInfo 表

2. 定义带外键的 User 表

3. 打开数据库并创建 schema

3.1 只读场景

4. 列类型与修饰符

4.1 索引

5. 行级 CRUD

5.1 插入

5.2 更新与删除

5.3 查询

5.4 RETURNING

6. 条件 / 谓词 DSL

7. 查询构造器 —— FormBuilder / QueryBuilder

7.1 CTE 与窗口函数

7.2 标量表达式

8. DDL

9. 观察数据

10. 事务与 Savepoint

11. 迁移

12. 杂项 PRAGMA

升级指南

参与贡献

开源协议

交流

1. 定义 `UserInfo` 表

2. 定义带外键的 `User` 表

5.4 `RETURNING`

7. 查询构造器 —— `FormBuilder` / `QueryBuilder`

1. 定义 `UserInfo` 表

2. 定义带外键的 `User` 表

5.4 `RETURNING`

7. 查询构造器 —— `FormBuilder` / `QueryBuilder`