Introducing a new way to review your PRs.

Read the announcement

usememos / memos

Memos is an open-source, self-hosted note-taking app that brings your everyday thoughts together in a personal timeline. Your data stays yours, and you choose what to keep private or share.

View on GitHub

25 documents · Updated Nov 20, 2026

docs / database-provisioning-and-migrations

Database provisioning and migrations

Memos stores its persistent state in SQLite, MySQL, or PostgreSQL. The selected backend opens the configured data source, prepares the current schema, and blocks normal server startup until the database is ready. Read Deployment, configuration, and operations for the wider startup context and Persistence and data evolution for the data model supported by this schema.

Database backends

Each backend provides the same application-level store behavior. The database boundary still differs in connection format, SQL rules, data types, and setup needs. The backend-specific store code keeps these differences behind the same persistence contract for memos, attachments, instance settings, and users.

SQLite

SQLite is the default for a single Memos instance. It uses a file in the data directory unless a different data source is set, which makes it suited to local use and small deployments. The driver uses write-ahead logging and a busy timeout to reduce normal lock errors. Demo data is available only with SQLite because its seed data uses SQLite files.

References: internal/…/profile.go• L60-117 store/…/sqlite.go• L21-60 store/migrator.go• L293-321

MySQL

MySQL is a server-backed option for deployments that use a separate database service. Its DSN identifies the server and database. The driver enables multi-statement execution because one migration can contain several SQL statements. MySQL keeps its own schema and migration SQL because its quoting, timestamp, JSON, conflict, and parameter rules differ from the other backends. Core memo and space writes also use transactions so access checks and related records are committed as one application action.

References: store/…/mysql.go• L20-40 store/…/mysql.go• L72-80 store/…/memo.go• L17-33 store/…/space.go• L17-63 store/…/instance_setting.go• L13-83 store/…/LATEST.sql• L1-157

PostgreSQL

PostgreSQL is the other server-backed option. It uses a PostgreSQL DSN and PostgreSQL-specific schema and query rules. Its schema can use native types such as JSONB, so its migration files remain separate from the SQLite and MySQL files while preserving the same application meaning.

References: store/…/postgres.go• L21-39 store/…/instance_setting.go• L13-75 store/…/LATEST.sql• L1-157 store/…/06__space_payload.sql• L1

SQL dialect alignment

The three store drivers adapt the same data operations to their database dialect. A shared query surface lets those operations run through either a database connection or a transaction. This keeps product behavior aligned while each driver supplies its own parameter markers, identifier rules, conflict syntax, and result handling.

ConcernSQLiteMySQLPostgreSQL
Query parametersUsually ?; numbered parameters are also used in some queries?Numbered parameters such as $1
Setting conflictsON CONFLICTINSERT IGNORE and ON DUPLICATE KEY UPDATEON CONFLICT
Generated row valuesRETURNINGInsert ID followed by a read when the full row is neededRETURNING
Empty user emailStored as SQL NULLStored as SQL NULLStored as SQL NULL

Operations that need both a new row and its generated values keep those steps consistent on every backend. SQLite and PostgreSQL can return the new row from the write. MySQL uses a follow-up read because its driver path does not use RETURNING; reaction upserts keep that insert and read in one transaction.

Reaction writes also preserve the same product rule across the drivers: a reaction can be created only for an existing memo, and an authorized change checks its policy before the write is committed. Each driver expresses the required lock and conflict behavior in its own SQL dialect.

Conditional instance-setting changes also keep one application contract across the drivers. A create-if-absent operation reports whether it inserted a row. A compare-and-update operation reports whether the stored value still matched the expected value. MySQL uses an exact binary comparison and handles an unchanged value as a successful no-op because its affected-row result differs from the other drivers.

User changes run inside transactions when they must preserve administrator rules. The store rejects a change that would remove the last active instance administrator or the last active administrator of a space. SQLite relies on its immediate transaction behavior for concurrent removals, while MySQL and PostgreSQL lock active administrators in a stable order. User creation with an external identity also runs as one transaction, so the user and its identity do not become separate application states.

MySQL applies the same boundary to memo and space actions. A memo write checks that its creator can write to the target space before commit. A new space and its first administrator are committed together, so the system does not expose a space without its initial access record.

References: store/…/executor.go• L8-14 store/…/executor.go• L8-14 store/…/executor.go• L8-14 store/…/reaction.go• L13-57 store/…/reaction.go• L13-53 store/…/reaction.go• L13-58 store/…/idp.go• L22-33 store/…/idp.go• L23-40 store/…/idp.go• L21-30 store/…/instance_setting.go• L23-63 store/…/instance_setting.go• L23-71 store/…/instance_setting.go• L23-62 store/…/user.go• L30-166 store/…/user.go• L46-186 store/…/user.go• L30-183 store/…/user_identity.go• L64-85 store/…/user_identity.go• L59-109 store/…/user_identity.go• L64-85 store/…/memo.go• L17-33 store/…/memo.go• L88-111 store/…/space.go• L17-63

Transaction retry signals

Authentication configuration changes use a serializable transaction because they validate shared settings and identity providers before applying one mutation. Each backend identifies its own transient transaction failures so the owning operation can retry only failures that may succeed when run again.

BackendRetryable conditions
SQLiteBusy or locked database state
MySQLLock wait timeout or deadlock
PostgreSQLSerialization failure or deadlock

The retry classification does not make permanent errors retryable. Constraint errors and other data errors remain visible to the caller.

References: store/…/auth_config.go• L13-23 store/…/auth_config.go• L13-23 store/…/auth_config.go• L13-23 store/…/auth_config_test.go• L11-16 store/…/auth_config_test.go• L11-16

JSON-backed token lookup

Personal access tokens are stored inside a user setting as JSON. Each backend must find a token hash without letting unrelated empty or malformed settings prevent valid users from authenticating. SQLite and MySQL use database JSON search rules before decoding the matching setting. PostgreSQL reads the relevant settings and checks the raw text first because strict JSON operators can fail on rows with an unexpected shape.

The lookup treats a missing token or a setting that does not carry the requested hash as a miss. If a row carries the hash but cannot be decoded, the lookup returns an error instead of silently treating that token as absent. This keeps corrupt credentials visible while allowing unrelated bad rows to be skipped.

References: store/…/user_setting.go• L89-128 store/…/user_setting.go• L77-112 store/…/user_setting.go• L87-142 store/…/user_setting_test.go• L15-187

Database setup

The server builds a profile from command-line flags or environment variables. The profile selects the driver and data source, checks the data directory, and passes the connection details to the matching database driver.

Connection inputs

The main connection inputs are available as flags and as MEMOS_ environment variables. The default values and backend rules are:

PurposeFlagEnvironment variableDefault or requirement
Select backend--driverMEMOS_DRIVERsqlite, mysql, or postgres; default is sqlite
Select database--dsnMEMOS_DSNRequired for MySQL and PostgreSQL; SQLite can use its default file
Select data directory--dataMEMOS_DATACreated when absent; SQLite uses it for the default database file
Enable demo data--demoMEMOS_DEMODisabled by default; valid only with SQLite

With an empty SQLite DSN, Memos uses memos_prod.db in the data directory. Demo mode uses memos_demo.db. MySQL and PostgreSQL use the database named by their DSN rather than a database name derived from the data directory.

References: cmd/…/main.go• L52-95 cmd/…/main.go• L99-123 internal/…/profile.go• L15-37 internal/…/profile.go• L60-117

Startup sequence

Database preparation is part of server startup. Memos does not load deployment settings or start its network service until the selected database passes schema setup or upgrade.

The startup path connects the operator’s settings to a ready request service:

sequenceDiagram
    participant Operator
    participant Server
    participant Driver
    participant Database
    Operator->>Server: Start with driver and DSN
    Server->>Driver: Open selected backend
    Driver->>Database: Connect and inspect schema
    Server->>Database: Initialize or upgrade schema
    Server->>Server: Apply declarative configuration
    Server->>Database: Seed demo content when enabled
    Server->>Server: Start request service

The database is initialized when the backend can find the memo table. An unknown driver, bad connection, or unavailable database stops startup before Memos serves traffic. Demo seeding is a later SQLite-only step after declarative configuration is applied; other backends skip it.

References: cmd/…/main.go• L126-168 store/…/db.go• L13-31 store/…/sqlite.go• L71-78 store/…/mysql.go• L51-57 store/…/postgres.go• L50-56 store/migrator.go• L229-320

Migration lifecycle

The migration system evolves the stored schema as part of startup. It records the schema version in the instance BASIC setting, uses the SQL for the selected backend, and changes the version marker only after the schema work succeeds.

The minimum supported migration baseline is schema version 0.31.8, shipped in v0.31.0. The current migration set advances that schema to 26.11.1, produced by 26.11/00__add_attachment_memo_index.sql. This schema version is separate from application release numbering. Historical scripts remain in released binaries and Git history, but the current binary uses each backend’s complete LATEST.sql for new databases and incremental migrations for later changes. New migration directories use YY.MM/NN__description.sql; each schema change must have a matching file for SQLite, MySQL, and PostgreSQL. A release with no schema change does not create a new schema version.

The lifecycle has two paths:

flowchart TD
    A[Open selected database] --> B{Memo table exists?}
    B -- No --> C[Apply backend LATEST schema]
    B -- Yes --> D[Read recorded schema version]
    C --> E[Record current schema version]
    D --> F{Version supported?}
    F -- No --> G[Stop with upgrade error]
    F -- Yes --> H[Apply ordered incremental migrations]
    E --> I[Complete database setup]
    H --> I
    I --> J{Demo mode?}
    J -- Yes --> K[Seed SQLite demo data after configuration]
    J -- No --> L[Complete startup]
    K --> L

New database initialization

An empty database receives the selected backend’s LATEST.sql through a transaction. This file represents the complete current schema, so a new installation does not replay the historical migration chain. SQLite and PostgreSQL keep the schema version write with the schema transaction. MySQL commits data-definition statements implicitly, so its version write is adjacent to the schema work rather than protected by the same rollback boundary. After setup, Memos records the schema version supported by the migration set. It also creates the instance access setting only when that setting does not already exist. Declarative configuration is applied before SQLite demo content is seeded; other backends skip that step.

References: store/migrator.go• L229-274 store/migrator.go• L285-320 store/migrator.go• L129-154 store/…/LATEST.sql• L1-157 store/…/LATEST.sql• L1-157 store/…/LATEST.sql• L1-158

Existing database upgrade

An existing database moves from its recorded version to the highest version supported by its driver. The system orders changes by version directory and numeric file prefix, which gives each change a stable position in the upgrade path. For example, 26.11/00__add_attachment_memo_index.sql becomes schema version 26.11.1. It runs the selected scripts in one transaction and records the new version only after the transaction commits.

For SQLite and PostgreSQL, this transaction boundary prevents a failed upgrade from leaving a falsely advanced version marker. MySQL commits data-definition statements implicitly, so a failed MySQL migration can leave some schema changes in place while the version marker remains old. After schema work, Memos creates the instance access setting if it is missing and keeps an existing value. A database already at the current version has no work to apply, so normal restarts are safe after a successful upgrade.

References: store/migrator.go• L96-132 store/migrator.go• L157-227 store/migrator.go• L368-429 store/migrator.go• L129-154 store/…/00__add_attachment_memo_index.sql• L1 store/…/00__add_attachment_memo_index.sql• L1-2 store/…/00__add_attachment_memo_index.sql• L1

Upgrade boundaries

The recorded version protects the database from unsupported release changes. A binary refuses to open a database with a newer schema version. Databases older than 0.22.0, or databases with no recorded schema version, must first run v0.25.3 and then v0.31.0 before they can use the current baseline.

The migration system also rejects invalid schema versions. Operators must use a binary that supports the database’s recorded version and must complete the required bridge releases before continuing to the current schema.

References: store/migrator.go• L111-127 store/migrator.go• L465-499 store/…/migrator_guardrail_test.go• L33-96 store/…/README.md• L3-17

Cross-backend schema changes

One product change can need different SQL in each backend while keeping the same data rule. The current schema and the incremental migration must stay aligned for all three drivers.

The current 26.11.1 change adds an attachment lookup index. SQLite and MySQL use the bounded (memo_id, type) key, while PostgreSQL uses memo_id because attachment.type is unbounded text there. Fresh schemas carry the target lookup support, while the incremental scripts express the driver-specific retry behavior. SQLite and PostgreSQL use IF NOT EXISTS; MySQL uses ordinary DDL and needs inspection if an earlier attempt changed the schema before failing.

The 0.31 changes show the same pattern. Spaces add optional links from memos and space-scoped visibility. Space payloads add structured metadata with an empty object for existing spaces. User email becomes optional, normalized, and unique within an instance. SQLite may rebuild a table for changes that its dialect cannot apply in place, while MySQL and PostgreSQL use their own column, index, JSON, and conflict rules.

References: store/…/00__add_attachment_memo_index.sql• L1 store/…/00__add_attachment_memo_index.sql• L1-2 store/…/00__add_attachment_memo_index.sql• L1 store/…/LATEST.sql• L82-101 store/…/LATEST.sql• L82-100 store/…/LATEST.sql• L83-101 store/…/03__multi_spaces.sql• L1-20 store/…/03__multi_spaces.sql• L1-20 store/…/03__multi_spaces.sql• L1-60 store/…/06__space_payload.sql• L1-3 store/…/06__space_payload.sql• L1 store/…/06__space_payload.sql• L1 store/…/07__unique_email.sql• L1-42 store/…/07__unique_email.sql• L1-21 store/…/07__unique_email.sql• L1-59

Data-preserving changes

Some migrations change the meaning of stored data instead of only changing table shape. These migrations move old settings to new settings, resolve old references to stable IDs, or retain compatible legacy fields for older clients.

Each backend defines its own safe rule for invalid JSON, duplicate replacement rows, and missing related records. For example, invalid setting JSON can remain unchanged, an existing replacement setting can win, and a reaction without a matching memo can be discarded instead of receiving a false link.

References: store/…/00__rename_shortcuts_to_memo_views.sql• L1-22 store/…/00__rename_shortcuts_to_memo_views.sql• L1-26 store/…/00__rename_shortcuts_to_memo_views.sql• L1-28 store/…/02__reaction_memo_id.sql• L1-24 store/…/02__reaction_memo_id.sql• L1-17

Change checklist

When these files or sections are changed, remember to consider these:

  • Add a complete LATEST.sql schema and one matching incremental migration for SQLite, MySQL, and PostgreSQL.
  • Keep the migration directory and numeric prefix aligned with the recorded schema version. Do not advance the 0.31.8 baseline for a later schema change.
  • Review JSON functions, reserved names, placeholders, conflict syntax, and multi-statement behavior in all three SQL dialects.
  • Test upgrades from the supported baseline and bridge releases, not only new empty databases.
  • Check that creator-based queries and their indexes stay aligned in fresh schemas, incremental scripts, and all three backend data paths.
  • Check that attachment lookup indexes have the same intended query coverage in fresh schemas and incremental scripts, while preserving SQLite and PostgreSQL retry behavior and MySQL’s ordinary DDL behavior.
  • Back up the database, stop all writers, and run one version to completion before starting other Memos instances.
  • Keep a change to spaces, memo visibility, space payloads, or user email rules aligned across fresh schemas, incremental scripts, and backend data paths.
  • Preserve the personal-access-token lookup contract when changing JSON setting queries: unrelated bad rows must not block a valid token, but a matching corrupt row must remain an error.
  • Preserve the transaction retry classification for lock, deadlock, and serialization errors. Do not classify permanent constraint errors as transient.
  • Keep conditional instance-setting writes aligned with each driver’s affected-row behavior, especially MySQL’s exact comparison and unchanged-value case.
  • Preserve the last-instance-admin and last-space-admin safeguards across all three user data paths.
  • Keep combined user and external identity creation atomic, and preserve each driver’s generated-value readback contract.
  • Keep reaction upserts consistent with memo existence checks, conflict handling, and policy transaction rules across all three drivers.
  • Define the result for invalid JSON, duplicate rows, missing references, and clients that still use legacy fields before shipping a data rewrite.

Important failure modes

TriggerConsequenceGuard
The driver name is not sqlite, mysql, or postgresStartup stops before the database opensUse a supported value in --driver or MEMOS_DRIVER
SQLite cannot open its DSN or data directoryThe store cannot startKeep the data directory writable or provide a valid SQLite DSN
MySQL does not allow multi-statement executionA MySQL migration fails during upgradeUse the Memos MySQL driver so it applies the required connection setting
A MySQL data-definition statement fails after an earlier statement commitsThe schema can be partly changed while the recorded version stays oldValidate the full MySQL migration and repair or restore the database before retrying
An attachment index build runs on a large tableStartup migration takes time and concurrent writes can blockSchedule the upgrade during a maintenance window
An existing attachment index has a conflicting definitionThe migration stops instead of marking the schema currentInspect the named index and repair it; do not advance the schema marker manually
An older binary opens a newer schemaMemos rejects the downgradeRun a binary that supports the recorded schema version
A database is older than 0.22.0 or has no version markerDirect upgrade to the current baseline is rejectedRun v0.25.3 successfully, then v0.31.0
A migration fails inside its transactionThe change is not committed and the version marker does not advanceFix the backend SQL or data condition, then retry
A fresh schema differs from its incremental pathNew and upgraded instances can expose different behaviorKeep each driver’s LATEST.sql and migration history aligned
A transient lock, deadlock, or serialization error is treated as permanentA valid concurrent mutation fails without a retryUse the backend retry classification and retry the whole transaction
A MySQL conditional setting write relies on generic affected-row behaviorAn unchanged value can look like a conflictKeep the exact comparison and no-op handling
A user change removes the last active instance or space administratorThe instance or space can lose its required administratorKeep the transaction checks and stable locking rules
User creation commits without its external identityThe account and external login record can become inconsistentUse the combined user-and-identity transaction
A reaction is written without confirming its memo or policyOrphaned reactions or unauthorized changes can enter the storeKeep the memo existence check and authorized transaction path in each driver
A PostgreSQL setting row carries a requested token hash but has invalid token dataThe token lookup returns an error instead of a false missKeep the raw-text precheck and decode matching rows
A unique-email cleanup leaves invalid or duplicate valuesThe unique index can fail or accounts can differ by backendNormalize values and apply the oldest-account rule before enforcing uniqueness

Key files

FileWhy to read it
cmd/…/main.goShows how runtime inputs become a profile and how database readiness gates server startup
internal/…/profile.goDefines data-directory defaults, SQLite file names, and demo-mode rules
store/…/db.goDefines the supported drivers and backend selection boundary
store/…/sqlite.goShows SQLite connection and initialization behavior
store/…/mysql.goShows MySQL DSN handling and multi-statement support
store/…/memo.goShows MySQL memo validation, transactional creation, and generated-row readback
store/…/space.goShows MySQL space creation with its initial administrator and transactional membership changes
store/…/postgres.goShows PostgreSQL connection and initialization behavior
store/db/{sqlite,mysql,postgres}/{memo,attachment,instance_setting,user,user_identity,user_setting,reaction,user_delete}.goCompares shared persistence behavior with each SQL dialect’s query, conflict, result, JSON lookup, identity creation, and cleanup rules
store/db/{sqlite,mysql,postgres}/auth_config.goShows transaction validation and backend-specific retry signals for shared authentication changes
store/db/{sqlite,mysql,postgres}/executor.goShows the common query surface used by database handles and transactions
store/migrator.goDefines fresh setup, version tracking, transaction boundaries, access-setting initialization, demo seeding, and upgrade guards
store/…/README.mdDefines the current baseline, naming rules, coordinated upgrade process, and maintenance rules for future migrations
store/…/LATEST.sqlShows the complete current SQLite schema for new databases
store/…/LATEST.sqlShows the complete current MySQL schema for new databases
store/…/LATEST.sqlShows the complete current PostgreSQL schema for new databases
store/migration/{sqlite,mysql,postgres}/26.11/00__add_attachment_memo_index.sqlShows how one attachment-index change is expressed for each backend and how their DDL behavior differs
store/…/migrator_test.goDemonstrates fresh setup, repeat runs, data retention, and cross-driver migration behavior
store/…/migrator_upgrade_test.goDemonstrates upgrades from older database schemas
store/…/user_setting_test.goVerifies token lookup behavior with missing, malformed, and valid setting data