Online SQL Compiler

Key Features of Tool:

1. Multiple Database Engine Support:
   • MySQL (SQL): A popular open-source relational database system typically used for server-based applications, especially on the web.
   • SQLite (SQL): A lightweight, file-based SQL database engine running in the browser using sql.js.
   • AlaSQL (SQL): Another client-side in-memory SQL database engine.
   • LokiJS (NoSQL): An in-memory JavaScript database for NoSQL operations.
   • Allows switching between engines to see how data and queries behave differently.
2. Data Loading Options:
   • Upload Files: Supports uploading .csv, .xls, and .xlsx Files to create tables/collections from the data.
   • Load Sample Data: A button to quickly load some predefined sample data for testing.
   • Create Table Manually: Provides an interface to define table names and columns with specific data types (TEXT, INTEGER, REAL, DATE) manually.
3. Data Type Configuration:
   • When uploading files, the tool attempts to infer data types for columns and provides an interface to review and change these inferred types before creating tables.
4. Database Management:
   • Create Database Table: Processes the pending uploaded or manually defined data to create tables or collections in the selected database engine.
   • Clear Database: Resets the selected database engine, removing all tables/collections and their data.
5. Database Explorer:
   • Schema Tab: Displays the structure of the current database, listing tables/collections and their columns/fields.
   • Query Suggestions Tab: Provides example queries based on the available tables, tailored to the selected database engine (SQL or NoSQL).
   • Query History Tab: Shows a list of previously executed queries, allowing easy re-use.
6. Query Executor:
   • Query Input Area: A text area to write SQL queries (for MySQL/SQLite/AlaSQL) or JavaScript code (for LokiJS).
   • Run Query Button: Executes the query entered in the input area.
   • Download Results: Buttons to download the query results as a CSV or Excel file.
   • Error Log: Displays any error messages encountered during query execution.
   • Output Area: Shows the results of a successful query, typically in a table format.
7. Persistence (for SQLite):
   • The SQLite database is saved to the browser’s local storage, so the data persists across sessions.
8. User Interface:
   • Organized into distinct panels for configuration, exploration, and execution.
   • Uses basic styling for readability and usability.
   • Includes tooltips for extra information on certain elements.

How to Use:

1. Select Database Engine:
   • In the “Database Configuration” panel, use the “Select Database Engine” dropdown to choose between MySQL, SQLite (SQL), AlaSQL (SQL), or LokiJS (NoSQL).
   • The available features and expected query syntax will change based on the selected engine.
2. Load Data (Choose one method):
   • Upload Data Files: Click the “Upload Data Files” input. Select one or more CSV or Excel files from your computer.
     • After selecting files, a “Configure Column Data Types” section will appear, showing the detected tables and their columns. You can review and change the inferred data types using the dropdowns next to each column name.
     • Click the “Create Database Table” button to load the data from the uploaded files into the selected database engine, creating the necessary tables/collections.
   • Load Sample Data: Click the “Load Sample Data” button. This will automatically create some predefined tables with sample data in the selected engine.
   • Create Table Manually: Click the “Create Table Manually” button.
     • Enter a name for your table in the “Table Name” field.
     • Use the “Add Column” button to add rows for defining columns. For each column, enter a name and select a data type from the dropdown.
     • Use the “Remove” button to remove a column definition row.
     • Click “Create Table” to create the empty table structure in the selected engine. You would then need to insert data using queries. Click “Cancel” to close the manual table creator.
3. Explore the Database:
   • Go to the “Database Explorer” panel.
   • Click the “Schema” tab to see the tables/collections that have been created and their respective columns/fields.
   • Click the “Query Suggestions” tab to see example queries relevant to your tables and the selected engine. Click on a suggestion to load it into the query input area.
   • Click the “Query History” tab to view queries you have executed previously. Click on a history item to load it into the query input area.
4. Run Queries:
   • Go to the “Query Executor” panel.
   • Type your SQL query (for MySQL/SQLite/AlaSQL) or JavaScript code (for LokiJS) in the large text area.
   • Click the “Run Query” button.
   • If the query is successful and returns data, the results will appear in a tble in the “Output” area below the buttons.
   • If there is an error, a red “Error Log” box will appear with details about the error.
5. Export Results:
   • After running a query that produces results, the “Download as CSV” and “Download as Excel” buttons will become active.
   • Click the desired button to download the displayed results in the selected format.
6. Clear the Database:
   • In the “Database Configuration” panel, click the “Clear Database” button to remove all data and tables from the currently selected engine. Use this with caution as it will erase your data.