LibSQL: Query a remote Turso database with cURL

Edit

Published on November 15, 2023

Author: Meet Rajesh Gor

#database #curl #libsql

If you are using a local libsql database, it is quite easy to query the database, but for a remote or a database on a hosted cloud platform like turso, we can use other clients or the api itself to query the data.

We can use the turso cli to get the authentication token for the database and then query the database.

Turso CLI

Using the turso-cli to access the turso platform. We will use turso cli to create a libsql database, create authentication tokens, and query the db.

Create a database (it's optional, you might already have a database)


bash
turso db create

You will get a database on the turso cloud platform with some random interesting name like a passphrase.

Use the command turso db list and copy the URL


graphql
DB_URL=dbname-orgname.turso.io

Create an authentication token for a particular database


bash
turso db tokens create db_name

Copy the JWT token and this will be used as a authentication token when accessing the remote database in the turso cloud.


bash
TOKEN=abcdef.12345.wxyz
DB_URL=dbname-orgname.turso.io

bash
curl -s -H "Authorization: bearer $TOKEN" \
     -d '{"statements": ["SELECT name FROM sqlite_master WHERE type=\"table\";"]}' \
     $DB_URL

We can use curl or any api client tools to send queries to the database hosted on the turso platform. We need to provide the JWT token in the Authorization header to connect to that particular database. The request's body is a JSON string with a list of statements to query the database.


graphql
[
    {
        "results":
            {
                "columns": ["name"],
                "rows":[
                    ["libsql_wasm_func_table"], ["_litestream_seq"], ["_litestream_lock"], ["sqlite_sequence"], ["user"]
                ]
            }
     }
]

The result is a list of key-value pairs as columns and rows for each of the statements in the body. The columns are a list of column names requested in the query, and the rows are a list of rows where each row is a list of field values from the query.

<a class='prev' href='/django-form-load-frontmatter'>

    <svg width="50px" height="50px" viewbox="0 0 24 24" fill="none" xmlns="http://www.w3.org/2000/svg">
        <path d="M13.5 8.25L9.75 12L13.5 15.75" stroke="var(--prevnext-color-angle)" stroke-width="1.5" stroke-linecap="round" stroke-linejoin="round"> </path>
    </svg>
    <div class='prevnext-text'>
        <p class='prevnext-subtitle'>prev</p>
        <p class='prevnext-title'>Django Blog DevLog: Load Frontmatter data into Template/Model Form Fields</p>
    </div>
</a>

<a class='next' href='/python-search-replace-file'>

    <div class='prevnext-text'>
        <p class='prevnext-subtitle'>next</p>
        <p class='prevnext-title'>Python: Search and Replace in File</p>
    </div>
    <svg width="50px" height="50px" viewbox="0 0 24 24" fill="none" xmlns="http://www.w3.org/2000/svg">
        <path d="M10.5 15.75L14.25 12L10.5 8.25" stroke="var(--prevnext-color-angle)" stroke-width="1.5" stroke-linecap="round" stroke-linejoin="round"></path>
    </svg>
</a>