Spyglass Database Setup Guide

Spyglass requires a MySQL database backend for storing experimental data and analysis results. This guide covers all setup options from quick local development to production deployments.

Quick Start (Recommended)

The easiest way to set up a database is using the installer with Docker Compose:

cd spyglass
python scripts/install.py
# Choose "Docker" option when prompted for database setup

This automatically:

Pulls the DataJoint MySQL 8.0 Docker image
Creates and starts a container named spyglass-db
Waits for MySQL to be ready
Creates configuration file with credentials

Or use Docker Compose directly:

cd spyglass
docker compose up -d

Setup Options

Option 1: Docker Compose (Recommended for Local Development)

Pros:

One-command setup (~2 minutes)
Version controlled
Easy to customize via .env file
Industry-standard tool
Persistent data storage
Health checks built-in

Cons:

Requires Docker Desktop with Compose plugin
Uses system resources when running

Prerequisites

Install Docker Desktop: (macOS, Windows, or Linux)
Start Docker Desktop and ensure it's running

Verify Compose is available:

docker compose --version
# Should show: Docker Compose version ##.##.##

Setup

Using installer (recommended):

python scripts/install.py --docker  # Will auto-detect and use compose command

Using Docker Compose directly:

# From spyglass repository root
docker compose up -d

The default configuration uses:

Port: 3306
Password: tutorial
Container name: spyglass-db
Persistent storage: spyglass-db-data volume

Customization (Optional)

Create a .env file to customize settings:

# Copy and edit example
cp example.env .env
nano .env # or use your favorite editor

Available options:

# Change port if 3306 is in use
MYSQL_PORT=3307

# Change root password (for production)
MYSQL_ROOT_PASSWORD=your-secure-password

# Use different MySQL version
MYSQL_IMAGE=datajoint/mysql:8.0.18

Important: If you change port or password, update your DataJoint config accordingly. You can either edit this file directly, or run the following in your python environment.

dj.config.save_global()

Management

Start/stop services:

# Start
docker compose up -d

# Stop (keeps data)
docker compose stop

# Stop and remove containers (keeps data)
docker compose down

# Stop and remove everything including data
docker compose down -v  # WARNING: Deletes all data!

View logs:

docker compose logs mysql
docker compose logs -f mysql  # Follow mode

Check status:

docker compose ps

Access MySQL shell:

docker compose exec mysql mysql -uroot -p

This will securely prompt you for a password. For test cases, you can directly append the password (e.g., -ptutorial).

Restart services:

docker compose restart

Option 2: Remote Database (Lab/Cloud Setup)

Pros:

Shared across team members
Production-ready
Professional backup/monitoring
Persistent storage

Cons:

Requires existing MySQL server
Network configuration needed
May need VPN/SSH tunnel

Prerequisites

MySQL 8.0+ server accessible over network
Database credentials (host, port, user, password)
Firewall rules allowing connection

Setup

Using installer (interactive):

python scripts/install.py --remote
# Enter connection details when prompted

Using installer (non-interactive for automation):

# Using CLI arguments
python scripts/install.py --remote \
    --db-host db.mylab.edu \
    --db-user myusername \
    --db-password mypassword

# Using environment variables (recommended for CI/CD)
export SPYGLASS_DB_PASSWORD=mypassword
python scripts/install.py --remote \
    --db-host db.mylab.edu \
    --db-user myusername

Manual configuration:

Create ~/.datajoint_config.json:

{
  "database.host": "db.mylab.edu",
  "database.port": 3306,
  "database.user": "myusername",
  "database.password": "mypassword",
  "database.use_tls": true
}

Test connection:

import datajoint as dj

dj.conn().ping()  # Should succeed

SSH Tunnel (For Remote Access)

If the database is behind firewall, use SSH tunnel:

# Create tunnel (keep running in terminal)
ssh -L 3306:localhost:3306 user@remote-server

# In separate terminal, configure as localhost
cat > ~/.datajoint_config.json << EOF
{
  "database.host": "localhost",
  "database.port": 3306,
  "database.user": "root",
  "database.password": "password",
  "database.use_tls": false
}
EOF

Or use autossh for persistent tunnel:

autossh -M 0 -L 3306:localhost:3306 user@remote-server

Option 3: Local MySQL Installation

Pros:

No Docker required
Direct system integration
Full control over configuration

Cons:

More complex setup
Platform-specific installation
Harder to reset/clean

macOS (Homebrew)

# Install MySQL
brew install mysql

# Start MySQL service
brew services start mysql

# Secure installation
mysql_secure_installation

# Create user
mysql -uroot -p

In MySQL shell:

CREATE USER 'spyglass'@'localhost' IDENTIFIED BY 'spyglass_password';
GRANT ALL PRIVILEGES ON *.* TO 'spyglass'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Configure DataJoint:

{
  "database.host": "localhost",
  "database.port": 3306,
  "database.user": "spyglass",
  "database.password": "spyglass_password",
  "database.use_tls": false
}

Linux (Ubuntu/Debian)

# Install MySQL
sudo apt-get update
sudo apt-get install mysql-server

# Start service
sudo systemctl start mysql
sudo systemctl enable mysql

# Secure installation
sudo mysql_secure_installation

# Create user
sudo mysql

In MySQL shell:

CREATE USER 'spyglass'@'localhost' IDENTIFIED BY 'spyglass_password';
GRANT ALL PRIVILEGES ON *.* TO 'spyglass'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Windows

Download MySQL Installer: https://dev.mysql.com/downloads/installer/
Run installer and select "Developer Default"
Follow setup wizard
Create spyglass user with full privileges

Configuration Reference

DataJoint Configuration File

Location: ~/.datajoint_config.json

Full configuration example:

{
  "database.host": "localhost",
  "database.port": 3306,
  "database.user": "root",
  "database.password": "tutorial",
  "database.use_tls": false,
  "database.charset": "utf8mb4",
  "connection.init_function": null,
  "loglevel": "INFO",
  "safemode": true,
  "fetch_format": "array"
}

Key settings:

database.host: MySQL server hostname or IP
database.port: MySQL port (default: 3306)
database.user: MySQL username
database.password: MySQL password
database.use_tls: Use TLS/SSL encryption (recommended for remote)

TLS/SSL Configuration

When to use TLS:

✅ Remote database connections
✅ Production environments
✅ When connecting over untrusted networks
❌ localhost connections
❌ Docker containers on same machine

Enable TLS:

{
  "database.use_tls": true
}

Custom certificate:

{
  "database.use_tls": {
    "ssl": {
      "ca": "/path/to/ca-cert.pem",
      "cert": "/path/to/client-cert.pem",
      "key": "/path/to/client-key.pem"
    }
  }
}

Security Best Practices

Development

For local development, simple credentials are acceptable:

User: root or dedicated user
Password: Simple but unique
TLS: Disabled for localhost

Production

For shared/production databases:

Strong passwords:

# Generate secure password
openssl rand -base64 32

User permissions: Manage by database prefix

-- Create user with specific database access
CREATE USER 'spyglass'@'%' IDENTIFIED BY 'strong_password';
GRANT ALL PRIVILEGES ON someprefix_*.* TO 'spyglass'@'%';
FLUSH PRIVILEGES;

Enable TLS:
```
{
  "database.use_tls": true
}
```
Network security:
- Use firewall rules
- Consider VPN for remote access
- Use SSH tunnels when appropriate
Credential management:
- Never commit config files to git
- Use environment variables for CI/CD (e.g., GitHub Actions)
- Consider secrets management tools

File Permissions

Protect configuration file by only allowing read/write access by you:

chmod 600 ~/.datajoint_config.json

Multi-User Setup

For lab environments with shared database:

Server-Side Setup

-- Create database prefix for lab
CREATE DATABASE yourlab_common;

-- Create users
CREATE USER 'alice'@'%' IDENTIFIED BY 'alice_password';
CREATE USER 'bob'@'%' IDENTIFIED BY 'bob_password';

-- Grant permissions
GRANT ALL PRIVILEGES ON yourlab_*.* TO 'alice'@'%';
GRANT ALL PRIVILEGES ON yourlab_*.* TO 'bob'@'%';
FLUSH PRIVILEGES;

Client-Side Setup

Each user creates their own config:

Alice's config:

{
  "database.host": "lab-db.university.edu",
  "database.user": "alice",
  "database.password": "alice_password",
  "database.use_tls": true
}

Bob's config:

{
  "database.host": "lab-db.university.edu",
  "database.user": "bob",
  "database.password": "bob_password",
  "database.use_tls": true
}

Troubleshooting

Cannot Connect

Check MySQL is running:

# Docker
docker ps | grep spyglass-db

# System service (Linux)
systemctl status mysql

# Homebrew (macOS)
brew services list | grep mysql

Test connection:

# With mysql client
mysql -h HOST -P PORT -u USER -p

# With Python
python -c "import datajoint as dj; dj.conn().ping()"

Permission Denied

Grant additional privileges to a given user in mysql:

GRANT ALL PRIVILEGES ON *.* TO 'user'@'host';
FLUSH PRIVILEGES;

Port Already in Use

# Find what's using port 3306
lsof -i :3306
netstat -an | grep 3306

# Use different port
docker run -p 3307:3306 ...
# Update config with port 3307

TLS Errors

# Disable TLS for localhost
config = {"database.host": "localhost", "database.use_tls": False}

For more troubleshooting help, see TROUBLESHOOTING.md.

Advanced Topics

Database Backup

=== Docer database

```bash
# Backup
docker exec spyglass-db mysqldump -uroot -ptutorial --all-databases > backup.sql

# Restore
docker exec -i spyglass-db mysql -uroot -ptutorial < backup.sql
```

=== System MySQL

```bash
# Backup
mysqldump -u USER -p --all-databases > backup.sql

# Restore
mysql -u USER -p < backup.sql
```


### Performance Tuning

**Increase buffer pool (Docker):**

```bash
docker run -d \
    --name spyglass-db \
    -p 3306:3306 \
    -e MYSQL_ROOT_PASSWORD=tutorial \
    datajoint/mysql:8.0 \
    --innodb-buffer-pool-size=2G

Optimize tables:

OPTIMIZE TABLE tablename;

Migration

Moving from Docker to Remote:

Backup Docker database
Restore to remote server
Update config to point to remote
Test connection

Example:

# Backup from Docker
docker exec spyglass-db mysqldump -uroot -ptutorial --all-databases > backup.sql

# Restore to remote
mysql -h remote-host -u user -p < backup.sql

# Update config
cat > ~/.datajoint_config.json << EOF
{
  "database.host": "remote-host",
  "database.user": "user",
  "database.password": "password",
  "database.use_tls": true
}
EOF

Getting Help

Issues: https://github.com/LorenFrankLab/spyglass/issues
Docs: See main Spyglass documentation
DataJoint: https://docs.datajoint.org/

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Spyglass Database Setup Guide

Quick Start (Recommended)

Setup Options

Option 1: Docker Compose (Recommended for Local Development)

Prerequisites

Setup

Customization (Optional)

Management

Option 2: Remote Database (Lab/Cloud Setup)

Prerequisites

Setup

SSH Tunnel (For Remote Access)

Option 3: Local MySQL Installation

macOS (Homebrew)

Linux (Ubuntu/Debian)

Windows

Configuration Reference

DataJoint Configuration File

TLS/SSL Configuration

Security Best Practices

Development

Production

File Permissions

Multi-User Setup

Server-Side Setup

Client-Side Setup

Troubleshooting

Cannot Connect

Permission Denied

Port Already in Use

TLS Errors

Advanced Topics

Database Backup

Migration

Getting Help

FilesExpand file tree

DATABASE.md

Latest commit

History

DATABASE.md

File metadata and controls

Spyglass Database Setup Guide

Quick Start (Recommended)

Setup Options

Option 1: Docker Compose (Recommended for Local Development)

Prerequisites

Setup

Customization (Optional)

Management

Option 2: Remote Database (Lab/Cloud Setup)

Prerequisites

Setup

SSH Tunnel (For Remote Access)

Option 3: Local MySQL Installation

macOS (Homebrew)

Linux (Ubuntu/Debian)

Windows

Configuration Reference

DataJoint Configuration File

TLS/SSL Configuration

Security Best Practices

Development

Production

File Permissions

Multi-User Setup

Server-Side Setup

Client-Side Setup

Troubleshooting

Cannot Connect

Permission Denied

Port Already in Use

TLS Errors

Advanced Topics

Database Backup

Migration

Getting Help