> ## Documentation Index
> Fetch the complete documentation index at: https://docs.turso.tech/llms.txt
> Use this file to discover all available pages before exploring further.

# Extensions

> Built-in and loadable extensions for additional SQL functions and virtual tables

# Extensions

Turso provides several built-in extensions that add specialized SQL functions and virtual tables. Extensions can be loaded at runtime using the `load_extension()` function.

## Loading Extensions

```sql theme={null}
SELECT load_extension('extension_name');
```

Turso supports loading Turso-native extensions. SQLite `.so`/`.dll` loadable extensions are not supported.

## UUID Extension

The UUID extension provides functions for generating and working with UUIDs. UUIDs are stored as 16-byte BLOBs by default.

### Functions

| Function                   | Parameters | Return Type | Description                                                     |
| -------------------------- | ---------- | ----------- | --------------------------------------------------------------- |
| `uuid4()`                  | none       | BLOB        | Generate a random UUID v4                                       |
| `uuid4_str()`              | none       | TEXT        | Generate a random UUID v4 as string. Alias: `gen_random_uuid()` |
| `uuid7()`                  | none       | BLOB        | Generate a time-ordered UUID v7                                 |
| `uuid7(seconds)`           | INTEGER    | BLOB        | Generate a UUID v7 with specified seconds since epoch           |
| `uuid7_timestamp_ms(uuid)` | BLOB       | INTEGER     | Extract milliseconds since epoch from a UUID v7                 |
| `uuid_str(uuid)`           | BLOB       | TEXT        | Convert a UUID blob to string representation                    |
| `uuid_blob(uuid)`          | TEXT       | BLOB        | Convert a UUID string to blob representation                    |

### Examples

```sql theme={null}
-- Generate UUIDs
SELECT uuid4_str();
-- '550e8400-e29b-41d4-a716-446655440000'

SELECT uuid_str(uuid4());
-- 'f47ac10b-58cc-4372-a567-0e02b2c3d479'

-- UUID v7 is time-ordered (good for primary keys)
SELECT uuid_str(uuid7());
-- '0190a5c0-1234-7abc-8def-0123456789ab'

-- Extract timestamp from UUID v7
SELECT uuid7_timestamp_ms(uuid7());
-- 1720000000000

-- Use in a table
CREATE TABLE documents (
    id BLOB PRIMARY KEY DEFAULT (uuid7()),
    title TEXT
);
INSERT INTO documents (title) VALUES ('My Document');
SELECT uuid_str(id), title FROM documents;
```

## Regexp Extension

The regexp extension provides regular expression functions compatible with [sqlean-regexp](https://github.com/nalgeon/sqlean/blob/main/docs/regexp.md).

### Functions

| Function                                       | Parameters          | Return Type | Description                                                   |
| ---------------------------------------------- | ------------------- | ----------- | ------------------------------------------------------------- |
| `regexp(pattern, source)`                      | TEXT, TEXT          | INTEGER     | Returns 1 if source matches pattern                           |
| `regexp_like(source, pattern)`                 | TEXT, TEXT          | INTEGER     | Returns 1 if source matches pattern (argument order reversed) |
| `regexp_substr(source, pattern)`               | TEXT, TEXT          | TEXT        | Returns the first substring matching pattern, or NULL         |
| `regexp_capture(source, pattern)`              | TEXT, TEXT          | TEXT        | Returns the first capture group match                         |
| `regexp_capture(source, pattern, n)`           | TEXT, TEXT, INTEGER | TEXT        | Returns the n-th capture group match                          |
| `regexp_replace(source, pattern, replacement)` | TEXT, TEXT, TEXT    | TEXT        | Replace matches with replacement string                       |

### Examples

```sql theme={null}
-- Pattern matching
SELECT regexp('[0-9]+', 'abc123def');
-- 1

-- The REGEXP operator uses this extension
SELECT 'hello123' REGEXP '[a-z]+[0-9]+';
-- 1

-- Extract matching substring
SELECT regexp_substr('Price: $42.50', '[0-9]+\.[0-9]+');
-- '42.50'

-- Capture groups
SELECT regexp_capture('2025-03-15', '(\d{4})-(\d{2})-(\d{2})', 1);
-- '2025'

-- Replace
SELECT regexp_replace('Hello World', '[aeiou]', '*');
-- 'H*ll* W*rld'
```

## Vector Extension

<Info>
  **Turso Extension**: Native vector search for similarity search and semantic search applications.
</Info>

The vector extension provides functions for creating, storing, and searching vector embeddings. Vectors are stored as BLOBs.

For the complete function reference, see [Vector Functions](/sql-reference/functions/vector).

### Quick Reference

| Function                      | Description                  |
| ----------------------------- | ---------------------------- |
| `vector32(json)`              | Create a 32-bit float vector |
| `vector64(json)`              | Create a 64-bit float vector |
| `vector_distance_cos(v1, v2)` | Cosine distance              |
| `vector_distance_l2(v1, v2)`  | Euclidean distance           |
| `vector_extract(v)`           | Convert vector to JSON       |
| `vector_concat(v1, v2)`       | Concatenate vectors          |
| `vector_slice(v, start, end)` | Extract vector slice         |

### Example

```sql theme={null}
CREATE TABLE docs (
    id INTEGER PRIMARY KEY,
    content TEXT,
    embedding BLOB
);

INSERT INTO docs VALUES (1, 'database systems', vector32('[0.1, 0.2, 0.3]'));
INSERT INTO docs VALUES (2, 'machine learning', vector32('[0.4, 0.5, 0.6]'));

-- Find similar documents
SELECT content, vector_distance_cos(embedding, vector32('[0.1, 0.25, 0.35]')) AS dist
FROM docs
ORDER BY dist
LIMIT 5;
```

## Time Extension

The time extension is compatible with [sqlean-time](https://github.com/nalgeon/sqlean/blob/main/docs/time.md) and provides advanced time manipulation functions.

<Info>
  A **time value** (`t` in the tables below) is an opaque 13-byte **BLOB**, not an integer — this matches sqlean-time byte-for-byte (`version(1) + seconds(8) + nanoseconds(4)`, with seconds counted from year 0001). Any function that produces a time (`time_now`, `time_date`, `time_add`, …) returns a BLOB, and `typeof(time_now())` is `blob`. Use `time_to_unix` / `time_to_nano` to get an INTEGER, and the `time_fmt_*` functions to get TEXT.

  To store a time value in a `STRICT` table, use a `BLOB` column, or convert it first:

  ```sql theme={null}
  CREATE TABLE X (t BLOB) STRICT;
  INSERT INTO X (t) VALUES (time_now());

  -- Or store a Unix integer:
  CREATE TABLE Y (t INTEGER) STRICT;
  INSERT INTO Y (t) VALUES (time_to_nano(time_now()));
  ```
</Info>

### Key Functions

| Function                    | Parameters       | Return Type | Description                         |
| --------------------------- | ---------------- | ----------- | ----------------------------------- |
| `time_now()`                | none             | BLOB        | Current time                        |
| `time_date(y,m,d,...)`      | INTEGER...       | BLOB        | Create time from components         |
| `make_date(y,m,d)`          | INTEGER...       | BLOB        | Create time from a date             |
| `make_timestamp(...)`       | INTEGER...       | BLOB        | Create time from a timestamp        |
| `time_unix(sec)`            | INTEGER          | BLOB        | Create time from Unix seconds       |
| `to_timestamp(sec)`         | INTEGER          | BLOB        | Create time from Unix seconds       |
| `time_milli(ms)`            | INTEGER          | BLOB        | Create time from milliseconds       |
| `time_micro(us)`            | INTEGER          | BLOB        | Create time from microseconds       |
| `time_nano(ns)`             | INTEGER          | BLOB        | Create time from nanoseconds        |
| `time_to_unix(t)`           | BLOB             | INTEGER     | Convert to Unix seconds             |
| `time_to_milli(t)`          | BLOB             | INTEGER     | Convert to milliseconds             |
| `time_to_micro(t)`          | BLOB             | INTEGER     | Convert to microseconds             |
| `time_to_nano(t)`           | BLOB             | INTEGER     | Convert to nanoseconds              |
| `time_get_year(t)`          | BLOB             | INTEGER     | Extract year                        |
| `time_get_month(t)`         | BLOB             | INTEGER     | Extract month (1-12)                |
| `time_get_day(t)`           | BLOB             | INTEGER     | Extract day (1-31)                  |
| `time_get_hour(t)`          | BLOB             | INTEGER     | Extract hour (0-23)                 |
| `time_get_minute(t)`        | BLOB             | INTEGER     | Extract minute (0-59)               |
| `time_get_second(t)`        | BLOB             | INTEGER     | Extract second (0-59)               |
| `time_get_weekday(t)`       | BLOB             | INTEGER     | Day of week (0=Sunday)              |
| `time_get(t, field)`        | BLOB, TEXT       | INTEGER     | Extract named field                 |
| `time_add(t, d)`            | BLOB, INTEGER    | BLOB        | Add duration                        |
| `time_add_date(t, y, m, d)` | BLOB, INTEGER... | BLOB        | Add years/months/days               |
| `time_sub(t, u)`            | BLOB, BLOB       | INTEGER     | Difference between times (duration) |
| `time_since(t)`             | BLOB             | INTEGER     | Duration since time                 |
| `time_until(t)`             | BLOB             | INTEGER     | Duration until time                 |
| `time_trunc(t, field)`      | BLOB, TEXT       | BLOB        | Truncate to field                   |
| `time_round(t, d)`          | BLOB, INTEGER    | BLOB        | Round to nearest duration           |
| `time_after(t, u)`          | BLOB, BLOB       | INTEGER     | 1 if t is after u                   |
| `time_before(t, u)`         | BLOB, BLOB       | INTEGER     | 1 if t is before u                  |
| `time_compare(t, u)`        | BLOB, BLOB       | INTEGER     | -1, 0, or 1                         |
| `time_equal(t, u)`          | BLOB, BLOB       | INTEGER     | 1 if equal                          |
| `time_fmt_iso(t)`           | BLOB             | TEXT        | Format as ISO 8601                  |
| `time_fmt_datetime(t)`      | BLOB             | TEXT        | Format as datetime                  |
| `time_fmt_date(t)`          | BLOB             | TEXT        | Format as date                      |
| `time_fmt_time(t)`          | BLOB             | TEXT        | Format as time                      |
| `time_parse(s)`             | TEXT             | BLOB        | Parse time string                   |

#### Duration Constants

| Function   | Value         |
| ---------- | ------------- |
| `dur_ns()` | 1 nanosecond  |
| `dur_us()` | 1 microsecond |
| `dur_ms()` | 1 millisecond |
| `dur_s()`  | 1 second      |
| `dur_m()`  | 1 minute      |
| `dur_h()`  | 1 hour        |

### Example

```sql theme={null}
-- Current time formatted
SELECT time_fmt_iso(time_now());
-- '2025-03-15T10:30:00Z'

-- Date arithmetic
SELECT time_fmt_date(time_add_date(time_now(), 0, 1, 0));
-- add 1 month to today

-- Duration since a timestamp
SELECT time_since(time_date(2025, 1, 1, 0, 0, 0)) / dur_h();
-- hours since Jan 1, 2025
```

## Full-Text Search (FTS)

<Info>
  **Turso Extension**: Full-text search powered by Tantivy, replacing SQLite's FTS3/FTS4/FTS5.
</Info>

Turso provides full-text search through the FTS index method. For the complete reference including query syntax, tokenizers, and scoring, see [FTS Functions](/sql-reference/functions/fts).

### Quick Reference

```sql theme={null}
-- Create an FTS index
CREATE INDEX idx ON articles USING fts (title, body);

-- Search
SELECT title, fts_score('idx') AS score
FROM articles
WHERE fts_match('idx', 'database search')
ORDER BY score;

-- Highlighted results
SELECT fts_highlight('idx', 0, '<b>', '</b>') AS title
FROM articles
WHERE fts_match('idx', 'database');
```

## CSV Extension

The CSV extension provides a virtual table for reading CSV files.

### Usage

```sql theme={null}
CREATE VIRTUAL TABLE temp.csv_data USING csv(
    filename='/path/to/data.csv',
    header=yes
);

SELECT * FROM csv_data;
```

| Parameter  | Description                                  |
| ---------- | -------------------------------------------- |
| `filename` | Path to the CSV file                         |
| `header`   | `yes` if the first row contains column names |

## Percentile Extension

Statistical aggregate functions for computing percentiles.

### Functions

| Function                | Parameters   | Return Type | Description                                |
| ----------------------- | ------------ | ----------- | ------------------------------------------ |
| `median(X)`             | column       | REAL        | Median value (50th percentile)             |
| `percentile(Y, P)`      | column, REAL | REAL        | P-th percentile of Y (P between 0 and 100) |
| `percentile_cont(Y, P)` | column, REAL | REAL        | Continuous percentile (interpolated)       |
| `percentile_disc(Y, P)` | column, REAL | REAL        | Discrete percentile (nearest value)        |

### Examples

```sql theme={null}
CREATE TABLE scores (value REAL);
INSERT INTO scores VALUES (10), (20), (30), (40), (50);

SELECT median(value) FROM scores;
-- 30.0

SELECT percentile(value, 75) FROM scores;
-- 40.0

SELECT percentile_cont(value, 0.25) FROM scores;
-- 20.0
```

## Crypto Extension

The crypto extension provides hashing and encoding functions, compatible with [sqlean-crypto](https://github.com/nalgeon/sqlean/blob/main/docs/crypto.md).

### Functions

| Function                      | Parameters | Return Type | Description                                                                  |
| ----------------------------- | ---------- | ----------- | ---------------------------------------------------------------------------- |
| `crypto_md5(data)`            | TEXT/BLOB  | BLOB        | MD5 hash                                                                     |
| `crypto_sha1(data)`           | TEXT/BLOB  | BLOB        | SHA-1 hash                                                                   |
| `crypto_sha256(data)`         | TEXT/BLOB  | BLOB        | SHA-256 hash                                                                 |
| `crypto_sha384(data)`         | TEXT/BLOB  | BLOB        | SHA-384 hash                                                                 |
| `crypto_sha512(data)`         | TEXT/BLOB  | BLOB        | SHA-512 hash                                                                 |
| `crypto_blake3(data)`         | TEXT/BLOB  | BLOB        | BLAKE3 hash                                                                  |
| `crypto_encode(data, format)` | BLOB, TEXT | TEXT        | Encode a blob as text using `format` (e.g. `hex`, `base32`, `base64`, `url`) |
| `crypto_decode(text, format)` | TEXT, TEXT | BLOB        | Decode text in the given `format` back into a blob                           |

```sql theme={null}
-- Hash a value and encode it as hex
SELECT crypto_encode(crypto_sha256('hello'), 'hex');

-- Base64 round-trip
SELECT crypto_decode(crypto_encode(x'CAFE', 'base64'), 'base64');
```

## Fuzzy Extension

The fuzzy extension provides string-similarity, distance, and phonetic functions, compatible with [sqlean-fuzzy](https://github.com/nalgeon/sqlean/blob/main/docs/fuzzy.md).

### Functions

| Function               | Parameters | Return Type | Description                             |
| ---------------------- | ---------- | ----------- | --------------------------------------- |
| `fuzzy_editdist(a, b)` | TEXT, TEXT | INTEGER     | Spellcheck-style edit distance          |
| `fuzzy_leven(a, b)`    | TEXT, TEXT | INTEGER     | Levenshtein distance                    |
| `fuzzy_damlev(a, b)`   | TEXT, TEXT | INTEGER     | Damerau-Levenshtein distance            |
| `fuzzy_osadist(a, b)`  | TEXT, TEXT | INTEGER     | Optimal string alignment distance       |
| `fuzzy_hamming(a, b)`  | TEXT, TEXT | INTEGER     | Hamming distance                        |
| `fuzzy_jarowin(a, b)`  | TEXT, TEXT | REAL        | Jaro-Winkler similarity                 |
| `fuzzy_soundex(s)`     | TEXT       | TEXT        | Soundex phonetic code                   |
| `fuzzy_rsoundex(s)`    | TEXT       | TEXT        | Refined Soundex code                    |
| `fuzzy_caver(s)`       | TEXT       | TEXT        | Caverphone phonetic code                |
| `fuzzy_phonetic(s)`    | TEXT       | TEXT        | US Census phonetic code                 |
| `fuzzy_translit(s)`    | TEXT       | TEXT        | Transliterate Unicode text to ASCII     |
| `fuzzy_script(s)`      | TEXT       | INTEGER     | Identify the writing script of the text |
| `phonetics(a, b)`      | TEXT, TEXT | INTEGER     | 1 if two strings are phonetically equal |

```sql theme={null}
-- Distance between two strings
SELECT fuzzy_leven('awesome', 'aewsime');
-- 3

-- Phonetic encoding
SELECT fuzzy_soundex('Robert');
-- 'R163'
```

## IP Address Extension

The ipaddr extension provides functions for working with IPv4 and IPv6 addresses and networks.

### Functions

| Function                  | Parameters | Return Type | Description                                         |
| ------------------------- | ---------- | ----------- | --------------------------------------------------- |
| `ipfamily(ip)`            | TEXT       | INTEGER     | Returns 4 for IPv4 or 6 for IPv6                    |
| `iphost(ip)`              | TEXT       | TEXT        | The host portion of an address (strips any netmask) |
| `ipmasklen(cidr)`         | TEXT       | INTEGER     | The prefix (mask) length of a CIDR network          |
| `ipnetwork(cidr)`         | TEXT       | TEXT        | The network address of a CIDR value                 |
| `ipcontains(network, ip)` | TEXT, TEXT | INTEGER     | 1 if the CIDR network contains the given address    |

```sql theme={null}
SELECT ipfamily('192.168.1.1');            -- 4
SELECT ipnetwork('192.168.1.42/24');       -- '192.168.1.0/24'
SELECT ipcontains('10.0.0.0/8', '10.1.2.3'); -- 1
```

## generate\_series

The `generate_series` table-valued function generates a sequence of integers.

```sql theme={null}
SELECT value FROM generate_series(start, stop);
SELECT value FROM generate_series(start, stop, step);
```

| Parameter | Type    | Description                 |
| --------- | ------- | --------------------------- |
| start     | INTEGER | Starting value (inclusive)  |
| stop      | INTEGER | Ending value (inclusive)    |
| step      | INTEGER | Step increment (default: 1) |

```sql theme={null}
SELECT value FROM generate_series(1, 5);
-- 1, 2, 3, 4, 5

SELECT value FROM generate_series(0, 10, 2);
-- 0, 2, 4, 6, 8, 10

SELECT value FROM generate_series(10, 1, -3);
-- 10, 7, 4, 1
```

## See Also

* [Vector Functions](/sql-reference/functions/vector) for detailed vector function reference
* [FTS Functions](/sql-reference/functions/fts) for full-text search function reference
* [Aggregate Functions](/sql-reference/functions/aggregate) for percentile functions used with GROUP BY
* [Compatibility](/sql-reference/compatibility) for extension support status