# UUID in Manticore: A Practical Guide

A practical guide to using UUID as a document ID in Manticore Search 28.5.0: SQL, JSON API, /bulk, automatic ID generation, errors, and replication.

In the [overview article](/blog/uuid-document-ids/) we explained why it makes sense to use the same UUID in search and in the primary database, when one already exists. Here we will go straight to practice: create a table, run the core operations through SQL and the JSON API, and then load several documents through `/bulk`.

All examples are for Manticore Search 28.5.0 or later. The `<generated UUID>` value in the responses means the UUID that Manticore creates when processing the request. You do not need to copy this string into the next request: substitute the actual `id` from your own response.

## Table for all examples

To make UUID the document ID, first declare the field as `id uuid`. Aside from the `id` type, the RT table schema does not change:

```sql
CREATE TABLE products_uuid (
    id uuid,
    title text,
    sku string,
    price int
);
```

`DESC products_uuid` will show that the `id` field has the `uuid` type. Both SQL and the HTTP API use this value as the document ID, so there is no need to copy the UUID into a string attribute.

## Working with UUID through SQL

First, insert a product with a prebuilt UUID:

```sql
INSERT INTO products_uuid (id, title, sku, price)
VALUES (
    '550e8400-e29b-41d4-a716-446655440000',
    'Mechanical keyboard',
    'KB-001',
    149
);
```

In SQL, the UUID must be enclosed in single quotes. You can find the document with a regular `WHERE id = '...'` condition:

```sql
SELECT id, title, sku, price
FROM products_uuid
WHERE id = '550e8400-e29b-41d4-a716-446655440000';
```

Manticore can also generate the UUID. To do that, simply do not pass any `id` value:

```sql
INSERT INTO products_uuid (title, sku, price)
VALUES ('USB microphone', 'MIC-001', 89);

SELECT LAST_INSERT_ID();
```

`LAST_INSERT_ID()` will return the UUID created by this request:

```text
+--------------------------------------+
| last_insert_id()                     |
+--------------------------------------+
| <generated UUID>                     |
+--------------------------------------+
```

`INSERT` for multiple documents and the `@@session.last_insert_id` variable are covered in detail in the documentation section on [adding documents](https://manual.manticoresearch.com/dev/Data_creation_and_modification/Adding_documents_to_a_table/Adding_documents_to_a_real-time_table).

Let's add another document with a known ID to check `IN`:

```sql
INSERT INTO products_uuid (id, title, sku, price)
VALUES (
    '550e8400-e29b-41d4-a716-446655440001',
    'USB-C dock',
    'DOCK-001',
    119
);

SELECT id, sku, price
FROM products_uuid
WHERE id IN (
    '550e8400-e29b-41d4-a716-446655440000',
    '550e8400-e29b-41d4-a716-446655440001'
);
```

You can change attributes with a regular `UPDATE`. The `id` itself stays the same:

```sql
UPDATE products_uuid
SET price = 139
WHERE id = '550e8400-e29b-41d4-a716-446655440000';
```

An `INSERT` with an already existing UUID will not overwrite the document. Manticore will return a duplicate error:

```sql
INSERT INTO products_uuid (id, title, sku, price)
VALUES (
    '550e8400-e29b-41d4-a716-446655440000',
    'Duplicate keyboard',
    'KB-DUP',
    1
);
```

To create a new version of the document with the same UUID, use `REPLACE`:

```sql
REPLACE INTO products_uuid (id, title, sku, price)
VALUES (
    '550e8400-e29b-41d4-a716-446655440000',
    'Mechanical keyboard, revised',
    'KB-001',
    129
);
```

To change only the price, `UPDATE` is enough. Full-text fields and columnar attributes require `REPLACE`: it marks the old version of the document with the same ID as deleted and writes the new one. If that ID does not exist yet, Manticore simply adds the document. More details are in the documentation: [UPDATE](https://manual.manticoresearch.com/dev/Data_creation_and_modification/Updating_documents/UPDATE) and [REPLACE](https://manual.manticoresearch.com/dev/Data_creation_and_modification/Updating_documents/REPLACE).

Delete the document by the same UUID:

```sql
DELETE FROM products_uuid
WHERE id = '550e8400-e29b-41d4-a716-446655440001';
```

Let's check the current state of the document whose UUID we set on insert:

```sql
SELECT id, title, sku, price
FROM products_uuid
WHERE id = '550e8400-e29b-41d4-a716-446655440000';
```

The SQL client sends and receives a UUID as a string. Pass it as a string parameter, and read the `id` from `SELECT` as a string. There is no need to convert it to a number or `BINARY(16)`. Code written for a numeric document ID will need to be adjusted.

## The same operations through the JSON API

When writing through the JSON API, the `id` field is passed alongside `table`, not inside `doc`. In the first example, we use an uppercase UUID:

```bash
curl -sS http://localhost:9308/insert \
  -H 'Content-Type: application/json' \
  -d '{
    "table": "products_uuid",
    "id": "AAAAAAAA-AAAA-4AAA-8AAA-AAAAAAAAAAAA",
    "doc": {
      "title": "Wireless keyboard",
      "sku": "JSON-KB-001",
      "price": 159
    }
  }'
```

Manticore accepts UUID in uppercase, stores it in lowercase, and returns it lowercased in the response:

```json
{
  "table": "products_uuid",
  "id": "aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa",
  "created": true,
  "result": "created",
  "status": 201
}
```

For automatic generation, remove the `id` field entirely:

```bash
curl -sS http://localhost:9308/insert \
  -H 'Content-Type: application/json' \
  -d '{
    "table": "products_uuid",
    "doc": {
      "title": "Portable speaker",
      "sku": "JSON-SPK-001",
      "price": 79
    }
  }'
```

The response contains the ID that should be saved for subsequent operations:

```json
{
  "table": "products_uuid",
  "id": "<generated UUID>",
  "created": true,
  "result": "created",
  "status": 201
}
```

In `/search`, you can use UUID in the `equals` filter. If you request `id` in `_source`, the result will contain the same UUID in both `_id` and `_source.id`:

```bash
curl -sS http://localhost:9308/search \
  -H 'Content-Type: application/json' \
  -d '{
    "table": "products_uuid",
    "query": {
      "equals": {
        "id": "aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa"
      }
    },
    "_source": ["id", "title", "sku", "price"]
  }'
```

```json
{
  "timed_out": false,
  "hits": {
    "total": 1,
    "total_relation": "eq",
    "hits": [
      {
        "_id": "aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa",
        "_score": 1,
        "_source": {
          "id": "aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa",
          "title": "Wireless keyboard",
          "sku": "JSON-KB-001",
          "price": 159
        }
      }
    ]
  }
}
```

`_id` is part of the search result metadata, while `_source.id` is the document field. For a UUID table, they contain the same string.

Now let's call the remaining endpoints for modifying data one by one. `UPDATE` changes only the price:

```bash
curl -sS http://localhost:9308/update \
  -H 'Content-Type: application/json' \
  -d '{
    "table": "products_uuid",
    "id": "aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa",
    "doc": {"price": 149}
  }'
```

To replace the document completely, call `/replace`:

```bash
curl -sS http://localhost:9308/replace \
  -H 'Content-Type: application/json' \
  -d '{
    "table": "products_uuid",
    "id": "aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa",
    "doc": {
      "title": "Wireless keyboard, revised",
      "sku": "JSON-KB-001",
      "price": 139
    }
  }'
```

Now delete the replaced document:

```bash
curl -sS http://localhost:9308/delete \
  -H 'Content-Type: application/json' \
  -d '{
    "table": "products_uuid",
    "id": "aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa"
  }'
```

After `/delete`, there is no document with this UUID left in the table. A `/search` by the same ID will return `total: 0`:

```bash
curl -sS http://localhost:9308/search \
  -H 'Content-Type: application/json' \
  -d '{
    "table": "products_uuid",
    "query": {
      "equals": {
        "id": "aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa"
      }
    }
  }'
```

```json
{
  "timed_out": false,
  "hits": {
    "total": 0,
    "total_relation": "eq",
    "hits": []
  }
}
```

Examples of requests for deleting by ID and by condition are collected in the documentation section on [deleting documents](https://manual.manticoresearch.com/dev/Data_creation_and_modification/Deleting_documents).

## Where to generate UUID

If the UUID is already issued by the primary database, just pass it to Manticore as `id`. If the request is repeated, the UUID will stay the same. `INSERT` will report a duplicate, and `REPLACE` will write a new version of the document under the same ID. The same rule applies to `insert` and `replace` in `/bulk`.

Manticore can also generate the UUID itself: just do not pass `id`. But resending such a request will create another document, so for automatic retries it is better to set the UUID explicitly.

For an explicit ID, any UUID version from v1 to v8 is suitable. For automatic generation, Manticore uses its own UUIDv8 structure, but it does not convert UUIDs received from the client into it.

## Batch loading through `/bulk`

`/bulk` accepts NDJSON: each line contains a separate operation. The UUID is passed in the `id` field, just like in the other JSON API requests:

```http
POST /bulk
Content-Type: application/x-ndjson

{"insert":{"table":"products_uuid","id":"bbbbbbbb-bbbb-4bbb-8bbb-bbbbbbbbbbbb","doc":{"title":"USB hub","sku":"BULK-HUB-001","price":49}}}
{"insert":{"table":"products_uuid","id":"bbbbbbbb-bbbb-4bbb-8bbb-bbbbbbbbbbbc","doc":{"title":"Laptop stand","sku":"BULK-STAND-001","price":39}}}
```

After the last data line, a trailing newline is required. With `curl`, it is convenient to pass the body through `--data-binary` so it does not strip newlines.

Both operations belong to one table, so Manticore executes them in a single transaction. The shortened response shows how many documents were added and how the entire batch finished:

```json
{
  "items": [
    {
      "bulk": {
        "created": 2,
        "status": 201
      }
    }
  ],
  "current_line": 2,
  "skipped_lines": 0,
  "errors": false
}
```

On an error, `current_line` shows the line where processing stopped, and `skipped_lines` shows the number of skipped lines. If an empty line or a table switch split the request into multiple transactions, Manticore will not roll back the ones that already completed.

When reloading a document, it is important to choose the right operation. If the UUID already exists, `insert` will fail with a duplicate error. `replace` will write the document again, and if no such ID exists, it will add a new document.

The `id` field in `/bulk` can also be omitted, and Manticore will generate a UUID. But the `/bulk` response contains only the overall transaction result, without separate results for each inserted row. If you need to keep the ID of each document, it is more convenient to generate the UUID before the batch request or add documents one by one through `/insert`.

## Validation and common errors

Manticore validates the UUID before adding the document. SQL, the JSON API, and `/bulk` return errors differently, so do not bind your code to the exact wording of the message.

| What you sent | What Manticore will do |
|---|---|
| `550e8400-e29b-41d4-a716-446655440000` | Add the document |
| `AAAAAAAA-AAAA-4AAA-8AAA-AAAAAAAAAAAA` | Add the document, store the UUID, and return it in lowercase |
| A string without hyphens or with an invalid character | Return an error because the UUID format is invalid |
| `00000000-0000-0000-0000-000000000000` | Return an error: zero UUID cannot be used |
| A number, including `0` | Return an error: `id` must be a string |
| A UUID with a version outside the 1-8 range or an RFC-invalid `variant` value | Return a validation error |
| `INSERT` or `insert` in `/bulk` with an already existing ID | Return a duplicate error |

A few points:
- The canonical form consists of 36 characters split into `8-4-4-4-12` groups. UUID versions from 1 to 8 are valid; in the RFC `variant` position, one of these characters must appear: `8`, `9`, `a`, or `b`.
- Manticore validates only the ID format. Correct generation is the application's responsibility: for UUIDv4, randomness matters, and for UUIDv7, the time component and compliance with the rules matter. Manticore does not convert passed v4 and v7 values into v8.
- In a table with UUID IDs, `id = 0` does not trigger automatic generation unlike numeric IDs: the request will fail. But if you do not pass `id`, Manticore will generate its own UUIDv8 structure and return it in the response. Keep in mind that it can be used as a document ID, but not as, for example, an access token.
- You can validate the UUID with a standard library on input, but keep in mind that Manticore still performs its own validation.
- In the `/bulk` response, check `errors`, `current_line`, and `skipped_lines`: they show where processing stopped and which part of the batch could not be committed. In SQL batch requests, the entire request is rolled back if an error occurs.

## Columnar RT and replication

UUID can also be used as the document ID in RT tables with columnar storage. Only the engine declaration changes:

```sql
CREATE TABLE products_uuid_columnar (
    id uuid,
    title text,
    sku string,
    price int
) engine='columnar';
```

For regular and columnar RT tables, `INSERT`, `REPLACE`, and `DELETE`, exact-match searches, and `IN` conditions are written the same way. The client code does not depend on the table engine. But `UPDATE` does not change columnar attributes, so, for example, the price in such a table can only be updated together with the whole document through `REPLACE`.

Replication also supports UUID. Suppose the `catalog` cluster has already been created, the nodes have joined, and the local `products_uuid` table exists. Let's add it to the cluster:

```sql
ALTER CLUSTER catalog ADD products_uuid;
```

In SQL, a colon goes between the cluster name and the table name:

```sql
INSERT INTO catalog:products_uuid (id, title, sku, price)
VALUES (
    '550e8400-e29b-41d4-a716-446655441000',
    'Replicated keyboard',
    'REPL-KB-001',
    169
);

SELECT id, sku, price
FROM catalog:products_uuid
WHERE id = '550e8400-e29b-41d4-a716-446655441000';
```

In the JSON API, the table name is passed unchanged, while the cluster goes in a separate field. For example, the following operation will update a document previously added through SQL:

```bash
curl -sS http://localhost:9308/update \
  -H 'Content-Type: application/json' \
  -d '{
    "cluster": "catalog",
    "table": "products_uuid",
    "id": "550e8400-e29b-41d4-a716-446655441000",
    "doc": {"price": 159}
  }'
```

After replication, the document keeps the same UUID on all nodes. You can run exact-match searches, `UPDATE`, `REPLACE`, and `DELETE` by it.

## What to consider before rollout

- The `uuid` type can be assigned only to the `id` field. It is supported in RT tables, including those with columnar storage and replication, but not in plain, percolate/PQ, or shard tables.
- An existing table cannot be switched from a numeric ID to a UUID or back with `ALTER TABLE`. This decision has to be made when creating the new schema.
- A UUID can be used in `=` and `IN` conditions. The `<`, `<=`, `>`, and `>=` ranges, as well as arithmetic on `id`, are not supported.
- UUIDv7 contains time, but filtering `id` by range is still not allowed. For date-based selection, add an attribute such as `created_at timestamp` and filter by it.
- The document ID cannot be changed with `UPDATE`. To give an object a different UUID, you will need to create a document with a new ID and delete the old one separately.

The behavior of SQL sessions and the full list of limitations are described in the documentation: [UUID document IDs](https://manual.manticoresearch.com/dev/Creating_a_table/Data_types#UUID-document-IDs). Support for UUID as a document ID appeared in [Manticore Search 28.5.0](/blog/manticore-search-28-5-0/).