sqlsqliterustsqlx

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


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?


Solution

  • 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(())
    }