Live Web - Database

User Guide: How to Use the Database in Your Live Web Editor

1. How to Open the Database

• Left sidebar: list of your tables
• Right area: table data + management buttons

2. Creating Your First Table

3. Adding Columns

4. Adding Rows (Data)

5. Editing & Deleting

• Delete a row: Click the trash icon on the right of any row.
• Delete a column: Click the small trash icon in the column header.
• Delete entire table: Click the big red trash icon next to the table name (Warning: irreversible!).

6. Importing from SQL (Super Useful!)

• Users table
• Products table
• Blog posts + comments

7. Using the Database in Your Web Page (The Magic Part)

const data = await WebDB.select('tableName');

await WebDB.insert('tableName', { name: "John", age: 25 });

let users = await WebDB.select('users');
users = users.map(u => {
    if (u.id === 1) u.name = "New Name";
    return u;
});
// Save the updated list back to the server
await WebDB._setRaw('users', { columns: ['id','name','email','updated_at'], rows: users });

8. NEW: Handling File Uploads from Your Live Site

1. The user selects a file using an HTML <input type="file">.
2. You pass that input to WebDB.uploadFile(inputElement).
3. It securely uploads the file to your project's user_assets/ folder and returns the file path.
4. You save that file path into your database using WebDB.insert().

9. Tips & Important Notes

• Auto-saving: Data is saved securely to the server's disk every time you add/edit/delete.
• Instant Updates: Works in the live preview immediately.
• File Security: Uploaded files are automatically renamed to prevent overwrites, and strict file-type checking keeps your site safe.
• No Complex Queries: You cannot run complex SQL queries like SELECT * WHERE age > 18. Instead, just do it in JavaScript: users.filter(u => u.age > 18).
• Survives Refreshes: Tables and data are permanent and survive even if you close the browser or clear your cache.

10. OPTIONAL: Realtime Updates

Simple Realtime Example:

let lastCount = 0;
 
 async function startRealtime() {
     checkUpdates();
     setInterval(checkUpdates, 5000);
 }
 
 async function checkUpdates() {
     const data = await WebDB.select('posts');
     const count = data ? data.length : 0;
 
     if (count !== lastCount) {
         lastCount = count;
         renderPosts(data);
     }
 }
 
 startRealtime();

Better Version:

let lastUpdated = 0;
 
 async function checkUpdates() {
     const data = await WebDB.select('posts');
 
     if (!data || data.length === 0) return;
 
     const latest = Math.max(...data.map(d => new Date(d.updated_at).getTime()));
 
     if (latest !== lastUpdated) {
         lastUpdated = latest;
         renderPosts(data);
     }
 }

Quick Start Template:

<div id="feed">Loading...</div>
 
 <script>
 let last = 0;
 
 async function render() {
     const posts = await WebDB.select('posts');
     document.getElementById('feed').innerHTML =
         posts.map(p => `<p>${p.text}</p>`).join('');
 }
 
 async function sync() {
     const posts = await WebDB.select('posts');
     if (!posts) return;
 
     if (posts.length !== last) {
         last = posts.length;
         render();
     }
 }
 
 setInterval(sync, 5000);
 render();
 </script>

Quick Start Template 1: User List

<h2>Our Users</h2>
<div id="user-list">Loading...</div>

<script>
async function showUsers() {
    const users = await WebDB.select('users');
    if (!users || users.length === 0) {
        document.getElementById('user-list').innerHTML = "<p>No users found.</p>";
        return;
    }
    document.getElementById('user-list').innerHTML = 
        users.map(u => `<p><b>${u.username}</b> - ${u.email}</p>`).join('');
}
showUsers();
</script>

Quick Start Template 2: Image Upload Form & Gallery

<h2>Public Photo Gallery</h2>

<!-- The Upload Form -->
<input type="file" id="photoInput" accept="image/*">
<button onclick="uploadPhoto()" id="uploadBtn">Upload Photo</button>
<p id="statusMsg"></p>

<!-- The Gallery Display -->
<div id="gallery-grid" style="display: flex; gap: 10px; margin-top: 20px;"></div>

<script>
async function uploadPhoto() {
    const fileInput = document.getElementById('photoInput');
    const statusMsg = document.getElementById('statusMsg');
    const btn = document.getElementById('uploadBtn');

    if (fileInput.files.length === 0) {
        alert("Please select a file first!");
        return;
    }

    btn.innerText = "Uploading...";
    btn.disabled = true;

    // 1. Upload the file to the server
    const savedPath = await WebDB.uploadFile(fileInput);

    if (savedPath) {
        // 2. Save the path to the 'gallery' table
        await WebDB.insert('gallery', { image_path: savedPath });
        
        statusMsg.innerText = "Upload successful!";
        fileInput.value = ''; // Clear the input
        loadGallery(); // Refresh the images
    } else {
        statusMsg.innerText = "Upload failed. (Is your project set to Public?)";
    }

    btn.innerText = "Upload Photo";
    btn.disabled = false;
}

async function loadGallery() {
    const photos = await WebDB.select('gallery');
    const grid = document.getElementById('gallery-grid');
    
    if (!photos || photos.length === 0) {
        grid.innerHTML = "<p>No photos yet.</p>";
        return;
    }

    // 3. Display the images using the saved paths!
    grid.innerHTML = photos.map(photo => 
        `<img src="${photo.image_path}" style="width: 150px; height: 150px; object-fit: cover; border-radius: 8px;">`
    ).join('');
}

// Load gallery when page opens
loadGallery();
</script>