Database Configuration

Relevant source files

• store/db/db.go
• store/db/mysql/instance_setting.go
• store/db/mysql/mysql.go
• store/db/postgres/instance_setting.go
• store/db/postgres/postgres.go
• store/db/sqlite/functions.go
• store/db/sqlite/instance_setting.go
• store/db/sqlite/sqlite.go
• store/migration/mysql/LATEST.sql
• store/migration/postgres/LATEST.sql
• store/migration/sqlite/0.26/02__drop_indexes.sql
• store/migration/sqlite/LATEST.sql
• store/test/migrator_test.go
• store/test/store.go

This document explains how to configure and connect memos to different database backends. Memos supports three database systems: SQLite (default), MySQL, and PostgreSQL. The system uses a driver-based architecture to provide a unified interface for database operations.

---

Overview

Memos uses a store.Driver interface to abstract database operations. The backend selects the appropriate driver based on the configuration profile provided at startup. The NewDBDriver function in store/db/db.go serves as the factory for instantiating these drivers.

Driver Factory and Implementation Association

The following diagram shows how the profile.Profile configuration maps to specific implementation structs and how the store.Store orchestrates migrations.

Sources: store/db/db.go13-32 store/db/sqlite/sqlite.go16-24 store/db/mysql/mysql.go14-20 store/db/postgres/postgres.go16-21

---

Supported Databases

Database	Driver Name	Implementation Class	Notes
SQLite	sqlite	sqlite.DB	Uses modernc.org/sqlite (CGO-free). Supports WAL mode.
MySQL	mysql	mysql.DB	Requires multiStatements=true for migrations.
PostgreSQL	postgres	postgres.DB	Uses lib/pq driver.

Sources: store/db/db.go18-27 store/db/sqlite/sqlite.go16-19 store/db/mysql/mysql.go14-18 store/db/postgres/postgres.go16-19

---

SQLite Configuration

SQLite is the default backend. The implementation optimizes for concurrency and provides custom Unicode support.

Connection Parameters

The driver automatically appends several PRAGMAs to the DSN in sqlite.NewDB to ensure stability and performance:

• foreign_keys(0): Explicitly manages constraints (currently disabled by default).
• journal_mode(WAL): Enables Write-Ahead Logging for better concurrency.
• busy_timeout(10000): Sets a 10-second wait for locked databases.
• mmap_size(0): Disables memory mapping to prevent OOM errors.

Custom SQLite Functions

Because the modernc.org/sqlite driver lacks built-in ICU or Regexp extensions, Memos registers Go-backed scalar functions:

• memos_unicode_lower: Provides Unicode case folding via golang.org/x/text/cases.
• regexp: Enables the value REGEXP pattern operator using Go's regexp package and a regexpCache for performance.

Sources: store/db/sqlite/sqlite.go53-56 store/db/sqlite/functions.go29-46 store/db/sqlite/functions.go59-89 store/db/sqlite/functions.go92-104

---

MySQL Configuration

MySQL support requires specific DSN configuration to handle the multi-statement migration scripts used by Memos.

Connection String Format

The mysql.NewDB function calls mergeDSN to ensure the connection allows multiple SQL statements:

Database Size and Status

The driver implements GetDatabaseSize by querying information_schema.tables for the sum of data_length and index_length for the current database.

Sources: store/db/mysql/mysql.go20-41 store/db/mysql/mysql.go61-70 store/db/mysql/mysql.go72-80

---

PostgreSQL Configuration

PostgreSQL utilizes the lib/pq driver and leverages advanced types like JSONB for the memo.payload field.

Initialization Check

The PostgreSQL driver checks if the database is already provisioned by looking for the memo table:

Database Size

It uses the native pg_database_size(current_database()) function to report storage usage.

Sources: store/db/postgres/postgres.go21-40 store/db/postgres/postgres.go50-57 store/db/postgres/postgres.go59-67

---

Database Schema and Migrations

Memos features an automated migration system that runs on startup. It handles database schema versioning and upgrades.

Migration Lifecycle

Migration Integrity and Testing

The system ensures data integrity during migrations through comprehensive testing:

• Fresh Install: Verifies LATEST.sql applies correctly.
• Idempotency: Verifies that re-running Migrate() on an already migrated database (e.g., on server restart) causes no issues.
• Data Persistence: Verifies that existing user and memo data is preserved across migration runs.

Sources: store/test/migrator_test.go20-37 store/test/migrator_test.go42-61 store/test/migrator_test.go65-91 store/test/migrator_test.go93-115

Core Tables

The schema is consistent across drivers, though data types vary (e.g., SERIAL in Postgres vs AUTOINCREMENT in SQLite/MySQL).

Table	Purpose
system_setting	Global configuration and schema_version. store/migration/sqlite/LATEST.sql2-7
user	Account info, password_hash, and role. store/migration/sqlite/LATEST.sql10-22
user_setting	Per-user preferences (e.g., UI settings). store/migration/sqlite/LATEST.sql25-30
memo	Core content, visibility, and payload (JSON/JSONB). store/migration/sqlite/LATEST.sql33-44
attachment	File metadata and storage references. store/migration/sqlite/LATEST.sql55-69
memo_share	Shared links with expiration timestamps. store/migration/sqlite/LATEST.sql102-110
user_identity	External OAuth/IDP provider mappings. store/migration/sqlite/LATEST.sql114-123

Sources: store/migration/sqlite/LATEST.sql1-127 store/migration/mysql/LATEST.sql1-126 store/migration/postgres/LATEST.sql1-126

---

Instance Settings Management

Instance-level settings (like the schema version) are stored in the system_setting table. Each driver provides methods for CRUD operations on these settings, typically using UpsertInstanceSetting to handle updates.

For MySQL, this uses the ON DUPLICATE KEY UPDATE syntax to ensure atomic updates.

Sources: store/db/mysql/instance_setting.go10-26 store/db/mysql/instance_setting.go28-59 store/db/mysql/instance_setting.go61-65