Skip to content

Commit 52901a9

Browse files
newbe36524hagicode-ai
newbe36524
and
hagicode-ai
committed
docs(blog): add February and March engineering articles
主要变更： - 添加 .NET Core 双数据库实战文章 - PostgreSQL 与 SQLite 的优雅融合 - 最佳实践和性能优化 - 添加 HagiCode React 19 hydration 相关文章 - Splash screen 实现方案 - 添加 Starlight 文档集成 Microsoft Clarity - 添加 AI 组合提交实现详解 - 添加豆包语音识别相关文章 - WebSocket 代理实现 - 热词支持功能 - 添加 Codex SDK 相关文章 - TypeScript 到 C# 移植指南 - 控制台消息解析 - 添加多 AI Provider 相关文章 - 切换互操作实现 - 架构设计 - 添加 C# CodeBuddy CLI 集成指南 - 添加 imgbin CLI 工具资产管理 - 添加主要职业管理功能 - 添加 AI 编程游戏化实践 - 添加 HagiCode AI Agent Party 相关文章 - 多 Agent 协作配置实战 - 添加 .NET 代码保护相关文章 - 从混淆到 VMProtect - 添加 HagiCode Hermes Agent Core - 添加 HagiCode libs 统一 CLI 集成 - 添加 AI 生产力计算器科学分析 Co-Authored-By: Hagicode <[email protected]> Signed-off-by: newbe36524 <[email protected]>
1 parent decefe6 commit 52901a9

20 files changed

Lines changed: 7547 additions & 0 deletions

‎src/content/docs/en/blog/2026-02-01-net-core-双数据库实战优雅融合-postgresql-与-sqlite-的最佳实践.mdx‎

Lines changed: 226 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,226 @@
1+
---
2+
title: ".NET Core Dual-Database in Practice: Best Practices for Elegantly Combining PostgreSQL and SQLite"
3+
date: 2026-02-01
4+
tags: [ASP.NET Core, PostgreSQL, SQLite, Database Architecture, Orleans]
5+
---
6+
7+
## .NET Core Dual-Database in Practice: Let PostgreSQL and SQLite Coexist Peacefully
8+
9+
> When building modern applications, we often face this trade-off: development environments want something lightweight and convenient, while production environments demand high concurrency and high availability. This article shares how to elegantly support both PostgreSQL and SQLite in a .NET Core project and implement the best practice of "SQLite for development, PostgreSQL for production."
10+
11+
12+
## Background
13+
14+
In software development, differences between environments have always been one of the hardest problems for engineering teams. Take the **HagiCode** platform we are building as an example: it is an AI-assisted development system based on ASP.NET Core 10 and React, with Orleans integrated internally for distributed state management. The stack is modern and fairly sophisticated.
15+
16+
Early in the project, we ran into a classic engineering pain point: developers wanted the local environment to work out of the box, without having to install and configure a heavy PostgreSQL database. But in production, we needed to handle high-concurrency writes and complex JSON queries, and that is exactly where lightweight SQLite starts to show its limits.
17+
18+
How can we keep a single codebase while allowing the application to benefit from SQLite's portability like a desktop app, and also leverage PostgreSQL's powerful performance like an enterprise-grade service? That is the core question this article explores.
19+
20+
## About HagiCode
21+
22+
The dual-database adaptation approach shared in this article comes directly from our hands-on experience in the **HagiCode** project. HagiCode is a next-generation development platform that integrates AI prompt management and the OpenSpec workflow. It was precisely to balance developer experience with production stability that we arrived at this proven architectural pattern.
23+
24+
Feel free to visit our GitHub repository to see the full project: [HagiCode-org/site](https://github.com/HagiCode-org/site).
25+
26+
## Core Topic 1: Architecture Design and Unified Abstraction
27+
28+
To support two databases in .NET Core, the key idea is to depend on abstractions rather than concrete implementations. We need to separate database selection from business code and let the configuration layer decide.
29+
30+
### Design Approach
31+
32+
1. **Unified interface**: All business logic should depend on the `DbContext` base class or custom interfaces, rather than a specific `PostgreSqlDbContext`.
33+
2. **Configuration-driven**: Use configuration items in `appsettings.json` to dynamically decide which database provider to load at application startup.
34+
3. **Feature isolation**: Add adaptation logic for PostgreSQL-specific capabilities, such as JSONB, so the application can still degrade gracefully on SQLite.
35+
36+
### Code Implementation: Dynamic Context Configuration
37+
38+
In ASP.NET Core's `Program.cs`, we should not hard-code `UseNpgsql` or `UseSqlite`. Instead, we should read configuration and decide dynamically.
39+
40+
First, define the configuration class:
41+
42+
```csharp
43+
public class DatabaseSettings
44+
{
45+
public const string SectionName = "Database";
46+
47+
// Database type: PostgreSQL or SQLite
48+
public string DbType { get; set; } = "PostgreSQL";
49+
50+
// Connection string
51+
public string ConnectionString { get; set; } = string.Empty;
52+
}
53+
```
54+
55+
Then register the service in `Program.cs` based on configuration:
56+
57+
```csharp
58+
// Read configuration
59+
var databaseSettings = builder.Configuration.GetSection(DatabaseSettings.SectionName).Get<DatabaseSettings>();
60+
61+
// Register DbContext
62+
builder.Services.AddDbContext<ApplicationDbContext>(options =>
63+
{
64+
if (databaseSettings?.DbType?.ToLower() == "sqlite")
65+
{
66+
// SQLite configuration
67+
options.UseSqlite(databaseSettings.ConnectionString);
68+
69+
// Handling SQLite's concurrent write limitations
70+
// Note: in production, enabling WAL mode is recommended to improve concurrency performance
71+
}
72+
else
73+
{
74+
// PostgreSQL configuration (default)
75+
options.UseNpgsql(databaseSettings.ConnectionString, npgsqlOptions =>
76+
{
77+
// Enable JSONB support, which is very useful when handling AI conversation records
78+
npgsqlOptions.UseJsonNet();
79+
});
80+
81+
// Configure connection pool retry policy
82+
options.EnableRetryOnFailure(3);
83+
}
84+
});
85+
```
86+
87+
## Core Topic 2: Handling Differences and Migration Strategy
88+
89+
Although PostgreSQL and SQLite both support the SQL standard, they differ significantly in specific capabilities and behavior. If these differences are not handled carefully, you can easily end up with the awkward situation where everything works locally but fails after deployment.
90+
91+
### 1. Handling JSON Types
92+
93+
In HagiCode, we need to store a large amount of prompts and AI metadata, which usually involves JSON columns.
94+
- **PostgreSQL**: Has a native `JSONB` type with excellent query performance.
95+
- **SQLite**: Does not have a native JSON type (newer versions include the JSON1 extension, but object mapping still differs), so data is usually stored as TEXT.
96+
97+
**Solution**:
98+
In EF Core entity mapping, we configure it as a convertible type.
99+
100+
```csharp
101+
protected override void OnModelCreating(ModelBuilder modelBuilder)
102+
{
103+
base.OnModelCreating(modelBuilder);
104+
105+
// Configure entity
106+
modelBuilder.Entity<PromptTemplate>(entity =>
107+
{
108+
entity.Property(e => e.Metadata)
109+
.HasColumnType("jsonb") // PG uses jsonb
110+
.HasConversion(
111+
v => JsonSerializer.Serialize(v, (JsonSerializerOptions)null),
112+
v => JsonSerializer.Deserialize<Dictionary<string, object>>(v, (JsonSerializerOptions)null)
113+
);
114+
});
115+
}
116+
```
117+
118+
When SQLite is used, `HasColumnType("jsonb")` may be ignored or trigger a warning. However, because `HasConversion` is configured, the data is still serialized and deserialized correctly as strings stored in a TEXT field, ensuring compatibility.
119+
120+
### 2. Separating Migration Strategies
121+
122+
Never try to make one set of Migration scripts work for both PostgreSQL and SQLite at the same time. Differences in primary key generation strategies, index syntax, and other database details will inevitably cause failures.
123+
124+
**Recommended practice**:
125+
Maintain two migration branches or projects. In the HagiCode development workflow, this is how we handle it:
126+
127+
1. **Development stage**: Work mainly with SQLite. Use `Add-Migration Init_Sqlite -OutputDir Migrations/Sqlite`.
128+
2. **Adaptation stage**: After developing a feature, switch the connection string to PostgreSQL and run `Add-Migration Init_Postgres -OutputDir Migrations/Postgres`.
129+
3. **Automation scripts**: Write a simple PowerShell or Bash script to automatically apply the correct migration based on the current environment variable.
130+
131+
```bash
132+
# Pseudocode for simple deployment logic
133+
if [ "$DATABASE_PROVIDER" = "PostgreSQL" ]; then
134+
dotnet ef database update --project Migrations.Postgres
135+
else
136+
dotnet ef database update --project Migrations.Sqlite
137+
fi
138+
```
139+
140+
## Core Topic 3: Lessons Learned from HagiCode in Production
141+
142+
While refactoring **HagiCode** from a single-database model to dual-database support, we hit a few pitfalls and gathered some important lessons that may help you avoid the same mistakes.
143+
144+
### 1. Differences in Concurrency and Transactions
145+
146+
PostgreSQL uses a server-client architecture and supports high-concurrency writes with powerful transaction isolation levels. SQLite uses file locking, so write operations lock the entire database file unless WAL mode is enabled.
147+
148+
**Recommendation**:
149+
When writing business logic that involves frequent writes, such as real-time saving of a user's editing state, you must take SQLite's locking model into account. When designing the OpenSpec collaboration module in **HagiCode**, we introduced a "merge before write" mechanism to reduce the frequency of direct database writes, allowing us to maintain good performance on both databases.
150+
151+
### 2. Lifecycle Management of Connection Strings
152+
153+
Establishing a PostgreSQL connection is relatively expensive and depends on connection pooling. SQLite connections are very lightweight, but if they are not released promptly, file locks may cause later operations to time out.
154+
155+
In `Program.cs`, we can fine-tune behavior for each database:
156+
157+
```csharp
158+
if (databaseSettings?.DbType?.ToLower() == "sqlite")
159+
{
160+
// SQLite: keeping connections open can improve performance, but watch out for file locks
161+
options.UseSqlite(connectionString, sqliteOptions =>
162+
{
163+
// Set command timeout
164+
sqliteOptions.CommandTimeout(30);
165+
});
166+
}
167+
else
168+
{
169+
// PG: make full use of connection pooling
170+
options.UseNpgsql(connectionString, npgsqlOptions =>
171+
{
172+
npgsqlOptions.MaxBatchSize(100);
173+
npgsqlOptions.CommandTimeout(30);
174+
});
175+
}
176+
```
177+
178+
### 3. The Importance of Test Coverage
179+
180+
Many developers, including some early members of our team, tend to make one common mistake: running unit tests only in the development environment, which is usually SQLite.
181+
182+
In HagiCode's CI/CD pipeline, we enforced a GitHub Actions step to make sure every pull request runs PostgreSQL integration tests.
183+
184+
```yaml
185+
# Example snippet from .github/workflows/test.yml
186+
- name: Run Integration Tests (PostgreSQL)
187+
run: |
188+
docker-compose up -d db_postgres
189+
dotnet test --filter "Category=Integration"
190+
```
191+
192+
This helped us catch countless bugs related to SQL syntax differences and case sensitivity.
193+
194+
## Conclusion
195+
196+
By introducing an abstraction layer and configuration-driven dependency injection, we successfully implemented a dual-track PostgreSQL and SQLite setup in the **HagiCode** project. This not only greatly lowered the onboarding barrier for new developers by removing the need to install PostgreSQL, but also provided strong performance guarantees for production.
197+
198+
To recap the key points:
199+
1. **Abstraction first**: Business code should not depend on concrete database implementations.
200+
2. **Separate configuration**: Use different `appsettings.json` files for development and production.
201+
3. **Separate migrations**: Do not try to make one Migration set work everywhere.
202+
4. **Feature degradation**: Prioritize compatibility in SQLite and performance in PostgreSQL.
203+
204+
This architectural pattern is not only suitable for HagiCode, but for any .NET project that needs to strike a balance between lightweight development and heavyweight production.
205+
206+
---
207+
208+
If this article helped you, feel free to give us a Star on GitHub, or experience the efficient development workflow brought by **HagiCode** directly:
209+
- Give us a Star on GitHub: [github.com/HagiCode-org/site](https://github.com/HagiCode-org/site)
210+
- Visit the official website to learn more: [hagicode.com](https://hagicode.com)
211+
- Watch the 30-minute hands-on demo: [www.bilibili.com/video/BV1pirZBuEzq/](https://www.bilibili.com/video/BV1pirZBuEzq/)
212+
- One-click installation experience: [hagicode.com/installation/docker-compose](https://hagicode.com/installation/docker-compose)
213+
214+
The public beta has started. Welcome to install it and give it a try!
215+
216+
217+
218+
---
219+
220+
Thank you for reading. If you found this article useful, please click the like button below 👍 so more people can discover it.
221+
222+
This content was created with AI-assisted collaboration, reviewed by me, and reflects my own views and position.
223+
224+
- **Author:** [newbe36524](https://www.newbe.pro)
225+
- **Article Link:** [https://hagicode.com/blog/2026-02-01-dotnet-core-dual-database-postgresql-sqlite/](https://hagicode.com/blog/2026-02-01-dotnet-core-dual-database-postgresql-sqlite/)
226+
- **Copyright Notice:** Unless otherwise stated, all articles on this blog are licensed under BY-NC-SA. Please indicate the source when reposting.

0 commit comments

Comments
 (0)