As part of this work, a few schema migrations were done.
DateField and DateTimeField were migrated to BigIntegerField
This is because we've been storing unix epoch timestamps into these fields, which SQLite doesn't care, but Postgres and MySQL do not accept integers in date or datetime fields.
There's also additional logic in the migration scripts to avoid having to actually migrate from date/datetime to integer, as this requires casting and custom logic per database/
Some CharFields were migrated to TextFields. CharField has a default max length of 255, but SQLite doesn't enforce this length limit.
This does not migrate an existing SQLite DB onto a remote DB.
To test locally with Postgres,
Start a postgres instance docker run --name webui-postgres -e POSTGRES_PASSWORD=mysecretpassword -p:5433:5432 --rm postgres
Run the backend with a Postgres DATABASE_URL: DATABASE_URL=postgres://postgres:mysecretpassword@localhost:5433/postgres GLOBAL_LOG_LEVEL=DEBUG ./dev.sh
Changelog Entry
Added
🛢️ External Database Support: Connect to a custom SQLite or Postgres database using the DATABASE_URL environment variable.
🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.
## 📋 Pull Request Information
**Original PR:** https://github.com/open-webui/open-webui/pull/1791
**Author:** [@cheahjs](https://github.com/cheahjs)
**Created:** 4/27/2024
**Status:** ✅ Merged
**Merged:** 4/27/2024
**Merged by:** [@tjbck](https://github.com/tjbck)
**Base:** `dev` ← **Head:** `feat/external-db-support`
---
### 📝 Commits (2)
- [`e91a49c`](https://github.com/open-webui/open-webui/commit/e91a49c455580456bf35a674ec96c007ccd8d611) feat: add support for using postgres for the backend DB
- [`47a33ac`](https://github.com/open-webui/open-webui/commit/47a33acfeb8033d9c419c3d46f2c82b951bb7963) feat: show error toast if trying to download db on external db
### 📊 Changes
**19 files changed** (+350 additions, -24 deletions)
<details>
<summary>View changed files</summary>
📝 `backend/apps/litellm/main.py` (+12 -1)
📝 `backend/apps/web/internal/db.py` (+5 -4)
📝 `backend/apps/web/internal/migrations/001_initial_schema.py` (+105 -0)
📝 `backend/apps/web/internal/migrations/005_add_updated_at.py` (+53 -0)
➕ `backend/apps/web/internal/migrations/006_migrate_timestamps_and_charfields.py` (+130 -0)
📝 `backend/apps/web/models/auths.py` (+1 -1)
📝 `backend/apps/web/models/chats.py` (+3 -3)
📝 `backend/apps/web/models/documents.py` (+3 -3)
📝 `backend/apps/web/models/modelfiles.py` (+1 -1)
📝 `backend/apps/web/models/prompts.py` (+2 -2)
📝 `backend/apps/web/models/tags.py` (+1 -1)
📝 `backend/apps/web/models/users.py` (+2 -2)
📝 `backend/apps/web/routers/auths.py` (+2 -0)
📝 `backend/apps/web/routers/utils.py` (+8 -2)
📝 `backend/config.py` (+7 -0)
📝 `backend/constants.py` (+2 -0)
📝 `backend/requirements.txt` (+2 -0)
📝 `src/lib/apis/utils/index.ts` (+7 -3)
📝 `src/lib/components/admin/Settings/Database.svelte` (+4 -1)
</details>
### 📄 Description
## Pull Request Checklist
- [x] **Description:** Briefly describe the changes in this pull request.
- [x] **Changelog:** Ensure a changelog entry following the format of [Keep a Changelog](https://keepachangelog.com/) is added at the bottom of the PR description.
- [ ] **Documentation:** Have you updated relevant documentation?
- [ ] **Dependencies:** Are there any new dependencies? Have you updated the dependency versions in the documentation?
---
## Description
Addresses the application DB portion of https://github.com/open-webui/open-webui/issues/1397
Adds a new env var `DATABASE_URL` that can be used to point to a SQLite database, Postgres database, or MySQL database. I have only tested with SQLite and Postgres. The URL schema can be found at https://docs.peewee-orm.com/en/latest/peewee/playhouse.html#connect
As part of this work, a few schema migrations were done.
1. `DateField` and `DateTimeField` were migrated to `BigIntegerField`
* This is because we've been storing unix epoch timestamps into these fields, which SQLite doesn't care, but Postgres and MySQL do not accept integers in `date` or `datetime` fields.
* There's also additional logic in the migration scripts to avoid having to actually migrate from `date`/`datetime` to `integer`, as this requires casting and custom logic per database/
2. Some `CharField`s were migrated to `TextFields`. `CharField` has a default max length of 255, but SQLite doesn't enforce this length limit.
This **does not** migrate an existing SQLite DB onto a remote DB.
To test locally with Postgres,
1. Start a postgres instance `docker run --name webui-postgres -e POSTGRES_PASSWORD=mysecretpassword -p:5433:5432 --rm postgres`
4. Run the backend with a Postgres DATABASE_URL: `DATABASE_URL=postgres://postgres:mysecretpassword@localhost:5433/postgres GLOBAL_LOG_LEVEL=DEBUG ./dev.sh`
---
### Changelog Entry
### Added
- **🛢️ External Database Support**: Connect to a custom SQLite or Postgres database using the `DATABASE_URL` environment variable.
---
<sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>
Blocking a user prevents them from interacting with repositories, such as opening or commenting on pull requests or issues. Learn more about blocking a user.
📋 Pull Request Information
Original PR: https://github.com/open-webui/open-webui/pull/1791
Author: @cheahjs
Created: 4/27/2024
Status: ✅ Merged
Merged: 4/27/2024
Merged by: @tjbck
Base:
dev← Head:feat/external-db-support📝 Commits (2)
e91a49cfeat: add support for using postgres for the backend DB47a33acfeat: show error toast if trying to download db on external db📊 Changes
19 files changed (+350 additions, -24 deletions)
View changed files
📝
backend/apps/litellm/main.py(+12 -1)📝
backend/apps/web/internal/db.py(+5 -4)📝
backend/apps/web/internal/migrations/001_initial_schema.py(+105 -0)📝
backend/apps/web/internal/migrations/005_add_updated_at.py(+53 -0)➕
backend/apps/web/internal/migrations/006_migrate_timestamps_and_charfields.py(+130 -0)📝
backend/apps/web/models/auths.py(+1 -1)📝
backend/apps/web/models/chats.py(+3 -3)📝
backend/apps/web/models/documents.py(+3 -3)📝
backend/apps/web/models/modelfiles.py(+1 -1)📝
backend/apps/web/models/prompts.py(+2 -2)📝
backend/apps/web/models/tags.py(+1 -1)📝
backend/apps/web/models/users.py(+2 -2)📝
backend/apps/web/routers/auths.py(+2 -0)📝
backend/apps/web/routers/utils.py(+8 -2)📝
backend/config.py(+7 -0)📝
backend/constants.py(+2 -0)📝
backend/requirements.txt(+2 -0)📝
src/lib/apis/utils/index.ts(+7 -3)📝
src/lib/components/admin/Settings/Database.svelte(+4 -1)📄 Description
Pull Request Checklist
Description
Addresses the application DB portion of https://github.com/open-webui/open-webui/issues/1397
Adds a new env var
DATABASE_URLthat can be used to point to a SQLite database, Postgres database, or MySQL database. I have only tested with SQLite and Postgres. The URL schema can be found at https://docs.peewee-orm.com/en/latest/peewee/playhouse.html#connectAs part of this work, a few schema migrations were done.
DateFieldandDateTimeFieldwere migrated toBigIntegerFielddateordatetimefields.date/datetimetointeger, as this requires casting and custom logic per database/CharFields were migrated toTextFields.CharFieldhas a default max length of 255, but SQLite doesn't enforce this length limit.This does not migrate an existing SQLite DB onto a remote DB.
To test locally with Postgres,
docker run --name webui-postgres -e POSTGRES_PASSWORD=mysecretpassword -p:5433:5432 --rm postgresDATABASE_URL=postgres://postgres:mysecretpassword@localhost:5433/postgres GLOBAL_LOG_LEVEL=DEBUG ./dev.shChangelog Entry
Added
DATABASE_URLenvironment variable.🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.