Nestjs框架: 基于TypeORM的多租户功能集成和优化

概述

• 我们现在要集成多租户的环境，考虑到使用2个mysql和1个postgresql的数据库
• 现在要集成到 nestjs 的项目中来，考虑到配置通用和性能的处理

配置docker-compose启动服务

docker-compose.multi.yaml

services:
  mysql:
    image: mysql:8
    container_name: mysql_app
    restart: always
    ports:
      - "13306:3306"
    environment:
      - MYSQL_ROOT_PASSWORD=123456_mysql
    volumes:
      # - ./mysql/conf.d:/etc/mysql/conf.d # 默认加载这里的配置
      - ./docker-dbconfig/mysql/data:/var/lib/mysql
      - ./docker-dbconfig/mysql/logs:/var/log/mysql
    networks:
      - light_network

  mysql2:
    image: mysql:8
    container_name: mysql_app2
    restart: always
    ports:
      - "13307:3306"
    environment:
      - MYSQL_ROOT_PASSWORD=123456_mysql
    volumes:
      # - ./mysql/conf.d:/etc/mysql/conf.d # 默认加载这里的配置
      - ./docker-dbconfig/mysql/data2:/var/lib/mysql
      - ./docker-dbconfig/mysql/logs2:/var/log/mysql
    networks:
      - light_network

  postgresql:
    image: postgres:16
    restart: always
    environment:
      POSTGRES_PASSWORD: 123456_postgresql
      POSTGRES_DB: testdb
      POSTGRES_USER: pguser # 注意，不能用 root
    ports:
      - 15432:5432
    volumes:
      - ./docker-dbconfig/postgres/data:/var/lib/postgresql/data
      - ./docker-dbconfig/postgres/logs:/var/log/postgresql
    networks:
      - light_network

  adminer:
    image: adminer:5.3.0
    container_name: adminer_app
    restart: always
    ports:
      - 18080:8080
    networks:
      - light_network

networks:
  light_network:
    external: true

执行 $ docker ps

CONTAINER ID   IMAGE                  COMMAND                   CREATED          STATUS         PORTS                                NAMES
6c8879abf8bd   mysql:8                "docker-entrypoint.s…"   10 seconds ago   Up 9 seconds   33060/tcp, 0.0.0.0:13306->3306/tcp   mysql_app
e8dfd71eab60   mysql:8                "docker-entrypoint.s…"   10 seconds ago   Up 9 seconds   33060/tcp, 0.0.0.0:13307->3306/tcp   mysql_app2
5737bcb3c24e   postgres:16            "docker-entrypoint.s…"   10 seconds ago   Up 9 seconds   0.0.0.0:15432->5432/tcp              hello-nest-postgresql-1
af724225edea   adminer:5.3.0          "entrypoint.sh docke…"   10 seconds ago   Up 9 seconds   0.0.0.0:18080->8080/tcp              adminer_app

• 这里能看到4个服务，其中3个数据库mysql, mysql2, postgresql 分别为: 13306, 13307, 15432

1 ) 连接 mysql

• 访问: http://localhost:18080

• 输入

  • 服务器: mysql
  • 用户名: root
  • 密码: 123456_mysql

• 连接上之后，创建数据库 testdb

2 ）连接另一台 mysql2

• 输入

  • 服务器: mysql2
  • 用户名: root
  • 密码: 123456_mysql

• 创建数据库 testdb

3 ) 连接 postgresql

• 系统选择：PostgreSQL

• 输入

  • 服务器: postgresql
  • 用户名: pguser
    • 注意这里不能用 root
  • 密码: 123456_postgresql

• 创建数据库 testdb

配置 TypeORM CLI 命令

• TypeORM 也是支持配置文件的，参考官网 ormconfig
• typeorm cli 和之前使用的 eslint 一样，也是支持配置文件的
• 配置之后，就可以使用 typeorm 提供的 init 方法来初始化数据库了

1 ) 安装依赖和配置

• $ pnpm add dotenv

• 配置 ormconfig.ts

  import {
       
        DataSource, DataSourceOptions } from 'typeorm';
  import * as dotenv from 'dotenv';
  import * as fs from 'fs';
  import {
       
        TypeOrmModuleOptions } from '@nestjs/typeorm';
  
  export function getEnv(env: string): Record<string, unknown> | undefined {
       
       
  	if (fs.existsSync(env)) {
       
       
  		return dotenv.parse(fs.readFileSync(env));
  	}
  }
  
  export function buildConnectionOptions(){
       
       
  	const defaultConfig = getEnv('.env');
  	const envConfig = getEnv(`.env.${
         
         process.env.NODE_ENV || 'development'}`);
  	const config = {
       
       ...defaultConfig, ...envConfig };
  	return {
       
       
  		type: config['DB_TYPE'],
  		host: config['DB_HOST'],
  		port: config['DB_PORT'],
  		username: config['DB_USERNAME'],
  		password: config ['DB_PASSWORD'],
  		database: config['DB_DATABASE'],
  		entities: [__dirname + '/**/*.entity{.ts,.js}'],
  		synchronize: Boolean(config['DB_SYNC']),
  		autoLoadEntities: Boolean(config['DB_AUTOLOAD']),
  	} as TypeOrmModuleOptions;
  }
  
  export default new DataSource({
       
       
  	...buildConnectionOptions(),
  } as DataSourceOptions);
  

2 ) 配置脚本并同步

• 配置 package.json 中的 scripts, 添加如下

  "typeorm": "typeorm-ts-node-commonjs -d ormconfig.ts",
  "typeorm:sync": "npm run typeorm schema:sync"
  

• 参考官网 using-cli

• 目前有2个mysql的服务和一个postgresql 的服务

  • mysql 的 .env 配置

    DB_TYPE=mysql
    DB_HOST=localhost
    DB_PORT=13306
    DB_USERNAME=root
    DB_PASSWORD=123456_mysql
    DB_DATABASE=testdb
    DB_AUTOLOAD=true
    DB_SYNC=true
    

  • mysql2 的 .env 配置

    DB_TYPE=mysql
    DB_HOST=localhost
    DB_PORT=13307
    DB_USERNAME=root
    DB_PASSWORD=123456_mysql
    DB_DATABASE=testdb
    DB_AUTOLOAD=true
    DB_SYNC=true
    

  • postgresql 的 .env 配置

    DB_TYPE=postgres
    DB_HOST=localhost
    DB_PORT=15432
    DB_USERNAME=pguser
    DB_PASSWORD=123456_postgresql
    DB_DATABASE=testdb
    DB_AUTOLOAD=true
    DB_SYNC=true
    

• 对于 postgres 数据库，需要安装依赖 $ pnpm add pg

• 切换上面的不同 .env 配置，分别执行 $ pnpm run typeorm:sync

• mysql 的输出

  query: SELECT VERSION() AS `version`
  query: START TRANSACTION
  query: SELECT DATABASE() AS `db_name`
  query: SELECT `TABLE_SCHEMA`, `TABLE_NAME`, `TABLE_COMMENT` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA` = 'testdb' AND `TABLE_NAME` = 'user' UNION SELECT `TABLE_SCHEMA`, `TABLE_NAME`, `TABLE_COMMENT` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA` = 'testdb' AND `TABLE_NAME` = 'user'
  query:
                  SELECT
                      *
                  FROM
                      `INFORMATION_SCHEMA`.`COLUMNS`
                  WHERE
                      `TABLE_SCHEMA` = 'testdb'
                      AND
                      `TABLE_NAME` = 'user'
  
  query: SELECT * FROM (
                  SELECT
                      *
                  FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` `kcu`
                  WHERE
                      `kcu`.`TABLE_SCHEMA` = 'testdb'
                      AND
                      `kcu`.`TABLE_NAME` = 'user'
              ) `kcu` WHERE `CONSTRAINT_NAME` = 'PRIMARY'
  query:
              SELECT
                  `SCHEMA_NAME`,
                  `DEFAULT_CHARACTER_SET_NAME` as `CHARSET`,
                  `DEFAULT_COLLATION_NAME` AS `COLLATION`
              FROM `INFORMATION_SCHEMA`.`SCHEMATA`
  
  query:
              SELECT
                  `s`.*
              FROM (
                  SELECT
                      *
                  FROM `INFORMATION_SCHEMA`.`STATISTICS`
                  WHERE
                      `TABLE_SCHEMA` = 'testdb'
                      AND
                      `TABLE_NAME` = 'user'
              ) `s`
              LEFT JOIN (
                  SELECT
                      *
                  FROM `INFORMATION_SCHEMA`.`REFERENTIAL_CONSTRAINTS`
                  WHERE
                      `CONSTRAINT_SCHEMA` = 'testdb'
                      AND
                      `TABLE_NAME` = 'user'
              ) `rc`
                  ON
                      `s`.`INDEX_NAME` = `rc`.`CONSTRAINT_NAME`
                      AND
                      `s`.`TABLE_SCHEMA` = `rc`.`CONSTRAINT_SCHEMA`
              WHERE
                  `s`.`INDEX_NAME` != 'PRIMARY'
                  AND
                  `rc`.`CONSTRAINT_NAME` IS NULL
  
  query:
              SELECT
                  `kcu`.`TABLE_SCHEMA`,
                  `kcu`.`TABLE_NAME`,
                  `kcu`.`CONSTRAINT_NAME`,
                  `kcu`.`COLUMN_NAME`,
                  `kcu`.`REFERENCED_TABLE_SCHEMA`,
                  `kcu`.`REFERENCED_TABLE_NAME`,
                  `kcu`.`REFERENCED_COLUMN_NAME`,
                  `rc`.`DELETE_RULE` `ON_DELETE`,
                  `rc`.`UPDATE_RULE` `ON_UPDATE`
              FROM (
                  SELECT
                      *
                  FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` `kcu`
                  WHERE
                      `kcu`.`TABLE_SCHEMA` = 'testdb'
                      AND
                      `kcu`.`TABLE_NAME` = 'user'
              ) `kcu`
              INNER JOIN (
                  SELECT
                      *
                  FROM `INFORMATION_SCHEMA`.`REFERENTIAL_CONSTRAINTS`
                  WHERE
                      `CONSTRAINT_SCHEMA` = 'testdb'
                      AND
                      `TABLE_NAME` = 'user'