SQL Data Builder Documentation

Getting Started

Welcome to SQL Data Builder, a comprehensive database management interface that supports multiple database systems including MySQL, PostgreSQL, Oracle, MongoDB, Redis, and more.

What is SQL Data Builder?

SQL Data Builder is a professional-grade database management tool that provides:

• 15 Specialized Tabs - Each focused on specific database operations
• Multi-Database Support - PostgreSQL, MySQL, MariaDB, Oracle, MS SQL, MongoDB, Redis, and more
• Visual Interface - No SQL knowledge required for basic operations
• Advanced Features - SQL editor, query builder, ER diagrams, data visualization
• Professional Tools - Transaction control, foreign keys, indexes, user management
• Import/Export - Support for CSV, Excel, JSON, and SQL formats

Interface Overview

SQL Data Builder organizes database operations into 15 specialized tabs:

Database Connection

Supported Databases

Currently supported (11 database systems):

• PostgreSQL - Port 5432
• MySQL - Port 3306
• MariaDB - Port 3306
• Oracle Database - Port 1521
• Microsoft SQL Server - Port 1433
• MongoDB - Port 27017
• Redis (Beta) - Port 6379
• Azure SQL - Port 1433
• Amazon Redshift - Port 5439
• Amazon DynamoDB - Port 8000
• SQLite - File-based

Creating a Connection

1. Navigate to SQL Data Builder
2. Click "New Connection" tab
3. Search or browse database types
4. Select your database type
5. Fill in connection details:
   • Connection Name - Friendly name (e.g., "Production DB")
   • Host - Server address (localhost, IP, or domain)
   • Port - Auto-filled with default port
   • Database - Database name
   • Username - Database user
   • Password - User password
6. Click "Test Connection" to verify
7. Click "Save Connection"
8. Go to "Saved Connections" tab
9. Click "Connect" then "Open"

Structure Tab

View and modify table schema with a visual column editor.

Features

• View all columns with data types, nullable status, defaults, and primary keys
• Primary keys marked with "PK" badge and key icon
• Add new columns with full configuration
• Real-time schema updates

Adding a Column

1. Click "Add Column" button
2. Enter column name
3. Select data type from categorized list:
   • Numeric: INT, TINYINT, SMALLINT, BIGINT, DECIMAL, FLOAT, DOUBLE
   • String: VARCHAR (50/100/255/500/1000), CHAR, TEXT, MEDIUMTEXT, LONGTEXT
   • Date/Time: DATE, DATETIME, TIMESTAMP, TIME, YEAR
   • Other: BOOLEAN, ENUM, JSON, BLOB
4. Toggle nullable/not nullable
5. Set default value (optional)
6. Check "Primary Key" if applicable
7. Click "Save"

Data Tab

Browse table data with advanced pagination and sorting.

Features

• Pagination: Navigate large datasets (25/50/100/200 rows per page)
• Sorting: Click column headers to sort (ascending/descending toggle)
• Value Formatting:
  • NULL values - Purple badge
  • TRUE/FALSE - Green/red badges
  • Numbers - Blue color
• Navigation: First page, Previous, Next, Last page buttons
• Row Count Display: Shows "X-Y of Z rows"

Filters Tab

Create complex filter conditions and save filter presets.

Filter Operations

• Contains
• Equals / Not Equals
• Starts With / Ends With
• Greater Than / Less Than
• Is Empty / Is Not Empty

Features

• Active Filters: Add multiple filter conditions with AND logic
• Quick Filter Row: Toggle in-header filters for each column
• Saved Filters: Save filter configurations with custom names (stored in localStorage)
• Filter Management: Load, delete, and manage saved filters
• Apply Filters: Execute button to run all conditions

Code Tab (SQL Editor)

Execute raw SQL queries with syntax support.

Features

• Multi-line SQL editor with Tab key support
• Keyboard Shortcuts: Ctrl+Enter / Cmd+Enter to execute
• Execution time tracking (milliseconds)
• Row count and affected rows display
• Results displayed in dynamic table
• Error messages for failed queries
• Auto-saves to Query History

SQL Formatter Tab

Beautify and format SQL code with customizable settings.

Features

• Dual editor (input/output)
• One-click beautify
• Copy to clipboard
• Use output as input (swap)
• Formatting Settings (LocalStorage persistent):
  • Indent size (spaces)
  • Indent style (spaces/tabs)
  • Keyword case (UPPER, lower, Capitalize)
  • Comma position (before/after)
  • Lines between queries
  • Max column length
  • Align columns/values toggles

Supported SQL Keywords

SELECT, FROM, WHERE, JOIN, ON, AND, OR, ORDER BY, GROUP BY, HAVING, LIMIT, OFFSET, INSERT INTO, VALUES, UPDATE, SET, DELETE, CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE, AS, DISTINCT, UNION, CASE WHEN, IN, EXISTS, BETWEEN, LIKE, NULL operators, and more.

Query Builder Tab

Build SQL queries visually without writing code.

Supported Operations

• SELECT: Query data with column selection, WHERE conditions, ORDER BY, LIMIT
• INSERT: Add data with column-value pairs
• UPDATE: Modify data with SET and WHERE clauses
• DELETE: Remove data with WHERE conditions
• DROP: Delete tables (with confirmation)

SELECT Query Builder

1. Select operation type (SELECT)
2. Choose table from dropdown
3. Select columns (or leave empty for SELECT *)
4. Add WHERE conditions:
   • Choose column
   • Select operator (=, !=, >, <, >=, <=, LIKE)
   • Enter value
5. Add ORDER BY (column and direction)
6. Set LIMIT
7. View live SQL preview
8. Click "Execute Query"

ER Diagram Tab

Visualize your database schema with an interactive entity-relationship diagram.

Features

• Zoom Controls: Zoom in/out, reset to 100%, zoom range 50%-200%
• Draggable Tables: Reposition table cards on canvas
• Table Cards:
  • Table name and row count in header
  • Column list with icons (primary key, foreign key, regular)
  • Column names and data types
  • Scrollable column list (max 256px height)
  • Footer with column count
• Relationship Lines: Auto-detected based on "_id" column naming
  • Dashed SVG lines between related tables
  • Arrow indicators
  • Heuristic FK detection
• Legend: Symbol explanations for primary keys, foreign keys, and relationships

Visuals Tab (Data Visualization)

Create charts and graphs from your table data.

Chart Types

• Bar Chart: Vertical bars with color-coded values (max 20 bars)
• Line Chart: Multi-line support with smooth curves (max 50 points)
• Pie Chart: Percentage-based slices with legend (max 10 slices)
• Area Chart: Filled area under line curves

Creating a Chart

1. Select chart type
2. Choose X-axis column (categories)
3. Select Y-axis columns (values) - supports multiple
4. Set data limit (default: 50 rows)
5. Click "Generate Chart"

Features

• Color palette with 7 distinct colors
• Responsive scaling
• Hover effects and tooltips
• Data point count display
• Multi-series support for line charts

Editor Tab (Table Editor)

Edit table data inline with change tracking and batch commits.

Features

• Inline Editing: Click cells to edit directly
• Change Tracking:
  • Modified cells (orange background)
  • Deleted rows (red background, 50% opacity)
  • New rows (highlighted)
  • Unsaved changes badge with count
• Search: Real-time search with result highlighting and filtering
• Actions:
  • Add Row - Insert new records
  • Delete/Restore - Toggle row deletion
  • Rollback - Discard all changes (with confirmation)
  • Commit - Save all changes to database
• Batch Operations: Generates UPDATE, DELETE, and INSERT queries for all changes
• Auto-reload: Table data refreshes after successful commit

Import/Export Tab

Import data from files or export table data to various formats.

Import Features

• Import Modes:
  • Import to Existing Table - Map CSV/Excel columns to table columns
  • Create New Table - Auto-detect structure from file
• Supported Formats: CSV (.csv), Excel (.xlsx, .xls)
• Upload Methods: Drag & drop or click to browse
• Preview: Shows first N rows before import
• Column Mapping: Map source columns to destination columns (with "Skip" option)
• Auto-Detection: Infers column data types for new tables

Export Features

• Export Formats:
  • CSV - Comma-separated values
  • JSON - JSON array format
  • SQL - SQL INSERT statements
  • Excel (.xlsx) - Microsoft Excel format
• Exports current table data
• Browser download with proper MIME types
• Filename format: tablename.extension

Query History Tab

Track, search, and replay all executed queries.

Features

• History Storage: Stores last 100 queries in localStorage per connection
• Search: Filter queries by text
• Status Filter: All / Successful / Failed queries
• Sort Options: By time or by execution duration
• Statistics: Total query count, success count, failed count

Query Entry Information

• Status indicator (green dot = success, red dot = failed)
• Relative timestamp ("Just now", "5m ago", "2h ago")
• Execution time (for successful queries)
• Row count (for successful queries)
• Full query text in code block
• Error message (for failed queries)
• Result preview (first 5 rows, if available)

Actions

• Edit: Copy query to SQL editor
• Run: Re-execute query immediately
• Delete: Remove from history
• Clear All: Delete all history (with confirmation)

Transactions Tab

Control database transaction behavior with manual commit/rollback.

Auto-commit Mode

• ON (Default): All changes committed immediately
• OFF: Changes held in transaction until manual commit

Features

• Toggle Switch: Enable/disable auto-commit mode
• Status Badge: Green (ON) or Yellow (OFF)
• Warning Banner: Displayed when auto-commit is OFF
• Manual Controls (when OFF):
  • Commit button - Apply all changes
  • Rollback button - Discard all changes
  • Loading states during operations
• Best Practices Guide: When to use manual transactions

Foreign Keys Tab

Create and manage foreign key constraints between tables.

Features

• View all existing foreign keys
• Create new foreign key constraints
• Delete foreign keys with confirmation
• Refresh to reload all foreign keys

Creating a Foreign Key

1. Click "Add Foreign Key"
2. Enter constraint name (e.g., "fk_orders_customer_id")
3. Select source table and column
4. Select referenced table and column
5. Configure cascade options:
   • ON DELETE: NO ACTION, CASCADE, SET NULL, RESTRICT, SET DEFAULT
   • ON UPDATE: NO ACTION, CASCADE, SET NULL, RESTRICT, SET DEFAULT
6. Click "Create"

Cascade Actions Explained

Indexes Tab

Create and manage database indexes for optimal query performance.

Features

• Performance Hints: AI-powered suggestions for missing/duplicate/unused indexes
• Index Creation: Simple, composite, and unique indexes
• Index Types: BTREE, HASH, FULLTEXT, SPATIAL
• Index Management: View all indexes with details (size, cardinality)

Creating an Index

1. Click "Add Index"
2. Enter index name (e.g., "idx_users_email")
3. Select table
4. Select columns (supports multiple for composite indexes)
5. Check "Unique" if needed
6. Select index type:
   • BTREE: Balanced tree, general purpose (default)
   • HASH: Fast exact matches
   • FULLTEXT: Full-text search
   • SPATIAL: Geometric data
7. Click "Create"

Performance Hint Types

• Warning: Missing indexes on frequently queried columns
• Info: Duplicate or unused indexes
• Success: Optimization opportunities

Users & Permissions Tab

Manage database users and grant/revoke permissions.

Features

• View all database users with host information
• Create new database users
• Manage user permissions (GRANT/REVOKE)
• Delete users with confirmation
• Support for PostgreSQL roles and superuser flags

Creating a User

1. Click "Add User"
2. Enter username
3. Enter password
4. Set host (default: "%" for all hosts)
   • % - Any host
   • localhost - Local connections only
   • 192.168.1.% - Specific subnet
   • example.com - Specific domain
5. Click "Create"

Managing Permissions

1. Click "View/Edit Permissions" on a user
2. View current permissions list
3. Grant new permissions:
   • Select privilege (SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, etc.)
   • Select database
   • Select table (optional, for table-level permissions)
   • Click "Grant"
4. Revoke permissions with "Revoke" button

Best Practices

Database Design

• Use consistent naming (snake_case: user_profiles, created_at)
• Always create primary keys (auto-incrementing INT preferred)
• Index foreign key columns for JOIN performance
• Choose appropriate data types (smallest that fits)
• Normalize data to avoid duplication
• Use NOT NULL when data is required
• Add created_at and updated_at timestamps

Performance

• Create indexes on columns used in WHERE, JOIN, ORDER BY
• Avoid SELECT * - query only needed columns
• Use LIMIT for large result sets
• Monitor query execution time in Code tab
• Review performance hints in Indexes tab
• Use ER Diagram tab to understand relationships

Security

• Use strong passwords for database users
• Grant minimum required permissions (Users & Permissions tab)
• Export backups regularly (Import/Export tab)
• Test queries on dev database first
• Review Query History for suspicious activity

Workflow

• Structure Tab: Design schema and add columns
• Data Tab: Browse and verify data
• Filters Tab: Save common filter presets
• Editor Tab: Batch edit with change tracking
• Query Builder: Build queries visually before coding
• Code Tab: Execute complex queries
• SQL Formatter: Beautify before saving queries
• ER Diagram: Visualize schema before changes
• Transactions: Use for critical multi-step operations