Extensions

SingleStore provides a set of extensibility features enabling users to create different types of user-defined objects, such as user-defined functions (UDFs) and user-defined aggregate functions (UDAFs) using procedural SQL (PSQL) or WebAssembly (Wasm). Extensions allow you to combine these user-defined objects into a package (the Extension) with a script that facilitates the creation, deployment, and management of these objects and other resources.

Why use Extensions

Extensions offer a streamlined deployment model for your extensibility content. To build your (PSQL or Wasm) functions and aggregates using Extensions:

  • Store the code in a repository,

  • Integrate (add) your code into an Extension, and

  • Deploy it using the CREATE EXTENSION command.

You can define both Wasm-based and PSQL functions (UDFs or UDAFs) within the same Extension. Without Extensions, each function (PSQL or Wasm) will have to be created separately. And for Wasm functions, the Wasm modules (.wasm and .wit files) will have to be passed separately to each function definition, even if multiple functions share the same Wasm modules.

You can also create an Extension from a pre-packaged archive that contains an install script and the required resource files, from any of the supported sources.

Define Extensions

An Extension is an archive that contains an install script and, optionally, other resource files such as Wasm binaries and WIT interfaces.

Install Script

An install script is a SQL file that contains the CREATE statements for each of the user-defined objects in the Extension. The install script must be named in the <extension_name>.sql format, and it must only contain CREATE FUNCTION or CREATE AGGREGATE statements.

Remarks

  • Only use plain function names inside the install script. Do not specify the function name in the <database_name>.<function_name> format.

  • To pass the contents of Wasm modules (.wasm and .wit files) in an Extension archive to CREATE FUNCTION … AS WASM statements in the install script within the same archive, use the LOCAL INFILE <filename> clause.

    Note: The LOCAL INFILE <filename> clause is only supported in the CREATE FUNCTION ... AS WASM statements inside an install script.

  • Refer to Wasm and WIT files with the given names in the install script, for example, test.wit, test.wasm.

Wasm Binary and WIT Interface

An Extension can contain Wasm binaries and WIT interfaces in the .wasm and .wit file format, respectively. They can be imported by the CREATE commands in the install script. The names of the Wasm and WIT files must match each other.

Syntax

CREATE EXTENSION <extension_name> FROM <contentSrc> [GROW WASM TO <numBytes>]
<contentSrc> :
BASE64 "<base64>" |
HTTP "<url>" <cred> <config> |
LINK <linkName> <connStr> |
S3 <connStr> <cred> [<config>] |
AZURE <connStr> <cred> [<config>] |
GCS <connStr> <cred> [<config>]
linkName: <link connection name>
connStr: <provider-specific path to a resource>
path: <standard filesystem path>
base64: <base64-encoded string>
url: <standard URL form>
cred: CREDENTIALS " <credSpec> "
credSpec: <json with provider-specific credentials>
config: CONFIG " <configSpec> "
configSpec: <json with provider-specific configuration>
s3_config:
S3 { '<bucket-name>' | '<bucket-name/path>' }
[CONFIG '<configuration_json>']
CREDENTIALS '<credentials_json>'
azure_config:
AZURE { '<container-name>' | '<container-name/object-name>' | '<container-name/prefix/object-name>' }
CREDENTIALS '<credentials_json>'
[CONFIG '<configuration_json>']
gcs_config:
GCS { '<bucket-name>' | '<bucket-name/path>' }
CREDENTIALS '<credentials_json>'
[CONFIG '<configuration_json>']
DROP EXTENSION <extension_name>

Remarks

  • Each DDL statement in the install script must either be a CREATE FUNCTION or CREATE AGGREGATE statement.

  • A PSQL CREATE FUNCTION statement in the install script must not refer to session variables. It can refer to other functions defined in the install script or functions provided by the database.

  • If a function in the install script depends on another function in the script, the dependent function must be defined after the function it depends on.

  • To delete all the modules in an Extension object and all the associated metadata, run the DROP EXTENSION command.

  • The SingleStore database user must have the required permissions to run the respective command:

    Command

    Permission Required

    CREATE EXTENSION

    CREATE EXTENSION, SHOW EXTENSION, ALTER EXTENSION, DROP EXTENSION

    DROP EXTENSION

    DROP EXTENSION

    Refer to GRANT for information on granting permissions to a SingleStore database user.

Extension Sources

SingleStore supports following sources for creating Extensions:

  • Base-64 encoded string: You can pass the contents of an Extension module directly to the CREATE EXTENSION statement as an inline base-64 encoded string. The maximum size of the CREATE EXTENSION statement can be modified using the max_allowed_packet global variable.

  • Remote URL: To download the content of an Extension module from a remote endpoint, you can specify the URL of the endpoint using the HTTP clause.

    Optionally, you can specify the CONFIG and CREDENTIALS clauses for the URL. The LINK object is not supported with HTTP, and the credential configuration must be provided inline in the CREATE EXTENSION command.

  • Cloud Provider: You can download the content of an Extension module from the following cloud providers: S3, Azure, and GCS. You also can specify the URL and connection credentials using the LINK clause.

Examples

The following examples uses the String to TS9 GitHub repository to build or import the files required to create an Extension. The Extension creates two functions str_to_ts9() and ts9_to_str() that convert date-time values from string to TIMESTAMP(9) and vice-versa, respectively.

Example 1: Create an Extension from a Pre-packaged Archive

You can create an Extension from a pre-packaged archive that contains the install script and the required resource files from any of the supported sources.

For example, to create an Extension from a remote URL, run the following command:

CREATE EXTENSION ts9 FROM HTTP <link_to_ts9.tar>;

Run the following command to verify that the functions are created:

SHOW FUNCTIONS;
+---------------------+-----------------------+----------+-------------+--------------+------+
| Functions_in_dbTest | Function Type         | Definer  | Data Format | Runtime Type | Link |
+---------------------+-----------------------+----------+-------------+--------------+------+
| str_to_ts9          | User Defined Function | s2user@% |             | Wasm         |      |
| ts9_to_str          | User Defined Function | s2user@% |             | Wasm         |      |
+---------------------+-----------------------+----------+-------------+--------------+------+

To drop the Extension and delete all the modules, run the following command:

DROP EXTENSION ts9;

Example 2: Build an Extension using Custom Code

The following example demonstrates how to integrate your code into an Extension and then deploy it.

  1. Write the code for function definitions. This example uses the ts9.wasm and ts9.wit files from the repository to create Wasm-based functions.

  2. Create an install script, and add your code. For example ts9.sql:

    CREATE FUNCTION ts9_to_str
    AS WASM FROM LOCAL INFILE "ts9.wasm"
    WITH WIT FROM LOCAL INFILE "ts9.wit";
    CREATE FUNCTION str_to_ts9
    AS WASM FROM LOCAL INFILE "ts9.wasm"
    WITH WIT FROM LOCAL INFILE "ts9.wit";
  3. Package the files into an archive. For example, the following command adds the ts9.sql, ts9.wasm, and ts9.wit files to an archive named ts9.tar:

    tar cvf ts9.tar ts9.sql ts9.wasm ts9.wit
  4. Upload (or store) the ts9.tar archive to a supported Extension source of your choosing. This example uses an S3 bucket named ext-mod.

  5. Run the following command to create the Extension.

    CREATE EXTENSION ts9 FROM S3 'ext-mod/ts9.tar'
    CREDENTIALS '{
    "aws_access_key_id": "ASIAZPIKLSJ3HM7FK...",
    "aws_secret_access_key": FwoGZXIvYXdzEL3fv [...]"
    }'
    CONFIG '{"region": "us-east-1"}';

The Extension is now created, along with all the related modules.

SELECT str_to_ts9("2024-01-16 04:38:25.941891999") AS 'Result';
+---------------------+
| Result              |
+---------------------+
| 1705379905941891999 |
+---------------------+
SELECT ts9_to_str("1705379905941891999") AS 'Result';
+-------------------------------+
| Result                        |
+-------------------------------+
| 2024-01-16 04:38:25.941891999 |
+-------------------------------+

The following example demonstrates how to create an Extension by importing the archive from an S3 bucket named modules using the LINK clause.

CREATE LINK accessLink AS S3
CREDENTIALS '{
"aws_access_key_id": "ASIAZPIKLSJ3HM7FK...",
"aws_secret_access_key": FwoGZXIvYXdzEL3fv [...]"
}'
CONFIG '{"region": "us-east-1"}';
CREATE EXTENSION extMod FROM LINK accessLink 'modules/extMod.tar';

Last modified: August 21, 2024

Was this article helpful?