1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
import { Statement } from "https://esm.town/v/postpostscript/sqliteBuilder";
import { sqliteDump } from "https://esm.town/v/postpostscript/sqliteDump";
import { sqliteServe } from "https://esm.town/v/postpostscript/sqliteServe";
import { createSqlite } from "https://esm.town/v/postpostscript/sqliteWasm";
export const {
execute,
batch,
sqlite: sqlitePublic,
handler,
} = sqliteServe(async () => {
const dump = await sqliteDump({
tables: ["authIdExampleComments_comment"],
subset: {
authIdExampleComments_comment:
Statement`SELECT * FROM authIdExampleComments_comment WHERE username = "postpostscript"`,
},
});
const sqlite = createSqlite();
sqlite.batch(dump);
return sqlite;
});
export default handler;
SQLite Explorer
View and interact with your Val Town SQLite data. It's based off Steve's excellent SQLite Admin val, adding the ability to run SQLite queries directly in the interface. This new version has a revised UI and that's heavily inspired by LibSQL Studio by invisal. This is now more an SPA, with tables, queries and results showing up on the same page.
Install
Install the latest stable version (v81) by forking this val:
Authentication
Login to your SQLite Explorer with password authentication with your Val Town API Token as the password.
Todos / Plans
- improve error handling
- improve table formatting
- sticky table headers
- add codemirror
- add loading indication to the run button (initial version shipped)
- add ability to favorite queries
- add saving of last query run for a table (started)
- add visible output for non-query statements
- add schema viewing
- add refresh to table list sidebar after
CREATE/DROP/ALTERstatements - add automatic execution of initial select query on double click
- add views to the sidebar
- add triggers to sidebar
- add upload from SQL, CSV and JSON
- add ability to connect to a non-val town Turso database
- fix wonky sidebar separator height problem (thanks to @stevekrouse)
- make result tables scrollable
- add export to CSV, and JSON (CSV and JSON helper functions written in this val. Thanks to @pomdtr for merging the initial version!)
- add listener for cmd+enter to submit query
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
/** @jsxImportSource https://esm.sh/hono@latest/jsx **/
import { modifyFetchHandler } from "https://esm.town/v/andreterron/codeOnValTown?v=50";
import { iframeHandler } from "https://esm.town/v/nbbaier/iframeHandler";
import { resetStyle } from "https://esm.town/v/nbbaier/resetStyle";
import { sqlToCSV, sqlToJSON } from "https://esm.town/v/nbbaier/sqliteExportHelpers";
import {
EditorSection,
MockTable,
Separator,
Table,
TablesList,
} from "https://esm.town/v/nbbaier/sqliteStudioComponents";
import { sqliteStyle } from "https://esm.town/v/nbbaier/sqliteStyle";
import { passwordAuth } from "https://esm.town/v/pomdtr/password_auth?v=70";
import { ResultSet, sqlite } from "https://esm.town/v/std/sqlite";
import { reloadOnSaveFetchMiddleware } from "https://esm.town/v/stevekrouse/reloadOnSave";
import { Hono } from "npm:hono";
import type { FC } from "npm:hono/jsx";
import { jsxRenderer } from "npm:hono/jsx-renderer";
import papa from "npm:papaparse";
const HTML: FC = ({ children }) => {
return (
<html>
<head>
<title>SQLite Explorer</title>
<link rel="preconnect" href="https://fonts.googleapis.com" />
<link rel="preconnect" href="https://fonts.gstatic.com" crossorigin />
<link
href="https://fonts.googleapis.com/css2?family=Fira+Code:wght@300..700&family=Source+Sans+3:ital,wght@0,200..900;1,200..900&display=swap"
rel="stylesheet"
/>
<style
dangerouslySetInnerHTML={{ __html: resetStyle }}
/>
<style
dangerouslySetInnerHTML={{ __html: sqliteStyle }}
/>
<script src="https://unpkg.com/htmx.org@1.9.9/dist/htmx.min.js"></script>
<script src="https://unpkg.com/hyperscript.org@0.9.12"></script>
<script type="module" src="https://esm.town/v/nbbaier/resizeScript" />
<script type="module" src="https://esm.town/v/nbbaier/tableSelectScript" />
<script
type="module"
src="https://raw.esm.sh/code-mirror-web-component@0.0.20/dist/code-mirror.js"
>
</script>
</head>
<body _="
on keydown[event.metaKey and key is 'Enter'] log 'command + enter' then send submitForm to #sql-editor
">
<div class="root-container">
<header>
<h1>SQLite Explorer</h1>
</header>
{children}
</div>
<script type="module" src="https://esm.town/v/nbbaier/downloadScript" />
<script type="module" src="https://esm.town/v/nbbaier/enableButtonsScript" />
<script type="module" src="https://esm.town/v/nbbaier/getCodeScript" />
</body>
</html>
);
};
const app = new Hono();
app.use(
"*",
jsxRenderer(
({ children }) => {
return <HTML children={children} />;
},
{ docType: false },
),
);
app.get("/", async (c) => {
let data = await sqlite.batch(
[
`SELECT name FROM sqlite_schema WHERE type ='table' AND name NOT LIKE 'sqlite_%';`,
`SELECT name FROM sqlite_schema WHERE type ='view' AND name NOT LIKE 'sqlite_%';`,
],
);
let views = data[1].rows.map(view => {
return { type: "view", name: view[0] };
});
let tables = data[0].rows.map(view => {
return { type: "table", name: view[0] };
});
return c.render(
<main class="sidebar-layout">
<div class="sidebar">
<TablesList tables={[...tables, ...views]}></TablesList>
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
import { Statement } from "https://esm.town/v/postpostscript/sqliteBuilder";
import { sqlitePublic } from "https://esm.town/v/postpostscript/sqlitePublic";
const start = performance.now();
console.log(
await sqlitePublic.batch([
Statement`SELECT * FROM authIdExampleComments_comment`,
Statement`DELETE FROM authIdExampleComments_comment`,
Statement`INSERT INTO authIdExampleComments_comment VALUES (${"test"}, ${"ing"}, ${"this"}, 1)`,
Statement`SELECT * FROM authIdExampleComments_comment`,
]),
);
console.log("took", performance.now() - start, "ms");
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 { sqliteBlob } from "https://esm.town/v/postpostscript/sqliteBlob";
import { Statement } from "https://esm.town/v/postpostscript/sqliteBuilder";
import {
defaultPatterns,
sqliteUniverse,
sqliteUniverseWithOptions,
} from "https://esm.town/v/postpostscript/sqliteUniverseBeta";
import { sqlite as sqlitePrivate } from "https://esm.town/v/std/sqlite";
await (async () => {
const start = performance.now();
const result = await Statement`
SELECT t1.*, t2.*
FROM "@postpostscript/sqlitePublic:::authIdExampleComments_comment" t1
JOIN "https://pps-sqlitepublic.web.val.run:::example" t2
LIMIT 1
`.execute({
sqlite: sqliteUniverse,
});
console.log(result);
console.log("took", performance.now() - start, "ms");
})();
await (async () => {
const start = performance.now();
// to query against your private data, use sqliteUniverseWithOptions with the @std/sqlite interface option passed
// use sqliteBlob to query your blob metadata!
const sqlite = sqliteUniverseWithOptions({
interfaces: {
exact: {
"@std/sqlite": sqlitePrivate,
"@std/blob": () => sqliteBlob(),
},
patterns: defaultPatterns,
fallback() {
return sqlitePrivate;
},
},
});
const result = await Statement`
SELECT t1.*, t4.*
FROM "@std/sqlite:::authIdExampleComments_comment" t1
JOIN "@std/blob:::blobs" t4
LIMIT 1
`.execute({ sqlite });
// Statement`
// DELETE FROM "@std/sqlite/use_tracking"
// `,
console.log(result);
console.log("took", performance.now() - start, "ms");
})();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
import { sqliteBackup } from "https://esm.town/v/postpostscript/sqliteBackup";
import { email } from "https://esm.town/v/std/email?v=11";
export async function sqliteBackupWithEmail() {
const { name, content } = await sqliteBackup();
return email({
subject: "SQLite Backup",
attachments: [
{
content: btoa(content),
filename: name.replace(/[^\w\d]+/g, "_").replace(/_+/, "_") + ".json",
type: "json",
disposition: "attachment",
},
],
});
}
export default function() {
return sqliteBackupWithEmail();
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
import { Statement } from "https://esm.town/v/postpostscript/sqliteBuilder";
import { Parser } from "npm:@json2csv/plainjs";
import type { ResultSet } from "npm:@libsql/client";
export const sqlToJSON = (result: ResultSet) => {
const { columns, rows } = result;
return rows.map(row =>
columns.reduce((obj, col, index) => {
obj[col] = row[index];
return obj;
}, {})
);
};
export const sqlToCSV = (result: ResultSet) => {
try {
const { columns, rows } = result;
const records = sqlToJSON(result);
return new Parser({ fields: columns }).parse(records);
} catch (error) {
return error.message;
}
};
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
import { Statement } from "https://esm.town/v/postpostscript/sqliteBuilder";
// this is apparently a dubious solution (https://github.com/microsoft/TypeScript/issues/13298#issuecomment-468375328),
// but this seems to work: https://github.com/microsoft/TypeScript/issues/13298#issuecomment-885980381
type UnionToIntersection<U> = (
U extends never ? never : (arg: U) => never
) extends (arg: infer I) => void ? I
: never;
type UnionToTuple<T> = UnionToIntersection<
T extends never ? never : (t: T) => T
> extends (_: never) => infer W ? [...UnionToTuple<Exclude<T, W>>, W]
: [];
// ---
type EntryTuple<T> = UnionToTuple<
{
[K in keyof T]: [K, T[K]];
}[keyof T]
>;
type KeyTuple<T> = EntryTuple<T> extends infer E ? {
[Index in keyof E]: E[Index] extends [infer K, any] ? K
: never;
}
: never;
type ValueTuple<T> = EntryTuple<T> extends infer E ? {
[Index in keyof E]: E[Index] extends [any, infer V] ? V
: never;
}
: never;
export function StatementTyped<T>(
strings: TemplateStringsArray,
// @ts-ignore
...replacements: ValueTuple<T>
) {
// @ts-ignore
return Statement(strings, ...replacements);
}
sqliteBlob: make sqlite queries against your blobs!*
- does not include blob values
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
import { Statement, type StatementInstance } from "https://esm.town/v/postpostscript/sqliteBuilder";
import { createSqlite } from "https://esm.town/v/postpostscript/sqliteWasm";
import { blob as blobAPI } from "https://esm.town/v/std/blob";
export async function sqliteBlob(options: SqliteBlobOptions = {}) {
const schema = sqliteBlobSchema(options);
const sqlite = createSqlite();
sqlite.batch(await schema);
return sqlite;
}
export async function sqliteBlobSchema(
{ prefix = undefined, table = Statement`blobs`, blob = blobAPI }: SqliteBlobOptions = {},
) {
const blobs = await blob.list(prefix);
return [
Statement`
CREATE TABLE ${table} (
key TEXT PRIMARY KEY,
size INTEGER NOT NULL,
lastModified DATETIME NOT NULL
)
`,
...blobs.map(({ key, size, lastModified }) => {
return Statement`
INSERT INTO ${table} VALUES (
${key},
${size},
${new Date(lastModified).getTime() / 1000}
)
`;
}),
];
}
export type SqliteBlobOptions = {
prefix?: string;
table?: StatementInstance;
blob?: {
list: (...args: any[]) => any;
};
};
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
import { Statement } from "https://esm.town/v/postpostscript/sqliteBuilder";
import { sqlitePublic } from "https://esm.town/v/postpostscript/sqlitePublic";
const start = performance.now();
console.log(
await sqlitePublic.batch([
Statement`SELECT * FROM authIdExampleComments_comment`,
Statement`DELETE FROM authIdExampleComments_comment`,
Statement`INSERT INTO authIdExampleComments_comment VALUES (${"test"}, ${"ing"}, ${"this"}, 1)`,
Statement`SELECT * FROM authIdExampleComments_comment`,
]),
);
console.log("took", performance.now() - start, "ms");
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 {
authMiddlewareCookie,
type HonoEnvOptional,
userActionsDisplay,
} from "https://esm.town/v/postpostscript/authMiddleware";
import { html, type RawHTML } from "https://esm.town/v/postpostscript/html";
import { Layout } from "https://esm.town/v/postpostscript/Layout";
import { MyFooter } from "https://esm.town/v/postpostscript/MyFooter";
import { Profile } from "https://esm.town/v/postpostscript/ProfileImage";
import { Statement } from "https://esm.town/v/postpostscript/sqliteBuilder";
import { email } from "https://esm.town/v/std/email?v=11";
import { sqlite } from "https://esm.town/v/std/sqlite?v=4";
import { type Context, Hono } from "npm:hono";
const app = new Hono<HonoEnvOptional>();
app.use(
"*",
authMiddlewareCookie({
title: "Comments",
optional: true,
verify: {
issuer: undefined,
},
}),
);
app.get("/", (c) => createPage(c));
app.post("/", async (c) => {
if (!c.get("auth")) {
return c.redirect("/login");
}
const comment = await c.req.formData().then(data => data.get("comment")) as string;
await sqlite.batch([
SQL_CREATE_TABLE,
Statement`
INSERT INTO ${SQL_TABLE} VALUES (
${crypto.randomUUID()},
${c.get("auth").sub.replace(/^@/, "").split("/")[0]},
${comment},
${new Date().getTime() / 1000}
)
`,
]);
email({
subject: "New Comment on @postpostscript/authIdExampleComments",
html:
"New comment left on @postpostscript/authIdExampleComments: https://postpostscript-authidexamplecomments.web.val.run",
});
return createPage(c);
});
export default app.fetch;
async function createPage(c: Context<HonoEnvOptional>) {
const auth = c.get("auth");
const [
comments,
footer,
] = await Promise.all([
Statement`
SELECT *
FROM ${SQL_TABLE}
ORDER BY date_added DESC
`.execute({
fallback: () => [],
}),
MyFooter(),
]);
return c.html(Layout`
<header>
${auth && userActionsDisplay(auth)}
</header>
<main>
<h1>Comments</h1>
${auth ? commentForm : html`${signIn} to leave a comment`}
${
comments.map(({ username, comment, date_added }) => {
return html`
<div class="comment">
<span class="profile">
<a href="https://val.town/u/${username}" target="_blank" style="text-decoration: none; margin-right: 0;">
${Profile(username)}
</a>
</span>
<span class="username">
<a href="https://val.town/u/${username}" target="_blank" style="margin-right: 0;">
${username}
</a>
</span>
said at ${new Date(date_added * 1000).toLocaleString()} UTC: ${comment}
</div>
`;
})
}
<br>
</main>
SQLite Explorer (Dev Branch)
View and interact with your Val Town SQLite data. It's based off Steve's excellent SQLite Admin val, adding the ability to run SQLite queries directly in the interface. This new version has a revised UI and that's heavily inspired by LibSQL Studio by invisal. This is now more an SPA, with tables, queries and results showing up on the same page.
Install
Install the latest stable version (v66) by forking this val:
Authentication
Login to your SQLite Explorer with password authentication with your Val Town API Token as the password.
Todos / Plans
- improve error handling
- improve table formatting
- sticky table headers
- add codemirror
- add loading indication to the run button (initial version shipped)
- add ability to favorite queries
- add saving of last query run for a table (started)
- add visible output for non-query statements
- add schema viewing
- add refresh to table list sidebar after
CREATE/DROP/ALTERstatements - add automatic execution of initial select query on double click
- add views to the sidebar
- add triggers to sidebar
- add upload from SQL, CSV and JSON
- add ability to connect to a non-val town Turso database
- fix wonky sidebar separator height problem (thanks to @stevekrouse)
- make result tables scrollable
- add export to CSV, and JSON (CSV and JSON helper functions written in this val. Thanks to @pomdtr for merging the initial version!)
- add listener for cmd+enter to submit query
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
/** @jsxImportSource https://esm.sh/hono@latest/jsx **/
import { modifyFetchHandler } from "https://esm.town/v/andreterron/codeOnValTown?v=50";
import { resetStyle } from "https://esm.town/v/nbbaier/resetStyle";
import { sqlToCSV, sqlToJSON } from "https://esm.town/v/nbbaier/sqliteExportHelpers";
import {
EditorSection,
MockTable,
Separator,
Table,
TablesList,
} from "https://esm.town/v/nbbaier/sqliteStudioComponents_DEV";
import { sqliteStyle } from "https://esm.town/v/nbbaier/sqliteStyle_DEV";
import { passwordAuth } from "https://esm.town/v/pomdtr/password_auth?v=70";
import { Statement } from "https://esm.town/v/postpostscript/sqliteBuilder";
import { ResultSet, sqlite } from "https://esm.town/v/std/sqlite";
import { reloadOnSaveFetchMiddleware } from "https://esm.town/v/stevekrouse/reloadOnSave";
import { Hono } from "npm:hono";
import type { FC } from "npm:hono/jsx";
import { jsxRenderer } from "npm:hono/jsx-renderer";
import papa from "npm:papaparse";
const HTML: FC = ({ children }) => {
return (
<html>
<head>
<title>SQLite Explorer (dev)</title>
<link rel="preconnect" href="https://fonts.googleapis.com" />
<link rel="preconnect" href="https://fonts.gstatic.com" crossorigin />
<link
href="https://fonts.googleapis.com/css2?family=Fira+Code:wght@300..700&family=Source+Sans+3:ital,wght@0,200..900;1,200..900&display=swap"
rel="stylesheet"
/>
<style
dangerouslySetInnerHTML={{ __html: resetStyle }}
/>
<style
dangerouslySetInnerHTML={{ __html: sqliteStyle }}
/>
<script src="https://unpkg.com/htmx.org@1.9.9/dist/htmx.min.js"></script>
<script src="https://unpkg.com/hyperscript.org@0.9.12"></script>
<script type="module" src="https://esm.town/v/nbbaier/resizeScript" />
<script type="module" src="https://esm.town/v/nbbaier/tableSelectScript" />
<script
type="module"
src="https://raw.esm.sh/code-mirror-web-component@0.0.20/dist/code-mirror.js"
>
</script>
</head>
<body _="
on keydown[event.metaKey and key is 'Enter'] log 'command + enter' then send submitForm to #sql-editor
">
<div class="root-container">
<header>
<h1>SQLite Explorer (dev)</h1>
</header>
{children}
</div>
<script type="module" src="https://esm.town/v/nbbaier/downloadScript_DEV" />
<script type="module" src="https://esm.town/v/nbbaier/enableButtonsScript_DEV" />
<script type="module" src="https://esm.town/v/nbbaier/getCodeScript" />
</body>
</html>
);
};
const app = new Hono();
app.use(
"*",
jsxRenderer(
({ children }) => {
return <HTML children={children} />;
},
{ docType: false },
),
);
app.get("/", async (c) => {
let data = await sqlite.batch(
[
`SELECT name FROM sqlite_schema WHERE type ='table' AND name NOT LIKE 'sqlite_%';`,
`SELECT name FROM sqlite_schema WHERE type ='view' AND name NOT LIKE 'sqlite_%';`,
],
);
let views = data[1].rows.map(view => {
return { type: "view", name: view[0] };
});
let tables = data[0].rows.map(view => {
return { type: "table", name: view[0] };
});
return c.render(
<main class="sidebar-layout">
<div class="sidebar">
<TablesList tables={[...tables, ...views]}></TablesList>
</div>
1
2
3
4
import { Statement } from "https://esm.town/v/postpostscript/sqliteBuilder?v=34";
import Table from "npm:easy-table";
console.log(Table.print(await Statement`select * from kv limit 10`.execute()));
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(),
};
}