Laravel中通过API实现运行时添加与使用数据库

一、核心逻辑梳理 #

• 用默认数据库myproject存储组织基础信息（ID、专属库名称、认证信息等）
• 组织注册完成后，自动创建专属数据库并初始化业务表结构
• 组织登录时切换数据库连接至其专属库，后续所有业务操作均使用该连接

二、Laravel后端实现步骤 #

1. 组织模型与默认数据库配置 #

• 在默认数据库中创建organizations表，执行以下命令生成模型和迁移文件：

php artisan make:model Organization -m

• 修改迁移文件（database/migrations/xxxx_xx_xx_create_organizations_table.php）：

public function up()
{
    Schema::create('organizations', function (Blueprint $table) {
        $table->id();
        $table->string('name')->unique();
        $table->string('db_name')->unique();
        $table->string('db_username')->default(env('DB_USERNAME'));
        $table->string('db_password')->default(env('DB_PASSWORD'));
        $table->timestamps();
    });
}

• 执行迁移：

php artisan migrate

2. 注册时创建组织专属数据库 #

• 创建注册API控制器：

php artisan make:controller Api/OrganizationRegistrationController

• 控制器核心代码：

namespace App\Http\Controllers\Api;

use App\Models\Organization;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class OrganizationRegistrationController extends Controller
{
    public function register(Request $request)
    {
        $validated = $request->validate([
            'name' => 'required|string|max:255',
            'password' => 'required|string|min:6',
        ]);

        // 生成唯一专属库名称
        $dbName = 'org_' . str()->slug($validated['name']) . '_' . uniqid();

        // 创建数据库
        try {
            DB::statement("CREATE DATABASE `$dbName`");
        } catch (\Exception $e) {
            return response()->json(['error' => '专属数据库创建失败'], 500);
        }

        // 保存组织信息到默认库
        $organization = Organization::create([
            'name' => $validated['name'],
            'db_name' => $dbName,
            'password' => bcrypt($validated['password']),
        ]);

        // 初始化专属库表结构
        $this->initTenantDb($dbName);

        return response()->json(['message' => '注册成功', 'org_id' => $organization->id], 201);
    }

    private function initTenantDb(string $dbName)
    {
        // 临时配置专属库连接
        config(['database.connections.tenant' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST'),
            'port' => env('DB_PORT'),
            'database' => $dbName,
            'username' => env('DB_USERNAME'),
            'password' => env('DB_PASSWORD'),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
        ]]);

        // 复制默认库的业务表结构（排除系统表）
        $defaultTables = Schema::getConnection()->getDoctrineSchemaManager()->listTableNames();
        $excludeTables = ['migrations', 'organizations', 'password_resets', 'personal_access_tokens'];
        $businessTables = array_diff($defaultTables, $excludeTables);

        foreach ($businessTables as $table) {
            $tableSchema = Schema::getConnection()->getDoctrineSchemaManager()->listTableDetails($table);
            $createSql = $tableSchema->toSql(Schema::getConnection()->getDoctrineConnection()->getDatabasePlatform());
            DB::connection('tenant')->statement($createSql[0]);
        }
    }
}

• 在routes/api.php注册路由：

Route::post('/org/register', [App\Http\Controllers\Api\OrganizationRegistrationController::class, 'register']);

3. 登录时切换数据库连接 #

• 创建登录API控制器：

php artisan make:controller Api/OrganizationLoginController

• 控制器核心代码：

namespace App\Http\Controllers\Api;

use App\Models\Organization;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Config;
use Illuminate\Support\Facades\DB;

class OrganizationLoginController extends Controller
{
    public function login(Request $request)
    {
        $validated = $request->validate([
            'name' => 'required|string',
            'password' => 'required|string',
        ]);

        $organization = Organization::where('name', $validated['name'])->first();
        if (!$organization || !password_verify($validated['password'], $organization->password)) {
            return response()->json(['error' => '认证失败'], 401);
        }

        // 生成API令牌
        $token = $organization->createToken('org_auth')->plainTextToken;

        // 切换到专属库连接
        $this->switchToTenantDb($organization);

        return response()->json([
            'access_token' => $token,
            'token_type' => 'Bearer',
            'org_name' => $organization->name
        ]);
    }

    private function switchToTenantDb(Organization $org)
    {
        Config::set('database.connections.tenant', [
            'driver' => 'mysql',
            'host' => env('DB_HOST'),
            'port' => env('DB_PORT'),
            'database' => $org->db_name,
            'username' => $org->db_username,
            'password' => $org->db_password,
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
        ]);

        // 设置默认连接为专属库
        Config::set('database.default', 'tenant');
        DB::purge('tenant');
        DB::reconnect('tenant');
    }
}

• 注册登录路由：

Route::post('/org/login', [App\Http\Controllers\Api\OrganizationLoginController::class, 'login']);

4. 全局中间件自动切换数据库 #

• 创建中间件：

php artisan make:middleware TenantDbMiddleware

• 中间件代码：

namespace App\Http\Middleware;

use App\Models\Organization;
use Closure;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Config;
use Illuminate\Support\Facades\DB;

class TenantDbMiddleware
{
    public function handle(Request $request, Closure $next)
    {
        $token = $request->bearerToken();
        if (!$token) {
            return response()->json(['error' => '未提供认证令牌'], 401);
        }

        // 通过令牌获取组织信息
        $organization = Organization::whereHas('tokens', function ($query) use ($token) {
            $query->where('token', hash('sha256', $token));
        })->first();

        if (!$organization) {
            return response()->json(['error' => '无效令牌'], 401);
        }

        // 切换数据库连接
        Config::set('database.connections.tenant', [
            'driver' => 'mysql',
            'host' => env('DB_HOST'),
            'port' => env('DB_PORT'),
            'database' => $organization->db_name,
            'username' => $organization->db_username,
            'password' => $organization->db_password,
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
        ]);

        Config::set('database.default', 'tenant');
        DB::purge('tenant');
        DB::reconnect('tenant');

        return $next($request);
    }
}

• 在app/Http/Kernel.php注册中间件：

protected $routeMiddleware = [
    // 其他中间件
    'tenant.db' => \App\Http\Middleware\TenantDbMiddleware::class,
];

• 给业务路由添加中间件：

Route::middleware(['tenant.db'])->group(function () {
    Route::get('/orders', [App\Http\Controllers\Api\OrderController::class, 'index']);
    Route::post('/users', [App\Http\Controllers\Api\UserController::class, 'store']);
    // 其他业务路由
});

三、Angular前端配合实现 #

1. 组织注册组件 #

• 核心代码示例：

import { HttpClient } from '@angular/common/http';
import { Component } from '@angular/core';
import { Router } from '@angular/router';

@Component({
  selector: 'app-org-register',
  templateUrl: './org-register.component.html'
})
export class OrgRegisterComponent {
  orgName: string = '';
  password: string = '';

  constructor(private http: HttpClient, private router: Router) {}

  onSubmit() {
    this.http.post('/api/org/register', { name: this.orgName, password: this.password })
      .subscribe({
        next: () => {
          alert('注册成功，请登录');
          this.router.navigate(['/org-login']);
        },
        error: (err) => alert('注册失败：' + err.error.error)
      });
  }
}

2. 组织登录组件 #

• 核心代码示例：

import { HttpClient } from '@angular/common/http';
import { Component } from '@angular/core';
import { Router } from '@angular/router';

@Component({
  selector: 'app-org-login',
  templateUrl: './org-login.component.html'
})
export class OrgLoginComponent {
  orgName: string = '';
  password: string = '';

  constructor(private http: HttpClient, private router: Router) {}

  onSubmit() {
    this.http.post('/api/org/login', { name: this.orgName, password: this.password })
      .subscribe({
        next: (res: any) => {
          localStorage.setItem('auth_token', res.access_token);
          this.router.navigate(['/dashboard']);
        },
        error: (err) => alert('登录失败：' + err.error.error)
      });
  }
}

3. HTTP拦截器自动携带令牌 #

• 创建拦截器：

import { Injectable } from '@angular/core';
import { HttpRequest, HttpHandler, HttpEvent, HttpInterceptor } from '@angular/common/http';
import { Observable } from 'rxjs';

@Injectable()
export class AuthInterceptor implements HttpInterceptor {
  intercept(request: HttpRequest<unknown>, next: HttpHandler): Observable<HttpEvent<unknown>> {
    const token = localStorage.getItem('auth_token');
    if (token) {
      request = request.clone({
        setHeaders: { Authorization: `Bearer ${token}` }
      });
    }
    return next.handle(request);
  }
}

• 在app.module.ts注册拦截器：

import { HTTP_INTERCEPTORS } from '@angular/common/http';
import { AuthInterceptor } from './auth.interceptor';

@NgModule({
  providers: [
    { provide: HTTP_INTERCEPTORS, useClass: AuthInterceptor, multi: true }
  ]
})
export class AppModule { }

四、关键注意事项 #

• 确保数据库用户拥有CREATE DATABASE权限，否则无法创建专属库
• 初始化专属库时需排除系统表，避免冗余数据
• 生产环境建议使用Passport做令牌认证，增强安全性
• 可缓存组织的数据库配置，减少重复配置开销

内容的提问来源于stack exchange，提问作者Rakesh Hiray