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 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', '[e-mail]'),
   ('Bob', '[e-mail]'),
   ('Charlie', '[e-mail]');
   
   -- 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 property

   You 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. 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:

   sqlite> SELECT * FROM users;
   Alice|[e-mail]
   Bob|[e-mail]
   Charlie|[e-mail]
   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:

   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:

   "expectedOutputAsBase64UrlEncoded": "<base64url-of-your-expected-output>"
   

5. 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]
   • Receive a webhook automatically if you specified callbackUrlOnExecutionCompletion Learn more about this here

   A 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.

---