Infrastrucuture

• Public API
• Dolt Server Documentation

Public API

Dolt Server Documentation

Dolt Database Server Setup Guide

This document outlines the complete process for setting up a Dolt database server that automatically synchronizes with DoltHub and provides MySQL-compatible access for applications like NocoDB.

System Overview

• Database Platform: Dolt (git for data)
• Remote Repository: DoltHub (fb-api-public)
• Local Database Path: /dolt/dbs/fb-api-public
• Connection Type: MySQL-compatible (port 3306)
• Updates: Automatically pulled hourly from DoltHub

Initial Installation

Install Dolt

# Linux
sudo bash -c 'curl -L https://github.com/dolthub/dolt/releases/latest/download/install.sh | bash'

# macOS
brew install dolt

Clone the Database

# Create directory if it doesn't exist
sudo mkdir -p /dolt/dbs
sudo chmod 777 /dolt/dbs

# Clone the repository
cd /dolt/dbs
dolt clone https://doltremoteapi.dolthub.com/fullbuff/fb-api-public

User Management

Connect to the Dolt SQL Shell

# Connect to the database using Dolt's SQL client
cd /dolt/dbs/fb-api-public
dolt -u root -p "" sql

Create a User for Applications

In the SQL shell:

-- Create a user with a password
CREATE USER 'nocodb'@'%' IDENTIFIED BY 'securepassword';

-- Grant read permissions
GRANT SELECT ON *.* TO 'nocodb'@'%';

-- Apply changes
FLUSH PRIVILEGES;

-- Verify user creation
SELECT User, Host FROM mysql.user WHERE User = 'nocodb';

Automated Service Setup

Create Update Script

Create a script to pull updates from DoltHub:

sudo nano /usr/local/bin/update-dolt.sh

Script content:

#!/bin/bash

# Log file for output
LOG_FILE="/var/log/dolt-update.log"

# Full path to dolt executable
DOLT_PATH=$(which dolt)

# Function to log messages
log() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" >> "$LOG_FILE"
}

# Check if dolt is available
if [ -z "$DOLT_PATH" ]; then
    log "ERROR: dolt executable not found in PATH"
    exit 1
fi

log "Using dolt at: $DOLT_PATH"

# Check if database directory exists
if [ ! -d "/dolt/dbs/fb-api-public" ]; then
    log "ERROR: Database directory /dolt/dbs/fb-api-public does not exist"
    exit 1
fi

# Change to the database directory
cd /dolt/dbs/fb-api-public || {
    log "ERROR: Failed to change to database directory"
    exit 1
}

# Pull updates from main branch
log "Starting pull from origin main"
$DOLT_PATH pull origin main >> "$LOG_FILE" 2>&1
RESULT=$?

if [ $RESULT -eq 0 ]; then
    log "Successfully pulled updates"
else
    log "Failed to pull updates, exit code: $RESULT"
fi

exit $RESULT

Make the script executable:

sudo chmod +x /usr/local/bin/update-dolt.sh

Create Systemd Services

Create the Dolt Server service:

sudo nano /etc/systemd/system/dolt-server.service

Content:

[Unit]
Description=Dolt SQL Server
After=network.target

[Service]
Type=simple
User=root
WorkingDirectory=/dolt/dbs/fb-api-public
ExecStart=/usr/local/bin/dolt sql-server --host 0.0.0.0 --port 3306
Restart=always
RestartSec=10

[Install]
WantedBy=multi-user.target

Create the update service:

sudo nano /etc/systemd/system/dolt-update.service

Content:

[Unit]
Description=Dolt Database Update
After=network.target
Before=dolt-server.service

[Service]
Type=oneshot
User=root
ExecStart=/usr/local/bin/update-dolt.sh
TimeoutStartSec=300

[Install]
WantedBy=multi-user.target

Configure Cron Job for Regular Updates

Set up a cron job to run the update script hourly:

sudo crontab -e

Add:

0 * * * * /usr/local/bin/update-dolt.sh

Enable and Start Services

sudo systemctl daemon-reload
sudo systemctl enable dolt-update.service
sudo systemctl enable dolt-server.service
sudo systemctl start dolt-update.service
sudo systemctl start dolt-server.service

Connecting to the Database

From the Command Line

# Connect as the root user
dolt -u root -p "" sql

# Connect as the application user
dolt -u nocodb -p "securepassword" sql

From Application (e.g., NocoDB)

Connection details:

• Host: Server IP address
• Port: 3306
• Username: nocodb
• Password: securepassword
• Database: fb_api_public

Troubleshooting

Check Service Status

sudo systemctl status dolt-server.service
sudo systemctl status dolt-update.service

View Update Logs

cat /var/log/dolt-update.log

Manual Database Operations

cd /dolt/dbs/fb-api-public

# Check status
dolt status

# View remote configuration
dolt remote -v

# Manually pull updates
dolt pull origin main

Restart Services

sudo systemctl restart dolt-update.service
sudo systemctl restart dolt-server.service

Notes

• The server runs in read-write mode to allow updates from DoltHub
• Local changes will be overwritten by the scheduled pull
• All permanent changes should be made through DoltHub's PR process
• The database is automatically updated hourly and on system boot
• Both services are configured to start automatically on system boot
• User authentication is handled with a password for security

This configuration provides a robust, automatically updating Dolt database server that maintains synchronization with DoltHub while providing MySQL-compatible access for applications.