Skip to main content

QueryResult

lattice_sql_client

Struct QueryResult 

Source 
pub struct QueryResult {
    pub columns: Vec<String>,
    pub rows: Vec<Vec<Value>>,
}
Expand description

The result of a SELECT query.

Rows are ordered as returned by the engine. Each row is a Vec of serde_json::Value aligned with columns.

Fields§

§columns: Vec<String>

Column names in projection order.

§rows: Vec<Vec<Value>>

Rows as arrays of JSON values, one per projected column.

Implementations§

Source§

impl QueryResult

Source

pub fn row_count(&self) -> usize

Number of rows in the result set.

Examples found in repository?
examples/smoke_test.rs (line 160)
60async fn run_tests() {
61    let t0 = wasip3::clocks::monotonic_clock::now();
62    let db = connect().await;
63
64    // Unique table names per run to avoid catalog collisions.
65    let ts = wasip3::clocks::monotonic_clock::now();
66    let users = format!("smoke_u{ts}");
67    let orders = format!("smoke_o{ts}");
68
69    // ── DDL: CREATE TABLE ──────────────────────────────────────
70
71    run_test!("CREATE TABLE users", {
72        db.ddl(&format!(
73            "CREATE TABLE {users} (id TEXT PRIMARY KEY, name TEXT NOT NULL, age INTEGER, city TEXT)"
74        ))
75        .await
76        .unwrap();
77    });
78
79    run_test!("CREATE TABLE orders", {
80        db.ddl(&format!(
81            "CREATE TABLE {orders} (id TEXT PRIMARY KEY, user_id TEXT, amount INTEGER)"
82        ))
83        .await
84        .unwrap();
85    });
86
87    run_test!("CREATE TABLE IF NOT EXISTS (duplicate is ok)", {
88        db.ddl(&format!(
89            "CREATE TABLE IF NOT EXISTS {users} (id TEXT PRIMARY KEY, name TEXT)"
90        ))
91        .await
92        .unwrap();
93    });
94
95    // ── DML: INSERT ────────────────────────────────────────────
96
97    run_test!("INSERT alice", {
98        let affected = db
99            .exec(&format!(
100                "INSERT INTO {users} (id, name, age, city) VALUES ('alice', 'Alice', 30, 'Helsinki')"
101            ))
102            .await
103            .unwrap();
104        assert_eq!(affected, 1, "affected_rows should be 1");
105    });
106
107    run_test!("INSERT bob", {
108        let affected = db
109            .exec(&format!(
110                "INSERT INTO {users} (id, name, age, city) VALUES ('bob', 'Bob', 25, 'Tampere')"
111            ))
112            .await
113            .unwrap();
114        assert_eq!(affected, 1);
115    });
116
117    run_test!("INSERT carol", {
118        let affected = db
119            .exec(&format!(
120                "INSERT INTO {users} (id, name, age, city) VALUES ('carol', 'Carol', 35, 'Helsinki')"
121            ))
122            .await
123            .unwrap();
124        assert_eq!(affected, 1);
125    });
126
127    run_test!("INSERT duplicate PK fails", {
128        let err = db
129            .exec(&format!(
130                "INSERT INTO {users} (id, name, age, city) VALUES ('alice', 'Alice2', 99, 'Oulu')"
131            ))
132            .await;
133        assert!(err.is_err(), "duplicate PK should return an error");
134    });
135
136    // Seed orders for JOIN tests.
137    db.exec(&format!(
138        "INSERT INTO {orders} (id, user_id, amount) VALUES ('o1', 'alice', 100)"
139    ))
140    .await
141    .unwrap();
142    db.exec(&format!(
143        "INSERT INTO {orders} (id, user_id, amount) VALUES ('o2', 'alice', 250)"
144    ))
145    .await
146    .unwrap();
147    db.exec(&format!(
148        "INSERT INTO {orders} (id, user_id, amount) VALUES ('o3', 'carol', 150)"
149    ))
150    .await
151    .unwrap();
152
153    // ── SELECT (basic) ─────────────────────────────────────────
154
155    run_test!("SELECT *", {
156        let result = db
157            .query(&format!("SELECT * FROM {users}"))
158            .await
159            .unwrap();
160        assert_eq!(result.row_count(), 3, "should have 3 rows");
161        assert_eq!(result.columns.len(), 4, "should have 4 columns");
162        assert!(result.columns.contains(&"id".to_string()));
163        assert!(result.columns.contains(&"name".to_string()));
164        assert!(result.columns.contains(&"age".to_string()));
165        assert!(result.columns.contains(&"city".to_string()));
166    });
167
168    run_test!("SELECT specific columns", {
169        let result = db
170            .query(&format!("SELECT id, name FROM {users}"))
171            .await
172            .unwrap();
173        assert_eq!(result.columns.len(), 2);
174        assert_eq!(result.columns[0], "id");
175        assert_eq!(result.columns[1], "name");
176        assert_eq!(result.row_count(), 3);
177    });
178
179    run_test!("cell() helper", {
180        let result = db
181            .query(&format!("SELECT * FROM {users} WHERE id = 'alice'"))
182            .await
183            .unwrap();
184        assert_eq!(result.row_count(), 1);
185        let name = result.cell(0, "name").unwrap();
186        assert_eq!(name, "Alice");
187        let age = result.cell(0, "age").unwrap();
188        assert_eq!(age, 30);
189    });
190
191    // ── SELECT (filtered) ─────────────────────────────────────
192
193    run_test!("SELECT WHERE eq", {
194        let result = db
195            .query(&format!("SELECT * FROM {users} WHERE city = 'Helsinki'"))
196            .await
197            .unwrap();
198        assert_eq!(result.row_count(), 2, "alice + carol are in Helsinki");
199    });
200
201    run_test!("SELECT WHERE gt", {
202        let result = db
203            .query(&format!("SELECT * FROM {users} WHERE age > 28"))
204            .await
205            .unwrap();
206        assert_eq!(result.row_count(), 2, "alice(30) and carol(35)");
207    });
208
209    run_test!("SELECT WHERE lt", {
210        let result = db
211            .query(&format!("SELECT * FROM {users} WHERE age < 30"))
212            .await
213            .unwrap();
214        assert_eq!(result.row_count(), 1, "only bob(25)");
215        let id = result.cell(0, "id").unwrap();
216        assert_eq!(id, "bob");
217    });
218
219    run_test!("SELECT WHERE AND", {
220        let result = db
221            .query(&format!(
222                "SELECT * FROM {users} WHERE city = 'Helsinki' AND age > 30"
223            ))
224            .await
225            .unwrap();
226        assert_eq!(result.row_count(), 1, "only carol (city=Helsinki, age=35)");
227        let id = result.cell(0, "id").unwrap();
228        assert_eq!(id, "carol");
229    });
230
231    // ── SELECT (sorted + paginated) ───────────────────────────
232
233    run_test!("SELECT ORDER BY ASC LIMIT OFFSET", {
234        let page1 = db
235            .query(&format!(
236                "SELECT * FROM {users} ORDER BY name ASC LIMIT 2 OFFSET 0"
237            ))
238            .await
239            .unwrap();
240        assert_eq!(page1.row_count(), 2);
241        // Alice < Bob < Carol
242        let first_name = page1.cell(0, "name").unwrap();
243        assert_eq!(first_name, "Alice");
244        let second_name = page1.cell(1, "name").unwrap();
245        assert_eq!(second_name, "Bob");
246
247        let page2 = db
248            .query(&format!(
249                "SELECT * FROM {users} ORDER BY name ASC LIMIT 2 OFFSET 2"
250            ))
251            .await
252            .unwrap();
253        assert_eq!(page2.row_count(), 1);
254        let last_name = page2.cell(0, "name").unwrap();
255        assert_eq!(last_name, "Carol");
256    });
257
258    run_test!("SELECT ORDER BY DESC", {
259        let result = db
260            .query(&format!(
261                "SELECT * FROM {users} ORDER BY name DESC LIMIT 1"
262            ))
263            .await
264            .unwrap();
265        let first_name = result.cell(0, "name").unwrap();
266        assert_eq!(first_name, "Carol");
267    });
268
269    // ── SELECT (aggregate) ────────────────────────────────────
270
271    run_test!("COUNT(*)", {
272        let result = db
273            .query(&format!("SELECT COUNT(*) FROM {users}"))
274            .await
275            .unwrap();
276        assert_eq!(result.row_count(), 1);
277        let count = &result.rows[0][0];
278        assert_eq!(count, 3, "should count 3 rows");
279    });
280
281    run_test!("SUM(age)", {
282        let result = db
283            .query(&format!("SELECT SUM(age) FROM {users}"))
284            .await
285            .unwrap();
286        let sum = result.rows[0][0].as_f64().unwrap();
287        assert_eq!(sum, 90.0, "30 + 25 + 35 = 90");
288    });
289
290    run_test!("AVG(age)", {
291        let result = db
292            .query(&format!("SELECT AVG(age) FROM {users}"))
293            .await
294            .unwrap();
295        let avg = result.rows[0][0].as_f64().unwrap();
296        assert_eq!(avg, 30.0, "(30+25+35)/3 = 30");
297    });
298
299    run_test!("MIN + MAX", {
300        let result = db
301            .query(&format!("SELECT MIN(age), MAX(age) FROM {users}"))
302            .await
303            .unwrap();
304        let min = result.rows[0][0].as_f64().unwrap();
305        let max = result.rows[0][1].as_f64().unwrap();
306        assert_eq!(min, 25.0);
307        assert_eq!(max, 35.0);
308    });
309
310    run_test!("GROUP BY city COUNT(*)", {
311        let result = db
312            .query(&format!(
313                "SELECT city, COUNT(*) FROM {users} GROUP BY city"
314            ))
315            .await
316            .unwrap();
317        // Helsinki: alice + carol = 2, Tampere: bob = 1
318        assert_eq!(result.row_count(), 2, "2 distinct cities");
319
320        // Find Helsinki group.
321        let helsinki_row = result
322            .rows
323            .iter()
324            .find(|row| row[0].as_str() == Some("Helsinki"))
325            .expect("Helsinki group missing");
326        assert_eq!(helsinki_row[1], 2, "Helsinki has 2 users");
327
328        let tampere_row = result
329            .rows
330            .iter()
331            .find(|row| row[0].as_str() == Some("Tampere"))
332            .expect("Tampere group missing");
333        assert_eq!(tampere_row[1], 1, "Tampere has 1 user");
334    });
335
336    // ── UPDATE ─────────────────────────────────────────────────
337
338    run_test!("UPDATE single row (PK fast path)", {
339        let affected = db
340            .exec(&format!(
341                "UPDATE {users} SET city = 'Espoo', age = 31 WHERE id = 'alice'"
342            ))
343            .await
344            .unwrap();
345        assert_eq!(affected, 1);
346
347        let result = db
348            .query(&format!("SELECT * FROM {users} WHERE id = 'alice'"))
349            .await
350            .unwrap();
351        assert_eq!(result.cell(0, "city").unwrap(), "Espoo");
352        assert_eq!(result.cell(0, "age").unwrap(), 31);
353    });
354
355    run_test!("UPDATE multiple rows", {
356        // Both alice (Espoo) and bob (Tampere) do NOT match, only carol (Helsinki).
357        let affected = db
358            .exec(&format!(
359                "UPDATE {users} SET city = 'Vantaa' WHERE city = 'Helsinki'"
360            ))
361            .await
362            .unwrap();
363        assert!(affected >= 1, "at least carol should be updated");
364
365        let result = db
366            .query(&format!("SELECT * FROM {users} WHERE city = 'Vantaa'"))
367            .await
368            .unwrap();
369        assert!(result.row_count() >= 1, "vantaa row exists after update");
370    });
371
372    // ── JOIN ───────────────────────────────────────────────────
373
374    run_test!("INNER JOIN", {
375        // alice has 2 orders (o1, o2), carol has 1 (o3), bob has none.
376        // INNER JOIN → 3 combined rows.
377        let result = db
378            .query(&format!(
379                "SELECT {users}.id, {users}.name, {orders}.amount \
380                 FROM {users} INNER JOIN {orders} ON {users}.id = {orders}.user_id"
381            ))
382            .await
383            .unwrap();
384        assert_eq!(result.row_count(), 3, "alice×2 + carol×1 = 3 inner join rows");
385        assert!(result.col_index("amount").is_some(), "amount column present");
386    });
387
388    run_test!("LEFT JOIN", {
389        // LEFT JOIN includes bob with null amount.
390        let result = db
391            .query(&format!(
392                "SELECT {users}.id, {users}.name, {orders}.amount \
393                 FROM {users} LEFT JOIN {orders} ON {users}.id = {orders}.user_id"
394            ))
395            .await
396            .unwrap();
397        // alice×2 + bob×1(null) + carol×1 = 4 rows.
398        assert_eq!(result.row_count(), 4, "alice×2 + bob + carol = 4 left join rows");
399    });
400
401    // ── deserialize_rows ───────────────────────────────────────
402
403    run_test!("deserialize_rows<T>", {
404        #[derive(serde::Deserialize, PartialEq, Debug)]
405        struct User {
406            id: String,
407            name: String,
408        }
409
410        let result = db
411            .query(&format!("SELECT id, name FROM {users} ORDER BY name ASC"))
412            .await
413            .unwrap();
414        let users_vec: Vec<User> = result.deserialize_rows().unwrap();
415        assert_eq!(users_vec.len(), 3);
416        assert_eq!(users_vec[0].name, "Alice");
417        assert_eq!(users_vec[2].name, "Carol");
418    });
419
420    // ── sql() auto-detect ──────────────────────────────────────
421
422    run_test!("sql() → SqlResult::Query for SELECT", {
423        let r = db
424            .sql(&format!("SELECT COUNT(*) FROM {users}"))
425            .await
426            .unwrap();
427        assert!(r.is_query(), "SELECT should return Query variant");
428    });
429
430    run_test!("sql() → SqlResult::Exec for DML", {
431        let r = db
432            .sql(&format!(
433                "UPDATE {users} SET age = 31 WHERE id = 'alice'"
434            ))
435            .await
436            .unwrap();
437        assert!(r.is_exec(), "UPDATE should return Exec variant");
438        let n = r.into_affected_rows().unwrap();
439        assert_eq!(n, 1);
440    });
441
442    // ── DELETE ─────────────────────────────────────────────────
443
444    run_test!("DELETE single row", {
445        let affected = db
446            .exec(&format!("DELETE FROM {users} WHERE id = 'bob'"))
447            .await
448            .unwrap();
449        assert_eq!(affected, 1);
450
451        let result = db
452            .query(&format!("SELECT * FROM {users}"))
453            .await
454            .unwrap();
455        assert_eq!(result.row_count(), 2, "bob removed → 2 rows remain");
456    });
457
458    run_test!("DELETE multiple rows", {
459        let affected = db
460            .exec(&format!("DELETE FROM {orders} WHERE user_id = 'alice'"))
461            .await
462            .unwrap();
463        assert_eq!(affected, 2, "alice had 2 orders");
464
465        let result = db
466            .query(&format!("SELECT * FROM {orders}"))
467            .await
468            .unwrap();
469        assert_eq!(result.row_count(), 1, "only carol's order remains");
470    });
471
472    // ── Error handling ─────────────────────────────────────────
473
474    run_test!("query() on DML returns WrongResultType", {
475        // Run an INSERT through query() — service will return affected_rows,
476        // so into_query() should fail.
477        let err = db
478            .query(&format!(
479                "INSERT INTO {users} (id, name, age, city) VALUES ('dave', 'Dave', 28, 'Turku')"
480            ))
481            .await;
482        // This may succeed if lattice-sql happens to send columns (it won't),
483        // or fail with WrongResultType — either demonstrates the type check.
484        // What matters is that exec() with the same SQL succeeds.
485        let _ = err; // tolerate either branch for robustness
486    });
487
488    run_test!("invalid SQL returns Db error", {
489        let err = db.sql("THIS IS NOT VALID SQL AT ALL").await;
490        assert!(err.is_err(), "invalid SQL should error");
491        assert!(
492            matches!(err.unwrap_err(), lattice_sql_client::Error::Db(_)),
493            "should be a Db error"
494        );
495    });
496
497    run_test!("nonexistent table returns Db error", {
498        let err = db
499            .query(&format!("SELECT * FROM _smoke_no_table_{ts}"))
500            .await;
501        assert!(err.is_err());
502        assert!(matches!(err.unwrap_err(), lattice_sql_client::Error::Db(_)));
503    });
504
505    // ── DDL: CREATE INDEX / DROP INDEX ─────────────────────────
506
507    run_test!("CREATE INDEX", {
508        db.ddl(&format!("CREATE INDEX ON {users} (name)"))
509            .await
510            .unwrap();
511    });
512
513    run_test!("DROP INDEX", {
514        db.ddl("DROP INDEX name").await.unwrap();
515    });
516
517    // ── DDL: DROP TABLE ────────────────────────────────────────
518
519    run_test!("DROP TABLE", {
520        db.ddl(&format!("DROP TABLE {orders}")).await.unwrap();
521        db.ddl(&format!("DROP TABLE {users}")).await.unwrap();
522    });
523
524    run_test!("DROP TABLE IF EXISTS (nonexistent)", {
525        db.ddl(&format!("DROP TABLE IF EXISTS _smoke_gone_{ts}"))
526            .await
527            .unwrap();
528    });
529
530    run_test!("SELECT from dropped table → error", {
531        let err = db.query(&format!("SELECT * FROM {users}")).await;
532        assert!(err.is_err(), "dropped table should return an error");
533    });
534
535    // ── Done ───────────────────────────────────────────────────
536
537    let total_ms = (wasip3::clocks::monotonic_clock::now() - t0) / 1_000_000;
538    println!("\nAll SQL SDK tests passed! (total: {total_ms} ms)");
539}
Source

pub fn col_index(&self, name: &str) -> Option<usize>

Find the zero-based index of a column by name. Returns None if the column is not in the projection.

Examples found in repository?
examples/smoke_test.rs (line 385)
60async fn run_tests() {
61    let t0 = wasip3::clocks::monotonic_clock::now();
62    let db = connect().await;
63
64    // Unique table names per run to avoid catalog collisions.
65    let ts = wasip3::clocks::monotonic_clock::now();
66    let users = format!("smoke_u{ts}");
67    let orders = format!("smoke_o{ts}");
68
69    // ── DDL: CREATE TABLE ──────────────────────────────────────
70
71    run_test!("CREATE TABLE users", {
72        db.ddl(&format!(
73            "CREATE TABLE {users} (id TEXT PRIMARY KEY, name TEXT NOT NULL, age INTEGER, city TEXT)"
74        ))
75        .await
76        .unwrap();
77    });
78
79    run_test!("CREATE TABLE orders", {
80        db.ddl(&format!(
81            "CREATE TABLE {orders} (id TEXT PRIMARY KEY, user_id TEXT, amount INTEGER)"
82        ))
83        .await
84        .unwrap();
85    });
86
87    run_test!("CREATE TABLE IF NOT EXISTS (duplicate is ok)", {
88        db.ddl(&format!(
89            "CREATE TABLE IF NOT EXISTS {users} (id TEXT PRIMARY KEY, name TEXT)"
90        ))
91        .await
92        .unwrap();
93    });
94
95    // ── DML: INSERT ────────────────────────────────────────────
96
97    run_test!("INSERT alice", {
98        let affected = db
99            .exec(&format!(
100                "INSERT INTO {users} (id, name, age, city) VALUES ('alice', 'Alice', 30, 'Helsinki')"
101            ))
102            .await
103            .unwrap();
104        assert_eq!(affected, 1, "affected_rows should be 1");
105    });
106
107    run_test!("INSERT bob", {
108        let affected = db
109            .exec(&format!(
110                "INSERT INTO {users} (id, name, age, city) VALUES ('bob', 'Bob', 25, 'Tampere')"
111            ))
112            .await
113            .unwrap();
114        assert_eq!(affected, 1);
115    });
116
117    run_test!("INSERT carol", {
118        let affected = db
119            .exec(&format!(
120                "INSERT INTO {users} (id, name, age, city) VALUES ('carol', 'Carol', 35, 'Helsinki')"
121            ))
122            .await
123            .unwrap();
124        assert_eq!(affected, 1);
125    });
126
127    run_test!("INSERT duplicate PK fails", {
128        let err = db
129            .exec(&format!(
130                "INSERT INTO {users} (id, name, age, city) VALUES ('alice', 'Alice2', 99, 'Oulu')"
131            ))
132            .await;
133        assert!(err.is_err(), "duplicate PK should return an error");
134    });
135
136    // Seed orders for JOIN tests.
137    db.exec(&format!(
138        "INSERT INTO {orders} (id, user_id, amount) VALUES ('o1', 'alice', 100)"
139    ))
140    .await
141    .unwrap();
142    db.exec(&format!(
143        "INSERT INTO {orders} (id, user_id, amount) VALUES ('o2', 'alice', 250)"
144    ))
145    .await
146    .unwrap();
147    db.exec(&format!(
148        "INSERT INTO {orders} (id, user_id, amount) VALUES ('o3', 'carol', 150)"
149    ))
150    .await
151    .unwrap();
152
153    // ── SELECT (basic) ─────────────────────────────────────────
154
155    run_test!("SELECT *", {
156        let result = db
157            .query(&format!("SELECT * FROM {users}"))
158            .await
159            .unwrap();
160        assert_eq!(result.row_count(), 3, "should have 3 rows");
161        assert_eq!(result.columns.len(), 4, "should have 4 columns");
162        assert!(result.columns.contains(&"id".to_string()));
163        assert!(result.columns.contains(&"name".to_string()));
164        assert!(result.columns.contains(&"age".to_string()));
165        assert!(result.columns.contains(&"city".to_string()));
166    });
167
168    run_test!("SELECT specific columns", {
169        let result = db
170            .query(&format!("SELECT id, name FROM {users}"))
171            .await
172            .unwrap();
173        assert_eq!(result.columns.len(), 2);
174        assert_eq!(result.columns[0], "id");
175        assert_eq!(result.columns[1], "name");
176        assert_eq!(result.row_count(), 3);
177    });
178
179    run_test!("cell() helper", {
180        let result = db
181            .query(&format!("SELECT * FROM {users} WHERE id = 'alice'"))
182            .await
183            .unwrap();
184        assert_eq!(result.row_count(), 1);
185        let name = result.cell(0, "name").unwrap();
186        assert_eq!(name, "Alice");
187        let age = result.cell(0, "age").unwrap();
188        assert_eq!(age, 30);
189    });
190
191    // ── SELECT (filtered) ─────────────────────────────────────
192
193    run_test!("SELECT WHERE eq", {
194        let result = db
195            .query(&format!("SELECT * FROM {users} WHERE city = 'Helsinki'"))
196            .await
197            .unwrap();
198        assert_eq!(result.row_count(), 2, "alice + carol are in Helsinki");
199    });
200
201    run_test!("SELECT WHERE gt", {
202        let result = db
203            .query(&format!("SELECT * FROM {users} WHERE age > 28"))
204            .await
205            .unwrap();
206        assert_eq!(result.row_count(), 2, "alice(30) and carol(35)");
207    });
208
209    run_test!("SELECT WHERE lt", {
210        let result = db
211            .query(&format!("SELECT * FROM {users} WHERE age < 30"))
212            .await
213            .unwrap();
214        assert_eq!(result.row_count(), 1, "only bob(25)");
215        let id = result.cell(0, "id").unwrap();
216        assert_eq!(id, "bob");
217    });
218
219    run_test!("SELECT WHERE AND", {
220        let result = db
221            .query(&format!(
222                "SELECT * FROM {users} WHERE city = 'Helsinki' AND age > 30"
223            ))
224            .await
225            .unwrap();
226        assert_eq!(result.row_count(), 1, "only carol (city=Helsinki, age=35)");
227        let id = result.cell(0, "id").unwrap();
228        assert_eq!(id, "carol");
229    });
230
231    // ── SELECT (sorted + paginated) ───────────────────────────
232
233    run_test!("SELECT ORDER BY ASC LIMIT OFFSET", {
234        let page1 = db
235            .query(&format!(
236                "SELECT * FROM {users} ORDER BY name ASC LIMIT 2 OFFSET 0"
237            ))
238            .await
239            .unwrap();
240        assert_eq!(page1.row_count(), 2);
241        // Alice < Bob < Carol
242        let first_name = page1.cell(0, "name").unwrap();
243        assert_eq!(first_name, "Alice");
244        let second_name = page1.cell(1, "name").unwrap();
245        assert_eq!(second_name, "Bob");
246
247        let page2 = db
248            .query(&format!(
249                "SELECT * FROM {users} ORDER BY name ASC LIMIT 2 OFFSET 2"
250            ))
251            .await
252            .unwrap();
253        assert_eq!(page2.row_count(), 1);
254        let last_name = page2.cell(0, "name").unwrap();
255        assert_eq!(last_name, "Carol");
256    });
257
258    run_test!("SELECT ORDER BY DESC", {
259        let result = db
260            .query(&format!(
261                "SELECT * FROM {users} ORDER BY name DESC LIMIT 1"
262            ))
263            .await
264            .unwrap();
265        let first_name = result.cell(0, "name").unwrap();
266        assert_eq!(first_name, "Carol");
267    });
268
269    // ── SELECT (aggregate) ────────────────────────────────────
270
271    run_test!("COUNT(*)", {
272        let result = db
273            .query(&format!("SELECT COUNT(*) FROM {users}"))
274            .await
275            .unwrap();
276        assert_eq!(result.row_count(), 1);
277        let count = &result.rows[0][0];
278        assert_eq!(count, 3, "should count 3 rows");
279    });
280
281    run_test!("SUM(age)", {
282        let result = db
283            .query(&format!("SELECT SUM(age) FROM {users}"))
284            .await
285            .unwrap();
286        let sum = result.rows[0][0].as_f64().unwrap();
287        assert_eq!(sum, 90.0, "30 + 25 + 35 = 90");
288    });
289
290    run_test!("AVG(age)", {
291        let result = db
292            .query(&format!("SELECT AVG(age) FROM {users}"))
293            .await
294            .unwrap();
295        let avg = result.rows[0][0].as_f64().unwrap();
296        assert_eq!(avg, 30.0, "(30+25+35)/3 = 30");
297    });
298
299    run_test!("MIN + MAX", {
300        let result = db
301            .query(&format!("SELECT MIN(age), MAX(age) FROM {users}"))
302            .await
303            .unwrap();
304        let min = result.rows[0][0].as_f64().unwrap();
305        let max = result.rows[0][1].as_f64().unwrap();
306        assert_eq!(min, 25.0);
307        assert_eq!(max, 35.0);
308    });
309
310    run_test!("GROUP BY city COUNT(*)", {
311        let result = db
312            .query(&format!(
313                "SELECT city, COUNT(*) FROM {users} GROUP BY city"
314            ))
315            .await
316            .unwrap();
317        // Helsinki: alice + carol = 2, Tampere: bob = 1
318        assert_eq!(result.row_count(), 2, "2 distinct cities");
319
320        // Find Helsinki group.
321        let helsinki_row = result
322            .rows
323            .iter()
324            .find(|row| row[0].as_str() == Some("Helsinki"))
325            .expect("Helsinki group missing");
326        assert_eq!(helsinki_row[1], 2, "Helsinki has 2 users");
327
328        let tampere_row = result
329            .rows
330            .iter()
331            .find(|row| row[0].as_str() == Some("Tampere"))
332            .expect("Tampere group missing");
333        assert_eq!(tampere_row[1], 1, "Tampere has 1 user");
334    });
335
336    // ── UPDATE ─────────────────────────────────────────────────
337
338    run_test!("UPDATE single row (PK fast path)", {
339        let affected = db
340            .exec(&format!(
341                "UPDATE {users} SET city = 'Espoo', age = 31 WHERE id = 'alice'"
342            ))
343            .await
344            .unwrap();
345        assert_eq!(affected, 1);
346
347        let result = db
348            .query(&format!("SELECT * FROM {users} WHERE id = 'alice'"))
349            .await
350            .unwrap();
351        assert_eq!(result.cell(0, "city").unwrap(), "Espoo");
352        assert_eq!(result.cell(0, "age").unwrap(), 31);
353    });
354
355    run_test!("UPDATE multiple rows", {
356        // Both alice (Espoo) and bob (Tampere) do NOT match, only carol (Helsinki).
357        let affected = db
358            .exec(&format!(
359                "UPDATE {users} SET city = 'Vantaa' WHERE city = 'Helsinki'"
360            ))
361            .await
362            .unwrap();
363        assert!(affected >= 1, "at least carol should be updated");
364
365        let result = db
366            .query(&format!("SELECT * FROM {users} WHERE city = 'Vantaa'"))
367            .await
368            .unwrap();
369        assert!(result.row_count() >= 1, "vantaa row exists after update");
370    });
371
372    // ── JOIN ───────────────────────────────────────────────────
373
374    run_test!("INNER JOIN", {
375        // alice has 2 orders (o1, o2), carol has 1 (o3), bob has none.
376        // INNER JOIN → 3 combined rows.
377        let result = db
378            .query(&format!(
379                "SELECT {users}.id, {users}.name, {orders}.amount \
380                 FROM {users} INNER JOIN {orders} ON {users}.id = {orders}.user_id"
381            ))
382            .await
383            .unwrap();
384        assert_eq!(result.row_count(), 3, "alice×2 + carol×1 = 3 inner join rows");
385        assert!(result.col_index("amount").is_some(), "amount column present");
386    });
387
388    run_test!("LEFT JOIN", {
389        // LEFT JOIN includes bob with null amount.
390        let result = db
391            .query(&format!(
392                "SELECT {users}.id, {users}.name, {orders}.amount \
393                 FROM {users} LEFT JOIN {orders} ON {users}.id = {orders}.user_id"
394            ))
395            .await
396            .unwrap();
397        // alice×2 + bob×1(null) + carol×1 = 4 rows.
398        assert_eq!(result.row_count(), 4, "alice×2 + bob + carol = 4 left join rows");
399    });
400
401    // ── deserialize_rows ───────────────────────────────────────
402
403    run_test!("deserialize_rows<T>", {
404        #[derive(serde::Deserialize, PartialEq, Debug)]
405        struct User {
406            id: String,
407            name: String,
408        }
409
410        let result = db
411            .query(&format!("SELECT id, name FROM {users} ORDER BY name ASC"))
412            .await
413            .unwrap();
414        let users_vec: Vec<User> = result.deserialize_rows().unwrap();
415        assert_eq!(users_vec.len(), 3);
416        assert_eq!(users_vec[0].name, "Alice");
417        assert_eq!(users_vec[2].name, "Carol");
418    });
419
420    // ── sql() auto-detect ──────────────────────────────────────
421
422    run_test!("sql() → SqlResult::Query for SELECT", {
423        let r = db
424            .sql(&format!("SELECT COUNT(*) FROM {users}"))
425            .await
426            .unwrap();
427        assert!(r.is_query(), "SELECT should return Query variant");
428    });
429
430    run_test!("sql() → SqlResult::Exec for DML", {
431        let r = db
432            .sql(&format!(
433                "UPDATE {users} SET age = 31 WHERE id = 'alice'"
434            ))
435            .await
436            .unwrap();
437        assert!(r.is_exec(), "UPDATE should return Exec variant");
438        let n = r.into_affected_rows().unwrap();
439        assert_eq!(n, 1);
440    });
441
442    // ── DELETE ─────────────────────────────────────────────────
443
444    run_test!("DELETE single row", {
445        let affected = db
446            .exec(&format!("DELETE FROM {users} WHERE id = 'bob'"))
447            .await
448            .unwrap();
449        assert_eq!(affected, 1);
450
451        let result = db
452            .query(&format!("SELECT * FROM {users}"))
453            .await
454            .unwrap();
455        assert_eq!(result.row_count(), 2, "bob removed → 2 rows remain");
456    });
457
458    run_test!("DELETE multiple rows", {
459        let affected = db
460            .exec(&format!("DELETE FROM {orders} WHERE user_id = 'alice'"))
461            .await
462            .unwrap();
463        assert_eq!(affected, 2, "alice had 2 orders");
464
465        let result = db
466            .query(&format!("SELECT * FROM {orders}"))
467            .await
468            .unwrap();
469        assert_eq!(result.row_count(), 1, "only carol's order remains");
470    });
471
472    // ── Error handling ─────────────────────────────────────────
473
474    run_test!("query() on DML returns WrongResultType", {
475        // Run an INSERT through query() — service will return affected_rows,
476        // so into_query() should fail.
477        let err = db
478            .query(&format!(
479                "INSERT INTO {users} (id, name, age, city) VALUES ('dave', 'Dave', 28, 'Turku')"
480            ))
481            .await;
482        // This may succeed if lattice-sql happens to send columns (it won't),
483        // or fail with WrongResultType — either demonstrates the type check.
484        // What matters is that exec() with the same SQL succeeds.
485        let _ = err; // tolerate either branch for robustness
486    });
487
488    run_test!("invalid SQL returns Db error", {
489        let err = db.sql("THIS IS NOT VALID SQL AT ALL").await;
490        assert!(err.is_err(), "invalid SQL should error");
491        assert!(
492            matches!(err.unwrap_err(), lattice_sql_client::Error::Db(_)),
493            "should be a Db error"
494        );
495    });
496
497    run_test!("nonexistent table returns Db error", {
498        let err = db
499            .query(&format!("SELECT * FROM _smoke_no_table_{ts}"))
500            .await;
501        assert!(err.is_err());
502        assert!(matches!(err.unwrap_err(), lattice_sql_client::Error::Db(_)));
503    });
504
505    // ── DDL: CREATE INDEX / DROP INDEX ─────────────────────────
506
507    run_test!("CREATE INDEX", {
508        db.ddl(&format!("CREATE INDEX ON {users} (name)"))
509            .await
510            .unwrap();
511    });
512
513    run_test!("DROP INDEX", {
514        db.ddl("DROP INDEX name").await.unwrap();
515    });
516
517    // ── DDL: DROP TABLE ────────────────────────────────────────
518
519    run_test!("DROP TABLE", {
520        db.ddl(&format!("DROP TABLE {orders}")).await.unwrap();
521        db.ddl(&format!("DROP TABLE {users}")).await.unwrap();
522    });
523
524    run_test!("DROP TABLE IF EXISTS (nonexistent)", {
525        db.ddl(&format!("DROP TABLE IF EXISTS _smoke_gone_{ts}"))
526            .await
527            .unwrap();
528    });
529
530    run_test!("SELECT from dropped table → error", {
531        let err = db.query(&format!("SELECT * FROM {users}")).await;
532        assert!(err.is_err(), "dropped table should return an error");
533    });
534
535    // ── Done ───────────────────────────────────────────────────
536
537    let total_ms = (wasip3::clocks::monotonic_clock::now() - t0) / 1_000_000;
538    println!("\nAll SQL SDK tests passed! (total: {total_ms} ms)");
539}
Source

pub fn cell(&self, row: usize, col: &str) -> Option<&Value>

Get a single cell value by row index and column name.

Returns None if the row index is out of bounds or the column name is not in the projection.

Examples found in repository?
examples/smoke_test.rs (line 185)
60async fn run_tests() {
61    let t0 = wasip3::clocks::monotonic_clock::now();
62    let db = connect().await;
63
64    // Unique table names per run to avoid catalog collisions.
65    let ts = wasip3::clocks::monotonic_clock::now();
66    let users = format!("smoke_u{ts}");
67    let orders = format!("smoke_o{ts}");
68
69    // ── DDL: CREATE TABLE ──────────────────────────────────────
70
71    run_test!("CREATE TABLE users", {
72        db.ddl(&format!(
73            "CREATE TABLE {users} (id TEXT PRIMARY KEY, name TEXT NOT NULL, age INTEGER, city TEXT)"
74        ))
75        .await
76        .unwrap();
77    });
78
79    run_test!("CREATE TABLE orders", {
80        db.ddl(&format!(
81            "CREATE TABLE {orders} (id TEXT PRIMARY KEY, user_id TEXT, amount INTEGER)"
82        ))
83        .await
84        .unwrap();
85    });
86
87    run_test!("CREATE TABLE IF NOT EXISTS (duplicate is ok)", {
88        db.ddl(&format!(
89            "CREATE TABLE IF NOT EXISTS {users} (id TEXT PRIMARY KEY, name TEXT)"
90        ))
91        .await
92        .unwrap();
93    });
94
95    // ── DML: INSERT ────────────────────────────────────────────
96
97    run_test!("INSERT alice", {
98        let affected = db
99            .exec(&format!(
100                "INSERT INTO {users} (id, name, age, city) VALUES ('alice', 'Alice', 30, 'Helsinki')"
101            ))
102            .await
103            .unwrap();
104        assert_eq!(affected, 1, "affected_rows should be 1");
105    });
106
107    run_test!("INSERT bob", {
108        let affected = db
109            .exec(&format!(
110                "INSERT INTO {users} (id, name, age, city) VALUES ('bob', 'Bob', 25, 'Tampere')"
111            ))
112            .await
113            .unwrap();
114        assert_eq!(affected, 1);
115    });
116
117    run_test!("INSERT carol", {
118        let affected = db
119            .exec(&format!(
120                "INSERT INTO {users} (id, name, age, city) VALUES ('carol', 'Carol', 35, 'Helsinki')"
121            ))
122            .await
123            .unwrap();
124        assert_eq!(affected, 1);
125    });
126
127    run_test!("INSERT duplicate PK fails", {
128        let err = db
129            .exec(&format!(
130                "INSERT INTO {users} (id, name, age, city) VALUES ('alice', 'Alice2', 99, 'Oulu')"
131            ))
132            .await;
133        assert!(err.is_err(), "duplicate PK should return an error");
134    });
135
136    // Seed orders for JOIN tests.
137    db.exec(&format!(
138        "INSERT INTO {orders} (id, user_id, amount) VALUES ('o1', 'alice', 100)"
139    ))
140    .await
141    .unwrap();
142    db.exec(&format!(
143        "INSERT INTO {orders} (id, user_id, amount) VALUES ('o2', 'alice', 250)"
144    ))
145    .await
146    .unwrap();
147    db.exec(&format!(
148        "INSERT INTO {orders} (id, user_id, amount) VALUES ('o3', 'carol', 150)"
149    ))
150    .await
151    .unwrap();
152
153    // ── SELECT (basic) ─────────────────────────────────────────
154
155    run_test!("SELECT *", {
156        let result = db
157            .query(&format!("SELECT * FROM {users}"))
158            .await
159            .unwrap();
160        assert_eq!(result.row_count(), 3, "should have 3 rows");
161        assert_eq!(result.columns.len(), 4, "should have 4 columns");
162        assert!(result.columns.contains(&"id".to_string()));
163        assert!(result.columns.contains(&"name".to_string()));
164        assert!(result.columns.contains(&"age".to_string()));
165        assert!(result.columns.contains(&"city".to_string()));
166    });
167
168    run_test!("SELECT specific columns", {
169        let result = db
170            .query(&format!("SELECT id, name FROM {users}"))
171            .await
172            .unwrap();
173        assert_eq!(result.columns.len(), 2);
174        assert_eq!(result.columns[0], "id");
175        assert_eq!(result.columns[1], "name");
176        assert_eq!(result.row_count(), 3);
177    });
178
179    run_test!("cell() helper", {
180        let result = db
181            .query(&format!("SELECT * FROM {users} WHERE id = 'alice'"))
182            .await
183            .unwrap();
184        assert_eq!(result.row_count(), 1);
185        let name = result.cell(0, "name").unwrap();
186        assert_eq!(name, "Alice");
187        let age = result.cell(0, "age").unwrap();
188        assert_eq!(age, 30);
189    });
190
191    // ── SELECT (filtered) ─────────────────────────────────────
192
193    run_test!("SELECT WHERE eq", {
194        let result = db
195            .query(&format!("SELECT * FROM {users} WHERE city = 'Helsinki'"))
196            .await
197            .unwrap();
198        assert_eq!(result.row_count(), 2, "alice + carol are in Helsinki");
199    });
200
201    run_test!("SELECT WHERE gt", {
202        let result = db
203            .query(&format!("SELECT * FROM {users} WHERE age > 28"))
204            .await
205            .unwrap();
206        assert_eq!(result.row_count(), 2, "alice(30) and carol(35)");
207    });
208
209    run_test!("SELECT WHERE lt", {
210        let result = db
211            .query(&format!("SELECT * FROM {users} WHERE age < 30"))
212            .await
213            .unwrap();
214        assert_eq!(result.row_count(), 1, "only bob(25)");
215        let id = result.cell(0, "id").unwrap();
216        assert_eq!(id, "bob");
217    });
218
219    run_test!("SELECT WHERE AND", {
220        let result = db
221            .query(&format!(
222                "SELECT * FROM {users} WHERE city = 'Helsinki' AND age > 30"
223            ))
224            .await
225            .unwrap();
226        assert_eq!(result.row_count(), 1, "only carol (city=Helsinki, age=35)");
227        let id = result.cell(0, "id").unwrap();
228        assert_eq!(id, "carol");
229    });
230
231    // ── SELECT (sorted + paginated) ───────────────────────────
232
233    run_test!("SELECT ORDER BY ASC LIMIT OFFSET", {
234        let page1 = db
235            .query(&format!(
236                "SELECT * FROM {users} ORDER BY name ASC LIMIT 2 OFFSET 0"
237            ))
238            .await
239            .unwrap();
240        assert_eq!(page1.row_count(), 2);
241        // Alice < Bob < Carol
242        let first_name = page1.cell(0, "name").unwrap();
243        assert_eq!(first_name, "Alice");
244        let second_name = page1.cell(1, "name").unwrap();
245        assert_eq!(second_name, "Bob");
246
247        let page2 = db
248            .query(&format!(
249                "SELECT * FROM {users} ORDER BY name ASC LIMIT 2 OFFSET 2"
250            ))
251            .await
252            .unwrap();
253        assert_eq!(page2.row_count(), 1);
254        let last_name = page2.cell(0, "name").unwrap();
255        assert_eq!(last_name, "Carol");
256    });
257
258    run_test!("SELECT ORDER BY DESC", {
259        let result = db
260            .query(&format!(
261                "SELECT * FROM {users} ORDER BY name DESC LIMIT 1"
262            ))
263            .await
264            .unwrap();
265        let first_name = result.cell(0, "name").unwrap();
266        assert_eq!(first_name, "Carol");
267    });
268
269    // ── SELECT (aggregate) ────────────────────────────────────
270
271    run_test!("COUNT(*)", {
272        let result = db
273            .query(&format!("SELECT COUNT(*) FROM {users}"))
274            .await
275            .unwrap();
276        assert_eq!(result.row_count(), 1);
277        let count = &result.rows[0][0];
278        assert_eq!(count, 3, "should count 3 rows");
279    });
280
281    run_test!("SUM(age)", {
282        let result = db
283            .query(&format!("SELECT SUM(age) FROM {users}"))
284            .await
285            .unwrap();
286        let sum = result.rows[0][0].as_f64().unwrap();
287        assert_eq!(sum, 90.0, "30 + 25 + 35 = 90");
288    });
289
290    run_test!("AVG(age)", {
291        let result = db
292            .query(&format!("SELECT AVG(age) FROM {users}"))
293            .await
294            .unwrap();
295        let avg = result.rows[0][0].as_f64().unwrap();
296        assert_eq!(avg, 30.0, "(30+25+35)/3 = 30");
297    });
298
299    run_test!("MIN + MAX", {
300        let result = db
301            .query(&format!("SELECT MIN(age), MAX(age) FROM {users}"))
302            .await
303            .unwrap();
304        let min = result.rows[0][0].as_f64().unwrap();
305        let max = result.rows[0][1].as_f64().unwrap();
306        assert_eq!(min, 25.0);
307        assert_eq!(max, 35.0);
308    });
309
310    run_test!("GROUP BY city COUNT(*)", {
311        let result = db
312            .query(&format!(
313                "SELECT city, COUNT(*) FROM {users} GROUP BY city"
314            ))
315            .await
316            .unwrap();
317        // Helsinki: alice + carol = 2, Tampere: bob = 1
318        assert_eq!(result.row_count(), 2, "2 distinct cities");
319
320        // Find Helsinki group.
321        let helsinki_row = result
322            .rows
323            .iter()
324            .find(|row| row[0].as_str() == Some("Helsinki"))
325            .expect("Helsinki group missing");
326        assert_eq!(helsinki_row[1], 2, "Helsinki has 2 users");
327
328        let tampere_row = result
329            .rows
330            .iter()
331            .find(|row| row[0].as_str() == Some("Tampere"))
332            .expect("Tampere group missing");
333        assert_eq!(tampere_row[1], 1, "Tampere has 1 user");
334    });
335
336    // ── UPDATE ─────────────────────────────────────────────────
337
338    run_test!("UPDATE single row (PK fast path)", {
339        let affected = db
340            .exec(&format!(
341                "UPDATE {users} SET city = 'Espoo', age = 31 WHERE id = 'alice'"
342            ))
343            .await
344            .unwrap();
345        assert_eq!(affected, 1);
346
347        let result = db
348            .query(&format!("SELECT * FROM {users} WHERE id = 'alice'"))
349            .await
350            .unwrap();
351        assert_eq!(result.cell(0, "city").unwrap(), "Espoo");
352        assert_eq!(result.cell(0, "age").unwrap(), 31);
353    });
354
355    run_test!("UPDATE multiple rows", {
356        // Both alice (Espoo) and bob (Tampere) do NOT match, only carol (Helsinki).
357        let affected = db
358            .exec(&format!(
359                "UPDATE {users} SET city = 'Vantaa' WHERE city = 'Helsinki'"
360            ))
361            .await
362            .unwrap();
363        assert!(affected >= 1, "at least carol should be updated");
364
365        let result = db
366            .query(&format!("SELECT * FROM {users} WHERE city = 'Vantaa'"))
367            .await
368            .unwrap();
369        assert!(result.row_count() >= 1, "vantaa row exists after update");
370    });
371
372    // ── JOIN ───────────────────────────────────────────────────
373
374    run_test!("INNER JOIN", {
375        // alice has 2 orders (o1, o2), carol has 1 (o3), bob has none.
376        // INNER JOIN → 3 combined rows.
377        let result = db
378            .query(&format!(
379                "SELECT {users}.id, {users}.name, {orders}.amount \
380                 FROM {users} INNER JOIN {orders} ON {users}.id = {orders}.user_id"
381            ))
382            .await
383            .unwrap();
384        assert_eq!(result.row_count(), 3, "alice×2 + carol×1 = 3 inner join rows");
385        assert!(result.col_index("amount").is_some(), "amount column present");
386    });
387
388    run_test!("LEFT JOIN", {
389        // LEFT JOIN includes bob with null amount.
390        let result = db
391            .query(&format!(
392                "SELECT {users}.id, {users}.name, {orders}.amount \
393                 FROM {users} LEFT JOIN {orders} ON {users}.id = {orders}.user_id"
394            ))
395            .await
396            .unwrap();
397        // alice×2 + bob×1(null) + carol×1 = 4 rows.
398        assert_eq!(result.row_count(), 4, "alice×2 + bob + carol = 4 left join rows");
399    });
400
401    // ── deserialize_rows ───────────────────────────────────────
402
403    run_test!("deserialize_rows<T>", {
404        #[derive(serde::Deserialize, PartialEq, Debug)]
405        struct User {
406            id: String,
407            name: String,
408        }
409
410        let result = db
411            .query(&format!("SELECT id, name FROM {users} ORDER BY name ASC"))
412            .await
413            .unwrap();
414        let users_vec: Vec<User> = result.deserialize_rows().unwrap();
415        assert_eq!(users_vec.len(), 3);
416        assert_eq!(users_vec[0].name, "Alice");
417        assert_eq!(users_vec[2].name, "Carol");
418    });
419
420    // ── sql() auto-detect ──────────────────────────────────────
421
422    run_test!("sql() → SqlResult::Query for SELECT", {
423        let r = db
424            .sql(&format!("SELECT COUNT(*) FROM {users}"))
425            .await
426            .unwrap();
427        assert!(r.is_query(), "SELECT should return Query variant");
428    });
429
430    run_test!("sql() → SqlResult::Exec for DML", {
431        let r = db
432            .sql(&format!(
433                "UPDATE {users} SET age = 31 WHERE id = 'alice'"
434            ))
435            .await
436            .unwrap();
437        assert!(r.is_exec(), "UPDATE should return Exec variant");
438        let n = r.into_affected_rows().unwrap();
439        assert_eq!(n, 1);
440    });
441
442    // ── DELETE ─────────────────────────────────────────────────
443
444    run_test!("DELETE single row", {
445        let affected = db
446            .exec(&format!("DELETE FROM {users} WHERE id = 'bob'"))
447            .await
448            .unwrap();
449        assert_eq!(affected, 1);
450
451        let result = db
452            .query(&format!("SELECT * FROM {users}"))
453            .await
454            .unwrap();
455        assert_eq!(result.row_count(), 2, "bob removed → 2 rows remain");
456    });
457
458    run_test!("DELETE multiple rows", {
459        let affected = db
460            .exec(&format!("DELETE FROM {orders} WHERE user_id = 'alice'"))
461            .await
462            .unwrap();
463        assert_eq!(affected, 2, "alice had 2 orders");
464
465        let result = db
466            .query(&format!("SELECT * FROM {orders}"))
467            .await
468            .unwrap();
469        assert_eq!(result.row_count(), 1, "only carol's order remains");
470    });
471
472    // ── Error handling ─────────────────────────────────────────
473
474    run_test!("query() on DML returns WrongResultType", {
475        // Run an INSERT through query() — service will return affected_rows,
476        // so into_query() should fail.
477        let err = db
478            .query(&format!(
479                "INSERT INTO {users} (id, name, age, city) VALUES ('dave', 'Dave', 28, 'Turku')"
480            ))
481            .await;
482        // This may succeed if lattice-sql happens to send columns (it won't),
483        // or fail with WrongResultType — either demonstrates the type check.
484        // What matters is that exec() with the same SQL succeeds.
485        let _ = err; // tolerate either branch for robustness
486    });
487
488    run_test!("invalid SQL returns Db error", {
489        let err = db.sql("THIS IS NOT VALID SQL AT ALL").await;
490        assert!(err.is_err(), "invalid SQL should error");
491        assert!(
492            matches!(err.unwrap_err(), lattice_sql_client::Error::Db(_)),
493            "should be a Db error"
494        );
495    });
496
497    run_test!("nonexistent table returns Db error", {
498        let err = db
499            .query(&format!("SELECT * FROM _smoke_no_table_{ts}"))
500            .await;
501        assert!(err.is_err());
502        assert!(matches!(err.unwrap_err(), lattice_sql_client::Error::Db(_)));
503    });
504
505    // ── DDL: CREATE INDEX / DROP INDEX ─────────────────────────
506
507    run_test!("CREATE INDEX", {
508        db.ddl(&format!("CREATE INDEX ON {users} (name)"))
509            .await
510            .unwrap();
511    });
512
513    run_test!("DROP INDEX", {
514        db.ddl("DROP INDEX name").await.unwrap();
515    });
516
517    // ── DDL: DROP TABLE ────────────────────────────────────────
518
519    run_test!("DROP TABLE", {
520        db.ddl(&format!("DROP TABLE {orders}")).await.unwrap();
521        db.ddl(&format!("DROP TABLE {users}")).await.unwrap();
522    });
523
524    run_test!("DROP TABLE IF EXISTS (nonexistent)", {
525        db.ddl(&format!("DROP TABLE IF EXISTS _smoke_gone_{ts}"))
526            .await
527            .unwrap();
528    });
529
530    run_test!("SELECT from dropped table → error", {
531        let err = db.query(&format!("SELECT * FROM {users}")).await;
532        assert!(err.is_err(), "dropped table should return an error");
533    });
534
535    // ── Done ───────────────────────────────────────────────────
536
537    let total_ms = (wasip3::clocks::monotonic_clock::now() - t0) / 1_000_000;
538    println!("\nAll SQL SDK tests passed! (total: {total_ms} ms)");
539}
Source

pub fn deserialize_rows<T: DeserializeOwned>(&self) -> Result<Vec<T>, String>

Deserialise every row into a typed Rust struct.

Each row is first converted to a JSON object keyed by column name, then deserialized via serde_json. Useful when the columns match a #[derive(Deserialize)] struct.

ⓘ
#[derive(serde::Deserialize)]
struct User { id: String, name: String, age: i64 }

let users: Vec<User> = result.deserialize_rows().map_err(|e| Error::Json(e))?;
Examples found in repository?
examples/smoke_test.rs (line 414)
60async fn run_tests() {
61    let t0 = wasip3::clocks::monotonic_clock::now();
62    let db = connect().await;
63
64    // Unique table names per run to avoid catalog collisions.
65    let ts = wasip3::clocks::monotonic_clock::now();
66    let users = format!("smoke_u{ts}");
67    let orders = format!("smoke_o{ts}");
68
69    // ── DDL: CREATE TABLE ──────────────────────────────────────
70
71    run_test!("CREATE TABLE users", {
72        db.ddl(&format!(
73            "CREATE TABLE {users} (id TEXT PRIMARY KEY, name TEXT NOT NULL, age INTEGER, city TEXT)"
74        ))
75        .await
76        .unwrap();
77    });
78
79    run_test!("CREATE TABLE orders", {
80        db.ddl(&format!(
81            "CREATE TABLE {orders} (id TEXT PRIMARY KEY, user_id TEXT, amount INTEGER)"
82        ))
83        .await
84        .unwrap();
85    });
86
87    run_test!("CREATE TABLE IF NOT EXISTS (duplicate is ok)", {
88        db.ddl(&format!(
89            "CREATE TABLE IF NOT EXISTS {users} (id TEXT PRIMARY KEY, name TEXT)"
90        ))
91        .await
92        .unwrap();
93    });
94
95    // ── DML: INSERT ────────────────────────────────────────────
96
97    run_test!("INSERT alice", {
98        let affected = db
99            .exec(&format!(
100                "INSERT INTO {users} (id, name, age, city) VALUES ('alice', 'Alice', 30, 'Helsinki')"
101            ))
102            .await
103            .unwrap();
104        assert_eq!(affected, 1, "affected_rows should be 1");
105    });
106
107    run_test!("INSERT bob", {
108        let affected = db
109            .exec(&format!(
110                "INSERT INTO {users} (id, name, age, city) VALUES ('bob', 'Bob', 25, 'Tampere')"
111            ))
112            .await
113            .unwrap();
114        assert_eq!(affected, 1);
115    });
116
117    run_test!("INSERT carol", {
118        let affected = db
119            .exec(&format!(
120                "INSERT INTO {users} (id, name, age, city) VALUES ('carol', 'Carol', 35, 'Helsinki')"
121            ))
122            .await
123            .unwrap();
124        assert_eq!(affected, 1);
125    });
126
127    run_test!("INSERT duplicate PK fails", {
128        let err = db
129            .exec(&format!(
130                "INSERT INTO {users} (id, name, age, city) VALUES ('alice', 'Alice2', 99, 'Oulu')"
131            ))
132            .await;
133        assert!(err.is_err(), "duplicate PK should return an error");
134    });
135
136    // Seed orders for JOIN tests.
137    db.exec(&format!(
138        "INSERT INTO {orders} (id, user_id, amount) VALUES ('o1', 'alice', 100)"
139    ))
140    .await
141    .unwrap();
142    db.exec(&format!(
143        "INSERT INTO {orders} (id, user_id, amount) VALUES ('o2', 'alice', 250)"
144    ))
145    .await
146    .unwrap();
147    db.exec(&format!(
148        "INSERT INTO {orders} (id, user_id, amount) VALUES ('o3', 'carol', 150)"
149    ))
150    .await
151    .unwrap();
152
153    // ── SELECT (basic) ─────────────────────────────────────────
154
155    run_test!("SELECT *", {
156        let result = db
157            .query(&format!("SELECT * FROM {users}"))
158            .await
159            .unwrap();
160        assert_eq!(result.row_count(), 3, "should have 3 rows");
161        assert_eq!(result.columns.len(), 4, "should have 4 columns");
162        assert!(result.columns.contains(&"id".to_string()));
163        assert!(result.columns.contains(&"name".to_string()));
164        assert!(result.columns.contains(&"age".to_string()));
165        assert!(result.columns.contains(&"city".to_string()));
166    });
167
168    run_test!("SELECT specific columns", {
169        let result = db
170            .query(&format!("SELECT id, name FROM {users}"))
171            .await
172            .unwrap();
173        assert_eq!(result.columns.len(), 2);
174        assert_eq!(result.columns[0], "id");
175        assert_eq!(result.columns[1], "name");
176        assert_eq!(result.row_count(), 3);
177    });
178
179    run_test!("cell() helper", {
180        let result = db
181            .query(&format!("SELECT * FROM {users} WHERE id = 'alice'"))
182            .await
183            .unwrap();
184        assert_eq!(result.row_count(), 1);
185        let name = result.cell(0, "name").unwrap();
186        assert_eq!(name, "Alice");
187        let age = result.cell(0, "age").unwrap();
188        assert_eq!(age, 30);
189    });
190
191    // ── SELECT (filtered) ─────────────────────────────────────
192
193    run_test!("SELECT WHERE eq", {
194        let result = db
195            .query(&format!("SELECT * FROM {users} WHERE city = 'Helsinki'"))
196            .await
197            .unwrap();
198        assert_eq!(result.row_count(), 2, "alice + carol are in Helsinki");
199    });
200
201    run_test!("SELECT WHERE gt", {
202        let result = db
203            .query(&format!("SELECT * FROM {users} WHERE age > 28"))
204            .await
205            .unwrap();
206        assert_eq!(result.row_count(), 2, "alice(30) and carol(35)");
207    });
208
209    run_test!("SELECT WHERE lt", {
210        let result = db
211            .query(&format!("SELECT * FROM {users} WHERE age < 30"))
212            .await
213            .unwrap();
214        assert_eq!(result.row_count(), 1, "only bob(25)");
215        let id = result.cell(0, "id").unwrap();
216        assert_eq!(id, "bob");
217    });
218
219    run_test!("SELECT WHERE AND", {
220        let result = db
221            .query(&format!(
222                "SELECT * FROM {users} WHERE city = 'Helsinki' AND age > 30"
223            ))
224            .await
225            .unwrap();
226        assert_eq!(result.row_count(), 1, "only carol (city=Helsinki, age=35)");
227        let id = result.cell(0, "id").unwrap();
228        assert_eq!(id, "carol");
229    });
230
231    // ── SELECT (sorted + paginated) ───────────────────────────
232
233    run_test!("SELECT ORDER BY ASC LIMIT OFFSET", {
234        let page1 = db
235            .query(&format!(
236                "SELECT * FROM {users} ORDER BY name ASC LIMIT 2 OFFSET 0"
237            ))
238            .await
239            .unwrap();
240        assert_eq!(page1.row_count(), 2);
241        // Alice < Bob < Carol
242        let first_name = page1.cell(0, "name").unwrap();
243        assert_eq!(first_name, "Alice");
244        let second_name = page1.cell(1, "name").unwrap();
245        assert_eq!(second_name, "Bob");
246
247        let page2 = db
248            .query(&format!(
249                "SELECT * FROM {users} ORDER BY name ASC LIMIT 2 OFFSET 2"
250            ))
251            .await
252            .unwrap();
253        assert_eq!(page2.row_count(), 1);
254        let last_name = page2.cell(0, "name").unwrap();
255        assert_eq!(last_name, "Carol");
256    });
257
258    run_test!("SELECT ORDER BY DESC", {
259        let result = db
260            .query(&format!(
261                "SELECT * FROM {users} ORDER BY name DESC LIMIT 1"
262            ))
263            .await
264            .unwrap();
265        let first_name = result.cell(0, "name").unwrap();
266        assert_eq!(first_name, "Carol");
267    });
268
269    // ── SELECT (aggregate) ────────────────────────────────────
270
271    run_test!("COUNT(*)", {
272        let result = db
273            .query(&format!("SELECT COUNT(*) FROM {users}"))
274            .await
275            .unwrap();
276        assert_eq!(result.row_count(), 1);
277        let count = &result.rows[0][0];
278        assert_eq!(count, 3, "should count 3 rows");
279    });
280
281    run_test!("SUM(age)", {
282        let result = db
283            .query(&format!("SELECT SUM(age) FROM {users}"))
284            .await
285            .unwrap();
286        let sum = result.rows[0][0].as_f64().unwrap();
287        assert_eq!(sum, 90.0, "30 + 25 + 35 = 90");
288    });
289
290    run_test!("AVG(age)", {
291        let result = db
292            .query(&format!("SELECT AVG(age) FROM {users}"))
293            .await
294            .unwrap();
295        let avg = result.rows[0][0].as_f64().unwrap();
296        assert_eq!(avg, 30.0, "(30+25+35)/3 = 30");
297    });
298
299    run_test!("MIN + MAX", {
300        let result = db
301            .query(&format!("SELECT MIN(age), MAX(age) FROM {users}"))
302            .await
303            .unwrap();
304        let min = result.rows[0][0].as_f64().unwrap();
305        let max = result.rows[0][1].as_f64().unwrap();
306        assert_eq!(min, 25.0);
307        assert_eq!(max, 35.0);
308    });
309
310    run_test!("GROUP BY city COUNT(*)", {
311        let result = db
312            .query(&format!(
313                "SELECT city, COUNT(*) FROM {users} GROUP BY city"
314            ))
315            .await
316            .unwrap();
317        // Helsinki: alice + carol = 2, Tampere: bob = 1
318        assert_eq!(result.row_count(), 2, "2 distinct cities");
319
320        // Find Helsinki group.
321        let helsinki_row = result
322            .rows
323            .iter()
324            .find(|row| row[0].as_str() == Some("Helsinki"))
325            .expect("Helsinki group missing");
326        assert_eq!(helsinki_row[1], 2, "Helsinki has 2 users");
327
328        let tampere_row = result
329            .rows
330            .iter()
331            .find(|row| row[0].as_str() == Some("Tampere"))
332            .expect("Tampere group missing");
333        assert_eq!(tampere_row[1], 1, "Tampere has 1 user");
334    });
335
336    // ── UPDATE ─────────────────────────────────────────────────
337
338    run_test!("UPDATE single row (PK fast path)", {
339        let affected = db
340            .exec(&format!(
341                "UPDATE {users} SET city = 'Espoo', age = 31 WHERE id = 'alice'"
342            ))
343            .await
344            .unwrap();
345        assert_eq!(affected, 1);
346
347        let result = db
348            .query(&format!("SELECT * FROM {users} WHERE id = 'alice'"))
349            .await
350            .unwrap();
351        assert_eq!(result.cell(0, "city").unwrap(), "Espoo");
352        assert_eq!(result.cell(0, "age").unwrap(), 31);
353    });
354
355    run_test!("UPDATE multiple rows", {
356        // Both alice (Espoo) and bob (Tampere) do NOT match, only carol (Helsinki).
357        let affected = db
358            .exec(&format!(
359                "UPDATE {users} SET city = 'Vantaa' WHERE city = 'Helsinki'"
360            ))
361            .await
362            .unwrap();
363        assert!(affected >= 1, "at least carol should be updated");
364
365        let result = db
366            .query(&format!("SELECT * FROM {users} WHERE city = 'Vantaa'"))
367            .await
368            .unwrap();
369        assert!(result.row_count() >= 1, "vantaa row exists after update");
370    });
371
372    // ── JOIN ───────────────────────────────────────────────────
373
374    run_test!("INNER JOIN", {
375        // alice has 2 orders (o1, o2), carol has 1 (o3), bob has none.
376        // INNER JOIN → 3 combined rows.
377        let result = db
378            .query(&format!(
379                "SELECT {users}.id, {users}.name, {orders}.amount \
380                 FROM {users} INNER JOIN {orders} ON {users}.id = {orders}.user_id"
381            ))
382            .await
383            .unwrap();
384        assert_eq!(result.row_count(), 3, "alice×2 + carol×1 = 3 inner join rows");
385        assert!(result.col_index("amount").is_some(), "amount column present");
386    });
387
388    run_test!("LEFT JOIN", {
389        // LEFT JOIN includes bob with null amount.
390        let result = db
391            .query(&format!(
392                "SELECT {users}.id, {users}.name, {orders}.amount \
393                 FROM {users} LEFT JOIN {orders} ON {users}.id = {orders}.user_id"
394            ))
395            .await
396            .unwrap();
397        // alice×2 + bob×1(null) + carol×1 = 4 rows.
398        assert_eq!(result.row_count(), 4, "alice×2 + bob + carol = 4 left join rows");
399    });
400
401    // ── deserialize_rows ───────────────────────────────────────
402
403    run_test!("deserialize_rows<T>", {
404        #[derive(serde::Deserialize, PartialEq, Debug)]
405        struct User {
406            id: String,
407            name: String,
408        }
409
410        let result = db
411            .query(&format!("SELECT id, name FROM {users} ORDER BY name ASC"))
412            .await
413            .unwrap();
414        let users_vec: Vec<User> = result.deserialize_rows().unwrap();
415        assert_eq!(users_vec.len(), 3);
416        assert_eq!(users_vec[0].name, "Alice");
417        assert_eq!(users_vec[2].name, "Carol");
418    });
419
420    // ── sql() auto-detect ──────────────────────────────────────
421
422    run_test!("sql() → SqlResult::Query for SELECT", {
423        let r = db
424            .sql(&format!("SELECT COUNT(*) FROM {users}"))
425            .await
426            .unwrap();
427        assert!(r.is_query(), "SELECT should return Query variant");
428    });
429
430    run_test!("sql() → SqlResult::Exec for DML", {
431        let r = db
432            .sql(&format!(
433                "UPDATE {users} SET age = 31 WHERE id = 'alice'"
434            ))
435            .await
436            .unwrap();
437        assert!(r.is_exec(), "UPDATE should return Exec variant");
438        let n = r.into_affected_rows().unwrap();
439        assert_eq!(n, 1);
440    });
441
442    // ── DELETE ─────────────────────────────────────────────────
443
444    run_test!("DELETE single row", {
445        let affected = db
446            .exec(&format!("DELETE FROM {users} WHERE id = 'bob'"))
447            .await
448            .unwrap();
449        assert_eq!(affected, 1);
450
451        let result = db
452            .query(&format!("SELECT * FROM {users}"))
453            .await
454            .unwrap();
455        assert_eq!(result.row_count(), 2, "bob removed → 2 rows remain");
456    });
457
458    run_test!("DELETE multiple rows", {
459        let affected = db
460            .exec(&format!("DELETE FROM {orders} WHERE user_id = 'alice'"))
461            .await
462            .unwrap();
463        assert_eq!(affected, 2, "alice had 2 orders");
464
465        let result = db
466            .query(&format!("SELECT * FROM {orders}"))
467            .await
468            .unwrap();
469        assert_eq!(result.row_count(), 1, "only carol's order remains");
470    });
471
472    // ── Error handling ─────────────────────────────────────────
473
474    run_test!("query() on DML returns WrongResultType", {
475        // Run an INSERT through query() — service will return affected_rows,
476        // so into_query() should fail.
477        let err = db
478            .query(&format!(
479                "INSERT INTO {users} (id, name, age, city) VALUES ('dave', 'Dave', 28, 'Turku')"
480            ))
481            .await;
482        // This may succeed if lattice-sql happens to send columns (it won't),
483        // or fail with WrongResultType — either demonstrates the type check.
484        // What matters is that exec() with the same SQL succeeds.
485        let _ = err; // tolerate either branch for robustness
486    });
487
488    run_test!("invalid SQL returns Db error", {
489        let err = db.sql("THIS IS NOT VALID SQL AT ALL").await;
490        assert!(err.is_err(), "invalid SQL should error");
491        assert!(
492            matches!(err.unwrap_err(), lattice_sql_client::Error::Db(_)),
493            "should be a Db error"
494        );
495    });
496
497    run_test!("nonexistent table returns Db error", {
498        let err = db
499            .query(&format!("SELECT * FROM _smoke_no_table_{ts}"))
500            .await;
501        assert!(err.is_err());
502        assert!(matches!(err.unwrap_err(), lattice_sql_client::Error::Db(_)));
503    });
504
505    // ── DDL: CREATE INDEX / DROP INDEX ─────────────────────────
506
507    run_test!("CREATE INDEX", {
508        db.ddl(&format!("CREATE INDEX ON {users} (name)"))
509            .await
510            .unwrap();
511    });
512
513    run_test!("DROP INDEX", {
514        db.ddl("DROP INDEX name").await.unwrap();
515    });
516
517    // ── DDL: DROP TABLE ────────────────────────────────────────
518
519    run_test!("DROP TABLE", {
520        db.ddl(&format!("DROP TABLE {orders}")).await.unwrap();
521        db.ddl(&format!("DROP TABLE {users}")).await.unwrap();
522    });
523
524    run_test!("DROP TABLE IF EXISTS (nonexistent)", {
525        db.ddl(&format!("DROP TABLE IF EXISTS _smoke_gone_{ts}"))
526            .await
527            .unwrap();
528    });
529
530    run_test!("SELECT from dropped table → error", {
531        let err = db.query(&format!("SELECT * FROM {users}")).await;
532        assert!(err.is_err(), "dropped table should return an error");
533    });
534
535    // ── Done ───────────────────────────────────────────────────
536
537    let total_ms = (wasip3::clocks::monotonic_clock::now() - t0) / 1_000_000;
538    println!("\nAll SQL SDK tests passed! (total: {total_ms} ms)");
539}

Trait Implementations§

Source§

impl Clone for QueryResult

Source§

fn clone(&self) -> QueryResult

Returns a duplicate of the value. Read more
1.0.0 · Source§

fn clone_from(&mut self, source: &Self)

Performs copy-assignment from source. Read more
Source§

impl Debug for QueryResult

Source§

fn fmt(&self, f: &mut Formatter<'_>) -> Result

Formats the value using the given formatter. Read more

Auto Trait Implementations§

Blanket Implementations§

Source§

impl<T> Any for T
where T: 'static + ?Sized,

Source§

fn type_id(&self) -> TypeId

Gets the TypeId of self. Read more
Source§

impl<T> Borrow<T> for T
where T: ?Sized,

Source§

fn borrow(&self) -> &T

Immutably borrows from an owned value. Read more
Source§

impl<T> BorrowMut<T> for T
where T: ?Sized,

Source§

fn borrow_mut(&mut self) -> &mut T

Mutably borrows from an owned value. Read more
Source§

impl<T> CloneToUninit for T
where T: Clone,

Source§

unsafe fn clone_to_uninit(&self, dest: *mut u8)

🔬This is a nightly-only experimental API. (clone_to_uninit)
Performs copy-assignment from self to dest. Read more
Source§

impl<T> From<T> for T

Source§

fn from(t: T) -> T

Returns the argument unchanged.

Source§

impl<T, U> Into<U> for T
where U: From<T>,

Source§

fn into(self) -> U

Calls U::from(self).

That is, this conversion is whatever the implementation of From<T> for U chooses to do.

Source§

impl<T> Same for T

Source§

type Output = T

Should always be Self
Source§

impl<T> ToOwned for T
where T: Clone,

Source§

type Owned = T

The resulting type after obtaining ownership.
Source§

fn to_owned(&self) -> T

Creates owned data from borrowed data, usually by cloning. Read more
Source§

fn clone_into(&self, target: &mut T)

Uses borrowed data to replace owned data, usually by cloning. Read more
Source§

impl<T, U> TryFrom<U> for T
where U: Into<T>,

Source§

type Error = Infallible

The type returned in the event of a conversion error.
Source§

fn try_from(value: U) -> Result<T, <T as TryFrom<U>>::Error>

Performs the conversion.
Source§

impl<T, U> TryInto<U> for T
where U: TryFrom<T>,

Source§

type Error = <U as TryFrom<T>>::Error

The type returned in the event of a conversion error.
Source§

fn try_into(self) -> Result<U, <U as TryFrom<T>>::Error>

Performs the conversion.