Database Management - Migz93/3dq GitHub Wiki

Database Management

3DQ uses SQLite for data storage, which provides a lightweight, file-based database solution. This guide explains how to manage the 3DQ database.

Database Location

The 3DQ database is stored in the /config directory inside the container, which is mapped to a directory on your host system (e.g., ~/3dq-data or ./data). The database file is named 3dq.sqlite.

Database Initialization

When 3DQ starts for the first time, it will automatically:

  1. Create a new SQLite database file if one doesn't exist
  2. Initialize the database schema with all required tables
  3. Set up any initial data or default settings

Database Configuration

The application uses the following environment variables for database configuration:

  • NODE_ENV: Set to 'production' for production use
  • CONFIG_DIR: Directory where the database and configuration files are stored (defaults to '/config' in Docker)

Database Backup

Regular backups are essential to prevent data loss. Here's how to back up your 3DQ database:

Manual Backup

# Stop the container to ensure data integrity
docker stop 3dq

# Copy the database file to a backup location
cp ~/3dq-data/3dq.sqlite ~/3dq-backups/3dq-backup-$(date +%Y%m%d).sqlite

# Restart the container
docker start 3dq

Automated Backup

You can create a simple cron job to automate backups:

# Create a backup script
cat > ~/backup-3dq.sh << 'EOF'
#!/bin/bash
BACKUP_DIR=~/3dq-backups
mkdir -p $BACKUP_DIR
docker stop 3dq
cp ~/3dq-data/3dq.sqlite $BACKUP_DIR/3dq-backup-$(date +%Y%m%d).sqlite
docker start 3dq
# Keep only the last 7 backups
ls -t $BACKUP_DIR/3dq-backup-* | tail -n +8 | xargs rm -f
EOF

# Make it executable
chmod +x ~/backup-3dq.sh

# Add to crontab (runs every Sunday at 2 AM)
(crontab -l 2>/dev/null; echo "0 2 * * 0 ~/backup-3dq.sh") | crontab -

Database Restoration

If you need to restore from a backup:

# Stop the container
docker stop 3dq

# Backup the current database (just in case)
cp ~/3dq-data/3dq.sqlite ~/3dq-data/3dq.sqlite.before-restore

# Copy the backup file to the data directory
cp ~/3dq-backups/3dq-backup-20230101.sqlite ~/3dq-data/3dq.sqlite

# Restart the container
docker start 3dq

Database Reinitialization

If your database becomes corrupted or you want to start fresh:

# Stop the container
docker stop 3dq

# Backup the current database (just in case)
cp ~/3dq-data/3dq.sqlite ~/3dq-data/3dq.sqlite.backup

# Remove the database file
rm ~/3dq-data/3dq.sqlite

# Restart the container
docker start 3dq

The application will automatically create a new database with default data when it starts.

Database Migrations

3DQ uses a simple migration system to handle database schema updates. When the application starts, it checks the current database version and applies any pending migrations.

Migration Process

  1. The application checks the version key in the settings table to determine the current database version.
  2. If the database is new, it's created with the latest schema.
  3. If the database version is older than the current version, the appropriate migration scripts are run in order.
  4. After successful migration, the version number in the settings table is updated.

Migration History

Version 1.1.0

  • Added quantity column to the quotes table (default value: 1)
  • Added discount_percent column to the quotes table
  • Updated calculations to support quantity and discount features

Version 1.0.0

  • Initial database schema

Database Schema

The 3DQ database consists of several tables that store all application data. The schema is designed to be flexible and efficient for managing 3D printing quotes and related information.

Settings Table

Stores application settings as key-value pairs, including:

  • Application configuration
  • Tax rate
  • Company information
  • Integration settings (e.g., Spoolman URL)

Filaments Table

Stores information about filaments, including:

  • Name
  • Type (PLA, ABS, PETG, etc.)
  • Price per kg
  • Color (stored as hex code)
  • Vendor information

Printers Table

Stores information about printers, including:

  • Name and model
  • Purchase price
  • Service cost
  • Depreciation time (in hours)
  • Power usage (in watts)
  • Maintenance schedule

Hardware Table

Stores information about hardware components, including name and unit price.

Quotes Table

Stores information about quotes, including title, customer name, markup, and total cost.

Quote Filaments Table

Stores the filaments used in each quote, including quantity and cost.

Quote Hardware Table

Stores the hardware components used in each quote, including quantity and cost.

Quote Print Setup Table

Stores the print setup for each quote, including printer, print time, and costs.

Quote Labour Table

Stores the labor information for each quote, including design time, preparation time, and cost.

For a detailed schema reference, see the technical documentation.

Advanced Database Management

Accessing the Database Directly

For advanced users who need to access the database directly:

# Enter the container
docker exec -it 3dq /bin/bash

# Access the SQLite database
sqlite3 /config/3dq.sqlite

# Run SQL commands
.tables
SELECT * FROM settings;
.quit

# Exit the container
exit

Database Optimization

SQLite databases can benefit from occasional optimization:

# Enter the container
docker exec -it 3dq /bin/bash

# Optimize the database
sqlite3 /config/3dq.sqlite 'VACUUM;'

# Exit the container
exit

The VACUUM command rebuilds the database file, repacking it into a minimal amount of disk space.

Troubleshooting

Database Locked Errors

If you encounter "database is locked" errors:

  1. Stop the 3DQ container
  2. Wait a few seconds for any pending transactions to complete
  3. Restart the container

Database Corruption

Signs of database corruption include:

  • Application crashes
  • Error messages mentioning "malformed" or "corrupt" database
  • Missing or incomplete data

If you suspect corruption, follow the database reinitialization steps above.

Best Practices

  1. Regular backups: Schedule regular backups of your database
  2. Version control: Include the date in backup filenames
  3. Test restores: Periodically test restoring from backups
  4. Monitor disk space: Ensure sufficient disk space for database growth
  5. Update the application: Keep 3DQ updated to benefit from database improvements
⚠️ **GitHub.com Fallback** ⚠️