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

# SQLAlchemy Data Layer

This custom layer has been tested for PostgreSQL, however it should support more SQL databases thanks to the use of the SQL Alchemy database.

This data layer also supports the `BaseStorageClient` that enables you to store your elements into Azure Blob Storage or AWS S3.

<Warning>
  The schema below reflects the latest version of Chainlit. If you are upgrading from an earlier version, three columns have been added to the `steps` table since 2.0.0:

  | Column        | Since | Migration guide                             |
  | ------------- | ----- | ------------------------------------------- |
  | `command`     | 2.1.0 | [Migrate to 2.1.0](/guides/migration/2.1.0) |
  | `defaultOpen` | 2.3.0 | [Migrate to 2.3.0](/guides/migration/2.3.0) |
  | `modes`       | 2.9.4 | [Migrate to 2.9.4](/guides/migration/2.9.4) |
</Warning>

Here is the SQL used to create the schema for this data layer:

```sql theme={null}
CREATE TABLE users (
    "id" UUID PRIMARY KEY,
    "identifier" TEXT NOT NULL UNIQUE,
    "metadata" JSONB NOT NULL,
    "createdAt" TEXT
);

CREATE TABLE IF NOT EXISTS threads (
    "id" UUID PRIMARY KEY,
    "createdAt" TEXT,
    "name" TEXT,
    "userId" UUID,
    "userIdentifier" TEXT,
    "tags" TEXT[],
    "metadata" JSONB,
    FOREIGN KEY ("userId") REFERENCES users("id") ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS steps (
    "id" UUID PRIMARY KEY,
    "name" TEXT NOT NULL,
    "type" TEXT NOT NULL,
    "threadId" UUID NOT NULL,
    "parentId" UUID,
    "streaming" BOOLEAN NOT NULL,
    "waitForAnswer" BOOLEAN,
    "isError" BOOLEAN,
    "metadata" JSONB,
    "tags" TEXT[],
    "input" TEXT,
    "output" TEXT,
    "createdAt" TEXT,
    "command" TEXT,
    "start" TEXT,
    "end" TEXT,
    "generation" JSONB,
    "showInput" TEXT,
    "language" TEXT,
    "indent" INT,
    "defaultOpen" BOOLEAN,
    "modes" JSONB,
    FOREIGN KEY ("threadId") REFERENCES threads("id") ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS elements (
    "id" UUID PRIMARY KEY,
    "threadId" UUID,
    "type" TEXT,
    "url" TEXT,
    "chainlitKey" TEXT,
    "name" TEXT NOT NULL,
    "display" TEXT,
    "objectKey" TEXT,
    "size" TEXT,
    "page" INT,
    "language" TEXT,
    "forId" UUID,
    "mime" TEXT,
    "props" JSONB,
    FOREIGN KEY ("threadId") REFERENCES threads("id") ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS feedbacks (
    "id" UUID PRIMARY KEY,
    "forId" UUID NOT NULL,
    "threadId" UUID NOT NULL,
    "value" INT NOT NULL,
    "comment" TEXT,
    FOREIGN KEY ("threadId") REFERENCES threads("id") ON DELETE CASCADE
);
```

## Example

Here is an example of setting up this data layer on a PostgreSQL database with an Azure storage client. First install the required dependencies:

```bash theme={null}
pip install asyncpg SQLAlchemy azure-identity azure-storage-file-datalake aiohttp greenlet
```

Import the custom data layer and storage client, and indicate which data layer to use with `@cl.data_layer` at the beginning of your Chainlit app:

```python theme={null}
import chainlit as cl
from chainlit.data.sql_alchemy import SQLAlchemyDataLayer
from chainlit.data.storage_clients.azure import AzureStorageClient

storage_client = AzureStorageClient(account_url="<your_account_url>", container="<your_container>")

@cl.data_layer
def get_data_layer():
    return SQLAlchemyDataLayer(conninfo="<your conninfo>", storage_provider=storage_client)
```

Note that you need to add `+asyncpg` to the protocol in the `conninfo` string so that it uses the asyncpg library.