Overview

Local Query Engine (SQLite) is an OutSystems Forge component designed to facilitate the execution of a wide range of SQL commands on local databases using SQLite and SQL.js. Initially built for DQL operations, the component now allows for comprehensive local database management, including DDL and DML commands. This makes it an essential tool for offline-capable applications, client-side data processing, and rapid development.

Key Features

1. Execute SQL Queries:

   • DQL (Data Query Language): Execute SELECT queries to retrieve data from the local database.
   • DDL (Data Definition Language): Use CREATE, ALTER, and DROP commands to manage database schema, including creating or modifying tables and indexes.
   • DML (Data Manipulation Language): Perform INSERT, UPDATE, and DELETE operations to manipulate data within the local database.

2. Web Block Inclusion:

   • The component includes a web block that contains the essential JavaScript file required for SQL query execution.
   • This web block must be added to any screen where SQL operations will be performed.

3. Client Actions:

   • ExecuteQuery: This client action allows you to execute SQL DQL, DDL, and DML commands on your local SQLite database, providing an easy interface for comprehensive data management.
   • DatabaseToJSONMobile: Converts the local SQLite database data into a JSON object, optimized for mobile applications.
   • DatabaseToJSONWeb: Converts local database data to a JSON object, optimized for web applications.

Getting Started

1. Installation:

   • Install the Local Query Engine (SQLite) component from the OutSystems Forge into your OutSystems application.

2. Web Block Setup:

   • Add the provided Local Query Engine (SQLite) web block to any screen where you intend to perform SQL queries.
   • Ensure this web block is included on all necessary screens to load the required JavaScript files for the component's functionality.

3. Executing SQL Queries:

   • Use the ExecuteQuery client action to run SQL commands on your local SQLite database.
   • The action supports DQL (SELECT), DDL (CREATE, ALTER, DROP), and DML (INSERT, UPDATE, DELETE) commands, returning results or confirming execution.

4. Converting Database to JSON:

   • Use DatabaseToJSONMobile and DatabaseToJSONWeb client actions to convert the local SQLite database data into JSON format.
   • Choose the appropriate action for mobile or web applications to ensure optimal performance and compatibility.
   • Utilize the resulting JSON object for data manipulation, storage, or synchronization with remote servers.

Use Cases

• Comprehensive Local Database Management: Perform all major SQL operations, including table creation, record insertion, and data retrieval, directly on the client side.
• Offline-First Applications: Ensure your application works seamlessly offline by storing and managing data locally.
• Client-Side Data Processing: Conduct data analysis and manipulation without relying on server-side operations, enhancing performance and responsiveness.
• Rapid Prototyping and Testing: Quickly prototype applications with full database capabilities for fast testing and iteration.

Best Practices

• Always include the Local Query Engine (SQLite) web block on any screen where SQL operations are required to ensure the necessary JavaScript files are loaded.
• Use DDL and DML commands carefully to maintain the integrity of your database schema and data.
• Test all SQL commands thoroughly in the local environment to ensure proper functionality, especially for offline scenarios.

Troubleshooting

• SQL Query Execution Errors: Verify that the Local Query Engine (SQLite) web block is present on the screen where SQL queries are performed.
• Database Schema Changes: Use DDL commands with caution to avoid unintentional data loss or application errors.
• Data Conversion Issues: Ensure you're using the correct DatabaseToJSON client action (Mobile or Web) for the respective platform to avoid performance issues.

Conclusion

Local Query Engine (SQLite) extends the capabilities of OutSystems applications by enabling a full range of SQL operations on local databases. With support for DQL, DDL, and DML commands, this component offers powerful tools for managing and manipulating data on the client side. Whether building offline-first applications or conducting client-side data processing, Local Query Engine (SQLite) provides a robust solution for efficient local database management.