Query Console

Complete guide to the SQL Query Console and Visual Query Builder in D1 Database Manager.

Overview

D1 Database Manager provides two ways to work with SQL:

Query Console - Direct SQL editor with syntax highlighting
Visual Query Builder - Build SELECT queries visually, then customize

Features:

🔀 Tabbed interface - Query, SQL Diff, Drizzle, and Visual Query Builder
📝 SQL editor with syntax highlighting and SQL formatter (one-click format)
🔮 SQL autocomplete with toggle (preference persisted)
🔧 Visual query builder with editable output and Send to Editor
⚡ Execute queries with keyboard shortcuts
📊 Formatted result display with metrics (time, rows affected)
💾 Save and load queries; 📜 Query history tracking
🗂️ SQL Diff tab for side-by-side comparisons
🗄️ Drizzle ORM console (introspect, migrations, push, export)
📤 Export results to CSV
⚠️ Validation and safety checks

Opening the Query Console

From Database Card

Click the "Query" button on any database card.

From Table View

Click "Query Console" in the navigation when viewing a database.

Tabbed Interface

The Query Console has four tabs:

Tab	Icon	Description
SQL Editor	`</>`	Write and execute raw SQL with full editor features and formatter
Query Builder	`✨`	Build SELECT queries visually with a form-based interface
SQL Diff	`↔`	Compare two SQL queries side-by-side with diff highlighting
Drizzle	`🗄️`	Drizzle ORM console for introspection, migrations, push, and export

Workflow

Start with Builder: Use the Query Builder tab to construct a query visually
Send to Editor: Click "Send to Editor" to transfer the generated SQL
Customize: Make manual edits in the SQL Editor
Execute: Run the query and view results
Compare: Use the Compare tab to see differences between versions

This workflow lets you leverage visual query building while retaining full SQL control.

Editor Utilities

Format: Click the magic-wand Format button to run the SQLite-aware SQL formatter (sql-formatter) on the current query. Invalid SQL is left unchanged.
Autocomplete Toggle: Use the checkbox above the editor to enable/disable SQL suggestions. The preference is persisted in localStorage.

Visual Query Builder

The Visual Query Builder tab provides a point-and-click interface for building SELECT queries.

The generated SQL editor in Query Builder includes all the same advanced features as the SQL Editor tab:

Syntax highlighting with Prism.js
Line numbers and Find/Replace
Autocomplete for keywords, tables, and columns
Error squiggles and real-time validation
Word wrap toggle and hover documentation
Auto-pairing brackets/quotes and smart indentation

Accessing the Builder

Navigate to a database
Click the "Query Builder" tab

Building a Query

┌─────────────────────────────────────────────────────┐
│ Visual Query Builder                                │
│ my-database                                         │
├─────────────────────────────────────────────────────┤
│ Select Table: [users           ▼]                   │
│                                                     │
│ Select Columns:                                     │
│ ☑ All columns (*)  □ id  □ name  □ email           │
│                                                     │
│ WHERE Conditions:              [+ Add Condition]    │
│ ┌─────────────────────────────────────────────────┐│
│ │ [status ▼] [Equals ▼] [active    ] [🗑️]        ││
│ └─────────────────────────────────────────────────┘│
│                                                     │
│ Order By: [created_at ▼]  Direction: [DESC ▼]      │
│ Limit: [100        ]                               │
└─────────────────────────────────────────────────────┘

Generated SQL (Editable)

The SQL generated by the builder is fully editable:

┌─────────────────────────────────────────────────────┐
│ Custom SQL                    [✏️ Edited]           │
│                               [Reset] [Save] [Run]  │
├─────────────────────────────────────────────────────┤
│ SELECT * FROM users                                 │
│ WHERE status = 'active'                             │
│ ORDER BY created_at DESC                            │
│ LIMIT 100;                                          │
│                                                     │
│ ℹ️ You can edit the SQL directly to customize your │
│    query. Click Reset to restore the auto-generated │
│    query.                                           │
└─────────────────────────────────────────────────────┘

Editing Features:

Direct editing - Click in the textarea to modify the SQL
Edit indicator - Title changes to "Custom SQL" with "Edited" badge when modified
Reset button - Appears when edited; click to restore auto-generated SQL
Preserved edits - Your changes are used when saving or executing

Use Cases for Manual Editing:

Add JOINs that the visual builder doesn't support
Add GROUP BY and aggregate functions
Add subqueries or CTEs
Fine-tune complex WHERE conditions
Add SQL features like CASE expressions

Saving Queries

Build or edit your query
Click "Save Query"
Enter a name (must be unique)
Optionally add a description
Click "Save"

Note: If you try to save with a name that already exists, you'll see: "A query with this name already exists. Please choose a different name."

SQL Diff Editor (Compare Tab)

The Compare tab provides a side-by-side diff view to compare two SQL queries.

Comparison Sources

You can compare any combination of:

Source	Description
Current Query	The SQL currently in the SQL Editor tab
Saved Query	Any query from your saved queries list
Custom Text	Paste any SQL directly for comparison

Using the Diff Editor

Click the "Compare" tab in Query Console
Select a source for the Left (Original) side
Select a source for the Right (Modified) side
View the diff with syntax highlighting

Diff Visualization

Green lines = Additions (present in right, not in left)
Red lines = Deletions (present in left, not in right)
Unchanged lines appear on both sides
Line numbers shown for each side
Statistics bar shows count of additions, deletions, and unchanged lines

Actions

Button	Description
Swap ↻	Reverse the left and right sides
Copy	Copy either side to clipboard
Load to Editor	Load either side into the SQL Editor tab

Use Cases

Compare your current query with a saved version
Review changes before saving a query
Compare two different saved queries
Paste and compare SQL from external sources

Drizzle ORM Console

The Drizzle tab provides a console for Drizzle ORM operations on D1 databases.

Accessing the Drizzle Console

Navigate to a database
Click the "Drizzle" tab in the Query Console

Available Commands

Command	Description
Introspect	Pull schema from database and generate Drizzle TypeScript code
Migration Status	View applied Drizzle migrations and history
Generate	Preview migration SQL from schema changes
Push	Push schema changes directly to database (with dry-run option)
Check	Validate schema against current database state

Using Introspect

Select "Introspect" from the command dropdown
Click "Execute"
View the generated Drizzle schema TypeScript code
Use "Copy" to copy to clipboard or "Export" to download as schema.ts

Generated Schema Example:

// Auto-generated Drizzle schema
// Generated by D1 Manager

import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  createdAt: text('created_at').default(sql`CURRENT_TIMESTAMP`)
});

export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;

Using Push

Select "Push" from the command dropdown
Click "Execute" to preview changes
Review the SQL statements in the confirmation dialog
Toggle "Dry run" to test without applying changes
Click "Push Changes" to apply (or "Run Dry Run" to simulate)

Safety Features:

Dry run mode enabled by default
Confirmation dialog shows all SQL statements
Warning banner displayed for non-dry-run execution
Recommend backup before pushing changes

Migration History

View all applied Drizzle migrations:

┌─────────────────────────────────────────┐
│ Migration History                        │
├─────────────────────────────────────────┤
│ #1  abc123def456...                     │
│     Applied: 2024-01-15 10:30:00        │
│                                         │
│ #2  789xyz012abc...                     │
│     Applied: 2024-01-20 14:45:00        │
└─────────────────────────────────────────┘

Output Log

The Drizzle Console includes an output log panel showing:

Command execution timestamps
Success/failure messages
Table counts and operation details
Error details for troubleshooting

Drizzle API Endpoints

Endpoint	Method	Description
`/api/drizzle/:dbId/introspect`	POST	Introspect database schema
`/api/drizzle/:dbId/migrations`	GET	Get migration history
`/api/drizzle/:dbId/push`	POST	Push schema changes
`/api/drizzle/:dbId/generate`	POST	Generate migration preview
`/api/drizzle/:dbId/check`	POST	Check schema against database
`/api/drizzle/:dbId/export`	GET	Export schema as TypeScript file

SQL Autocomplete

The Query Console provides intelligent autocomplete suggestions as you type.

How It Works

Start typing and suggestions appear automatically:

┌─────────────────────────────────────────────────────┐
│ SELECT * FROM us|                                   │
│                 ┌──────────────────────────┐        │
│                 │ 📋 users        Table    │        │
│                 │ 📋 user_roles   Table    │        │
│                 │ 📋 user_logs    Table    │        │
│                 └──────────────────────────┘        │
└─────────────────────────────────────────────────────┘

Context-Aware Suggestions

Context	What You Type	Suggestions
After FROM, JOIN, INTO	`FROM u`	Table names
After SELECT, WHERE, ON	`WHERE n`	Column names from tables in query
After ORDER BY, GROUP BY	`ORDER BY c`	Column names
Dot notation	`users.`	Columns from the specified table
Start of statement	`SEL`	SQL keywords

Compound Keywords

Autocomplete suggests multi-word keywords as single items:

ORDER BY
GROUP BY
LEFT JOIN, RIGHT JOIN, INNER JOIN
UNION ALL

Keyboard Navigation

Key	Action
`↑` `↓`	Navigate suggestions
`Tab` or `Enter`	Accept selected suggestion
`Escape`	Close suggestions popup

Visual Icons

Suggestions are color-coded:

🟣 Purple - SQL keywords (SELECT, FROM, etc.)
🔵 Blue - Table names
🟢 Green - Column names

Editor Enhancements

Syntax Highlighting

The SQL editor provides real-time syntax highlighting powered by Prism.js:

Element	Light Theme	Dark Theme
Keywords (SELECT, FROM, etc.)	Purple	Light Purple
Strings ('value')	Green	Light Green
Numbers	Orange	Light Orange
Comments (-- comment)	Gray Italic	Light Gray Italic
Functions (COUNT, SUM)	Blue	Light Blue
Operators (=, <, >)	Cyan	Light Cyan

Highlighting updates in real-time as you type, with full support for both light and dark themes.

Error Squiggles

The editor shows red wavy underlines at the exact position of syntax errors:

SELECT * FROM users WHERE name =
                               ~~~
                         Error: Comparison operator requires a value

Detected errors include:

Unclosed parentheses (
Unclosed string quotes ' or "
Incomplete statements (SELECT without columns, FROM without table)
Trailing commas
Missing clauses (WHERE without condition, ORDER BY without column)

The squiggle appears on the specific character or word where the error was detected, making it easy to locate and fix issues.

Line Numbers

The editor displays line numbers in a gutter on the left side:

Line numbers automatically update as you add/remove lines
Gutter width adjusts based on the number of digits needed
Scrolling is synchronized between the line numbers and editor content
Styled consistently in both light and dark themes

Word Wrap Toggle

A small toolbar above the editor contains a word wrap toggle button:

Icon	State	Description
🔵 (highlighted)	On	Long lines wrap to the next line (default)
⚪ (dimmed)	Off	Long lines extend horizontally with scrollbar

Click the wrap icon (↩) to toggle between modes. Word wrap is enabled by default for easier reading.

Hover Documentation

Hover over any SQL keyword or function to see inline documentation:

┌────────────────────────────────────┐
│ SELECT                             │
│ Retrieves data from one or more    │
│ tables                             │
│ ┌────────────────────────────────┐ │
│ │ SELECT column1, column2 FROM   │ │
│ │ table                          │ │
│ └────────────────────────────────┘ │
└────────────────────────────────────┘

Documented elements include:

Statements: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER
Clauses: FROM, WHERE, ORDER BY, GROUP BY, HAVING, LIMIT, JOIN
Operators: AND, OR, NOT, IN, BETWEEN, LIKE, IS NULL
Functions: COUNT, SUM, AVG, MAX, MIN, COALESCE, LENGTH, SUBSTR
Date functions: DATE, TIME, DATETIME, STRFTIME
JSON functions: JSON_EXTRACT, JSON_SET, JSON_ARRAY, JSON_OBJECT
Data types: INTEGER, TEXT, REAL, BLOB, NUMERIC

Hover delay is 300ms to avoid flickering while typing.

Find and Replace

Press Ctrl+F (or Cmd+F on Mac) to open the Find/Replace bar:

┌─────────────────────────────────────────────────────┐
│ 🔍 [Find input______] 1 of 5  [Aa] [↑] [↓] [⇄] [✕] │
│ ⇄  [Replace input___]         [Replace] [All]      │
└─────────────────────────────────────────────────────┘

Features:

Search: Type to find all matches, highlighted in the editor
Case Sensitive: Toggle the Aa button for exact case matching
Navigation: Use ↑/↓ buttons or Enter/Shift+Enter to jump between matches
Replace: Replace current match or all matches at once
Match Count: Shows "X of Y" for current position in results

Keyboard Shortcuts:

Key	Action
`Enter`	Go to next match
`Shift+Enter`	Go to previous match
`F3`	Go to next match
`Shift+F3`	Go to previous match
`Escape`	Close Find/Replace bar

Auto-Pairing

The editor automatically pairs brackets and quotes:

( → ()
{ → {}
[ → []
< → <>
' → ''
" → ""

Smart Indentation

Press Enter for automatic indentation:

After opening brackets (, {, [, <
After SQL keywords: SELECT, FROM, WHERE, JOIN, ORDER, GROUP, etc.
Maintains current indentation level

Multi-Line Indent/Unindent

Select multiple lines and:

Press Tab to indent all selected lines
Press Shift+Tab to unindent all selected lines

Smart Backspace

When cursor is between empty pairs like () or [], pressing Backspace deletes both characters.

Editor Interface

┌─────────────────────────────────────────────────────┐
│ Query Console - my-database                         │
├─────────────────────────────────────────────────────┤
│                                                     │
│ 1  SELECT * FROM users                              │
│ 2  WHERE created_at > date('now', '-7 days')        │
│ 3  ORDER BY created_at DESC;                        │
│                                                     │
│ [Execute] [Save Query] [Load] [History] [Export]   │
│ □ Skip validation                                   │
└─────────────────────────────────────────────────────┘

Writing Queries

Basic SELECT

SELECT * FROM users;

SELECT with WHERE

SELECT id, name, email
FROM users
WHERE created_at > date('now', '-30 days');

JOIN Queries

SELECT 
  u.name,
  p.title,
  p.created_at
FROM users u
INNER JOIN posts p ON u.id = p.user_id
ORDER BY p.created_at DESC
LIMIT 10;

Aggregate Functions

SELECT 
  status,
  COUNT(*) as count,
  AVG(total) as avg_total,
  SUM(total) as sum_total
FROM orders
GROUP BY status;

Subqueries

SELECT name, email
FROM users
WHERE id IN (
  SELECT DISTINCT user_id 
  FROM orders 
  WHERE total > 100
);

Executing Queries

Method 1: Keyboard Shortcut (Recommended)

Press Ctrl+Enter (Windows/Linux) or Cmd+Enter (Mac)

Method 2: Execute Button

Click the "Execute" button

Query Results

Table Display

Results appear in a formatted table:

┌──────┬──────────┬──────────────────┬─────────────┐
│ id   │ name     │ email            │ created_at  │
├──────┼──────────┼──────────────────┼─────────────┤
│ 1    │ Alice    │ alice@email.com  │ 2024-11-01  │
│ 2    │ Bob      │ bob@email.com    │ 2024-11-02  │
│ 3    │ Charlie  │ charlie@email.com│ 2024-11-03  │
└──────┴──────────┴──────────────────┴─────────────┘

✓ Query executed successfully
⏱ Duration: 15ms
📊 3 rows returned

Execution Metrics

After each query:

Duration - Query execution time in milliseconds
Rows Returned - Number of rows in result set (SELECT)
Rows Affected - Number of rows modified (INSERT/UPDATE/DELETE)
Changes - Total database changes
Last Row ID - Last inserted ROWID (INSERT)

Error Display

If a query fails:

❌ Query failed: near "SELEC": syntax error

⏱ Duration: 2ms

Common errors:

Syntax errors
Table/column not found
Type mismatches
Constraint violations

Query Types

SELECT Queries

Read data from tables:

-- All columns
SELECT * FROM users;

-- Specific columns
SELECT name, email FROM users;

-- With conditions
SELECT * FROM users WHERE id = 1;

-- With sorting
SELECT * FROM users ORDER BY name ASC;

-- With limit
SELECT * FROM users LIMIT 10;

-- With offset (pagination)
SELECT * FROM users LIMIT 10 OFFSET 20;

INSERT Queries

Add new data:

-- Single row
INSERT INTO users (name, email) 
VALUES ('Alice', 'alice@example.com');

-- Multiple rows
INSERT INTO users (name, email) VALUES
  ('Bob', 'bob@example.com'),
  ('Charlie', 'charlie@example.com');

-- From another table
INSERT INTO users_backup 
SELECT * FROM users WHERE created_at < date('now', '-1 year');

UPDATE Queries

Modify existing data:

-- Update single row
UPDATE users 
SET email = 'newemail@example.com'
WHERE id = 1;

-- Update multiple rows
UPDATE users 
SET status = 'active'
WHERE created_at > date('now', '-30 days');

-- Update with calculation
UPDATE products 
SET price = price * 1.10
WHERE category = 'electronics';

DELETE Queries

Remove data:

-- Delete specific rows
DELETE FROM users 
WHERE id = 1;

-- Delete with condition
DELETE FROM sessions 
WHERE expires_at < datetime('now');

-- Delete all (use with caution!)
DELETE FROM temp_data;

⚠️ Warning: DELETE is permanent. Always backup first!

CREATE Queries

Create new database objects:

-- Create table
CREATE TABLE products (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  price REAL DEFAULT 0.0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Create index
CREATE INDEX idx_products_name ON products(name);

-- Create unique index
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Create view
CREATE VIEW active_users AS
SELECT * FROM users WHERE status = 'active';

DROP Queries

Delete database objects:

-- Drop table
DROP TABLE temp_data;

-- Drop table if exists (safe)
DROP TABLE IF EXISTS temp_data;

-- Drop index
DROP INDEX idx_old_index;

-- Drop view
DROP VIEW old_view;

⚠️ Warning: DROP is permanent and cannot be undone!

ALTER Queries

Modify table structure:

-- Add column
ALTER TABLE users ADD COLUMN phone TEXT;

-- Rename column
ALTER TABLE users RENAME COLUMN name TO full_name;

-- Drop column
ALTER TABLE users DROP COLUMN old_column;

-- Rename table
ALTER TABLE old_name RENAME TO new_name;

Safety Features

Query Validation

D1 Manager validates queries before execution:

Destructive Operations - DROP, DELETE, TRUNCATE
Confirmation Required - For operations that modify data
System Tables - Protection against modifying sqlite_* tables

Skip Validation

For advanced users, check "Skip validation" to bypass warnings:

□ Skip validation (bypass safety checks)

Use with caution! Only skip validation if you understand the consequences.

Protected Tables

These tables cannot be modified via Query Console:

sqlite_master - System catalog
sqlite_sequence - Auto-increment tracking
Any table in d1-manager-metadata database

Saved Queries

Save a Query

Write your query
Click "Save Query"
Enter query details:
- Name - Descriptive name (required)
- Description - Optional notes
Click "Save"

Example:

Name: Active Users Report
Description: Get all users active in last 30 days
Query:
  SELECT name, email, last_login
  FROM users
  WHERE last_login > date('now', '-30 days')
  ORDER BY last_login DESC;

Load a Saved Query

Click "Load" button
Browse saved queries
Click on a query to load it
Edit if needed
Execute

Saved Query List

┌─────────────────────────────────────────┐
│ Saved Queries                           │
├─────────────────────────────────────────┤
│ • Active Users Report                   │
│   Last 30 days of active users          │
│                                         │
│ • Monthly Sales Summary                 │
│   Aggregate sales by month              │
│                                         │
│ • Top Products                          │
│   Best selling products                 │
└─────────────────────────────────────────┘

Edit Saved Queries

Load the query
Modify as needed
Click "Save Query"
Use same name to update

Delete Saved Queries

Open saved queries list
Click delete icon (🗑️) next to query
Confirm deletion

Query History

All executed queries are automatically tracked.

View History

Click "History" to see recent queries:

┌─────────────────────────────────────────────────────┐
│ Query History                                       │
├─────────────────────────────────────────────────────┤
│ 🕐 2024-11-03 10:30 AM                              │
│ SELECT * FROM users WHERE id = 1;                   │
│ ✓ Success • 15ms • 1 row                            │
│ [Copy] [Rerun]                                      │
├─────────────────────────────────────────────────────┤
│ 🕐 2024-11-03 10:25 AM                              │
│ UPDATE users SET email = 'new@example.com' ...      │
│ ✓ Success • 23ms • 1 row affected                   │
│ [Copy] [Rerun]                                      │
├─────────────────────────────────────────────────────┤
│ 🕐 2024-11-03 10:20 AM                              │
│ SELECT COUNT(*) FROM posts;                         │
│ ❌ Failed • syntax error                            │
│ [Copy] [Rerun]                                      │
└─────────────────────────────────────────────────────┘

History Details:

Timestamp - When query was executed
Query - SQL statement
Status - Success or error
Metrics - Duration and row count
Actions - Copy to editor or rerun

Rerun from History

Click "Rerun" on any history entry
Query loads in editor
Press Execute to run again

Clear History

History is stored per-database and persists across sessions.

Export Results

Export query results to CSV format.

Export Single Query

Execute a SELECT query
Click "Export" button
Choose CSV format
File downloads as query-results.csv

CSV Format:

id,name,email,created_at
1,Alice,alice@email.com,2024-11-01
2,Bob,bob@email.com,2024-11-02

Large Result Sets

For large results:

Results are paginated in UI
Export includes all rows (not just visible page)
Consider adding LIMIT clause for very large tables

Advanced Features

Multiple Statements

Execute multiple statements separated by semicolons:

-- Multiple statements in one execution
CREATE TABLE temp_data (id INTEGER, value TEXT);
INSERT INTO temp_data VALUES (1, 'test');
SELECT * FROM temp_data;
DROP TABLE temp_data;

Note: Results shown for last statement only.

Transactions

Use transactions for atomic operations:

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;
-- Or ROLLBACK; to undo changes

PRAGMA Statements

Query database configuration:

-- List all tables and indexes
PRAGMA table_list;

-- Get table info
PRAGMA table_info(users);

-- Get foreign keys
PRAGMA foreign_key_list(users);

-- Check database integrity
PRAGMA integrity_check;

-- Get database statistics
PRAGMA optimize;

Common Table Expressions (CTE)

Use WITH for complex queries:

WITH recent_users AS (
  SELECT * FROM users 
  WHERE created_at > date('now', '-7 days')
)
SELECT 
  r.name,
  COUNT(p.id) as post_count
FROM recent_users r
LEFT JOIN posts p ON r.id = p.user_id
GROUP BY r.id, r.name;

Window Functions

Perform calculations across rows:

SELECT 
  name,
  score,
  RANK() OVER (ORDER BY score DESC) as rank,
  AVG(score) OVER () as avg_score
FROM players
ORDER BY rank;

Best Practices

1. Always Use WHERE with DELETE/UPDATE

-- Bad: Affects all rows
DELETE FROM users;

-- Good: Specific condition
DELETE FROM users WHERE status = 'inactive';

2. Test SELECT Before DELETE/UPDATE

-- First, preview what will be affected
SELECT * FROM users WHERE status = 'inactive';

-- Then execute the modification
DELETE FROM users WHERE status = 'inactive';

3. Use LIMIT for Exploration

-- Explore data with LIMIT
SELECT * FROM large_table LIMIT 10;

-- Not: SELECT * FROM large_table;

4. Use Indexes for Performance

-- Check query plan
EXPLAIN QUERY PLAN 
SELECT * FROM users WHERE email = 'test@example.com';

-- Create index if needed
CREATE INDEX idx_users_email ON users(email);

5. Save Complex Queries

Don't rewrite complex queries - save them for reuse!

6. Use Transactions for Multi-Step Operations

BEGIN TRANSACTION;
-- Multiple operations
COMMIT;

Troubleshooting

"Syntax error near..."

Cause: Invalid SQL syntax

Solution:

Check for typos
Verify table/column names exist
Check SQLite documentation for correct syntax

"No such table"

Cause: Table doesn't exist in database

Solution:

-- List all tables
SELECT name FROM sqlite_master WHERE type='table';

"No such column"

Cause: Column doesn't exist in table

Solution:

-- Check table schema
PRAGMA table_info(table_name);

"Query execution timeout"

Cause: Query taking too long

Solution:

Add LIMIT clause
Create indexes on filtered columns
Optimize query with EXPLAIN QUERY PLAN

"Permission denied"

Cause: Trying to modify protected tables

Solution:

Don't modify system tables (sqlite_*)
Don't modify metadata database tables

Keyboard Shortcuts

Shortcut	Action
`Ctrl+Enter` / `Cmd+Enter`	Execute query
`Ctrl+S` / `Cmd+S`	Save query
`Ctrl+L` / `Cmd+L`	Load saved query
`Ctrl+F` / `Cmd+F`	Open Find/Replace bar
`Tab`	Insert 2 spaces / Accept autocomplete suggestion
`Shift+Tab`	Unindent line or selected lines
`↑` `↓`	Navigate autocomplete / find results
`Esc`	Close autocomplete popup / Find bar / dialogs

API Endpoints

Execute Query

POST /api/query/:dbId/execute
Content-Type: application/json

{
  "query": "SELECT * FROM users LIMIT 10;",
  "skipValidation": false
}

Response:

{
  "results": [
    {"id": 1, "name": "Alice", "email": "alice@example.com"}
  ],
  "meta": {
    "duration": 15,
    "rows_read": 1,
    "rows_written": 0
  },
  "success": true
}

Execute Batch

POST /api/query/:dbId/batch
Content-Type: application/json

{
  "queries": [
    "INSERT INTO users (name) VALUES ('Alice');",
    "INSERT INTO users (name) VALUES ('Bob');"
  ]
}

Get Query History

GET /api/query/:dbId/history

Examples

Create and Populate Table

-- Create table
CREATE TABLE employees (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  department TEXT,
  salary REAL,
  hire_date DATE DEFAULT CURRENT_DATE
);

-- Add index
CREATE INDEX idx_employees_dept ON employees(department);

-- Insert data
INSERT INTO employees (first_name, last_name, department, salary) VALUES
  ('John', 'Doe', 'Engineering', 95000),
  ('Jane', 'Smith', 'Marketing', 85000),
  ('Bob', 'Johnson', 'Engineering', 105000);

-- Query data
SELECT * FROM employees WHERE department = 'Engineering';

Data Analysis

-- Department statistics
SELECT 
  department,
  COUNT(*) as employee_count,
  AVG(salary) as avg_salary,
  MIN(salary) as min_salary,
  MAX(salary) as max_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

Data Cleanup

-- Find duplicates
SELECT email, COUNT(*) as count
FROM users
GROUP BY email
HAVING count > 1;

-- Remove duplicates (keep first)
DELETE FROM users
WHERE rowid NOT IN (
  SELECT MIN(rowid)
  FROM users
  GROUP BY email
);