知识库

Appearance

Return to top
On this page

NestJS 数据库集成 (Database Integration)

NestJS 提供了多种方式来集成数据库，支持关系型数据库和非关系型数据库。NestJS 本身不强制使用特定的数据库或 ORM，但提供了与流行数据库库的集成方式，如 TypeORM、Sequelize、Mongoose 等。

基础概念

NestJS 中的数据库集成涉及：

  1. 数据源管理 - 配置和管理数据库连接
  2. 实体/模型定义 - 定义数据结构
  3. 仓储模式 - 数据访问层的实现
  4. 事务处理 - 确保数据一致性
  5. 查询构建 - 构建复杂查询

TypeORM 集成

TypeORM 是 NestJS 推荐的关系型数据库 ORM，支持多种数据库系统。

安装依赖

bash
npm install @nestjs/typeorm typeorm mysql2
# 或者使用其他数据库驱动
npm install @nestjs/typeorm typeorm pg # PostgreSQL
npm install @nestjs/typeorm typeorm sqlite3 # SQLite
npm install @nestjs/typeorm typeorm mongodb # MongoDB

配置数据库连接

typescript
// app.module.ts
import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { AppController } from './app.controller';
import { AppService } from './app.service';

@Module({
  imports: [
    TypeOrmModule.forRoot({
      type: 'mysql',
      host: 'localhost',
      port: 3306,
      username: 'root',
      password: 'root',
      database: 'test',
      entities: [__dirname + '/**/*.entity{.ts,.js}'],
      synchronize: true, // 注意：生产环境中不建议使用
    }),
  ],
  controllers: [AppController],
  providers: [AppService],
})
export class AppModule {}

定义实体

typescript
// cat.entity.ts
import { Entity, Column, PrimaryGeneratedColumn, ManyToOne, OneToMany } from 'typeorm';
import { User } from './user.entity';

@Entity()
export class Cat {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column({ type: 'int' })
  age: number;

  @Column({ unique: true })
  breed: string;

  @ManyToOne(() => User, user => user.cats)
  owner: User;
}

使用 Repository

typescript
// cats.module.ts
import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { Cat } from './cat.entity';
import { CatsController } from './cats.controller';
import { CatsService } from './cats.service';

@Module({
  imports: [TypeOrmModule.forFeature([Cat])],
  controllers: [CatsController],
  providers: [CatsService],
  exports: [CatsService],
})
export class CatsModule {}
typescript
// cats.service.ts
import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { Cat } from './cat.entity';

@Injectable()
export class CatsService {
  constructor(
    @InjectRepository(Cat)
    private catsRepository: Repository<Cat>,
  ) {}

  create(cat: Partial<Cat>): Promise<Cat> {
    return this.catsRepository.save(cat);
  }

  findAll(): Promise<Cat[]> {
    return this.catsRepository.find();
  }

  findOne(id: number): Promise<Cat> {
    return this.catsRepository.findOne({ where: { id } });
  }

  async remove(id: number): Promise<void> {
    await this.catsRepository.delete(id);
  }
}

高级查询

typescript
@Injectable()
export class CatsService {
  constructor(
    @InjectRepository(Cat)
    private catsRepository: Repository<Cat>,
  ) {}

  // 使用查询构建器
  findCatsByName(name: string): Promise<Cat[]> {
    return this.catsRepository
      .createQueryBuilder('cat')
      .where('cat.name = :name', { name })
      .getMany();
  }

  // 关联查询
  findCatsWithOwner(): Promise<Cat[]> {
    return this.catsRepository
      .createQueryBuilder('cat')
      .leftJoinAndSelect('cat.owner', 'owner')
      .getMany();
  }

  // 复杂查询
  findCatsByAgeRange(minAge: number, maxAge: number): Promise<Cat[]> {
    return this.catsRepository
      .createQueryBuilder('cat')
      .where('cat.age BETWEEN :minAge AND :maxAge', { minAge, maxAge })
      .orderBy('cat.age', 'ASC')
      .getMany();
  }
}

Mongoose 集成 (MongoDB)

安装依赖

bash
npm install @nestjs/mongoose mongoose
npm install --save-dev @types/mongoose

配置 MongoDB 连接

typescript
// app.module.ts
import { Module } from '@nestjs/common';
import { MongooseModule } from '@nestjs/mongoose';
import { CatsModule } from './cats/cats.module';

@Module({
  imports: [
    MongooseModule.forRoot('mongodb://localhost/nest'),
    CatsModule,
  ],
})
export class AppModule {}

定义 Schema

typescript
// schemas/cat.schema.ts
import { Prop, Schema, SchemaFactory } from '@nestjs/mongoose';
import { Document } from 'mongoose';

export type CatDocument = Cat & Document;

@Schema()
export class Cat {
  @Prop()
  name: string;

  @Prop()
  age: number;

  @Prop()
  breed: string;
}

export const CatSchema = SchemaFactory.createForClass(Cat);

使用 Mongoose Model

typescript
// cats.module.ts
import { Module } from '@nestjs/common';
import { MongooseModule } from '@nestjs/mongoose';
import { Cat, CatSchema } from './schemas/cat.schema';
import { CatsController } from './cats.controller';
import { CatsService } from './cats.service';

@Module({
  imports: [MongooseModule.forFeature([{ name: Cat.name, schema: CatSchema }])],
  controllers: [CatsController],
  providers: [CatsService],
})
export class CatsModule {}
typescript
// cats.service.ts
import { Injectable } from '@nestjs/common';
import { InjectModel } from '@nestjs/mongoose';
import { Model } from 'mongoose';
import { Cat, CatDocument } from './schemas/cat.schema';

@Injectable()
export class CatsService {
  constructor(@InjectModel(Cat.name) private catModel: Model<CatDocument>) {}

  async create(createCatDto: any): Promise<Cat> {
    const createdCat = new this.catModel(createCatDto);
    return createdCat.save();
  }

  async findAll(): Promise<Cat[]> {
    return this.catModel.find().exec();
  }

  async findOne(id: string): Promise<Cat> {
    return this.catModel.findById(id).exec();
  }

  async remove(id: string) {
    const result = await this.catModel.deleteOne({ _id: id }).exec();
    if (result.deletedCount === 0) {
      throw new Error('Cat not found');
    }
  }
}

Sequelize 集成

安装依赖

bash
npm install @nestjs/sequelize sequelize mysql2
# 或者使用其他数据库驱动
npm install pg pg-hstore # PostgreSQL
npm install sqlite3 # SQLite

配置 Sequelize

typescript
// app.module.ts
import { Module } from '@nestjs/common';
import { SequelizeModule } from '@nestjs/sequelize';
import { CatsModule } from './cats/cats.module';

@Module({
  imports: [
    SequelizeModule.forRoot({
      dialect: 'mysql',
      host: 'localhost',
      port: 3306,
      username: 'root',
      password: 'root',
      database: 'test',
      autoLoadModels: true,
      synchronize: true,
    }),
    CatsModule,
  ],
})
export class AppModule {}

定义模型

typescript
// cat.model.ts
import { Table, Column, Model, DataType } from 'sequelize-typescript';

@Table
export class Cat extends Model<Cat> {
  @Column({
    type: DataType.STRING,
    allowNull: false,
  })
  name: string;

  @Column({
    type: DataType.INTEGER,
    allowNull: false,
  })
  age: number;

  @Column({
    type: DataType.STRING,
    allowNull: false,
  })
  breed: string;
}

使用 Sequelize Model

typescript
// cats.service.ts
import { Injectable } from '@nestjs/common';
import { InjectModel } from '@nestjs/sequelize';
import { Cat } from './cat.model';

@Injectable()
export class CatsService {
  constructor(@InjectModel(Cat) private catModel: typeof Cat) {}

  async create(createCatDto: any): Promise<Cat> {
    const cat = new this.catModel(createCatDto);
    return cat.save();
  }

  async findAll(): Promise<Cat[]> {
    return this.catModel.findAll();
  }

  async findOne(id: number): Promise<Cat> {
    return this.catModel.findByPk(id);
  }

  async remove(id: number) {
    const cat = await this.catModel.findByPk(id);
    if (cat) {
      await cat.destroy();
    }
  }
}

数据库连接配置

环境配置

typescript
// config/typeorm.config.ts
import { TypeOrmModuleOptions } from '@nestjs/typeorm';

export const typeOrmConfig: TypeOrmModuleOptions = {
  type: process.env.DB_TYPE as any,
  host: process.env.DB_HOST,
  port: parseInt(process.env.DB_PORT, 10),
  username: process.env.DB_USERNAME,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  entities: [__dirname + '/../**/*.entity{.ts,.js}'],
  synchronize: process.env.NODE_ENV !== 'production',
  logging: process.env.NODE_ENV !== 'production',
};
typescript
// app.module.ts
import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { typeOrmConfig } from './config/typeorm.config';

@Module({
  imports: [TypeOrmModule.forRoot(typeOrmConfig)],
})
export class AppModule {}

多数据库连接

typescript
@Module({
  imports: [
    TypeOrmModule.forRoot({
      name: 'default',
      type: 'mysql',
      host: 'localhost',
      port: 3306,
      username: 'root',
      password: 'root',
      database: 'main',
      entities: [User, Post],
    }),
    TypeOrmModule.forRoot({
      name: 'slave',
      type: 'mysql',
      host: 'slave_host',
      port: 3306,
      username: 'root',
      password: 'root',
      database: 'slave',
      entities: [Analytics],
    }),
  ],
})
export class AppModule {}
typescript
// 使用多连接
@Injectable()
export class UserService {
  constructor(
    @InjectRepository(User)
    private userRepository: Repository<User>,
    
    @InjectRepository(Analytics, 'slave')
    private analyticsRepository: Repository<Analytics>,
  ) {}
}

事务处理

TypeORM 事务

typescript
import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository, DataSource } from 'typeorm';
import { Cat } from './cat.entity';

@Injectable()
export class CatsService {
  constructor(
    @InjectRepository(Cat)
    private catsRepository: Repository<Cat>,
    private dataSource: DataSource,
  ) {}

  async createWithTransaction(cats: Partial<Cat>[]): Promise<Cat[]> {
    const queryRunner = this.dataSource.createQueryRunner();
    await queryRunner.connect();
    await queryRunner.startTransaction();
    
    try {
      const results: Cat[] = [];
      for (const cat of cats) {
        const savedCat = await queryRunner.manager.save(Cat, cat);
        results.push(savedCat);
      }
      
      await queryRunner.commitTransaction();
      return results;
    } catch (err) {
      await queryRunner.rollbackTransaction();
      throw err;
    } finally {
      await queryRunner.release();
    }
  }
}

使用 @Transactional 装饰器

typescript
import { Transaction, TransactionRepository } from 'typeorm';
import { Repository } from 'typeorm';

@Injectable()
export class CatsService {
  @Transaction()
  async transferCats(
    @TransactionRepository(Cat) catRepository: Repository<Cat>,
    catIds: number[],
    newOwnerId: number,
  ): Promise<void> {
    for (const catId of catIds) {
      const cat = await catRepository.findOne({ where: { id: catId } });
      if (cat) {
        cat.ownerId = newOwnerId;
        await catRepository.save(cat);
      }
    }
  }
}

迁移管理

TypeORM 迁移

typescript
// 创建迁移文件
// 1. 配置 CLI
{
  "typeOrmConfig": {
    "type": "mysql",
    "host": "localhost",
    "port": 3306,
    "username": "root",
    "password": "root",
    "database": "test",
    "entities": ["src/**/*.entity{.ts,.js}"],
    "migrations": ["src/migrations/*{.ts,.js}"],
    "cli": {
      "migrationsDir": "src/migrations"
    }
  }
}

// 2. 创建迁移
// npx typeorm migration:create src/migrations/CreateCatsTable
typescript
// src/migrations/CreateCatsTable.ts
import { MigrationInterface, QueryRunner, Table } from 'typeorm';

export class CreateCatsTable1620000000000 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.createTable(new Table({
      name: 'cat',
      columns: [
        {
          name: 'id',
          type: 'int',
          isPrimary: true,
          isGenerated: true,
          generationStrategy: 'increment',
        },
        {
          name: 'name',
          type: 'varchar',
          length: '255',
        },
        {
          name: 'age',
          type: 'int',
        },
        {
          name: 'breed',
          type: 'varchar',
          length: '255',
        },
      ],
    }));
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.dropTable('cat');
  }
}

查询优化

索引定义

typescript
// 使用装饰器定义索引
@Entity()
@Index(['name', 'breed']) // 复合索引
@Index('cat_name_index', { synchronize: false }) // 自定义索引名
export class Cat {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ index: true }) // 单列索引
  name: string;

  @Column({ type: 'int' })
  age: number;

  @Column({ unique: true }) // 唯一索引
  breed: string;
}

分页查询

typescript
@Injectable()
export class CatsService {
  async findWithPagination(
    page: number,
    limit: number,
  ): Promise<{ data: Cat[]; total: number; page: number; limit: number }> {
    const [data, total] = await this.catsRepository.findAndCount({
      skip: (page - 1) * limit,
      take: limit,
    });

    return {
      data,
      total,
      page,
      limit,
    };
  }
}

数据库测试

测试配置

typescript
// test/cat.e2e-spec.ts
import { Test, TestingModule } from '@nestjs/testing';
import { INestApplication } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { CatsModule } from '../src/cats/cats.module';
import { Cat } from '../src/cats/cat.entity';

describe('CatsController (e2e)', () => {
  let app: INestApplication;

  beforeAll(async () => {
    const moduleFixture: TestingModule = await Test.createTestingModule({
      imports: [
        CatsModule,
        TypeOrmModule.forRoot({
          type: 'sqlite',
          database: ':memory:',
          entities: [Cat],
          synchronize: true,
        }),
      ],
    }).compile();

    app = moduleFixture.createNestApplication();
    await app.init();
  });

  afterAll(async () => {
    await app.close();
  });

  // 测试用例...
});

数据库最佳实践

1. 连接池配置

typescript
TypeOrmModule.forRoot({
  type: 'mysql',
  host: 'localhost',
  port: 3306,
  username: 'root',
  password: 'root',
  database: 'test',
  entities: [__dirname + '/**/*.entity{.ts,.js}'],
  // 连接池配置
  poolSize: 10,
  acquireTimeout: 30000,
  connectionTimeout: 2000,
  extra: {
    connectionLimit: 10,
    queueLimit: 0,
    acquireTimeout: 60000,
  },
  // 其他配置
  synchronize: false,
  logging: ['error'],
}),

2. 实体设计最佳实践

typescript
@Entity('cats') // 明确指定表名
@Unique(['name', 'breed']) // 唯一约束
export class Cat {
  @PrimaryGeneratedColumn('increment')
  id: number;

  @Column({
    type: 'varchar',
    length: 255,
    nullable: false,
    unique: true,
  })
  name: string;

  @Column({
    type: 'int',
    nullable: false,
    default: 0,
  })
  age: number;

  @Column({
    type: 'varchar',
    length: 255,
    nullable: false,
  })
  breed: string;

  @CreateDateColumn()
  createdAt: Date;

  @UpdateDateColumn()
  updatedAt: Date;

  @DeleteDateColumn() // 软删除
  deletedAt?: Date;
}

3. 仓储模式实现

typescript
export interface ICatRepository {
  create(cat: Partial<Cat>): Promise<Cat>;
  findById(id: number): Promise<Cat>;
  findByName(name: string): Promise<Cat>;
  findAll(): Promise<Cat[]>;
  update(id: number, updateData: Partial<Cat>): Promise<Cat>;
  delete(id: number): Promise<void>;
}

@Injectable()
export class CatRepository implements ICatRepository {
  constructor(
    @InjectRepository(Cat)
    private catsRepository: Repository<Cat>,
  ) {}

  async create(cat: Partial<Cat>): Promise<Cat> {
    const newCat = this.catsRepository.create(cat);
    return this.catsRepository.save(newCat);
  }

  async findById(id: number): Promise<Cat> {
    return this.catsRepository.findOne({ where: { id } });
  }

  async findByName(name: string): Promise<Cat> {
    return this.catsRepository.findOne({ where: { name } });
  }

  async findAll(): Promise<Cat[]> {
    return this.catsRepository.find();
  }

  async update(id: number, updateData: Partial<Cat>): Promise<Cat> {
    await this.catsRepository.update(id, updateData);
    return this.findById(id);
  }

  async delete(id: number): Promise<void> {
    await this.catsRepository.softDelete(id); // 使用软删除
  }
}

性能优化

1. 查询优化

typescript
@Injectable()
export class CatsService {
  // 使用 select 只获取需要的字段
  async findNamesOnly(): Promise<{ name: string }[]> {
    return this.catsRepository.find({
      select: ['name'],
    });
  }

  // 使用 relations 预加载关联数据
  async findWithOwner(): Promise<Cat[]> {
    return this.catsRepository.find({
      relations: ['owner'],
    });
  }

  // 使用 query builder 进行复杂查询
  async findCatsWithAgeAndOwner(minAge: number): Promise<Cat[]> {
    return this.catsRepository
      .createQueryBuilder('cat')
      .leftJoinAndSelect('cat.owner', 'owner')
      .where('cat.age >= :minAge', { minAge })
      .getMany();
  }
}

2. 缓存策略

typescript
import { CacheService } from '@nestjs/cache-manager';

@Injectable()
export class CatsService {
  constructor(
    @InjectRepository(Cat)
    private catsRepository: Repository<Cat>,
    private cacheManager: CacheService,
  ) {}

  async findAllWithCache(): Promise<Cat[]> {
    const cacheKey = 'cats:all';
    let cats = await this.cacheManager.get<Cat[]>(cacheKey);
    
    if (!cats) {
      cats = await this.catsRepository.find();
      await this.cacheManager.set(cacheKey, cats, 60000); // 缓存1分钟
    }
    
    return cats;
  }
}

总结

NestJS 提供了灵活的数据库集成方案，支持多种流行的数据库和 ORM。通过适当的配置和最佳实践，可以构建高性能、可维护的数据库驱动应用程序。

数据库集成的主要特点：

  • 支持多种数据库系统和 ORM
  • 提供依赖注入集成
  • 支持事务处理
  • 支持迁移管理
  • 提供查询优化工具
  • 支持连接池配置
  • 包含测试工具