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


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.