Skip to main content

LatticeSql

lattice_sql_client

Struct LatticeSql 

Source 
pub struct LatticeSql { /* private fields */ }
Expand description

Typed client for the lattice-sql SQL frontend.

All SQL statements are forwarded to SUBJECT as {"sql":"…"} JSON payloads. The response is parsed and returned as a strongly typed Rust value.

§Requirements

  • A running NATS server (JetStream not required for SQL queries)
  • storage-service running and listening on ldb.*
  • lattice-sql running and subscribed to ldb.sql.>

§Example

use lattice_sql_client::LatticeSql;
use nats_wasip3::client::{Client, ConnectConfig};

let client = Client::connect(ConnectConfig::default()).await?;
let db = LatticeSql::new(client);
db.ddl("CREATE TABLE items (id TEXT PRIMARY KEY, label TEXT)").await?;

Implementations§

Source§

impl LatticeSql

Source

pub const SUBJECT: &'static str = "ldb.sql.query"

NATS subject that lattice-sql subscribes to.

The service accepts any subject matching ldb.sql.> and treats them all identically. ldb.sql.query is used by convention.

Source

pub fn new(client: Client) -> Self

Create a new client with the default 10-second timeout.

The timeout is higher than LatticeDb’s default (5s) because a single SQL query may require several round-trips to the storage service.

Examples found in repository?
examples/smoke_test.rs (line 34)
27async fn connect() -> LatticeSql {
28    let client = Client::connect(ConnectConfig {
29        address: nats_address(),
30        ..Default::default()
31    })
32    .await
33    .unwrap();
34    LatticeSql::new(client)
35}
Source

pub fn with_timeout(client: Client, timeout: Duration) -> Self

Create a new client with a custom timeout (nanoseconds).

Source

pub fn with_auth(self, token: impl Into<String>) -> Self

Attach a shared auth token. Sent as _auth in every request.

Required when the server is configured with LDB_AUTH_TOKEN.

Source

pub async fn query(&self, sql: &str) -> Result<QueryResult, Error>

Execute a SELECT statement and return QueryResult.

Returns Error::WrongResultType if the SQL is not a SELECT.

Examples found in repository?
examples/smoke_test.rs (line 157)
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 async fn exec(&self, sql: &str) -> Result<u64, Error>

Execute an INSERT, UPDATE, or DELETE and return the affected row count.

Returns Error::WrongResultType if the SQL is a SELECT.

Examples found in repository?
examples/smoke_test.rs (lines 99-101)
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 async fn ddl(&self, sql: &str) -> Result<(), Error>

Execute a DDL statement (CREATE TABLE, DROP TABLE, CREATE INDEX, …).

DDL always reports zero affected rows, so the count is discarded. Returns () on success, Error::Db if the DDL fails.

Examples found in repository?
examples/smoke_test.rs (lines 72-74)
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 async fn sql(&self, sql: &str) -> Result<SqlResult, Error>

Send any SQL statement and return an auto-detected SqlResult.

Response shapes:

Examples found in repository?
examples/smoke_test.rs (line 424)
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}

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> 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, 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.