# 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](https://docs.singlestore.com/#section-idm461141348087043409096287227.md).

## 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

```sql
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>']
```

```sql
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](https://docs.singlestore.com/cloud/reference/sql-reference/security-management-commands/grant.md) 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](https://github.com/singlestore-labs/timestamp9/tree/main) 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:

```sql
CREATE EXTENSION ts9 FROM HTTP <link_to_ts9.tar>;
```

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

```sql
SHOW FUNCTIONS;

```

```output

+---------------------+-----------------------+----------+-------------+--------------+------+
| 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:

```sql
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**:
   ```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**:
   ```shell
   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.
   ```sql
   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.

```sql
SELECT str_to_ts9("2024-01-16 04:38:25.941891999") AS 'Result';

```

```output

+---------------------+
| Result              |
+---------------------+
| 1705379905941891999 |
+---------------------+
```

```sql
SELECT ts9_to_str("1705379905941891999") AS 'Result';

```

```output

+-------------------------------+
| Result                        |
+-------------------------------+
| 2024-01-16 04:38:25.941891999 |
+-------------------------------+
```

## Example 3: Create an Extension using `LINK`

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

```sql
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'; 
```

***

Modified at: August 21, 2024

Source: [/cloud/reference/sql-reference/procedural-sql-reference/extensions/](https://docs.singlestore.com/cloud/reference/sql-reference/procedural-sql-reference/extensions/)

(An index of the documentation is available at /llms.txt)