Local Query Engine (SQLite) lets an OutSystems 11 Mobile or Reactive app run real SQL against its local storage (local entities): SELECT, INSERT, UPDATE, DELETE and REPLACE, plus CREATE, DROP and ALTER on native devices. Version 2.0.0 is a full rewrite of the engine; existing apps keep working without changes.

You call one client action, ExecuteQuery, with the same code everywhere. The component detects where the app runs and picks the right engine:

Use it for queries local aggregates cannot express: sub-queries, GROUP BY ... HAVING, UNION, self-joins, bulk updates, and ad-hoc inspection of local data while debugging.

Installation and setup

Most apps need only steps 1 and 2; the rest is optional.

1. Install Local Query Engine (SQLite) from the Forge into your environment.
2. In your app module, open Manage Dependencies, select LocalQueryEngine and add the public actions you use (at least ExecuteQuery).
3. Optional: drop the LocalQueryEngine block once on your layout. It renders nothing and preloads sql.js in browser/PWA runtimes so the first query is faster. Native apps do not need it.
4. Optional: call Configure once (for example in the app's OnApplicationReady) when you must override the database name or serve sql.js from your own URL. See Best practices.
5. Run the app online at least once. The native database name is fetched from the server on first use and then cached for offline runs.

No extensibility configuration or Cordova plugin is required. Removed the cordova-sqlite-porter plugin; a mobile build that still lists it after the upgrade should be rebuilt.

Quick start

A SELECT returns its rows as a JSON array in JSONString; deserialize it into a list of your own structure.

1. Create a structure that matches the columns you select, for example ProductRow with Name (Text) and Price (Decimal).
2. In a client action, call ExecuteQuery:
   • SQL_Query = "SELECT Name AS Name, Price AS Price FROM LocalProduct WHERE Price > ? ORDER BY Price DESC"
   • Parameters = "[100]"
   • ReadOnly = True
3. Add an If on ExecuteQuery.IsError. On True, show ExecuteQuery.ErrorMessage.
4. On False, use a JSON Deserialize node: JSON String = ExecuteQuery.JSONString, Data Type = List of ProductRow.
5. Bind the deserialized list to your screen, or use ExecuteQuery.RowCount for a quick count.

A write statement works the same way; check RowsAffected instead of reading rows:

UPDATE LocalProduct SET IsModified = 1 WHERE CategoryId = ?

with Parameters = "[3]" returns RowsAffected = the number of products changed. Use AS aliases so the JSON property names match your structure's attribute names (see Supported SQL).

API reference

All public elements are client-side and work in both runtimes.

ExecuteQuery (client action)

Runs one SQL statement against local storage and returns the result as JSON. Errors never raise an exception; they come back in IsError and ErrorMessage.

DatabaseToJSONMobile / DatabaseToJSONWeb (client actions)

Return every user table of the local database as { "PhysicalTableName": [rows] }; system tables (OSSYS_*) are excluded. Both names behave the same in both runtimes; two names are kept for 1.x compatibility. They load all data, so avoid them on very large databases.

Configure (client action)

Optional overrides, stored in client variables so they survive restarts. An empty input keeps the current value.

SortJSONString (client function)

Sorts a JSON array of objects by the value of each object's first property (numbers numerically, text alphabetically) and returns it pretty-printed. Input that is not a JSON array is returned unchanged.

ExecuteQuery_Old (deprecated)

Kept so 1.x consumers keep working. It calls ExecuteQuery with SQL_Query and returns JSONString and IsError. Use ExecuteQuery in new code.

LocalQueryEngine (block, optional)

Renders nothing. Place it once on a layout to preload sql.js in browser/PWA runtimes. Not required: ExecuteQuery loads sql.js on demand.

Supported SQL and runtime differences

The dialect is SQLite in both runtimes, one statement per call. A trailing ; and -- comments are allowed.

Table names. Write entity names (LocalProduct) or physical names (OSUSR_ABC_LOCALPRODUCT). Entity names are matched case-insensitively, using OutSystems metadata (OSSYS_ENTITY) first and the physical-name prefix as a fallback. Names inside string literals and comments are never changed. When two modules have a local entity with the same name, use the physical name.

Column names. SQL is case-insensitive, so First_Name and FIRST_NAME both work. Result rows use the stored column names, usually the UPPERCASE attribute names (FIRST_NAME). Use AS aliases (SELECT First_Name AS FirstName) to control the JSON property names for JSON Deserialize.

Stored values. OutSystems stores Booleans as 1/0 and Identifiers and Long Integers as text. Compare Booleans with = 1. Identifier comparisons such as WHERE StudentsId = 4 work because SQLite converts the number to text.

Inserts. Local entities whose Id mirrors a server entity are not auto-number, so an INSERT must give the Id explicitly. For auto-number local entities the component assigns the next Id the same way OutSystems does.

Error handling

Always check IsError after ExecuteQuery and show ErrorMessage. The action never raises an exception for SQL problems, so your flow continues either way. Messages come from the component or from SQLite:

Best practices and security

• Use Parameters, never concatenation. Put ? in the SQL and the values in Parameters. Concatenating user input into SQL_Query allows SQL injection, which here can delete local data.
• Turn on ReadOnly for user-typed SQL. Search boxes, report builders and debug consoles should call ExecuteQuery with ReadOnly = True.
• Prefer aggregates for simple reads. Use ExecuteQuery for what aggregates cannot express: sub-queries, HAVING, UNION, self-joins, bulk updates.
• Alias every column you deserialize. SELECT S.First_Name AS FirstName ... keeps JSON names stable and matching your structure.
• Offline PWAs and strict CSP: host sql-wasm.js and sql-wasm.wasm (sql.js 1.8.0) as resources in your app and call Configure with SqlJsBaseUrl pointing at them. Otherwise the first browser query needs the cdnjs CDN.
• Writes bypass entity actions. INSERT/UPDATE/DELETE change local storage directly and do not run your offline-sync logic. Set your sync flags (for example IsModified = 1) in the statement yourself.
• Refresh after writes. In the browser, screens that already loaded a local aggregate do not see changes made through SQL until you refresh that aggregate.
• Performance: in the browser only the tables named in the query are loaded on every call. Keep queries narrow, avoid DatabaseToJSON* on large databases, and use DurationMs to measure. On native devices queries run at SQLite speed.

Limitations and upgrade notes

Known limitations

• One statement per call; no explicit transactions (BEGIN/COMMIT).
• DDL (CREATE, DROP, ALTER) runs on native devices only. Dropping or altering an OutSystems local entity's table can break the app; use it with care.
• In the browser each call loads the referenced tables into memory, so very large tables are slower than on a device.
• The native mobile path has been verified in code and design but not yet on a physical device build. Please report results in the Forge discussion.
• The browser path relies on how OutSystems 11 stores local entities in IndexedDB (verified on a Personal Environment, November 2026).

Upgrading from 1.x

All public actions keep their signatures; new inputs are optional, so existing calls compile after Refresh Dependencies. Review these behaviour changes:

FAQ and troubleshooting

Does the same code run on mobile and web? Yes. Your app calls the same actions with the same inputs and gets the same outputs; only DDL differs (native only).

My query returns no rows, but the aggregate shows data. Check the filter values against stored formats: Booleans are 1/0, Identifiers are text. Also confirm the entity belongs to the running app.

JSON Deserialize leaves my structure empty. The JSON property names do not match the attribute names. Alias the columns with AS to the exact attribute names.

Changes made with UPDATE do not appear on screen. Refresh the screen aggregate after the call; it does not watch local storage for direct writes.

Do SQL writes sync to the server? Only through your own sync logic. Set the flags your sync reads (for example IsModified = 1) in the statement.

Does it work offline? On native devices yes, after the database name has been resolved once online. In the browser yes once sql.js is cached, or always when you serve sql.js yourself through Configure.

Can end users type SQL? Only with ReadOnly = True and with values bound through Parameters.

Where do I report a problem? In the Forge discussion of Local Query Engine (SQLite). Include the runtime (device or browser), the SQL and the ErrorMessage.