query db for database

Query a database using SQL.

Signature

> query db {flags} (SQL)

Flags

  • --params, -p {any}: List of parameters for the SQL statement

Parameters

  • SQL: SQL to execute against the database.

Input/output types:

inputoutput
anyany

Examples

Execute SQL against a SQLite database

> open foo.db | query db "SELECT * FROM Bar"

Execute a SQL statement with parameters

> stor create -t my_table -c { first: str, second: int }
        stor open | query db "INSERT INTO my_table VALUES (?, ?)" -p [hello 123]

Execute a SQL statement with named parameters

> stor create -t my_table -c { first: str, second: int }
stor insert -t my_table -d { first: 'hello', second: '123' }
stor open | query db "SELECT * FROM my_table WHERE second = :search_second" -p { search_second: 123 }
╭───┬───────┬────────╮
│ # │ first │ second │
├───┼───────┼────────┤
│ 0 │ hello │    123 │
╰───┴───────┴────────╯

Execute a SQL query, selecting a declared JSON(B) column that will automatically be parsed

> stor create -t my_table -c {data: jsonb}
[{data: {name: Albert, age: 40}} {data: {name: Barnaby, age: 54}}] | stor insert -t my_table
stor open | query db "SELECT data FROM my_table WHERE data->>'age' < 45"
╭───┬───────────────────╮
│ # │       data        │
├───┼───────────────────┤
│ 0 │ ╭──────┬────────╮ │
│   │ │ name │ Albert │ │
│   │ │ age  │ 40     │ │
│   │ ╰──────┴────────╯ │
╰───┴───────────────────╯

Execute a SQL query selecting a sub-field of a JSON(B) column. In this case, results must be parsed afterwards because SQLite does not return declaration types when a JSON(B) column is not directly selected

> stor create -t my_table -c {data: jsonb}
stor insert -t my_table -d {data: {foo: foo, bar: 12, baz: [0 1 2]}}
stor open | query db "SELECT data->'baz' AS baz FROM my_table" | update baz {from json}
╭───┬───────────╮
│ # │    baz    │
├───┼───────────┤
│ 0 │ ╭───┬───╮ │
│   │ │ 0 │ 0 │ │
│   │ │ 1 │ 1 │ │
│   │ │ 2 │ 2 │ │
│   │ ╰───┴───╯ │
╰───┴───────────╯