data persistence with SQLAlchemyDataLayer possibility to change default schema public

[cesi08]

I followed the following documentation to have chat and feedback persistence https://docs.chainlit.io/data-persistence/custom

Below I report the code of my app and the requirements

import chainlit as cl
import os
from chainlit.data.storage_clients import AzureStorageClient
from chainlit.data.sql_alchemy import SQLAlchemyDataLayer
from azure.core.credentials import AzureNamedKeyCredential

account_name = ""
postgres_connection_string = ""
account_url = f"https://{account_name}.dfs.core.windows.net/"
account_key = ""
credential = AzureNamedKeyCredential(account_name, account_key)
storage_client = AzureStorageClient(account_url=account_url, container="chatbot", credential=credential)
sql_data_layer = SQLAlchemyDataLayer(conninfo=postgres_connection_string, storage_provider=storage_client,
                                     show_logger=True)
cl.data._data_layer = sql_data_layer
with_datalayer = True

@cl.on_chat_start
async def on_chat_start():
    await cl.Message(
        content="welcome to the chatbot! How can I help you today?"
    ).send()

@cl.on_message
async def on_message(message: cl.Message):
    msg = cl.Message(content="")
    await msg.send()
    msg.content = "hello"
    await msg.update()

requirements

azure-identity==1.19.0
boto3==1.35.59
azure-storage-file-datalake==12.17.0
aiohttp==3.10.10
SQLAlchemy==2.0.36
asyncpg==0.30.0

by default sql alchemy uses the public schema is there the possibility to specify the schema? because inside chainlit.data.sql_alchemy SQLAlchemyDataLayer there is no option to customize it

another thing I noticed is that the steps table must have the column
disableFeedback" bool NULL
because otherwise you always get the following error

@dokterbob @dosu

[dosubot]

The SQLAlchemyDataLayer class in Chainlit does not provide a direct method to specify a custom schema. However, you can typically specify a schema in SQLAlchemy by using the schema parameter in your table definitions or by setting the search_path in your PostgreSQL connection string.

For example, you can modify your PostgreSQL connection string to include a specific schema by using the options parameter:

postgres_connection_string = "postgresql://user:password@host:port/dbname?options=-csearch_path%3Dyour_schema"

Regarding the steps table, it seems there is a requirement for a disableFeedback column that should be nullable. You can add this column to your table definition to avoid the error you're encountering. Ensure that your table schema includes this column with a NULL default to prevent issues.

If you need further customization, you might need to extend or modify the SQLAlchemyDataLayer class to fit your specific requirements.

To continue talking to Dosu, mention @dosu.

[dokterbob]

@dosu You're confusing the Postgres for the SQLAlchemy conneciton string.

[dokterbob]

@cesi08 It's not currently supported functionality but we're open to receiving a PR, particularly if there's unit test coverage.

[dosubot]

To specify a schema in SQLAlchemy, you typically define it in your table models using the schema parameter. However, if you want to set a default schema for all operations, you can configure the search_path in your PostgreSQL connection string. This is done by including the options parameter in the connection string, which is a PostgreSQL-specific feature.

Here's how you can modify your connection string to set the search_path:

postgres_connection_string = "postgresql+asyncpg://user:password@host:port/dbname?options=-csearch_path%3Dyour_schema"

This approach uses the options parameter to set the search_path to your desired schema. Note that this is specific to PostgreSQL and not a feature of SQLAlchemy itself.

To continue talking to Dosu, mention @dosu.

[dn-scribe]

I tried this an got the following error from the migration app (chainlit-datalayer repo):

export DATABASE_URL=postgresql://admin:you-did-not-expect-this@localhost:5432/chainlit?options=-c%20search_path%3Dmyschema
npx prisma migrate deploy
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "chainlit", schema "public" at "localhost:5432"

2 migrations found in prisma/migrations

Error: Invariant violation: migration persistence is not initialized.
   0: schema_core::state::ApplyMigrations
             at schema-engine/core/src/state.rs:226

[dn-scribe]

I also did not work when removing the space after the -c

[github-actions]

This issue is stale because it has been open for 14 days with no activity.

[github-actions]

This issue was closed because it has been inactive for 7 days since being marked as stale.