Skip to main content
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 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:
1

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
-- 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 installed, you can run this locally using sqlite3 to convert your sql schema into an sqlite file:
sqlite3 db.sqlite < schema.sql
You can also utilize python scripts to convert your schema.sql file into a db.sqlite file.
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()
2

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
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.
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.
You can utilize this string while making the API request.
3

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] endpoint. Note that you also have to base64URL encode the source code and add the encoded string to the request for the sourceCodeAsBase64UrlEncoded propertyYou can take help of the following json schema while setting up your own requests for SQL executions.
{
  "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.
{
  "output": {
    "status": "ok",
    "data": {
      "taskIds": [ "<string>" ]
    }
  }
}
4

Retrieve results

You can utilize the task IDs received above to retrieve the results of DSA code executionA successful response will look like:
{
  "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>"
            }
          }
        }
      ]
    }
  }
}

​
Best practices

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