Deploying SurrealDB on RamNode VPS

sudo apt update
sudo apt install -y nginx certbot python3-certbot-nginx

sudo nano /etc/nginx/sites-available/surrealdb

upstream surrealdb {
    server 127.0.0.1:8000;
    keepalive 64;
}

server {
    listen 80;
    server_name db.yourdomain.com;
    return 301 https://$server_name$request_uri;
}

server {
    listen 443 ssl http2;
    server_name db.yourdomain.com;

    # Security headers
    add_header Strict-Transport-Security "max-age=31536000; includeSubDomains" always;
    add_header X-Frame-Options "SAMEORIGIN" always;
    add_header X-Content-Type-Options "nosniff" always;

    # Logging
    access_log /var/log/nginx/surrealdb-access.log;
    error_log /var/log/nginx/surrealdb-error.log;

    # Timeouts for long-running queries
    proxy_connect_timeout 60s;
    proxy_send_timeout 60s;
    proxy_read_timeout 60s;

    location / {
        proxy_pass http://surrealdb;
        proxy_http_version 1.1;

        # WebSocket support
        proxy_set_header Upgrade $http_upgrade;
        proxy_set_header Connection "upgrade";

        # Standard proxy headers
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;

        # Disable buffering for real-time queries
        proxy_buffering off;
    }
}

# Enable the site
sudo ln -s /etc/nginx/sites-available/surrealdb /etc/nginx/sites-enabled/

# Test configuration
sudo nginx -t

# Reload Nginx
sudo systemctl reload nginx

# Obtain SSL certificate
sudo certbot --nginx -d db.yourdomain.com

# Enable UFW if not already enabled
sudo ufw enable

# Allow SSH (important!)
sudo ufw allow 22/tcp

# Allow HTTP and HTTPS
sudo ufw allow 80/tcp
sudo ufw allow 443/tcp

# If not using Nginx reverse proxy, allow SurrealDB directly
# sudo ufw allow 8000/tcp

# Check status
sudo ufw status

# Install CLI if not already installed
curl -sSf https://install.surrealdb.com | sh

# Local connection
surreal sql --endpoint http://localhost:8000 --username root --password your_password --namespace test --database test

# Remote connection with SSL
surreal sql --endpoint https://db.yourdomain.com --username root --password your_password --namespace test --database test

curl -X POST \
  -H "Accept: application/json" \
  -H "NS: test" \
  -H "DB: test" \
  -u "root:your_password" \
  -d '{"name": "John Doe", "email": "john@example.com"}' \
  http://localhost:8000/key/users/john

-- Define a person table with schema
DEFINE TABLE person SCHEMAFULL;

-- Define fields with types and constraints
DEFINE FIELD name ON TABLE person TYPE string;
DEFINE FIELD email ON TABLE person TYPE string
    ASSERT string::is::email($value);
DEFINE FIELD age ON TABLE person TYPE int
    ASSERT $value > 0 AND $value < 150;
DEFINE FIELD created_at ON TABLE person TYPE datetime
    DEFAULT time::now();

-- Define an index
DEFINE INDEX idx_email ON TABLE person COLUMNS email UNIQUE;

-- Create a single record
CREATE person SET
    name = "Jane Smith",
    email = "jane@example.com",
    age = 28;

-- Create multiple records
INSERT INTO person [
    { name: "Alice Johnson", email: "alice@example.com", age: 32 },
    { name: "Bob Wilson", email: "bob@example.com", age: 45 }
];

-- Select all records
SELECT * FROM person;

-- Filter with WHERE
SELECT * FROM person WHERE age > 30;

-- Order and limit
SELECT * FROM person ORDER BY age DESC LIMIT 10;

-- Aggregate functions
SELECT count(), math::mean(age) as avg_age FROM person;

#!/bin/bash

# Configuration
BACKUP_DIR="/opt/surrealdb/backups"
DATA_DIR="/var/lib/surrealdb/data"  # Adjust based on your installation
RETENTION_DAYS=7
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

# Create backup directory if it doesn't exist
mkdir -p "$BACKUP_DIR"

# Create backup
echo "Creating backup: surrealdb_backup_$TIMESTAMP.tar.gz"
tar -czf "$BACKUP_DIR/surrealdb_backup_$TIMESTAMP.tar.gz" -C "$DATA_DIR" .

# Remove old backups
find "$BACKUP_DIR" -name "surrealdb_backup_*.tar.gz" -mtime +$RETENTION_DAYS -delete

echo "Backup completed successfully"

# Make executable
sudo chmod +x /usr/local/bin/surrealdb-backup.sh

# Edit crontab for daily backup at 2 AM
crontab -e

# Add this line:
0 2 * * * /usr/local/bin/surrealdb-backup.sh >> /var/log/surrealdb-backup.log 2>&1

# Export database
surreal export \
    --endpoint http://localhost:8000 \
    --username root \
    --password your_password \
    --namespace test \
    --database test \
    /opt/surrealdb/backups/export_$(date +%Y%m%d).surql

# Import database
surreal import \
    --endpoint http://localhost:8000 \
    --username root \
    --password your_password \
    --namespace test \
    --database test \
    /opt/surrealdb/backups/export_20240101.surql

deploy:
  resources:
    limits:
      cpus: '2'
      memory: 4G
    reservations:
      cpus: '1'
      memory: 2G

-- Define a user with specific permissions
DEFINE USER developer ON DATABASE PASSWORD 'strong_password_here'
    ROLES EDITOR;

-- Define scoped authentication
DEFINE SCOPE user_scope SESSION 24h
    SIGNIN (
        SELECT * FROM user WHERE email = $email AND crypto::argon2::compare(password, $password)
    )
    SIGNUP (
        CREATE user SET email = $email, password = crypto::argon2::generate($password)
    );

-- Implement Row-Level Security
DEFINE TABLE article SCHEMALESS
    PERMISSIONS
        FOR select WHERE published = true OR author = $auth.id
        FOR create, update WHERE $auth.id != NONE
        FOR delete WHERE author = $auth.id;