> ## Documentation Index
> Fetch the complete documentation index at: https://docs.fermion.app/llms.txt
> Use this file to discover all available pages before exploring further.

# Executing SQLite via API

> Learn how to execute SQLite code using Fermion DSA API, including preparing your database file and encoding it correctly.

SQL execution requires a small extra step compared to other languages: you need to upload a database file (`db.sqlite`), encoded as base64URL `string`, as part of your request.

This guide will walk you through everything: from preparing your `.sqlite` file to encoding and sending it through the API.

***

## What is SQLite

SQLite is a lightweight, self-contained, and serverless relational database engine.Unlike traditional database management systems, it does not require a separate server process, making it ideal for applications that need an embedded database with minimal setup.

SQLite stores the entire database, including tables, indexes, and data, in a single `.sqlite` file, which makes it highly portable and easy to distribute.

It supports standard SQL syntax, transactions, and most common database features, allowing you to run queries,
insert data, and perform updates directly within the database file.
Its simplicity and reliability have made SQLite widely used in desktop applications, mobile apps, and embedded systems.

Head over to [SQLite org](https://sqlite.org/index.html) to learn more about SQLite

## How to execute SQL over Fermion API

Fermion offers the ability to execute SQL queries over the API. You can utilize the following steps to execute SQLite queries:

<Steps>
  <Step title="Create a sample SQLite database">
    Create a simple SQLite database and populate it with sample data. Note that this is just a sample schema and you can write your own schema
    as per the [SQLite guide](https://sqlite.org/lang.html)

    ```sql theme={null}
    -- schema.sql
    CREATE TABLE users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      email TEXT UNIQUE NOT NULL
    );

    INSERT INTO users (name, email) VALUES
    ('Alice', 'alice@fermion.app'),
    ('Bob', 'bob@fermion.app'),
    ('Charlie', 'charlie@fermion.app');

    -- Add more tables and data as needed
    ```

    If you have [sqlite3](https://sqlite.org/index.html) installed, you can run this locally using sqlite3 to convert your `sql` schema into an `sqlite` file:

    ```bash theme={null}
    sqlite3 db.sqlite < schema.sql
    ```

    You can also utilize **python** scripts to convert your `schema.sql` file into a `db.sqlite` file.

    ```python theme={null}
    import sqlite3

    # Create database
    conn = sqlite3.connect('db.sqlite')
    cursor = conn.cursor()

    # Read and execute SQL file
    with open('schema.sql', 'r') as file:
      sql_script = file.read()
      cursor.executescript(sql_script)

    conn.commit()
    conn.close()
    ```
  </Step>

  <Step title="Prepare Files for API Request">
    The Fermion API requires:

    * A ZIP file containing your `db.sqlite` file (Base64URL encoded)

    Here’s a simple script to generate base64URL encodings in Python for your zip file. Make sure to have a `db.sqlite` file in the same folder when you run this script

    ```python theme={null}
    import base64
    import zipfile

    def base64url_encode(data: bytes) -> str:
      # Base64 URL-safe encoding without padding
      return base64.urlsafe_b64encode(data).decode("utf-8").rstrip("=")

    # Create a ZIP containing db.sqlite
    zip_filename = "db.zip"
    with zipfile.ZipFile(zip_filename, "w", zipfile.ZIP_DEFLATED) as zipf:
      zipf.write("db.sqlite", arcname="db.sqlite")  # must be exactly 'db.sqlite'

    print(f"{zip_filename} created successfully!")

    # Read ZIP bytes
    with open(zip_filename, "rb") as f:
      zip_bytes = f.read()

    # Base64URL encode
    encoded_zip = base64url_encode(zip_bytes)

    # Save encoded string to file
    with open("encoded.txt", "w") as f:
      f.write(encoded_zip)

    print("Base64URL encoded string saved to encoded.txt")
    ```

    This script will provide you with a `db.zip` file and another file called `encoded.txt`, which will have the `base64URL` encoded string of the db.zip file.

    <Warning>
      The ZIP must **contain exactly one file named `db.sqlite`** : the API expects this name. Do not rename it to anything else inside the ZIP archive.
    </Warning>

    You can utilize this string while making the API request.
  </Step>

  <Step title="Make the API Request">
    Once you have the encoded SQL and ZIP file, you can now execute SQL queries via the [Request DSA code execution \[batch\]](/api-reference/dsa/get-dsa-code-execution-result-\[batch]) endpoint.
    Note that you also have to base64URL encode the source code and add the encoded string to the request for the `sourceCodeAsBase64UrlEncoded` property

    You can take help of the following json schema while setting up your own requests for SQL executions.

    ```json theme={null}
    {
      "language": "Sqlite_3_48_0",
      "sourceCodeAsBase64UrlEncoded": "<your-sql-query-base64url>",
      "additionalFilesAsZip": {
        "type": "base64url-encoding",
        "base64UrlEncodedZip": "<your-db-zip-base64url>"
      },
      "runConfig": {
        "callbackUrlOnExecutionCompletion": "https://your-domain.com/webhook/sqlite-result",
        "expectedOutputAsBase64UrlEncoded": "<string>",
        "customMatcherToUseForExpectedOutput": "ExactMatch",
        "cpuTimeLimitInMilliseconds": 2000,
        "wallTimeLimitInMilliseconds": 5000,
        "memoryLimitInKilobyte": 512000
      }
    }
    ```

    This `POST` request will return a list of task IDs which you can utilize while retrieving the execution results.

    ```json theme={null}
    {
      "output": {
        "status": "ok",
        "data": {
          "taskIds": [ "<string>" ]
        }
      }
    }
    ```
  </Step>

  <Step title="SQLite test cases">
    SQLite can produce output in multiple formats (up to 14), but **Fermion’s API uses the `list` mode format** for evaluating SQL output.

    In list mode, each column value is separated by a `|` (pipe), and each row appears on a new line.\
    Here’s an example:

    ```bash theme={null}
    sqlite> SELECT * FROM users;
    Alice|alice@fermion.app
    Bob|bob@fermion.app
    Charlie|charlie@fermion.app
    sqlite>
    ```

    To get the expected output for your own query against your own database,
    create a file called script.sql that has the solution query, and run the following command locally on your system:

    ```bash theme={null}
    cat script.sql | sqlite3 db.sqlite
    ```

    This command will print the output in the same list format that Fermion expects.
    You can then take this exact output, encode it using Base64URL, and include it in your API request under:

    ```json theme={null}
    "expectedOutputAsBase64UrlEncoded": "<base64url-of-your-expected-output>"
    ```
  </Step>

  <Step title="Retrieve results">
    You can utilize the task IDs received above to retrieve the results of DSA code execution

    * Poll results via [Get DSA code execution result \[batch\]](/api-reference/dsa/get-dsa-code-execution-result-\[batch])
    * Receive a webhook automatically if you specified `callbackUrlOnExecutionCompletion` Learn more about this [here](/creating-io-coding-labs/api-usage#using-webhook-urls-for-instant-results-recommended)

    A successful response will look like:

    ```json theme={null}
    {
      "output": {
        "status": "ok",
        "data": {
          "tasks": [
            {
              "taskUniqueId": "<string>",
              "sourceCodeAsBase64UrlEncoded": "<string>",
              "language": "C",
              "runConfig": {
                  // your runConfig for this taskID
              },
              "codingTaskStatus": "Pending",
              "runResult": {
                "compilerOutputAfterCompilationBase64UrlEncoded": "<string>",
                "finishedAt": "2023-11-07T05:31:56Z",
                "runStatus": "compilation-error",
                "programRunData": {
                  "cpuTimeUsedInMilliseconds": 1,
                  "wallTimeUsedInMilliseconds": 1,
                  "memoryUsedInKilobyte": 1,
                  "exitSignal": 123,
                  "exitCode": 123,
                  "stdoutBase64UrlEncoded": "<any>",
                  "stderrBase64UrlEncoded": "<any>"
                }
              }
            }
          ]
        }
      }
    }
    ```
  </Step>
</Steps>

## Best practices

* Ensure the ZIP file only contains **`db.sqlite`** , not folders or other files.
* Always use **Base64URL**, not standard Base64.

***