Skip to main content
Basic Memory supports two database backends: SQLite (default) and PostgreSQL. Both provide the same functionality through SQLAlchemy 2.0’s async ORM.

​
Overview

SQLite

Default - Single-file database, zero configuration

PostgreSQL

Production - Multi-user, cloud deployments, advanced features

​
SQLite (Default)

SQLite is the default backend for local-first usage.

​
Features

  • Zero configuration: Works out of the box
  • Single file: Entire database in one .db file
  • Serverless: No daemon process required
  • Fast: Optimized for read-heavy workloads
  • Portable: Copy the file to move your data

​
Configuration

# ~/.config/basic-memory/config.yaml
database_backend: sqlite
database_url: sqlite+aiosqlite:///{base_dir}/basic-memory.db
Or via environment variable: 
export BASIC_MEMORY_DATABASE_BACKEND=sqlite
export BASIC_MEMORY_DATABASE_URL="sqlite+aiosqlite:///~/basic-memory/basic-memory.db"

​
Performance Optimizations

Basic Memory automatically applies these SQLite optimizations: 
# WAL mode for concurrent read/write
PRAGMA journal_mode=WAL

# In-memory temp tables
PRAGMA temp_store=memory

# Faster synchronization
PRAGMA synchronous=NORMAL

# Optimize for multi-threaded access
PRAGMA locking_mode=NORMAL  # Windows: EXCLUSIVE
On Windows, Basic Memory uses locking_mode=EXCLUSIVE and NullPool to avoid file locking issues with concurrent access.

​
Full-Text Search

SQLite uses FTS5 for full-text search: 
CREATE VIRTUAL TABLE entity_fts USING fts5(
    title,
    content,
    content=entity,
    content_rowid=id
);
Boolean operators supported:
  • term1 AND term2 - Both terms must appear
  • term1 OR term2 - Either term can appear
  • NOT term - Exclude term
  • "exact phrase" - Phrase search

​
Vector Search (Optional)

With semantic_search_enabled=true, SQLite uses sqlite-vec for vector similarity: 
# Install sqlite-vec extension
import sqlite_vec

# Create vector index
CREATE VIRTUAL TABLE vec_items USING vec0(
    embedding float[384]  # fastembed default dimension
);

​
PostgreSQL

PostgreSQL is recommended for:
  • Cloud deployments
  • Multi-user access
  • Advanced query features
  • Full-text search in multiple languages

​
Installation

brew install postgresql@16
brew services start postgresql@16

​
Configuration

# ~/.config/basic-memory/config.yaml
database_backend: postgres
database_url: postgresql+asyncpg://user:password@localhost:5432/basic_memory
Or via environment variable: 
export BASIC_MEMORY_DATABASE_BACKEND=postgres
export BASIC_MEMORY_DATABASE_URL="postgresql+asyncpg://user:password@localhost/basic_memory"

​
Creating a Database

# Using psql
psql -U postgres -c "CREATE DATABASE basic_memory;"

# Grant permissions
psql -U postgres -c "GRANT ALL PRIVILEGES ON DATABASE basic_memory TO your_user;"

​
Full-Text Search

PostgreSQL uses tsvector and GIN index for full-text search: 
CREATE INDEX entity_fts_idx ON entity 
USING GIN(to_tsvector('english', title || ' ' || content));
Advantages over SQLite FTS:
  • Multi-language support (English, Spanish, French, etc.)
  • Stemming and ranking
  • Phrase search with <-> operator
  • Better performance for large datasets

​
Vector Search (Optional)

With semantic_search_enabled=true, PostgreSQL uses pgvector extension: 
# Install pgvector extension
psql -d basic_memory -c "CREATE EXTENSION vector;"

-- Create vector column and index
ALTER TABLE entity ADD COLUMN embedding vector(384);
CREATE INDEX ON entity USING ivfflat (embedding vector_cosine_ops);
pgvector advantages:
  • Optimized vector operations
  • Multiple distance metrics (cosine, L2, inner product)
  • HNSW and IVFFlat indexes

​
Comparison

FeatureSQLitePostgreSQL
SetupZero configRequires server
Performance (single-user)FasterGood
Performance (multi-user)LimitedExcellent
Concurrent writesSequentialConcurrent
Full-text searchFTS5tsvector + GIN
Vector searchsqlite-vecpgvector
Cloud-readyLimitedYes
BackupCopy filepg_dump/pg_restore
Max database size281 TBUnlimited
Use caseLocal-first, single-userMulti-user, cloud, production

​
Switching Backends

Switching backends requires re-importing your data. Basic Memory does not provide automatic migration between backends.
1

Export your data

# Export to Memory JSON format
bm export memory-json > backup.jsonl
2

Update configuration

# Change database_backend and database_url
database_backend: postgres
database_url: postgresql+asyncpg://user:pass@localhost/basic_memory
3

Run migrations

Migrations run automatically on first startup
4

Re-import data

bm import memory-json < backup.jsonl
bm sync

​
Testing with Both Backends

Developers can test against both backends: 
# Test with SQLite (default)
just test-sqlite

# Test with PostgreSQL (uses testcontainers)
just test-postgres

# Test both
just test
PostgreSQL tests use testcontainers to automatically spin up a temporary PostgreSQL instance in Docker. No manual setup required.

​
Migrations

Both backends use Alembic for schema migrations: 
# Create a new migration
just migration "Add new feature"

# Migrations run automatically on startup
bm status  # Triggers migration check
Migrations are stored in src/basic_memory/alembic/versions/ and work with both SQLite and PostgreSQL.

​
Performance Tuning

​
SQLite

# Increase cache size
PRAGMA cache_size=-64000  # 64 MB

# Optimize for read-heavy workloads
PRAGMA query_only=OFF
PRAGMA read_uncommitted=ON

​
PostgreSQL

-- Increase shared buffers (25% of RAM)
ALTER SYSTEM SET shared_buffers = '256MB';

-- Optimize for SSD
ALTER SYSTEM SET random_page_cost = 1.1;

-- Reload configuration
SELECT pg_reload_conf();

​
Cloud Deployment

For cloud deployments, use PostgreSQL: 
version: '3.8'
services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_DB: basic_memory
      POSTGRES_PASSWORD: ${DB_PASSWORD}
    volumes:
      - postgres_data:/var/lib/postgresql/data
  
  basic-memory:
    image: basicmachines/basic-memory:latest
    environment:
      BASIC_MEMORY_DATABASE_BACKEND: postgres
      BASIC_MEMORY_DATABASE_URL: postgresql+asyncpg://postgres:${DB_PASSWORD}@postgres/basic_memory
    depends_on:
      - postgres

volumes:
  postgres_data:

​
Troubleshooting

Cause: Multiple processes writing to the same SQLite fileSolution: Enable WAL mode (default in Basic Memory) or use PostgreSQL for multi-user access
Cause: PostgreSQL server not runningSolution: Start the server:
# macOS
brew services start postgresql@16

# Linux
sudo systemctl start postgresql
Cause: Schema out of syncSolution: Reset and re-run migrations:
# SQLite: Delete database file
rm ~/.local/share/basic-memory/basic-memory.db

# PostgreSQL: Drop and recreate
psql -c "DROP DATABASE basic_memory;"
psql -c "CREATE DATABASE basic_memory;"

# Restart to run migrations
bm status

​
Next Steps

Semantic Search

Configure vector embeddings for semantic search

Development

Set up your development environment

Build docs developers (and LLMs) love

Get started for freeTalk to us