SQLite Viewer

A comprehensive web-based SQLite database viewer and manager built with Go and Gin framework. Features a clean, modern interface for browsing tables, executing queries, and managing database schemas.

Features

🗄️ Database Management

• File Upload: Drag & drop or browse to upload SQLite database files
• Manual Path Entry: Enter database file paths directly
• Auto-Upload Handling: Uploaded files are automatically saved with unique timestamps
• localStorage Persistence: Selected database is saved in browser for session continuity
• Database Availability Check: Automatic verification and redirect if database becomes unavailable

📊 Table Operations

• Table Browsing: View all database tables in a clean grid layout
• Schema Display: Complete table schema with column types, constraints, and defaults
• Data Pagination: Paginated table data viewing (20 rows per page)
• Primary Key Detection: Automatic identification and use of primary keys

🔧 Table Creation

• Dynamic Column Management: Add/remove columns during table creation
• Data Type Support: All SQLite types (INTEGER, TEXT, REAL, BLOB, NUMERIC)
• Constraint Configuration: Primary keys, NOT NULL constraints, default values
• Validation: Smart warnings for missing primary keys
• Seamless Redirect: Automatic navigation to newly created tables

📝 Data CRUD Operations

• Create: Insert new rows with form-based input
• Read: View table data with proper column alignment
• Update: Inline editing of existing rows with save/cancel options
• Delete: Row deletion with confirmation dialogs
• Smart Primary Key Handling: Automatic detection and use of table primary keys

🛠️ Schema Editing

• Inline Schema Editing: Edit table schema without leaving the page
• Add Columns: Add new columns with type and constraints
• Edit Columns: Modify column definitions (limited in SQLite)
• Drop Columns: Remove columns from tables
• AJAX Operations: All schema changes happen without page refreshes

🔍 SQL Query Interface

• Custom Queries: Execute any SQL query with results display
• Query Examples: Pre-populated example queries for common operations
• Keyboard Shortcuts: Ctrl+Enter to submit queries quickly
• Result Display: Tabular display of query results with row counts

🎨 User Interface

• Responsive Design: Works on desktop and mobile devices
• Modern CSS: Clean, professional styling with CSS Grid
• Navigation: Consistent navigation across all pages
• Error Handling: Clear error messages and success notifications
• Loading States: Proper feedback during operations

Architecture

Project Structure

sqlite-viewer/
├── main.go          # Application entry point
├── models.go        # Data structures and types
├── db.go           # Database management operations
├── handlers.go     # HTTP request handlers
├── routes.go       # Route configuration
├── templates/      # HTML templates
│   ├── home.html   # Database selection page
│   ├── tables.html # Tables listing with creation
│   ├── table.html  # Table data and schema view
│   └── query.html  # SQL query interface
├── static/         # Static assets
│   └── style.css   # Application styles
└── uploads/        # Uploaded database files

Technology Stack

• Backend: Go with Gin web framework
• Database: modernc.org/sqlite (pure Go SQLite driver)
• Frontend: HTML5, CSS3, JavaScript (ES6+)
• Templates: Go template engine with custom functions
• File Handling: Multipart form uploads with unique naming

Installation

Prerequisites

• Go 1.19 or later
• SQLite database files

Setup

1. Clone or download the project
2. Navigate to the project directory
3. Install dependencies:

   go mod tidy

4. Run the application:

   go run main.go

The application will start on http://localhost:8080

Usage

1. Opening a Database

• File Upload: Click "Browse" to select a SQLite file, or drag & drop onto the drop zone
• Manual Path: Click "Enter Path Manually" to type a database file path
• Session Persistence: Selected database is automatically saved for the session

2. Browsing Tables

• Select a database to view all tables in a grid layout
• Click "View Data" on any table card to see its contents
• Navigate paginated data using the pagination controls

3. Creating Tables

1. Navigate to the Tables page
2. Click "Create New Table" button
3. Enter table name
4. Add columns with types and constraints:
   • Column Name: Descriptive name for the column
   • Type: Data type (INTEGER, TEXT, REAL, BLOB, NUMERIC)
   • PK: Check for primary key
   • NOT NULL: Check for required fields
   • Default: Default value for the column
5. Click "Create Table" to create and navigate to the new table

4. Managing Data

• Insert: Click "Insert New Row" button, fill in the form, and submit
• Edit: Click "Edit" on any row, modify values in-place, and click "Save"
• Delete: Click "Delete" on any row and confirm the action

5. Editing Schema

1. Navigate to a table page
2. Click "Edit Schema" button
3. Edit Column: Click "Edit" on any column row, modify values, and click "Save"
4. Add Column: Click "Add New Column", enter details, and click "Save"
5. Delete Column: Click "Delete" on any non-primary key column and confirm
6. Click "Done Editing" to exit schema edit mode

6. Running Queries

1. Navigate to the Query page
2. Enter SQL queries in the textarea
3. Use Ctrl+Enter to execute quickly
4. View results in tabular format below

API Endpoints

Database Operations

• GET / - Home page (database selection)
• POST /opendb - Open database (file upload or path)
• GET /checkdb - Check database availability

Table Management

• GET /tables - List all tables
• GET /table/:name - View table data and schema
• GET /query - SQL query page
• POST /query - Execute SQL query

Data CRUD

• POST /table/:name/insert - Insert new row
• POST /table/:name/update/:id - Update existing row
• POST /table/:name/delete/:id - Delete row

Schema Management

• POST /table/:name/schema/add - Add column
• POST /table/:name/schema/update/:column - Update column
• POST /table/:name/schema/drop/:column - Drop column

Configuration

Default Settings

• Port: 8080
• Page Size: 20 rows
• Upload Directory: uploads/
• Static Files: static/
• Templates: templates/

Environment Variables

• GIN_MODE: Set to release for production deployment

Security Considerations

• File Upload: Uploaded files are saved with timestamp-based unique names
• SQL Injection: All queries use parameterized statements
• Path Traversal: Database file paths are validated for existence
• CORS: Not configured for cross-origin requests (security by default)

Limitations

SQLite Limitations

• Column Modification: SQLite doesn't support ALTER COLUMN directly - requires table recreation
• DROP COLUMN: Limited support in older SQLite versions
• Foreign Keys: No foreign key constraint management in current version

Application Limitations

• Concurrent Users: Single-user application (no user authentication)
• Database Size: Limited by available memory for large result sets
• File Types: Only SQLite database files (.db, .sqlite, .sqlite3) are supported

Development

Project Structure Explained

• main.go: Application entry point with minimal setup
• models.go: Data structures (ColumnInfo, TableData, QueryResult, DatabaseManager)
• db.go: All database operations and SQLite interactions
• handlers.go: HTTP request handlers for all endpoints
• routes.go: Route configuration and template setup
• templates/: Standalone HTML templates (no inheritance)
• static/style.css: Complete styling for all pages

Adding Features

1. Add new data structures to models.go
2. Implement database operations in db.go
3. Create HTTP handlers in handlers.go
4. Add routes in routes.go
5. Update templates as needed

Testing

go test ./...

Contributing

1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Test thoroughly
5. Submit a pull request

License

This project is open source. Feel free to use, modify, and distribute as needed.

Support

For issues, questions, or feature requests, please check the project documentation or create an issue in the repository.

---

Version: 1.0.0 Last Updated: 2025-10-11 Compatibility: Go 1.19+, Modern Browsers