// This val creates a SQLite dashboard admin panel with a sidebar for table names // It uses React for the frontend and the Val Town SQLite API for database operations // Now includes functionality to edit rows, using rowid or all columns as identifiers // and the ability to add new rows to tables // Column types are displayed next to column names in the UI /** @jsxImportSource https://esm.sh/react */ import React, { useEffect, useState } from "https://esm.sh/react"; import { createRoot } from "https://esm.sh/react-dom/client"; import { vtTokenSessionAuth } from "https://esm.town/v/stevekrouse/vtTokenSessionAuthSafe"; function App() { const [tables, setTables] = useState([]); const [selectedTable, setSelectedTable] = useState(null); const [tableData, setTableData] = useState([]); const [tableSchema, setTableSchema] = useState([]); const [error, setError] = useState(null); const [editingRow, setEditingRow] = useState(null); const [newRow, setNewRow] = useState({}); useEffect(() => { fetchTables(); }, []); const fetchTables = async () => { try { const response = await fetch("/tables"); const data = await response.json(); setTables(data); } catch (err) { setError("Failed to fetch tables"); } }; const fetchTableData = async (tableName) => { try { const dataResponse = await fetch(`/table/${tableName}`); const data = await dataResponse.json(); setTableData(data); const schemaResponse = await fetch(`/schema/${tableName}`); const schema = await schemaResponse.json(); setTableSchema(schema); setSelectedTable(tableName); // Initialize newRow with empty values for each column const emptyRow = schema.reduce((acc, col) => { acc[col.name] = ""; return acc; }, {}); setNewRow(emptyRow); } catch (err) { setError(`Failed to fetch data for table ${tableName}`); } }; const handleEdit = (index) => { setEditingRow(index); }; const handleSave = async (index) => { try { const response = await fetch(`/update/${selectedTable}`, { method: "POST", headers: { "Content-Type": "application/json", }, body: JSON.stringify(tableData[index]), }); if (!response.ok) { throw new Error("Failed to update row"); } setEditingRow(null); await fetchTableData(selectedTable); } catch (err) { setError(`Failed to update row: ${err.message}`); } }; const handleInputChange = (index, key, value) => { const newData = [...tableData]; newData[index][key] = value; setTableData(newData); }; const handleNewRowInputChange = (key, value) => { setNewRow(prev => ({ ...prev, [key]: value })); }; const handleAddRow = async () => { try { const response = await fetch(`/insert/${selectedTable}`, { method: "POST", headers: { "Content-Type": "application/json", }, body: JSON.stringify(newRow), }); if (!response.ok) { throw new Error("Failed to add new row"); } await fetchTableData(selectedTable); // Reset newRow to empty values const emptyRow = Object.keys(newRow).reduce((acc, key) => { acc[key] = ""; return acc; }, {}); setNewRow(emptyRow); } catch (err) { setError(`Failed to add new row: ${err.message}`); } }; return (

Tables

fetchTableData(table)} className={selectedTable === table ? "active" : ""} > {table}

SQLite Dashboard

{error &&

{error}

} {selectedTable && (

{selectedTable}

{tableSchema.map(col => ( ))} {tableData.map((row, index) => ( {tableSchema.map(col => ( ))} ))} {tableSchema.map(col => ( ))}

{col.name} ({col.type})	Actions
{editingRow === index ? ( handleInputChange(index, col.name, e.target.value)} /> ) : ( row[col.name] )}	{editingRow === index ? : }
handleNewRowInputChange(col.name, e.target.value)} placeholder={`New ${col.name}`} />

)}

View Source

); } function client() { createRoot(document.getElementById("root")).render(); } if (typeof document !== "undefined") { client(); } async function server(request: Request): Promise { const { sqlite } = await import("https://esm.town/v/stevekrouse/sqlite"); const url = new URL(request.url); if (url.pathname === "/tables") { const result = await sqlite.execute("SELECT name FROM sqlite_master WHERE type='table'"); return new Response(JSON.stringify(result.rows.map(row => row.name)), { headers: { "Content-Type": "application/json" }, }); } if (url.pathname.startsWith("/table/")) { const tableName = url.pathname.split("/").pop(); const result = await sqlite.execute(`SELECT rowid, * FROM ${tableName}`); return new Response(JSON.stringify(result.rows), { headers: { "Content-Type": "application/json" }, }); } if (url.pathname.startsWith("/schema/")) { const tableName = url.pathname.split("/").pop(); const result = await sqlite.execute(`PRAGMA table_info(${tableName})`); return new Response(JSON.stringify(result.rows.map(row => ({ name: row.name, type: row.type }))), { headers: { "Content-Type": "application/json" }, }); } if (url.pathname.startsWith("/update/")) { if (request.method !== "POST") { return new Response("Method Not Allowed", { status: 405 }); } const tableName = url.pathname.split("/").pop(); const data = await request.json(); const columns = Object.keys(data).filter(col => col !== "rowid"); const values = columns.map(col => data[col]); const setClause = columns.map(col => `${col} = ?`).join(", "); let query; let queryParams; if ("rowid" in data) { query = `UPDATE ${tableName} SET ${setClause} WHERE rowid = ?`; queryParams = [...values, data.rowid]; } else { const whereClause = columns.map(col => `${col} = ?`).join(" AND "); query = `UPDATE ${tableName} SET ${setClause} WHERE ${whereClause}`; queryParams = [...values, ...values]; } await sqlite.execute(query, queryParams); return new Response("Updated successfully", { status: 200 }); } if (url.pathname.startsWith("/insert/")) { if (request.method !== "POST") { return new Response("Method Not Allowed", { status: 405 }); } const tableName = url.pathname.split("/").pop(); const data = await request.json(); const columns = Object.keys(data).filter(col => col !== "rowid"); const values = columns.map(col => data[col]); const placeholders = columns.map(() => "?").join(", "); const query = `INSERT INTO ${tableName} (${columns.join(", ")}) VALUES (${placeholders})`; await sqlite.execute(query, values); return new Response("Inserted successfully", { status: 200 }); } return new Response( ` SQLite Dashboard

`, { headers: { "content-type": "text/html", }, }, ); } const css = ` body { font-family: Arial, sans-serif; margin: 0; padding: 0; background-color: #f0f0f0; } .container { display: flex; height: 100vh; } .sidebar { width: 200px; background-color: #333; color: white; padding: 20px; overflow-y: auto; } .sidebar h2 { margin-top: 0; } .sidebar ul { list-style-type: none; padding: 0; } .sidebar li { cursor: pointer; padding: 10px; margin-bottom: 5px; border-radius: 3px; } .sidebar li:hover { background-color: #444; } .sidebar li.active { background-color: #555; } .main-content { flex-grow: 1; padding: 20px; overflow-y: auto; } h1, h2 { color: #333; } table { width: 100%; border-collapse: collapse; margin-top: 20px; } th, td { border: 1px solid #ddd; padding: 8px; text-align: left; } th { background-color: #f2f2f2; } .column-type { font-size: 0.8em; color: #666; } .error { color: red; margin-bottom: 10px; } .source-link { margin-top: 20px; text-align: right; } .source-link a { color: #666; text-decoration: none; } .source-link a:hover { text-decoration: underline; } button { background-color: #4CAF50; border: none; color: white; padding: 5px 10px; text-align: center; text-decoration: none; display: inline-block; font-size: 12px; margin: 2px 2px; cursor: pointer; } input { width: 100%; padding: 5px; box-sizing: border-box; } `; export default vtTokenSessionAuth(server);

sqliteAdminDashboard