SQLDelight - Database trong KMP

[versions]
sqldelight = "2.0.1"
 
[plugins]
sqldelight = { id = "app.cash.sqldelight", version.ref = "sqldelight" }
 
[libraries]
sqldelight-runtime = { module = "app.cash.sqldelight:runtime", version.ref = "sqldelight" }
sqldelight-coroutines = { module = "app.cash.sqldelight:coroutines-extensions", version.ref = "sqldelight" }
sqldelight-android = { module = "app.cash.sqldelight:android-driver", version.ref = "sqldelight" }
sqldelight-native = { module = "app.cash.sqldelight:native-driver", version.ref = "sqldelight" }

plugins {
    alias(libs.plugins.kotlinMultiplatform)
    alias(libs.plugins.sqldelight)
}
 
kotlin {
    sourceSets {
        commonMain.dependencies {
            implementation(libs.sqldelight.runtime)
            implementation(libs.sqldelight.coroutines)
        }
        androidMain.dependencies {
            implementation(libs.sqldelight.android)
        }
        iosMain.dependencies {
            implementation(libs.sqldelight.native)
        }
    }
}
 
sqldelight {
    databases {
        create("AppDatabase") {
            packageName.set("com.example.db")
        }
    }
}

-- Tạo bảng
CREATE TABLE User (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    createdAt INTEGER NOT NULL
);
 
-- Insert
insertUser:
INSERT INTO User (name, email, createdAt) VALUES (?, ?, ?);
 
-- Select all
selectAll:
SELECT * FROM User ORDER BY createdAt DESC;
 
-- Select by ID
selectById:
SELECT * FROM User WHERE id = ?;
 
-- Select by email
selectByEmail:
SELECT * FROM User WHERE email = ?;
 
-- Update
updateUser:
UPDATE User SET name = ?, email = ? WHERE id = ?;
 
-- Delete
deleteById:
DELETE FROM User WHERE id = ?;
 
-- Delete all
deleteAll:
DELETE FROM User;
 
-- Count
countUsers:
SELECT COUNT(*) FROM User;

// commonMain/database/DatabaseDriverFactory.kt
expect class DatabaseDriverFactory {
    fun createDriver(): SqlDriver
}

// androidMain/database/DatabaseDriverFactory.android.kt
import android.content.Context
import app.cash.sqldelight.driver.android.AndroidSqliteDriver
 
actual class DatabaseDriverFactory(private val context: Context) {
    actual fun createDriver(): SqlDriver {
        return AndroidSqliteDriver(
            AppDatabase.Schema,
            context,
            "app.db"
        )
    }
}

// iosMain/database/DatabaseDriverFactory.ios.kt
import app.cash.sqldelight.driver.native.NativeSqliteDriver
 
actual class DatabaseDriverFactory {
    actual fun createDriver(): SqlDriver {
        return NativeSqliteDriver(
            AppDatabase.Schema,
            "app.db"
        )
    }
}

// commonMain/repository/UserRepository.kt
import app.cash.sqldelight.coroutines.asFlow
import app.cash.sqldelight.coroutines.mapToList
import app.cash.sqldelight.coroutines.mapToOne
import kotlinx.coroutines.Dispatchers
import kotlinx.coroutines.IO
import kotlinx.coroutines.flow.Flow
 
class UserRepository(driverFactory: DatabaseDriverFactory) {
    
    private val database = AppDatabase(driverFactory.createDriver())
    private val queries = database.userQueries
    
    // Insert
    suspend fun insertUser(name: String, email: String) {
        queries.insertUser(
            name = name,
            email = email,
            createdAt = System.currentTimeMillis()
        )
    }
    
    // Get all as Flow (reactive)
    fun observeAllUsers(): Flow<List<User>> {
        return queries.selectAll()
            .asFlow()
            .mapToList(Dispatchers.IO)
    }
    
    // Get by ID
    suspend fun getUserById(id: Long): User? {
        return queries.selectById(id).executeAsOneOrNull()
    }
    
    // Update
    suspend fun updateUser(id: Long, name: String, email: String) {
        queries.updateUser(name, email, id)
    }
    
    // Delete
    suspend fun deleteUser(id: Long) {
        queries.deleteById(id)
    }
    
    // Count
    suspend fun getUserCount(): Long {
        return queries.countUsers().executeAsOne()
    }
}

// commonMain/presentation/UserViewModel.kt
class UserViewModel(private val repository: UserRepository) {
    
    val users: StateFlow<List<User>> = repository.observeAllUsers()
        .stateIn(
            scope = viewModelScope,
            started = SharingStarted.WhileSubscribed(5000),
            initialValue = emptyList()
        )
    
    fun addUser(name: String, email: String) {
        viewModelScope.launch {
            repository.insertUser(name, email)
        }
    }
    
    fun deleteUser(user: User) {
        viewModelScope.launch {
            repository.deleteUser(user.id)
        }
    }
}

-- Search by name
searchByName:
SELECT * FROM User WHERE name LIKE '%' || ? || '%';
 
-- Pagination
selectPaged:
SELECT * FROM User ORDER BY createdAt DESC LIMIT ? OFFSET ?;
 
-- Join với bảng khác
selectUserWithPosts:
SELECT 
    User.id,
    User.name,
    COUNT(Post.id) AS postCount
FROM User
LEFT JOIN Post ON User.id = Post.userId
GROUP BY User.id;

CREATE TABLE Post (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    userId INTEGER NOT NULL,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    createdAt INTEGER NOT NULL,
    FOREIGN KEY (userId) REFERENCES User(id) ON DELETE CASCADE
);
 
insertPost:
INSERT INTO Post (userId, title, content, createdAt) VALUES (?, ?, ?, ?);
 
selectByUser:
SELECT * FROM Post WHERE userId = ? ORDER BY createdAt DESC;

sqldelight/
├── com/example/db/
│   ├── User.sq           # Current schema
│   └── migrations/
│       ├── 1.sqm         # Migration v1 → v2
│       └── 2.sqm         # Migration v2 → v3

-- Add avatar column to User table
ALTER TABLE User ADD COLUMN avatarUrl TEXT;

@Composable
fun UserListScreen(viewModel: UserViewModel) {
    val users by viewModel.users.collectAsState()
    var showAddDialog by remember { mutableStateOf(false) }
    
    Scaffold(
        floatingActionButton = {
            FloatingActionButton(onClick = { showAddDialog = true }) {
                Icon(Icons.Default.Add, "Add user")
            }
        }
    ) { padding ->
        LazyColumn(modifier = Modifier.padding(padding)) {
            items(users, key = { it.id }) { user ->
                UserItem(
                    user = user,
                    onDelete = { viewModel.deleteUser(user) }
                )
            }
        }
    }
    
    if (showAddDialog) {
        AddUserDialog(
            onDismiss = { showAddDialog = false },
            onConfirm = { name, email ->
                viewModel.addUser(name, email)
                showAddDialog = false
            }
        )
    }
}