> For the complete documentation index, see [llms.txt](https://docs.fractalworks.io/joule/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.fractalworks.io/joule/developer-guides/builder-sdk/analytics-api/sql-queries.md).

# SQL queries

## Overview

Joule embeds DuckDB, an in-memory database, in to the runtime process. The solution is ideal for supporting custom processor logic using various methods such as:

* Hosting and accessing custom reference data
* Scratchpad for stateful processing
* Ad-hoc custom complex queries 
* Capture and exporting streaming events

## SQLQueryInterface APIs

The below provides the required API documentation to leverage the internal in-memory database for your use cases.

### Package 

```java
com.fractalworks.streams.sdk.analytics.sql.SQLQueryInterface
```

### Examples

The below is a uses the raw internal database connection via a API call.

```java
try (Statement statement = SQLQueryInterface.getConnection().createStatement()) {
    statement.execute(query);
} catch (SQLException e) {
    throw new FailedSQLProcessingException(String.format("Failed to execute query [%s]", query), e);
}
```

This example registers a query, executes and returns an unpacked resultset and then unregisters the query.

```java
SQLQueryInterface.registerQuery("duckDBSetting","SELECT * FROM duckdb_settings();");
List<SQLQueryResult> results = SQLQueryInterface.executeSelectQuery("duckDBSetting", null);
SQLQueryInterface.unregisterQuery("duckDBSetting");
```

## Database  Ops

`DuckDBConnection getConnection() throws SQLException`

*Description* - Get an internal database connection. This will provide you the ability to interact directly using your own code. Visit the [DuckDB JDBC documentation](https://duckdb.org/docs/api/java) on how to leverage their features.

*Return* - In-process database connection

*Exception -* Thrown when a connection cannot be made to the internal database&#x20;

***

`void createSchema(final String schemaName) throws FailedSQLProcessingException`

*Description -* Create a database schema namespace

*Exception -* Thrown when the schema cannot be created due to an internal database issue

***

## Table Management

`void createTable(final String tableDefinition) throws FailedSQLProcessingException`

*Description -* Create a database table, if it does not exist, using the provided table SQL definition.&#x20;

*Exception -* Thrown when table failed to be created

***Parameters***

<table><thead><tr><th width="200.33333333333331">Parameter</th><th width="367">Description</th><th>Type</th></tr></thead><tbody><tr><td>tableDefinition</td><td>Table SQL definition</td><td>String</td></tr></tbody></table>

***

`boolean createIndex(final String schema, final String table, String[] fields, boolean unique)`

*Description -* Create a unique index on table. If the index already exists it will be dropped and recreated.

*Return* - True if index created otherwise false

*Exception -* Thrown when index failed to be created

***Parameters***

<table><thead><tr><th width="200.33333333333331">Parameter</th><th width="367">Description</th><th>Type</th></tr></thead><tbody><tr><td>schema</td><td>Schema where table exists</td><td>String</td></tr><tr><td>table</td><td>Name of database table</td><td>String</td></tr><tr><td>fields</td><td>List of fields to create index upon</td><td>String[]</td></tr><tr><td>unique</td><td>Flag to indicate if a unique index is to be created. True for unique indexes false otherwise</td><td>Boolean</td></tr></tbody></table>

***

`TableMetaData getTableMetaData(final String tablename) throws FailedSQLProcessingException`

*Description -* Get table metadata, see [TableMetaData](#tablemetadata) section for class details

*Exception -* Thrown due to an internal database issue

***Parameters***

<table><thead><tr><th width="200.33333333333331">Parameter</th><th width="367">Description</th><th>Type</th></tr></thead><tbody><tr><td>table</td><td>Name of the table</td><td>String</td></tr></tbody></table>

***

`void dropTable(final String table) throws FailedSQLProcessingException`

*Description -* Drop the table from the database.

*Exception -* Thrown when table failed to be dropped

***Parameters***

<table><thead><tr><th width="200.33333333333331">Parameter</th><th width="367">Description</th><th>Type</th></tr></thead><tbody><tr><td>table</td><td>Name of the table to be dropped</td><td>String</td></tr></tbody></table>

***

`void deleteTable(final String table, final String criteria) throws FailedSQLProcessingException`

*Description -* Delete table records using the passed criteria

*Exception -* Thrown when failed to delete table

***Parameters***

<table><thead><tr><th width="200.33333333333331">Parameter</th><th width="367">Description</th><th>Type</th></tr></thead><tbody><tr><td>queryName</td><td>Name of the query to be executed</td><td>String</td></tr><tr><td>params</td><td>Array of parameters to be passed in to query</td><td>Object[]</td></tr></tbody></table>

***

### Query Processing

`void registerQuery(String queryName, String query) throws DuplicateQueryException`

*Description* - Register a query in to the internal SQL engine. The query will be cached to improve runtime performance.

*Exception -* Thrown when the query name has already been used

***Parameters***

<table><thead><tr><th width="198.33333333333331">Parameter</th><th>Description</th><th>Type</th></tr></thead><tbody><tr><td>queryName</td><td>Name of query to be registered</td><td>String</td></tr><tr><td>query</td><td>Well formed SQL query</td><td>String</td></tr></tbody></table>

***

`List<SQLQueryResult> executeSelectQuery(String queryName, Object[] params) throws SQLException`

*Description* - Execute a registered query with the passed parameters and return a list of query results

*Response -* List of SQLQueryResults, see [SQLQueryResults](#sqlqueryresults) for more information

*Exception - Thrown when a query execution fails*&#x20;

***Parameters***

<table><thead><tr><th width="200.33333333333331">Parameter</th><th width="367">Description</th><th>Type</th></tr></thead><tbody><tr><td>queryName</td><td>Name of the query to be executed</td><td>String</td></tr><tr><td>params</td><td>Array of parameters to be passed in to query</td><td>Object[]</td></tr></tbody></table>

***

`Map<String, String> getAvailableQueries()`

*Description* - Get a list of available queries and the associated query SQL

*Response -* Map query names to associated SQL&#x20;

***

`boolean unregisterQuery(String queryName)`

*Description* - Unregister a cached query

*Response - T*rue query unregistered false otherwise

***Parameters***

<table><thead><tr><th width="200.33333333333331">Parameter</th><th width="367">Description</th><th>Type</th></tr></thead><tbody><tr><td>queryName</td><td>Name of the query to be unregistered</td><td>String</td></tr></tbody></table>

***

## **Data Structures**

We have kept the number of data structures to the minimum and as simple as possible.

### SQLQueryResults

This class is a convenience class to hold the query results as a `HashMap<String,Object>` &#x20;

### TableMetaData

This class provides a DuckDB specific unwrapping of the metadata for a specific database table.