Skip to main content
Wecode uses a relational database to store users, assignments, submissions, and test results. The system supports multiple database engines through Laravel’s database abstraction layer.

​
Supported Databases

Wecode supports the following database systems:
  • MySQL (recommended) - Version 5.7 or higher
  • PostgreSQL - Version 10 or higher
  • SQLite - Version 3.8 or higher (development only)
  • SQL Server - Version 2017 or higher
MySQL or PostgreSQL is recommended for production environments. SQLite should only be used for development and testing.

​
Database Connection

​
MySQL Configuration

Configure MySQL connection in your .env file:
.env
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=wecode
DB_USERNAME=wecode_user
DB_PASSWORD=your_secure_password

​
PostgreSQL Configuration

Configure PostgreSQL connection in your .env file:
.env
DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=wecode
DB_USERNAME=wecode_user
DB_PASSWORD=your_secure_password

​
SQLite Configuration

Configure SQLite connection (development only):
.env
DB_CONNECTION=sqlite
DB_DATABASE=/path/to/database.sqlite

​
Database Creation

​
MySQL

Create the database and user: 
-- Connect to MySQL as root
mysql -u root -p

-- Create database
CREATE DATABASE wecode CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create user and grant privileges
CREATE USER 'wecode_user'@'localhost' IDENTIFIED BY 'your_secure_password';
GRANT ALL PRIVILEGES ON wecode.* TO 'wecode_user'@'localhost';
FLUSH PRIVILEGES;

-- Exit
EXIT;

​
PostgreSQL

Create the database and user: 
-- Connect to PostgreSQL as postgres user
sudo -u postgres psql

-- Create user
CREATE USER wecode_user WITH PASSWORD 'your_secure_password';

-- Create database
CREATE DATABASE wecode OWNER wecode_user;

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE wecode TO wecode_user;

-- Exit
\q

​
SQLite

Create the SQLite database file: 
touch database/database.sqlite

​
Running Migrations

Migrations create the database schema (tables, indexes, etc.).

​
Initial Migration

Run migrations to create all tables: 
php artisan migrate:refresh
The migrate:refresh command drops all tables and recreates them. Only use this for initial setup or when explicitly needed. It will delete all data.

​
Safe Migrations

For production or when you have existing data: 
php artisan migrate
This runs only new migrations without dropping existing tables.

​
Check Migration Status

View which migrations have been run: 
php artisan migrate:status

​
Rollback Migrations

Rollback the last batch of migrations: 
php artisan migrate:rollback
Rollback all migrations: 
php artisan migrate:reset

​
Database Seeding

Seeding populates the database with initial data.

​
Installation Seeding

Run the installation seeder to create required initial data: 
php artisan db:seed --class=installation_seeding
This command is automatically run during installation (install.sh line 69).

​
What Gets Seeded

The installation seeder typically creates:
  • Default roles and permissions
  • System settings
  • Sample assignments (optional)
  • Test data (in development)

​
Custom Seeding

Run all seeders: 
php artisan db:seed
Run a specific seeder: 
php artisan db:seed --class=YourSeederClass

​
Complete Installation Flow

From install.sh, the complete database setup process: 
# 1. Generate application key (line 67)
php artisan key:generate

# 2. Run migrations and create schema (line 68)
php artisan migrate:refresh

# 3. Seed initial data (line 69)
php artisan db:seed --class=installation_seeding

# 4. Create admin users (lines 71-72)
php artisan add_admin truonganpn [email protected] password
php artisan add_admin username email password

​
Database Maintenance

​
Backup Database

Regular backups are essential. Choose the method for your database: 
# Create backup
mysqldump -u wecode_user -p wecode > backup_$(date +%Y%m%d_%H%M%S).sql

# Restore backup
mysql -u wecode_user -p wecode < backup_20260304_120000.sql

​
Automated Backups

Set up a cron job for automated daily backups: 
# Add to crontab (crontab -e)
0 2 * * * /path/to/backup_script.sh
Example backup script:
backup_script.sh
#!/bin/bash
BACKUP_DIR="/path/to/backups"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="wecode"
DB_USER="wecode_user"

# Create backup
mysqldump -u $DB_USER -p$DB_PASSWORD $DB_NAME > $BACKUP_DIR/backup_$DATE.sql

# Keep only last 7 days of backups
find $BACKUP_DIR -name "backup_*.sql" -mtime +7 -delete

​
Database Optimization

Optimize database tables (MySQL): 
mysql -u wecode_user -p -e "OPTIMIZE TABLE database_name.table_name;"

​
Clear Old Logs

Clear old submission logs and temporary data: 
php artisan wecode:cleanup-old-submissions

​
Connection Configuration

​
Connection Pooling

For high-traffic installations, configure connection pooling in config/database.php: 
'mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'wecode'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'prefix_indexes' => true,
    'strict' => true,
    'engine' => null,
],

​
SSL Connection

For secure connections, add SSL configuration:
.env
MYSQL_ATTR_SSL_CA=/path/to/ca-cert.pem

​
Read/Write Splitting

Configure separate read and write connections for better performance: 
'mysql' => [
    'read' => [
        'host' => ['192.168.1.1', '192.168.1.2'],
    ],
    'write' => [
        'host' => ['192.168.1.3'],
    ],
    'driver' => 'mysql',
    'database' => 'wecode',
    // ... other settings
],

​
Troubleshooting

​
Connection Refused

Problem: SQLSTATE[HY000] [2002] Connection refused Solutions:
  1. Verify database service is running: 
    sudo systemctl status mysql
  2. Check DB_HOST and DB_PORT in .env
  3. Verify firewall allows connections

​
Access Denied

Problem: SQLSTATE[HY000] [1045] Access denied for user Solutions:
  1. Verify credentials in .env file
  2. Check user has correct privileges: 
    SHOW GRANTS FOR 'wecode_user'@'localhost';
  3. Reset user password if needed

​
Database Not Found

Problem: SQLSTATE[HY000] [1049] Unknown database Solution: Create the database: 
CREATE DATABASE wecode;

​
Migration Fails

Problem: Migration errors during php artisan migrate Solutions:
  1. Check database connection
  2. Verify user has CREATE/ALTER privileges
  3. Check for existing tables: php artisan migrate:status
  4. If needed, rollback and retry: 
    php artisan migrate:rollback
php artisan migrate

​
Best Practices

  1. Regular Backups: Implement automated daily backups
  2. Monitoring: Monitor database performance and slow queries
  3. Indexing: Ensure proper indexes on frequently queried columns
  4. Security: Use strong passwords and limit database user privileges
  5. Updates: Keep database software updated with security patches
  6. Testing: Test migrations on staging before production
  7. Documentation: Document any custom schema changes
  8. Disk Space: Monitor disk space usage regularly

Build docs developers (and LLMs) love

Get started for freeTalk to us