Back to packages list
Vals using @libsql/client
Description from the NPM package:
libSQL driver for TypeScript and JavaScript
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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
import { InStatement, ResultSet } from "https://esm.sh/@libsql/client";
import {
convertSqliteType,
DatabaseHeader,
DatabaseResultSet,
DatabaseRow,
SqliteLikeBaseDriver,
} from "https://esm.sh/@libsqlstudio/gui/driver";
export function transformRawResult(raw: ResultSet): DatabaseResultSet {
const headerSet = new Set();
const headers: DatabaseHeader[] = raw.columns.map((colName, colIdx) => {
const colType = raw.columnTypes[colIdx];
let renameColName = colName;
for (let i = 0; i < 20; i++) {
if (!headerSet.has(renameColName)) break;
renameColName = `__${colName}_${i}`;
}
headerSet.add(renameColName);
return {
name: renameColName,
displayName: colName,
originalType: colType,
type: convertSqliteType(colType),
};
});
const rows = raw.rows.map((r) =>
headers.reduce((a, b, idx) => {
a[b.name] = r[idx];
return a;
}, {} as DatabaseRow)
);
return {
rows,
stat: {
rowsAffected: raw.rowsAffected,
// This is unique for stateless driver
rowsRead: (raw as any).rowsRead ?? null,
rowsWritten: (raw as any).rowsWritten ?? null,
queryDurationMs: (raw as any).queryDurationMS ?? null,
},
headers,
lastInsertRowid: raw.lastInsertRowid === undefined
? undefined
: Number(raw.lastInsertRowid),
};
}
export default class ValtownDriver extends SqliteLikeBaseDriver {
constructor() {
super();
}
async transaction(stmts: InStatement[]): Promise<DatabaseResultSet[]> {
const r = await fetch(`/api/batch`, {
method: "POST",
headers: {
"Content-Type": "application/json",
},
body: JSON.stringify({
statements: stmts,
mode: "write",
}),
});
const json = await r.json();
return json.map(transformRawResult);
}
supportBigInt(): boolean {
return false;
}
async query(stmt: InStatement): Promise<DatabaseResultSet> {
const r = await fetch(`/api/execute`, {
method: "POST",
headers: {
"Content-Type": "application/json",
},
body: JSON.stringify({ statement: stmt }),
});
const json = await r.json();
return transformRawResult(json as ResultSet);
}
close(): void {
// do nothing
}
}
Val Town SQLite - https://docs.val.town/std/sqlite
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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
import { API_URL } from "https://esm.town/v/std/API_URL";
import { LibsqlError, type TransactionMode } from "npm:@libsql/client";
import { z } from "npm:zod";
/**
* Every Val Town account comes with its own private
* [SQLite database](https://www.sqlite.org/) that
* is accessible from any of your vals.
* ([Docs ↗](https://docs.val.town/std/sqlite))
*/
export const sqlite = {
/**
* Executes a SQLite statement.
*
* @param {InStatement} statement - The SQLite statement to execute.
* @example String query:
* `sqlite.execute("SELECT 1;")`
* @example Query with arguments:
* `sqlite.execute({sql: "SELECT * from books WHERE year > ?;", args: [2020]})`
*/
execute,
/**
* Executes a batch of SQLite statements.
*
* @param {InStatement[]} statements - An array of SQLite statements to execute.
* @param {TransactionMode} [mode] - The transaction mode for the batch execution.
*/
batch,
};
// ------------
// Functions
// ------------
async function execute(statement: InStatement): Promise<ResultSet> {
const res = await fetch(`${API_URL}/v1/sqlite/execute`, {
method: "POST",
headers: {
Authorization: `Bearer ${Deno.env.get("valtown")}`,
},
body: JSON.stringify({ statement }),
});
if (!res.ok) {
throw createResError(await res.text());
}
return res.json();
}
async function batch(statements: InStatement[], mode?: TransactionMode): Promise<ResultSet[]> {
const res = await fetch(`${API_URL}/v1/sqlite/batch`, {
method: "POST",
headers: {
Authorization: `Bearer ${Deno.env.get("valtown")}`,
},
body: JSON.stringify({ statements, mode }),
});
if (!res.ok) {
throw createResError(await res.text());
}
return res.json();
}
function createResError(body: string) {
try {
const e = zLibsqlError.parse(JSON.parse(body));
// e.message already contains the code, and LibsqlError adds the
// code to the beginning, so we remove it here
const msg = e.message.replace(e.code, "").replace(/^:\s+/, "");
return new LibsqlError(msg, e.code, e.rawCode);
} catch (_) {
// Failed to parse libsql error
}
return new Error(body);
}
// ------------
// Helpers
// ------------
const zLibsqlError = z.object({
message: z.string(),
code: z.string(),
rawCode: z.number().optional(),
});
// We patch these types to only support JSON values
export type InValue = null | string | number | boolean;
export type InArgs = Array<InValue> | Record<string, InValue>;
export type InStatement = {
/**
* The SQL statement to execute.
*/
sql: string;
/**
* The arguments to bind to the SQL statement.
*/
args: InArgs;
} | string;
sqliteBuilder: Opinionated safe(r) query builder using tagged templates
Create valimport { Statement } from "https://esm.town/v/postpostscript/sqliteBuilder";
const unsafeId = "1234 or TRUE"
console.log(Statement`
SELECT *
FROM table
WHERE id = ${unsafeId}
${Statement`AND other_`}
`)
// StatementInstance {
// sql: "\nSELECT *\nFROM table\nWHERE id = ?\nAND otherCondition\n",
// args: [ "1234 or TRUE" ],
// log: false
// }
const results = await Statement`SELECT ...`.execute()
// [ { key: "value", anotherKey: "anotherValue" }, ... ]
Or you can pass it directly to @std/sqlite.execute:
Create valimport { sqlite } from "https://esm.town/v/std/sqlite"
await sqlite.execute(Statement`Select ...`)
You can combine multiple statements using Statement.prototype.combineWith:
Statement`...`.combineWith(Statement`...`, " AND ")
[
Statement`fieldA`,
Statement`fieldB`,
Statement`fieldC`,
].reduce((a, b) => a.combineWith(b, ", "))
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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
import { MaybePromise } from "https://esm.town/v/postpostscript/typeUtils";
import { type InStatement, type InValue, sqlite } from "https://esm.town/v/std/sqlite";
import { LibsqlError, type Row } from "npm:@libsql/client";
export { LibsqlError } from "npm:@libsql/client";
export type SqliteInterface = {
execute(statement: InStatement): MaybePromise<{
rows: Row[];
columns: string[];
}>;
};
export type ExecuteOptions<T extends Record<string, any>, I extends SqliteInterface> = {
fallback?: (
stmt: StatementInstance,
options: ExecuteOptions<T, I>,
error: Error,
) => MaybePromise<T[]>;
sqlite?: I;
};
export class StatementInstance {
public sql: string;
public args: InValue[];
public log: boolean;
constructor(sql: string, args: InValue[] = []) {
this.sql = sql;
this.args = args;
this.log = false;
}
combineWith(instance: StatementInstance, sep = "") {
return new StatementInstance(this.sql + sep + instance.sql, this.args.concat(instance.args));
}
execute<T extends Record<string, any>, I extends SqliteInterface>(
options: ExecuteOptions<T, I> = {},
): MaybePromise<T[]> {
if (this.log) {
console.log("executing", {
sql: this.sql,
args: this.args,
});
}
const callback = ({ columns, rows }) => {
return rows.map(row => {
return Object.fromEntries(columns.map((key, i) => {
return [key, row[i]];
}));
});
};
const errorHandle = (e: Error) => {
if (options.fallback) {
return options.fallback(this, {
...options,
fallback: undefined,
}, e);
}
throw e;
};
try {
const queryResult = (options.sqlite ?? sqlite).execute(this);
if (!(queryResult instanceof Promise)) {
return callback(queryResult);
}
return queryResult.then(callback).catch(errorHandle);
} catch (e) {
return errorHandle(e);
}
}
toJSON() {
return {
sql: this.sql,
args: this.args,
};
}
}
export function Statement(
strings: TemplateStringsArray,
...replacements: (InValue | StatementInstance)[]
) {
return strings.reduce((statement, string, index) => {
const stringInstance = new StatementInstance(string);
if (replacements.length >= index + 1) {
const replacement = replacements[index];
return statement
.combineWith(stringInstance)
.combineWith(
replacement instanceof StatementInstance ? replacement : new StatementInstance("?", [replacement]),
);
}
return statement.combineWith(stringInstance);
}, new StatementInstance(""));
Query All Public Vals
Example: val town leaderboard
Create valimport { zip } from "https://esm.town/v/pomdtr/sql";
import { db } from "https://esm.town/v/sqlite/db";
const res = await db.execute("SELECT author_username, COUNT(*) AS val_count FROM vals GROUP BY author_username ORDER BY 2 DESC LIMIT 10");
console.table(zip(res));
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
import { InStatement } from "https://esm.town/v/std/sqlite";
import type { ResultSet } from "npm:@libsql/client";
async function execute(statement: InStatement): Promise<ResultSet> {
const resp = await fetch("https://sqlite-execute.web.val.run", {
method: "POST",
body: JSON.stringify({
args: [statement],
}),
});
if (!resp.ok) {
throw new Error(await resp.text());
}
return resp.json();
}
export const db = {
execute,
};
sqliteUniverse: make queries against multiple vals or endpoints at the same time!
Example: @postpostscript/sqliteUniverseExample
Todo
- tests‼️
- update to support following syntax:
SELECT * FROM "@example/endpoint".someTableorSELECT * FROM "@example/endpoint:::someTable"
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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
import { getValEndpointFromName } from "https://esm.town/v/postpostscript/meta";
import { getTableNames, replaceTableNames } from "https://esm.town/v/postpostscript/sqliteAST";
import { sqliteFromBlob } from "https://esm.town/v/postpostscript/sqliteBackup";
import { Statement, StatementInstance } from "https://esm.town/v/postpostscript/sqliteBuilder";
import { sqliteDump } from "https://esm.town/v/postpostscript/sqliteDump";
import { sqliteFromAPI } from "https://esm.town/v/postpostscript/sqliteFromAPI";
import type { SqliteInterface } from "https://esm.town/v/postpostscript/sqliteTypes";
import { createSqlite } from "https://esm.town/v/postpostscript/sqliteWasm";
import type { MaybePromise } from "https://esm.town/v/postpostscript/typeUtils";
import type { InStatement } from "https://esm.town/v/std/sqlite";
import type { ResultSet } from "npm:@libsql/client";
export const sqliteUniverse = {
execute,
batch,
};
export function sqliteUniverseWithOptions(options: SqliteUniverseOptions) {
return {
execute(statement: InStatement) {
return execute(statement, options);
},
batch(statements: InStatement[]) {
return batch(statements, options);
},
};
}
async function execute(
statement: InStatement,
options: SqliteUniverseOptions = {},
): Promise<Omit<ResultSet, "columnTypes" | "lastInsertRowid">> {
const [res] = await batch([statement], options);
return res;
}
async function batch(
statements: InStatement[],
options: SqliteUniverseOptions = {},
): Promise<Omit<ResultSet, "columnTypes" | "lastInsertRowid">[]> {
const endpointTableMap: EndpointTableMap = {};
const fullTableNames = statements.map(getTableNames).reduce((res, { tables }) => {
return new Set([...res, ...tables]);
}, new Set<string>());
fullTableNames.forEach(fullTable => {
const parts = fullTable.split("/");
const endpoint = parts.slice(0, -1).join("/");
const table = parts.slice(-1)[0];
endpointTableMap[endpoint] ??= new Set<string>();
endpointTableMap[endpoint].add(table);
});
const sqlite = await createSqliteFromEndpointTables(endpointTableMap, options);
const normalized = statements.map(statement => {
let { sql, args } = typeof statement === "string"
? new StatementInstance(statement)
: statement;
let match;
while (match = sql.match(/"([^"]+\/)sqlite_schema"/)) {
const [full, start] = match;
const index = sql.indexOf(full);
const numPreviousArgs = sql.slice(0, index).split("?").length - 1;
if (!args) {
args = [];
}
args.splice(numPreviousArgs, 0, `${start}%`);
sql = [
sql.slice(0, index),
`(SELECT * FROM sqlite_schema WHERE tbl_name LIKE ?)`,
sql.slice(index + full.length),
].join("");
}
return new StatementInstance(
sql,
args,
);
});
return sqlite.batch(normalized);
}
async function createSqliteFromEndpointTables(
endpointTableMap: EndpointTableMap,
{ interfaces = defaultInterfaces }: SqliteUniverseOptions = {},
) {
const schemas = Object.entries(endpointTableMap).map(async ([endpoint, tables]) => {
let sqlite = await interfaces.exact?.[endpoint];
if (sqlite instanceof Function) {
sqlite = await sqlite({ endpoint, tables });
}
for (let i = 0; !sqlite && i < interfaces.patterns?.length; i++) {
const [test, method] = interfaces.patterns[i];
const match = endpoint.match(test);
if (match) {
sqlite = await method({
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
51
52
53
54
55
56
57
58
59
60
61
62
63
import { delay } from "https://deno.land/x/delay@v0.2.0/mod.ts";
import { extractValInfo } from "https://esm.town/v/pomdtr/extractValInfo?v=26";
import { Statement, StatementInstance } from "https://esm.town/v/postpostscript/sqliteBuilder";
import { sqliteDump } from "https://esm.town/v/postpostscript/sqliteDump";
import { createSqlite } from "https://esm.town/v/postpostscript/sqliteWasm";
import type { InStatement } from "https://esm.town/v/std/sqlite";
import { type ResultSet } from "npm:@libsql/client";
import { Hono } from "npm:hono";
const dumped = {};
async function database() {
const dump = await sqliteDump(dumped);
const sqlite = createSqlite();
sqlite.batch(dump);
return sqlite;
}
export const sqlitePublic = {
execute,
batch,
};
async function execute(statement: InStatement): Promise<ResultSet> {
const res = await fetch(`${ENDPOINT}/execute`, {
method: "POST",
body: JSON.stringify({ statement }),
});
if (!res.ok) {
throw new Error(await res.text());
}
return res.json();
}
async function batch(statements: InStatement[]): Promise<ResultSet[]> {
const res = await fetch(`${ENDPOINT}/batch`, {
method: "POST",
body: JSON.stringify({ statements }),
});
if (!res.ok) {
throw new Error(await res.text());
}
return res.json();
}
export const { httpEndpoint: ENDPOINT } = extractValInfo(import.meta.url);
const app = new Hono();
app.post("/execute", async (c) => {
const { statement } = await c.req.json();
const sqlite = await database();
const res = await sqlite.execute(statement);
console.log(typeof res, res);
return c.json(res);
});
app.post("/batch", async (c) => {
const { statements } = await c.req.json();
const sqlite = await database();
return c.json(sqlite.batch(statements));
});
export default app.fetch;
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
51
import { extractValInfo } from "https://esm.town/v/pomdtr/extractValInfo?v=26";
import { Statement } from "https://esm.town/v/postpostscript/sqliteBuilder";
import { sqliteDump } from "https://esm.town/v/postpostscript/sqliteDump";
import { createSqlite } from "https://esm.town/v/postpostscript/sqliteWasm";
import type { InStatement } from "https://esm.town/v/std/sqlite";
import { createServer } from "https://esm.town/v/vladimyr/sqliteServer";
import { toHonoHandler } from "https://esm.town/v/vladimyr/toHonoHandler";
import { type ResultSet } from "npm:@libsql/client";
import { Hono } from "npm:hono";
import ky from "npm:ky";
const dumped = {
tables: ["authIdExampleComments_comment"],
subset: {
authIdExampleComments_comment:
Statement`SELECT * FROM authIdExampleComments_comment WHERE username = "postpostscript"`,
},
};
const sqliteServer = createServer(async () => {
const dump = await sqliteDump(dumped);
const sqlite = createSqlite();
sqlite.batch(dump);
return sqlite;
});
const app = new Hono();
app.post("/execute", toHonoHandler(sqliteServer.handleExecute));
app.post("/batch", toHonoHandler(sqliteServer.handleBatch));
export default app.fetch;
export const { httpEndpoint: ENDPOINT } = extractValInfo(import.meta.url);
export const sqlitePublic = {
execute,
batch,
};
async function execute(statement: InStatement): Promise<ResultSet> {
return ky.post("execute", {
json: { statement },
prefixUrl: ENDPOINT,
}).json();
}
async function batch(statements: InStatement[]): Promise<ResultSet[]> {
return ky.post("batch", {
json: { statements },
prefixUrl: ENDPOINT,
}).json();
}
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
51
import { DB } from "https://deno.land/x/sqlite/mod.ts";
import { type InStatement, sqlite as sqliteAPI } from "https://esm.town/v/std/sqlite";
import { type ResultSet } from "npm:@libsql/client";
export function createSqlite() {
const db = new DB();
function execute(statement: InStatement): Omit<ResultSet, "columnTypes" | "lastInsertRowid"> {
const stmt = typeof statement === "string"
? {
sql: statement,
args: [],
}
: statement;
const lastChanges = db.totalChanges;
try {
const query = db.prepareQuery(stmt.sql);
const rows = query.all(stmt.args);
const res = {
rows,
rowsAffected: db.totalChanges - lastChanges,
columns: query.columns().map(({ name }) => name),
};
return {
...res,
toJSON() {
return res;
},
};
} catch (e) {
console.log("sqliteWasm execute errored:", stmt.sql);
throw e;
}
}
return {
db,
execute,
batch(statements: InStatement[]) {
return statements.map(execute);
},
close() {
return db.close();
},
[Symbol.dispose]: () => db.close(),
};
}
sqliteBuilder: Opinionated safe(r) query builder using tagged templates
Create valimport { Statement } from "https://esm.town/v/postpostscript/sqliteBuilder";
const unsafeId = "1234 or TRUE"
console.log(Statement`
SELECT *
FROM table
WHERE id = ${unsafeId}
${Statement`AND other_`}
`)
// StatementInstance {
// sql: "\nSELECT *\nFROM table\nWHERE id = ?\nAND otherCondition\n",
// args: [ "1234 or TRUE" ],
// log: false
// }
const results = await Statement`SELECT ...`.execute()
// [ { key: "value", anotherKey: "anotherValue" }, ... ]
Or you can pass it directly to @std/sqlite.execute:
Create valimport { sqlite } from "https://esm.town/v/std/sqlite"
await sqlite.execute(Statement`Select ...`)
You can combine multiple statements using Statement.prototype.combineWith:
Statement`...`.combineWith(Statement`...`, " AND ")
[
Statement`fieldA`,
Statement`fieldB`,
Statement`fieldC`,
].reduce((a, b) => a.combineWith(b, ", "))
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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
import { type SqliteInterface } from "https://esm.town/v/postpostscript/sqliteTypes";
import { MaybePromise } from "https://esm.town/v/postpostscript/typeUtils";
import { type InStatement, type InValue, sqlite } from "https://esm.town/v/std/sqlite";
import { LibsqlError, type Row } from "npm:@libsql/client";
export { LibsqlError } from "npm:@libsql/client";
export type ExecuteOptions<T extends Record<string, any>, I extends SqliteInterface> = {
fallback?: (
stmt: StatementInstance,
options: ExecuteOptions<T, I>,
error: Error,
) => MaybePromise<T[]>;
sqlite?: I;
};
export class StatementInstance {
public sql: string;
public args: InValue[];
public log: boolean;
constructor(sql: string, args: InValue[] = []) {
this.sql = sql;
this.args = args;
this.log = false;
}
combineWith(instance: StatementInstance, sep = "") {
return new StatementInstance(this.sql + sep + instance.sql, this.args.concat(instance.args));
}
execute<T extends Record<string, any>, I extends SqliteInterface>(
options: ExecuteOptions<T, I> = {},
): MaybePromise<T[]> {
if (this.log) {
console.log("executing", {
sql: this.sql,
args: this.args,
});
}
const callback = ({ columns, rows }) => {
return rows.map(row => {
return Object.fromEntries(columns.map((key, i) => {
return [key, row[i]];
}));
});
};
const errorHandle = (e: Error) => {
if (options.fallback) {
return options.fallback(this, {
...options,
fallback: undefined,
}, e);
}
throw e;
};
try {
const queryResult = (options.sqlite ?? sqlite).execute(this);
if (!(queryResult instanceof Promise)) {
return callback(queryResult);
}
return queryResult.then(callback).catch(errorHandle);
} catch (e) {
return errorHandle(e);
}
}
toJSON() {
return {
sql: this.sql,
args: this.args,
};
}
async debug() {
const result = await this.execute();
const { default: Table } = await import("npm:easy-table");
console.debug(Table.print(result));
return result;
}
}
export function Statement(
strings: TemplateStringsArray,
...replacements: (InValue | StatementInstance)[]
) {
return strings.reduce((statement, string, index) => {
const stringInstance = new StatementInstance(string);
if (replacements.length >= index + 1) {
const replacement = replacements[index];
return statement
.combineWith(stringInstance)
.combineWith(
replacement instanceof StatementInstance ? replacement : new StatementInstance("?", [replacement]),
);
}
return statement.combineWith(stringInstance);
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:
Create valimport { 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!
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
51
52
53
54
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
const arrowTable = new Table(tableData);
return tableToIPC(arrowTable);
}
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:
Create valimport { 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!
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
51
52
53
54
55
56
57
58
59
60
61
62
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";
import { zip } from "npm:lodash-es@4.17.21";
// Function to map SQLite data types to Apache Arrow data types
function sqliteTypeToArrowType(sqliteType: string): DataType {
switch (sqliteType.toUpperCase()) {
case "INTEGER":
case "NUMBER":
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";
// From https://www.val.town/v/pomdtr/sql zip
export function resultSetToJSON(res: ResultSet) {
return res.rows.map(row => Object.fromEntries(zip(res.columns, row)));
}
export function resultSetToArrowTable(res: ResultSet) {
const tableData = {};
res.columns.forEach((column, i) => {
const data = res.rows.map(row => row[i]);
tableData[column] = vectorFromArray(data, sqliteTypeToArrowType(res.columnTypes[i]));
});
return new Table(tableData);
}
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 resultSetToJSON(data);
}
const arrowTable = resultSetToArrowTable(data);
return tableToIPC(arrowTable);
}
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
51
52
53
54
55
56
57
58
59
60
import { sqlite } from "https://esm.town/v/std/sqlite?v=4";
import { Row } from "npm:@libsql/client";
import axios from "npm:axios";
const fromAddress = "reminder@plk-bjelowbar.hr";
const token = Deno.env.get("mailersend");
const mailService = "https://api.mailersend.com/v1/email";
const emailContent = (name: string, toAddress: string) => ({
from: {
email: fromAddress,
name: "Bjelowbar",
},
to: [{
email: toAddress,
name,
}],
subject: "Subscription reminder",
text: "Bla bla ovo je tekst koji se prikaže ako nije dostupan HTML u readeru",
html: `
<h1>Plati!</h1>
<p>Daj pare i ništa nemoj glumit!</p>
`,
});
const sendEmail = async (content) => {
const response = await axios.post(mailService, content, {
headers: {
"Content-Type": "application/json",
"X-Requested-With": "XMLHttpRequest",
"Authorization": `Bearer ${token}`,
},
});
console.log(response);
};
const runJobs = async (rows: Row[]) => {
if (rows?.length < 1) return;
rows.forEach((row) => {
if (!row || row.length < 2) return;
const content = emailContent(row[0] as string, row[1] as string);
sendEmail(content);
});
};
const job = async () => {
const day = 60 * 60 * 24;
const today: number = Math.floor(Number(new Date()) / 1000);
const yesterday = today - day;
const query =
`SELECT first_name, email FROM users WHERE membership_until > ${yesterday} and membership_until < ${today}`;
const { rows } = await sqlite.execute(query);
await runJobs(rows);
console.log("Emails sent:");
console.log(rows);
};
export default job;
This is a wrapper of the val town std sqlite library that adds tracing via https://www.val.town/v/saolsen/tracing.
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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
import { SpanStatusCode } from "https://cdn.skypack.dev/@opentelemetry/api";
import { type ResultSet, type TransactionMode } from "npm:@libsql/client";
import { InStatement, sqlite as std_sqlite } from "https://esm.town/v/std/sqlite?v=4";
import { get_tracer } from "https://esm.town/v/saolsen/tracing?v=136";
async function traced_execute(statement: InStatement): Promise<ResultSet> {
return await get_tracer().startActiveSpan(`sqlite:execute`, async (span) => {
if (span.isRecording()) {
if (typeof statement === "string") {
span.setAttributes({
"sqlite.statement": statement,
"sqlite.args": [],
});
} else {
span.setAttributes({
"sqlite.statement": statement.sql,
"sqlite.args": statement.args,
});
}
}
try {
const result = await std_sqlite.execute(statement);
if (span.isRecording()) {
span.setStatus({ code: SpanStatusCode.OK });
}
return result;
} catch (error) {
if (span.isRecording()) {
span.setStatus({
code: SpanStatusCode.ERROR,
message: error.message,
});
}
throw new Error(error);
} finally {
span.end();
}
});
}
async function traced_batch(statements: InStatement[], mode?: TransactionMode): Promise<ResultSet[]> {
return await get_tracer().startActiveSpan(`sqlite:batch`, async (span) => {
if (span.isRecording()) {
span.setAttributes({
"sqlite.statements": JSON.stringify(statements),
});
}
try {
const result = await std_sqlite.batch(statements, mode);
if (span.isRecording()) {
span.setStatus({ code: SpanStatusCode.OK });
}
return result;
} catch (error) {
if (span.isRecording()) {
span.setStatus({
code: SpanStatusCode.ERROR,
message: error.message,
});
}
throw new Error(error);
} finally {
span.end();
}
});
}
export const sqlite = {
execute: traced_execute,
batch: traced_batch,
};
Val Town SQLite
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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
import { API_URL } from "https://esm.town/v/std/API_URL";
import { LibsqlError, type ResultSet, type TransactionMode } from "npm:@libsql/client";
import { z } from "npm:zod";
export const sqlite = {
execute,
batch,
};
// ------------
// Functions
// ------------
async function execute(statement: InStatement): Promise<ResultSet> {
const res = await fetch(`${API_URL}/v1/sqlite/execute`, {
method: "POST",
headers: {
Authorization: `Bearer ${Deno.env.get("valtown")}`,
},
body: JSON.stringify({ statement }),
});
if (!res.ok) {
throw createResError(await res.text());
}
return res.json();
}
async function batch(statements: InStatement[], mode?: TransactionMode): Promise<ResultSet[]> {
const res = await fetch(`${API_URL}/v1/sqlite/batch`, {
method: "POST",
headers: {
Authorization: `Bearer ${Deno.env.get("valtown")}`,
},
body: JSON.stringify({ statements, mode }),
});
if (!res.ok) {
throw createResError(await res.text());
}
return res.json();
}
function createResError(body: string) {
try {
const e = zLibsqlError.parse(JSON.parse(body));
// e.message already contains the code, and LibsqlError adds the
// code to the beginning, so we remove it here
const msg = e.message.replace(e.code, "").replace(/^:\s+/, "");
return new LibsqlError(msg, e.code, e.rawCode);
} catch (_) {
// Failed to parse libsql error
}
return new Error(body);
}
// ------------
// Helpers
// ------------
const zLibsqlError = z.object({
message: z.string(),
code: z.string(),
rawCode: z.number().optional(),
});
// We patch these types to only support JSON values
export type InValue = null | string | number | boolean;
export type InArgs = Array<InValue> | Record<string, InValue>;
export type InStatement = {
sql: string;
args: InArgs;
} | string;
SQLite - Docs ↗
SQLite is a lightweight, standard database. Every Val Town account comes with its own private SQLite database that is accessible from any of your vals via std/sqlite.
Val Town SQLite is powered by Turso.
You may find these admin viewers helpful managing your database:
- SQLite Explorer (built in Val Town)
- LibSQL Studio
Limits
You can store 10mb on the free plan and up to 1gb on the paid plan. Contact us if you need more space.
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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
import { API_URL } from "https://esm.town/v/std/API_URL";
import { LibsqlError, type TransactionMode } from "npm:@libsql/client";
import { z } from "npm:zod";
/**
* Every Val Town account comes with its own private
* [SQLite database](https://www.sqlite.org/) that
* is accessible from any of your vals.
* ([Docs ↗](https://docs.val.town/std/sqlite))
*/
export const sqlite = {
/**
* Executes a SQLite statement.
*
* @param {InStatement} statement - The SQLite statement to execute.
* @example String query:
* `sqlite.execute("SELECT 1;")`
* @example Query with arguments:
* `sqlite.execute({sql: "SELECT * from books WHERE year > ?;", args: [2020]})`
*/
execute,
/**
* Executes a batch of SQLite statements.
*
* @param {InStatement[]} statements - An array of SQLite statements to execute.
* @param {TransactionMode} [mode] - The transaction mode for the batch execution.
*/
batch,
};
// ------------
// Functions
// ------------
async function execute(statement: InStatement): Promise<ResultSet> {
const res = await fetch(`${API_URL}/v1/sqlite/execute`, {
method: "POST",
headers: {
Authorization: `Bearer ${Deno.env.get("valtown")}`,
},
body: JSON.stringify({ statement }),
});
if (!res.ok) {
throw createResError(await res.text());
}
return res.json();
}
async function batch(statements: InStatement[], mode?: TransactionMode): Promise<ResultSet[]> {
const res = await fetch(`${API_URL}/v1/sqlite/batch`, {
method: "POST",
headers: {
Authorization: `Bearer ${Deno.env.get("valtown")}`,
},
body: JSON.stringify({ statements, mode }),
});
if (!res.ok) {
throw createResError(await res.text());
}
return res.json();
}
function createResError(body: string) {
try {
const e = zLibsqlError.parse(JSON.parse(body));
// e.message already contains the code, and LibsqlError adds the
// code to the beginning, so we remove it here
const msg = e.message.replace(e.code, "").replace(/^:\s+/, "");
return new LibsqlError(msg, e.code, e.rawCode);
} catch (_) {
// Failed to parse libsql error
}
return new Error(body);
}
// ------------
// Helpers
// ------------
const zLibsqlError = z.object({
message: z.string(),
code: z.string(),
rawCode: z.number().optional(),
});
// We patch these types to only support JSON values
export type InValue = null | string | number | boolean;
export type InArgs = Array<InValue> | Record<string, InValue>;
export type InStatement = {
/**
* The SQL statement to execute.
*/
sql: string;
/**
* The arguments to bind to the SQL statement.
*/
args: InArgs;
} | string;