1. SQLite databases in ACF

SQLite support gives an ACF function a local, file-based relational database without requiring an ODBC driver or a separate database server. It is useful for caches, configuration data, queues, interchange between host applications, and other data that should survive after an ACF function has finished.

SQLite databases in ACF

The most important functions are:

string result = OpenSQLite(string databasePath);
string result = CreateSQLiteDatabase(string databasePath, JSON schema);
string result = CloseSQLite();
string result = ExecuteSQLite(string sql {, string rowSeparator {, string columnSeparator}});
JSON schema = GetSQLiteSchema();
JSON report = UpdateSQLiteSchema(JSON schema);

SQLite is part of the common ACF runtime. The same compiled ACF package can therefore contain shared data-model and helper functions while exposing different entry points for FileMaker, InDesign, or 4D.

1.1. Connection lifetime ↑

An ACF runtime can have one SQLite database open at a time. The connection belongs to the current ACF execution, not to the host application or compiled package.

This means that a function should normally open or create the database, perform its work, and close it during the same call:

function FindCompany(string databasePath, string companyName)

    string result = OpenSQLite(databasePath);
    JSON companies;

    result = ExecuteSQLite(
        "SELECT Company_ID, Name, Address
         FROM Company
         WHERE Name = :companyName
         INTO :companies"
    );

    result = CloseSQLite();
    return companies;
end

Calling OpenSQLite in one FileMaker calculation and ExecuteSQLite in a later calculation does not preserve the connection. These are separate ACF runtime executions.

If an exception leaves the function before CloseSQLite is reached, the runtime destructor closes the database automatically. Calling CloseSQLite explicitly is still recommended because it makes the intended lifetime clear and releases the file as soon as the work is complete.

1.2. Creating a database ↑

CreateSQLiteDatabase creates a new database file, applies its JSON schema, and leaves the new database open for the current runtime.

JSON schema = JSON(
    "version", 1,
    "tables", JSONarray(
        JSON(
            "name", "Company",
            "columns", JSONarray(
                JSON(
                    "name", "Company_ID",
                    "type", "INTEGER",
                    "primaryKey", true,
                    "autoIncrement", true
                ),
                JSON("name", "Name", "type", "TEXT", "notNull", true),
                JSON("name", "Address", "type", "TEXT")
            ),
            "indexes", JSONarray(
                JSON(
                    "name", "Company_Name_idx",
                    "columns", JSONarray("Name")
                )
            )
        )
    )
);

string result = CreateSQLiteDatabase(databasePath, schema);

The parent directory must already exist. The operation fails if the database file already exists. If applying the initial schema fails, the incomplete database is closed and removed.

1.2.1. Schema object ↑

The root schema object contains:

Field	Required	Type	Meaning
`version`	No	integer	Non-negative application schema version stored in SQLite `PRAGMA user_version`.
`tables`	Yes	array	Tables to create or inspect.

A table object contains:

Field	Required	Type	Meaning
`name`	Yes	string	Table name.
`columns`	Yes	array	One or more column definitions.
`indexes`	No	array	Indexes belonging to the table.

A column object supports:

Field	Required	Type	Meaning
`name`	Yes	string	Column name.
`type`	No	string	SQLite type declaration. Defaults to `TEXT`.
`primaryKey`	No	boolean	Make the column a primary key.
`autoIncrement`	No	boolean	Add `AUTOINCREMENT`. Requires an `INTEGER` primary-key column.
`notNull`	No	boolean	Add `NOT NULL`.
`unique`	No	boolean	Add a unique constraint when the table is created.
`default`	No	string, number, boolean, or null	Literal default value. Strings are quoted safely.
`defaultExpression`	No	string	SQL expression used as the default, such as `CURRENT_TIMESTAMP`.

An index object contains:

Field	Required	Type	Meaning
`name`	Yes	string	Index name.
`columns`	Yes	array	Ordered column names in the index.
`unique`	No	boolean	Create a unique index. Defaults to `false`.

1.3. Opening and closing an existing database ↑

OpenSQLite opens an existing database for reading and writing:

string result = OpenSQLite(databasePath);

It returns "OK". It throws an exception if the file does not exist, cannot be opened, or another SQLite database is already open in the current runtime.

CloseSQLite closes the current database:

string result = CloseSQLite();

It returns "OK". Calling it when no database is open is harmless.

1.4. Executing SQL ↑

ExecuteSQLite accepts one SQLite statement per call and supports SELECT, INSERT, UPDATE, DELETE, SQLite pragmas, and other statements supported by the linked SQLite library.

string result = ExecuteSQLite(string sql);

For a statement without result columns, the return value is "OK":

string companyName = "Horneks Software AS";
string companyAddress = "Teknologiveien 7, 7042 Trondheim";

string result = ExecuteSQLite(
    "INSERT INTO Company (Name, Address)
     VALUES (:companyName, :companyAddress)"
);

For a query without an INTO clause, the function returns text. Rows are separated by carriage returns and columns by tabs by default:

string rows = ExecuteSQLite(
    "SELECT Name, Address FROM Company ORDER BY Name"
);

Custom separators use the same order as the current ACF ExecuteSQL implementation: row separator, then column separator.

string csv = ExecuteSQLite(
    "SELECT Name, Address FROM Company ORDER BY Name",
    "\n",
    ","
);

1.4.1. Named placeholders ↑

Use named ACF variables as SQL parameters. A placeholder begins with :, @, or $; the name after the prefix must match a variable visible in the current ACF function.

long companyID = 42;
string newAddress = "Storgata 10, Oslo";

string result = ExecuteSQLite(
    "UPDATE Company
     SET Address = :newAddress
     WHERE Company_ID = :companyID"
);

Placeholders are bound through SQLite rather than substituted into the SQL text. This handles quoting safely and should be preferred over constructing SQL by concatenating values.

Supported ACF placeholder types are:

NOTDEFINED, bound as SQL NULL
BOOL, INTEGER, and LONG
FLOAT
STRING, stored as UTF-8 text
DATE, TIME, and TIMESTAMP

An empty ACF date is bound as NULL. Other date/time values use ISO-style text representations.

Positional ? parameters are not supported. JSON, XML, arrays, and containers must be converted to a supported value before binding.

1.4.2. Returning rows with INTO ↑

Like ExecuteSQL and ExecuteMySQL, ExecuteSQLite supports the ACF INTO extension.

To populate parallel arrays, list one declared array for each selected column:

array string companyNames;
array string companyAddresses;

string result = ExecuteSQLite(
    "SELECT Name, Address
     FROM Company
     ORDER BY Name
     INTO :companyNames, :companyAddresses"
);

To receive structured data, use one declared JSON variable:

JSON companies;

string result = ExecuteSQLite(
    "SELECT Company_ID AS id, Name, Address
     FROM Company
     ORDER BY Name
     INTO :companies"
);

The JSON variable receives:

{
  "success": true,
  "rows_found": 2,
  "result": [
    {
      "id": "1",
      "Name": "Horneks Software AS",
      "Address": "Teknologiveien 7, 7042 Trondheim"
    },
    {
      "id": "2",
      "Name": "Société Démonstration",
      "Address": "18 Rue de l'Été, Paris"
    }
  ]
}

Column aliases become the JSON keys. SQLite result values are represented as strings; SQL NULL becomes an empty string.

ExecuteSQLite itself still returns "OK" when an INTO clause is used. The selected data is written into the variables named by INTO.

1.5. Inspecting the schema ↑

GetSQLiteSchema reads the current database and returns its tables, columns, application version, and explicitly created indexes:

JSON actualSchema = GetSQLiteSchema();

A typical result is:

{
  "version": 1,
  "tables": [
    {
      "name": "Company",
      "columns": [
        {
          "name": "Company_ID",
          "type": "INTEGER",
          "notNull": false,
          "primaryKey": true
        },
        {
          "name": "Name",
          "type": "TEXT",
          "notNull": true,
          "primaryKey": false
        }
      ],
      "indexes": [
        {
          "name": "Company_Name_idx",
          "columns": ["Name"],
          "unique": false
        }
      ]
    }
  ]
}

SQLite internal tables and automatically generated indexes are omitted.

1.6. Updating the schema ↑

UpdateSQLiteSchema applies an additive schema definition to the open database inside a savepoint:

JSON report = UpdateSQLiteSchema(newSchema);

It can:

Create missing tables.
Add missing columns to existing tables.
Create missing indexes.
Update PRAGMA user_version from the schema's version field.

It does not remove or rename tables, columns, or indexes, and it does not change existing column definitions. SQLite cannot add a primary-key, autoincrement, or unique column constraint to an existing table through this additive operation.

The returned report has this shape:

{
  "success": true,
  "changed": true,
  "actions": [
    "Added column Company.Email",
    "Created index Company_Email_idx"
  ],
  "warnings": []
}

If any schema operation fails, the savepoint is rolled back and the function throws an exception.

1.7. Complete CRUD example ↑

This compact example creates a database, inserts two companies, updates one, deletes one, and returns the remaining rows:

function SQLiteCompanyDemo(string databasePath)

    JSON schema = JSON(
        "version", 1,
        "tables", JSONarray(
            JSON(
                "name", "Company",
                "columns", JSONarray(
                    JSON("name", "Name", "type", "TEXT", "notNull", true),
                    JSON("name", "Address", "type", "TEXT")
                )
            )
        )
    );

    string result = CreateSQLiteDatabase(databasePath, schema);

    string companyName = "Horneks Utvikling AS";
    string companyAddress = "Blåbærveien 12, 7010 Trondheim";
    result = ExecuteSQLite(
        "INSERT INTO Company (Name, Address)
         VALUES (:companyName, :companyAddress)"
    );

    companyName = "Société Démonstration";
    companyAddress = "18 Rue de l'Été, Paris";
    result = ExecuteSQLite(
        "INSERT INTO Company (Name, Address)
         VALUES (:companyName, :companyAddress)"
    );

    string oldName = "Horneks Utvikling AS";
    string newName = "Horneks Software AS";
    string newAddress = "Teknologiveien 7, 7042 Trondheim";
    result = ExecuteSQLite(
        "UPDATE Company
         SET Name = :newName, Address = :newAddress
         WHERE Name = :oldName"
    );

    companyName = "Société Démonstration";
    result = ExecuteSQLite(
        "DELETE FROM Company WHERE Name = :companyName"
    );

    JSON companies;
    result = ExecuteSQLite(
        "SELECT Name, Address
         FROM Company
         ORDER BY Name
         INTO :companies"
    );

    result = CloseSQLite();
    return companies;
end

The complete executable test package is available in dsCodeCompiler/ACF/sqlite_tests.acf.

1.8. Error handling and practical rules ↑

Use a complete database file path and create its parent directory before creating the database.
Use one database per ACF execution. Close it before opening another.
Prefer named placeholders for all data values.
Use INTO :jsonVariable for structured query results.
Use explicit transactions when several data changes must succeed or fail together.
Do not pass several SQL statements in one ExecuteSQLite call.
Treat UpdateSQLiteSchema as an additive migration tool, not as a general schema-diff engine.
Keep shared SQLite helper functions host-independent. Use isFileMaker, isIndesign, and is4D only around behavior that genuinely depends on the host.

SQLite errors and invalid schema definitions throw ACF runtime exceptions with the underlying operation and SQLite error message.