✨ feat: support server db mode with Postgres / Drizzle ORM / tRPC

[arvinxx]

💻 变更类型 | Change Type

• [x] ✨ feat
• [ ] 🐛 fix
• [ ] ♻️ refactor
• [ ] 💄 style
• [ ] 🔨 chore
• [ ] ⚡️ perf
• [ ] 📝 docs

🔀 变更说明 | Description of Change

整体进展

LobeChat 服务端 DB 版本功能实现。目前已经完成大部分核心链路功能，先放一版出来和大家交流一下 db 相关实现。目前完成度为 90% ，还剩余模块：

• [ ] 用户配置相关实现
• [ ] 上传文件相关的乐观更新体验优化

由于我之前并没有写过 SQL 和 Postgres ，所以可能整体服务端实现上仍然存在瑕疵，也可能存在 critical 的问题， 欢迎大家点评指正。

技术栈

• DB：Postgres ，默认采用 Neon；
• ORM: Dirzzle ORM
• Auth: Clerk
• server router: trpc

由于基于 Drizzle ORM， 非常期待看到未来拓展到其他 Postgres DB、甚至 SQLite、MySQL 。 以及有待 @cy948 帮忙实现 next-auth 相关的 db 鉴权方案。

待解决工程问题

工程链路上还有几个问题待解决：

• [ ] 1. 数据库无感 migration；
• [ ] 2. 带有真实数据库的 ci/cd workflow；

服务端相关的配置

# Postgres DB Url
DATABASE_URL=

# 指定 service mode 为 server 
NEXT_PUBLIC_SERVICE_MODE=server

# Clerk 相关配置
NEXT_PUBLIC_CLERK_PUBLISHABLE_KEY=
CLERK_SECRET_KEY=
CLERK_WEBHOOK_SECRET=

# S3 相关配置（上传图片必须）
NEXT_PUBLIC_S3_DOMAIN=

S3_ACCESS_KEY_ID=
S3_SECRET_ACCESS_KEY=
S3_ENDPOINT=
S3_BUCKET=

📝 补充信息 | Additional Information

• https://github.com/lobehub/lobe-chat/discussions/1851

• close #1861
• close #1573
• close #1354
• close #1547

[vercel[bot]]

The latest updates on your projects. Learn more about Vercel for Git ↗︎

Name	Status	Preview	Comments	Updated (UTC)
lobe-chat-community	✅ Ready (Inspect)	Visit Preview	💬 Add feedback	Jun 16, 2024 10:59am

[lobehubbot]

👍 @arvinxx

Thank you for raising your pull request and contributing to our Community Please make sure you have followed our contributing guidelines. We will review it as soon as possible. If you encounter any problems, please feel free to connect with us.
非常感谢您提出拉取请求并为我们的社区做出贡献，请确保您已经遵循了我们的贡献指南，我们会尽快审查它。 如果您遇到任何问题，请随时与我们联系。

[Matinal111]

目前是可以配置服务端数据库了吗

[lobehubbot]

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

---

Is it possible to configure the server database now?

[arvinxx]

目前是可以配置服务端数据库了吗

@cfwl1111 等这个 PR 合并后，就可以了

[lobehubbot]

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

---

Is it possible to configure the server database now?

@cfwl1111 After this PR is merged, it will be fine

[coderabbitai[bot]]

[!IMPORTANT]

Review Skipped

Draft detected.

Please check the settings in the CodeRabbit UI or the .coderabbit.yaml file in this repository. To trigger a single review, invoke the @coderabbitai review command.

You can disable this status message by setting the reviews.review_status to false in the CodeRabbit configuration file.

---

Thank you for using CodeRabbit. We offer it for free to the OSS community and would appreciate your support in helping us grow. If you find it useful, would you consider giving us a shout-out on your favorite social media?

Share

• X
• Mastodon
• Reddit
• LinkedIn

Tips

Chat

There are 3 ways to chat with CodeRabbit:

• Review comments: Directly reply to a review comment made by CodeRabbit. Example:
  • I pushed a fix in commit <commit_id>.
  • Generate unit testing code for this file.
  • Open a follow-up GitHub issue for this discussion.
• Files and specific lines of code (under the "Files changed" tab): Tag @coderabbitai in a new review comment at the desired location with your query. Examples:
  • @coderabbitai generate unit testing code for this file.
  • @coderabbitai modularize this function.
• PR comments: Tag @coderabbitai in a new PR comment to ask questions about the PR branch. For the best results, please provide a very specific query, as very limited context is provided in this mode. Examples:
  • @coderabbitai generate interesting stats about this repository and render them as a table.
  • @coderabbitai show all the console.log statements in this repository.
  • @coderabbitai read src/utils.ts and generate unit testing code.
  • @coderabbitai read the files in the src/scheduler package and generate a class diagram using mermaid and a README in the markdown format.

Note: Be mindful of the bot's finite context window. It's strongly recommended to break down tasks such as reading entire modules into smaller chunks. For a focused discussion, use review comments to chat about specific files and their changes, instead of using the PR comments.

CodeRabbit Commands (invoked as PR comments)

• @coderabbitai pause to pause the reviews on a PR.
• @coderabbitai resume to resume the paused reviews.
• @coderabbitai review to trigger a review. This is useful when automatic reviews are disabled for the repository.
• @coderabbitai resolve resolve all the CodeRabbit review comments.
• @coderabbitai help to get help.

Additionally, you can add @coderabbitai ignore anywhere in the PR description to prevent this PR from being reviewed.

CodeRabbit Configration File (.coderabbit.yaml)

• You can programmatically configure CodeRabbit by adding a .coderabbit.yaml file to the root of your repository.
• Please see the configuration documentation for more information.
• If your editor has YAML language server enabled, you can add the path at the top of this file to enable auto-completion and validation: # yaml-language-server: $schema=https://coderabbit.ai/integrations/schema.v2.json

Documentation and Community

• Visit our Documentation for detailed information on how to use CodeRabbit.
• Join our Discord Community to get help, request features, and share feedback.
• Follow us on X/Twitter for updates and announcements.

[Matinal111]

目前是可以配置服务端数据库了吗

@cfwl1111 等这个 PR 合并后，就可以了

服务端 Postgres 准备好了，期待它的到来

[lobehubbot]

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

---

Is it possible to configure the server database now?

@cfwl1111 After this PR is merged, it will be fine

Server-side Postgres is ready and looking forward to its arrival

[lee88688]

图片的保存可以考虑支持直接存储在PostgreSQL数据库中。自部署还需要单独起一个S3兼容的后端服务管理起来更复杂，特别是大部分自部署用户的使用强度不是那么高，使用数据库完全没问题。

[lobehubbot]

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

---

The saving of pictures can be considered to be directly stored in the PostgreSQL database. Self-deployment also requires a separate S3-compatible back-end service, which is more complicated to manage, especially since most self-deployment users are not so intensive in usage, and there is no problem using the database.

[arvinxx]

图片的保存可以考虑支持直接存储在PostgreSQL数据库中。自部署还需要单独起一个S3兼容的后端服务管理起来更复杂，特别是大部分自部署用户的使用强度不是那么高，使用数据库完全没问题。

不建议，后面还要支持各种文件上传的。如果不想承担这个复杂度，要么仍然可以用客户端数据库db的模式，不需要s3。 或者用未来我们host的cloud 版

[lobehubbot]

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

---

The saving of images can be considered to be directly stored in the PostgreSQL database. Self-deployment also requires a separate S3-compatible back-end service, which is more complicated to manage, especially since most self-deployment users are not so intensive in usage, and there is no problem using the database.

Not recommended, it will support various file uploads later. If you don’t want to bear this complexity, you can either use the locally deployed version without s3, or use the cloud version of our host in the future.

[codecov[bot]]

Codecov Report

Attention: Patch coverage is 87.90582% with 339 lines in your changes missing coverage. Please review.

Project coverage is 93.25%. Comparing base (6f5be5d) to head (a418a5e).

Files	Patch %	Lines
src/services/session/server.ts	47.97%	77 Missing :warning:
src/services/message/server.ts	45.16%	51 Missing :warning:
src/services/plugin/server.ts	19.56%	37 Missing :warning:
src/services/topic/server.ts	48.52%	35 Missing :warning:
src/database/server/models/session.ts	90.34%	28 Missing :warning:
src/database/server/modules/DataImporter/index.ts	91.89%	27 Missing :warning:
src/services/file/server.ts	46.66%	24 Missing :warning:
src/services/user/server.ts	28.57%	20 Missing :warning:
src/libs/trpc/client.ts	63.46%	19 Missing :warning:
src/database/server/models/message.ts	96.29%	14 Missing :warning:
... and 3 more

Additional details and impacted files

@@               Coverage Diff               @@
##           release/1.0    #2556      +/-   ##
===============================================
- Coverage        94.11%   93.25%   -0.86%     
===============================================
  Files              359      380      +21     
  Lines            20953    23798    +2845     
  Branches          2495     2545      +50     
===============================================
+ Hits             19720    22193    +2473     
- Misses            1233     1605     +372     

Flag	Coverage Δ
app	93.25% <87.90%> (?)
server	65.86% <96.31%> (?)

Flags with carried forward coverage won't be shown. Click here to find out more.

:umbrella: View full report in Codecov by Sentry.
:loudspeaker: Have feedback on the report? Share it here.

[lobehubbot]

❤️ Great PR @arvinxx ❤️

The growth of project is inseparable from user feedback and contribution, thanks for your contribution! If you are interesting with the lobehub developer community, please join our discord and then dm @arvinxx or @canisminor1990. They will invite you to our private developer channel. We are talking about the lobe-chat development or sharing ai newsletter around the world.
项目的成长离不开用户反馈和贡献，感谢您的贡献! 如果您对 LobeHub 开发者社区感兴趣，请加入我们的 discord，然后私信 @arvinxx 或 @canisminor1990。他们会邀请您加入我们的私密开发者频道。我们将会讨论关于 Lobe Chat 的开发，分享和讨论全球范围内的 AI 消息。

[xingpingcn]

这个新特性是不是在文档中没有更新？

客户端数据库db的模式

这个又应该如何配置？

[lobehubbot]

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

---

Is this new feature not updated in the documentation?

Schema of client database db

How should this be configured?

[ZeroDeng01]

CLERK_WEBHOOK_SECRET这个配置后是否需要去clerk配置Endpoint？我clerk的相关密钥已经做了配置，用户也可以登录，但是我查看数据库并没有新建clerk的用户信息，现在我的项目完全无法使用。 我查询日志，就是因为在数据库获取不到用户信息。应为users表为空： @arvinxx

[lobehubbot]

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

---

After configuring CLERK_WEBHOOK_SECRET, do I need to go to clerk to configure the Endpoint? The relevant keys of my clerk have been configured and users can log in, but when I checked the database, there was no new user information for the clerk. Now my project is completely unusable. I checked the log because I couldn't get the user information in the database. The users table should be empty: @arvinxx

[arvinxx]

CLERK_WEBHOOK_SECRET这个配置后是否需要去clerk配置Endpoint？我clerk的相关密钥已经做了配置，用户也可以登录，但是我查看数据库并没有新建clerk的用户信息，现在我的项目完全无法使用。

需要的，类似这样：

@ZeroDeng01 @xingpingcn 目前文档还没完善，有问题可以在 Discord 里咨询，我们这几天会把部署指南补充完善

[cmj2002]

以及有待 @cy948 帮忙实现 next-auth 相关的 db 鉴权方案。

该部分工作是否有相关联的 issue/discussion/pull request ?

[lobehubbot]

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

---

And waiting for @cy948 to help implement next-auth related db authentication scheme.

Is there an associated issue/discussion/pull request for this part of the work?

[arvinxx]

该部分工作是否有相关联的 issue/discussion/pull request ?

@cy948 感觉可以先写一篇 RFC 来讨论下？

[lobehubbot]

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

---

Is there an associated issue/discussion/pull request for this part of the work?

@cy948 I feel like I can write an RFC to discuss it first?

[cy948]

@arvinxx 我试完第一个方案之后写个rfc描述下现在的情况吧

[lobehubbot]

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

---

@arvinxx After I try the first solution, I will write an RFC to describe the current situation.

[gaarry]

用上数据库了，朋友也注册我的网站，体验很棒～

[lobehubbot]

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

---

I have used the database and my friends have also registered on my website. The experience is great~

[gaspire]

用上数据库了，朋友也注册我的网站，体验很棒～

分享下 教程，谢谢！

[lobehubbot]

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

---

I have used the database, and my friends have also registered on my website, and the experience is great ~

Share the tutorial, thank you!