SQLite Table Export Utils

This allows for a val.town-hosted SQLite table to be exported as:

  • JSON (Record<string, unknown>[])
  • Arrow IPC (Uint8Array)
  • TODO: Others?

This can then be used by a HTTP endpoint, like so:

import { exportSQLiteTable, SQLiteTableExportFormat } from "https://esm.town/v/rlesser/sqliteTableExportUtils";

export default async function(req: Request): Promise<Response> {
  const tableName = new URL(req.url).searchParams.get("table");
  if (!tableName) {
    return new Response("Table name is required", { status: 400 });
  }
  const format = (new URL(req.url).searchParams.get("format") || "arrowIPC") as SQLiteTableExportFormat;
  const data = await exportSQLiteTable(tableName, format);
  if (data instanceof Uint8Array) {
    return new Response(data, {
      headers: { "Content-Type": "application/octet-stream" },
    });
  } else {
    return Response.json(data);
  }
}

TODO

  • Specify limit and offset of export, for pagination
  • Smart assessment of if the export is going to be over the val.town limit of 10MB, adjust to paginated of so.
  • Support other export formats.

PRs welcome!

Readme
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
import { sqlite } from "https://esm.town/v/std/sqlite";
import { type ResultSet } from "npm:@libsql/client";
import { DataType, Float64, Int32, Table, tableToIPC, Utf8, vectorFromArray } from "npm:apache-arrow";
// Function to map SQLite data types to Apache Arrow data types
function sqliteTypeToArrowType(sqliteType: string): DataType {
switch (sqliteType.toUpperCase()) {
case "INTEGER":
return new Int32();
case "TEXT":
return new Utf8();
case "REAL":
return new Float64();
// Add more mappings as needed
default:
return new Utf8(); // Default or throw error for unsupported types
}
}
export type SQLiteTableExportFormat = "json" | "arrowIPC";
export async function exportSQLiteTable(
tableName: string,
format: SQLiteTableExportFormat,
limit: number | null,
offset: number | null,
): Promise<Record<string, unknown>[] | Uint8Array> {
// Read data from the SQLite table
const data = await sqlite.execute(`
SELECT * FROM ${tableName} LIMIT ${limit || -1} OFFSET ${offset || 0}
`) as ResultSet;
const rows = data.rows;
// Dynamically read the schema of the table
const schemaInfo = await sqlite.execute(`PRAGMA table_info(${tableName})`) as ResultSet;
const columns = schemaInfo.rows.map(col => ({ name: String(col[1]), type: sqliteTypeToArrowType(String(col[2])) }));
// If json, output this right away
if (format == "json") {
return rows.map(r => Object.fromEntries(columns.map((c, idx) => [c.name, r[idx]])));
}
// Convert each column to an Arrow Vector
const tableData = {};
columns.forEach((column, i) => {
const data = rows.map(row => row[i]);
tableData[column.name] = vectorFromArray(data, column.type);
});
// Create an Apache Arrow Table using the dynamically determined schema and vectors
👆 This is a val. Vals are TypeScript snippets of code, written in the browser and run on our servers. Create scheduled functions, email yourself, and persist small pieces of data — all from the browser.