How do I retrieve SQLite engine introspection information via Rust SQLx?

Question

When trying to execute SQLite introspection queries in Rust SQLx I get:

error: error returned from database: (code: 1) no such table column: pragma_module_list.rowid

let recs = sqlx::query!(
    r#"
        SELECT rowid AS id, name
        FROM pragma_module_list()
        ORDER BY id
    "#
);

How can I bypass this SQLx limitation?

Answer 1

Below is an extended query that retrieves SQLite version and pragma / function / module / compile_option lists and returns a scalar string value containing formatted information (when printed to console or using a fixed-width font).

WITH
    version AS (
        SELECT concat_ws(x'0A',
            replace(printf('. %20s .', ''), ' ', '_'),
            '|    SQLite Version    |',
            replace(printf('| %20s |', ''), ' ', '-'),
            printf('| %20s |', sqlite_version()),
            replace(printf('| %20s |', ''), ' ', '_'),
            x'0A'
        ) AS info
    ),
    ids AS (
        SELECT concat_ws(x'0A',
            replace(printf('. %20s _ %20s _ %20s .', '', '', ''), ' ', '_'),
            '|    application_id    |     user_version     |    schema_version    |',
            replace(printf('| %20s | %20s | %20s |', '', '', ''), ' ', '-'),
            printf('| %20d | %20d | %20d |',
                   application_id, user_version, schema_version),
            replace(printf('| %20s | %20s | %20s |', '', '', ''), ' ', '_'),
            x'0A'
        ) AS info
        FROM pragma_application_id(), pragma_user_version(),
             pragma_schema_version()
    ),
    modules AS (
        SELECT concat_ws(x'0A',
            replace(printf('. %25s .', ''), ' ', '_'),
            '|           Modules         |',
            replace(printf('| %25s |', ''), ' ', '-'),
            group_concat(
                printf('| %-25s |', name), x'0A' ORDER BY name
            ),
            replace(printf('| %25s |', ''), ' ', '_'),
            x'0A'
        ) AS info
        FROM pragma_module_list()
    ),
    pragmas AS (
        SELECT concat_ws(x'0A',
            replace(printf('. %25s .', ''), ' ', '_'),
            '|           PRAGMA          |',
            replace(printf('| %25s |', ''), ' ', '-'),
            group_concat(
                printf('| %-25s |', name), x'0A' ORDER BY name
            ),
            replace(printf('| %25s |', ''), ' ', '_'),
            x'0A'
        ) AS info
        FROM pragma_pragma_list()
    ),
    compile_options AS (
        SELECT concat_ws(x'0A',
            replace(printf('. %35s .', ''), ' ', '_'),
            '|          Compile Options            |',
            replace(printf('| %35s |', ''), ' ', '-'),
            group_concat(
                printf('| %-35s |', compile_options),
                x'0A' ORDER BY compile_options
            ),
            replace(printf('| %35s |', ''), ' ', '_'),
            x'0A'
        ) AS info
        FROM pragma_compile_options()
    ),
    functions AS (
        SELECT concat_ws(x'0A',
            replace(printf('. %76s .', ''), ' ', '_'),
            printf('| %30s Function List  %30s |', '', ''),
            printf('| %30s -------------  %30s |', '', ''),
            '|              name              | builtin | type  | encoding | narg |  flags  |',
            replace(printf('| %76s |', ''), ' ', '-'),
            group_concat(printf('| %-30s |    %d    |   %s   |  %-7s |  %2d  | %7d |',
                name,
                builtin,
                type,
                enc,
                narg,
                flags
            ), x'0A' ORDER BY name, narg),
            replace(printf('| %30s | %7s | %5s | %8s | %4s | %7s |',
                '', '', '', '', '', ''), ' ', '_'),
            x'0A'
        ) AS info
        FROM pragma_function_list()
    ),
    info_schema AS (
        SELECT concat_ws(x'0A',
                version.info,
                ids.info,
                modules.info,
                pragmas.info,
                functions.info,
                compile_options .info
            ) AS info
        FROM version, ids, modules, pragmas, functions, compile_options 
    )
SELECT *
FROM info_schema;

This query can be executed in an SQLite shell (for example, the official SQLite fiddle) and also works with sqlx. For example, place the query in the "queries/intro.sql" file and the following code in the "src/main.rs":

use std::env;
use sqlx::sqlite::SqlitePool;


#[tokio::main(flavor = "current_thread")]
async fn main() -> anyhow::Result<()> {
    let pool = SqlitePool::connect(&env::var("DATABASE_URL")?).await?;

    println!("\nSQLite Introspection Information");
    exec(&pool).await?;

    Ok(())
}


async fn exec(pool: &SqlitePool) -> anyhow::Result<()> {
    let recs = sqlx::query_file!("queries/intro.sql")
        .fetch_all(pool)
        .await?;
    
    for rec in recs {
        println!(
            "{}",
            rec.info.unwrap(),
        );
    }

    Ok(())
}