Blog App Tutorial Part 5

transaction {
    UserRow newUser = check (check db.'from(User).create({
        email: "transaction@example.com",
        name: "Transaction User",
        status: ACTIVE
    })).cloneWithType();

    UserProfileRow _ = check (check db.'from(UserProfile).create({
        userId: newUser.id,
        bio: "Created within a transaction"
    })).cloneWithType();

    check commit;
}

transaction {
    UserRow _ = check (check db.'from(User).create({
        email: "rollback@example.com",
        name: "Rollback User",
        status: ACTIVE
    })).cloneWithType();

    check error("Simulated error for rollback");
    check commit;
} on fail {
    // rollback handled automatically
}

record {}? rolledBackRow = check db.'from(User)
    .'where({email: {'equals: "rollback@example.com"}})
    .findUnique();

if rolledBackRow is () {
    // expected: user not persisted
}

stream<record {}, error?> resultStream = check db.rawQuery(
    "SELECT u.name, COUNT(p.id) as post_count" +
    " FROM users u" +
    " LEFT JOIN posts p ON u.id = p.author_id" +
    " GROUP BY u.id, u.name" +
    " HAVING COUNT(p.id) > 0" +
    " ORDER BY COUNT(p.id) DESC"
);

record {}[] results = check from var row in resultStream select row;

_ = check db.rawExecute(
    "UPDATE users" +
    " SET updated_at = NOW()" +
    " WHERE id IN (SELECT DISTINCT author_id FROM posts WHERE status = 'PUBLISHED')"
);