Unified SQL data layer

[niklasdiehm]

Is your feature request related to a problem? Please describe.
This is a duplicate of this issue that was closed 10 months ago. I think now is a good time to think about it again after there were several changes in how the data layer is added to the project.

Pull request #836 was very nice to support sqlalchemy based data persistence.
However, the sqlalchemy data layer only works with postgres db. Whenever a change is made in the data layer, one must change the respective self written file for mssql, mysql, ...

Describe the solution you'd like
If you would change the implementation of the functions from query-strings to an more generic ORM-based approch (see SQL alchemy ORM, the sqlalchemy data layer would support any database supported by sqlalchemy with only the need to pass a connection string or engine object.

Describe alternatives you've considered
Alternatives would be to rename the sqlalchemy data layer to postgres data layer and add files for other databases, e.g. mssql and mysql. This would however require even more work to do when changing something at the data layer.

Additional context
I am willing to contribute to a feature request with maybe some help needed

[hayescode]

@niklasdiehm I want to switch use to use FastAPI's sqlmodel -> https://github.com/fastapi/sqlmodel

This requires us to switch the backend types (steps, elements, threads, etc.) to Pydantic BaseModels 1st, and we'll need schema migration probably via alembic.

The only downside to this is that there doesn't seem to be any ORM (sqlmodel, sqlalchemy, etc.) that works with NoSQL like how DynamoDB is.

We welcome all contributions towards this goal!

[asvishnyakov]

@hayescode I think it should just be multi-layer abstractions, and some implementations, like ORM, may need more layers than others, such as NoSQL solutions

[niklasdiehm]

@hayescode @asvishnyakov I suggest to create a middle-layer which is independent of the actual database implementation. With that, we can create the sqlmodel ORM models specific for relational databases. For the middleware, i suggest to create a repository for interacting with the data (e.g. user, thread) and then the actual implementations of the repositories for sqlmodel and dynamodb. After that, we can go on with alembic (requires that we already have the sqlmodel). Finally, i would add tests and documentation. If everything goes fine, we can deprecate the existing SQLAlchemyDataLayer. What do you think about that?

[hayescode]

I'm thinking of something like this:

from sqlmodel import SQLModel, Field
from pydantic import ConfigDict
from pydantic.alias_generators import to_camel
from typing import Optional

class Thread(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    message_count: int = Field(default=0)

    model_config = ConfigDict(
        alias_generator=to_camel,
        populate_by_name=True,
    )

my_thread = Thread(thread_id=123, message_count=10)

snake_case_dict = my_thread.model_dump()
print(f"Snake case: {snake_case_dict}")

camel_case_dict = my_thread.model_dump(by_alias=True)
print(f"Camel case: {camel_case_dict}")

Snake case: {'message_count': 10, 'id': None}
Camel case: {'messageCount': 10, 'id': None}

This would give us a single interface for threads in this example, that can be both snake case for the python backend and camel case for the frontend and also be the classes used in the ORM. That would mean no more Thread/ThreadDict, backwards-compatibility, and simplify the ORM/migrations. Thoughts?

[hayescode]

@niklasdiehm @asvishnyakov i started a very rough draft of the design I'm thinking of. This will not work as-is, but it's a start. I hope we can all collaborate on this and come up with a long-lasting design and enable much faster development going forward.

#2469

[asvishnyakov]

So, this is what I know as the "best" option from my experience. It may require some initial setup and significant refactoring, but it costs almost nothing in maintenance, which is our priority now as a community-maintained project.

• Migrations

  • Migrations are applied automatically at app startup. This allows us to change the DB schema without requiring users to apply them manually, enabling more frequent updates, fixes, etc.
  • Migrations have an option to be delivered separately before app start, to allow blue/green deployments.
  • Manual migrations (raw SQL or similar) supported.
  • Migrations should be safe to run multiple times without breaking state (check if already applied)
  • Migration downgrade may be supported, but that’s optional and can be left to the deployment person’s responsibility (i.e., backing up the DB).

• Compatibility

  • Users from both Chainlit and SQLAlchemy data layers are able to migrate into this layer without extra steps.

• ORM usage

  • There are two model layers:
    • Domain models (for public API, services, etc.)
    • Data layer models
  • Multiple DB providers (Postgres, SQL Server, MySQL, …) are supported and tested.
  • Strings are Unicode.
  • Timestamps are in ISO 8601 UTC.
  • Language/culture info follows an established spec, preferably BCP 47.

• Lifecycle service

  • A separate service (if not provided by the ORM) is responsible for handling creation/update metadata (timestamps, author, etc.) on object creation or modification.

• Validation

  • All models are validated.

• Tests

  • The current behavior and the behavior after migration of all data layers covered.
  • CI/CD test migrations and core operations on different db providers (Postgres, MySQL, SQL Server...)

[github-actions]

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

[asvishnyakov]

Keep it