NestJS中集成TypeORM进行数据库操作_Node.js

本文深入探讨了如何在nestjs中集成typeorm进行数据库操作,包括typeorm的配置和集成、实体设计和关系映射、repository模式的应用、事务处理方案、数据库迁移管理、性能优化策略。

typeorm 集成配置

1. 安装依赖

首先安装必要的依赖包：

npm install @nestjs/typeorm typeorm pg
# 如果使用 mysql
# npm install @nestjs/typeorm typeorm mysql2

2. 数据库配置

// src/config/database.config.ts
import { typeormmoduleoptions } from '@nestjs/typeorm';

export const databaseconfig: typeormmoduleoptions = {
  type: 'postgres',
  host: process.env.db_host || 'localhost',
  port: parseint(process.env.db_port) || 5432,
  username: process.env.db_username || 'postgres',
  password: process.env.db_password || 'postgres',
  database: process.env.db_database || 'nestjs_db',
  entities: ['dist/**/*.entity{.ts,.js}'],
  synchronize: process.env.node_env !== 'production',
  logging: process.env.node_env !== 'production',
  ssl: process.env.db_ssl === 'true',
};

// src/app.module.ts
import { module } from '@nestjs/common';
import { typeormmodule } from '@nestjs/typeorm';
import { databaseconfig } from './config/database.config';

@module({
  imports: [
    typeormmodule.forroot(databaseconfig),
    // 其他模块
  ],
})
export class appmodule {}

实体设计与关系映射

1. 基础实体设计

// src/entities/base.entity.ts
import { 
  primarygeneratedcolumn, 
  createdatecolumn, 
  updatedatecolumn,
  deletedatecolumn 
} from 'typeorm';

export abstract class baseentity {
  @primarygeneratedcolumn('uuid')
  id: string;

  @createdatecolumn()
  createdat: date;

  @updatedatecolumn()
  updatedat: date;

  @deletedatecolumn()
  deletedat: date;
}

// src/users/entities/user.entity.ts
import { entity, column, onetomany } from 'typeorm';
import { baseentity } from '../entities/base.entity';
import { post } from './post.entity';

@entity('users')
export class user extends baseentity {
  @column({ length: 100 })
  name: string;

  @column({ unique: true })
  email: string;

  @column({ select: false })
  password: string;

  @onetomany(() => post, post => post.author)
  posts: post[];
}

// src/posts/entities/post.entity.ts
import { entity, column, manytoone, joincolumn } from 'typeorm';
import { baseentity } from '../entities/base.entity';
import { user } from './user.entity';

@entity('posts')
export class post extends baseentity {
  @column()
  title: string;

  @column('text')
  content: string;

  @column({ default: false })
  published: boolean;

  @manytoone(() => user, user => user.posts)
  @joincolumn({ name: 'author_id' })
  author: user;
}

2. 关系映射策略

// src/users/entities/profile.entity.ts
import { entity, column, onetoone, joincolumn } from 'typeorm';
import { baseentity } from '../entities/base.entity';
import { user } from './user.entity';

@entity('profiles')
export class profile extends baseentity {
  @column()
  avatar: string;

  @column('text')
  bio: string;

  @onetoone(() => user)
  @joincolumn({ name: 'user_id' })
  user: user;
}

// src/posts/entities/tag.entity.ts
import { entity, column, manytomany } from 'typeorm';
import { baseentity } from '../entities/base.entity';
import { post } from './post.entity';

@entity('tags')
export class tag extends baseentity {
  @column({ unique: true })
  name: string;

  @manytomany(() => post, post => post.tags)
  posts: post[];
}

// 更新 post 实体，添加标签关系
@entity('posts')
export class post extends baseentity {
  // ... 其他字段

  @manytomany(() => tag, tag => tag.posts)
  @jointable({
    name: 'posts_tags',
    joincolumn: { name: 'post_id' },
    inversejoincolumn: { name: 'tag_id' }
  })
  tags: tag[];
}

数据库操作实现

1. repository 模式

// src/users/users.service.ts
import { injectable, notfoundexception } from '@nestjs/common';
import { injectrepository } from '@nestjs/typeorm';
import { repository } from 'typeorm';
import { user } from './entities/user.entity';
import { createuserdto, updateuserdto } from './dto';

@injectable()
export class usersservice {
  constructor(
    @injectrepository(user)
    private usersrepository: repository<user>
  ) {}

  async create(createuserdto: createuserdto): promise<user> {
    const user = this.usersrepository.create(createuserdto);
    return await this.usersrepository.save(user);
  }

  async findall(): promise<user[]> {
    return await this.usersrepository.find({
      relations: ['posts', 'profile']
    });
  }

  async findone(id: string): promise<user> {
    const user = await this.usersrepository.findone({
      where: { id },
      relations: ['posts', 'profile']
    });

    if (!user) {
      throw new notfoundexception(`user with id ${id} not found`);
    }

    return user;
  }

  async update(id: string, updateuserdto: updateuserdto): promise<user> {
    const user = await this.findone(id);
    object.assign(user, updateuserdto);
    return await this.usersrepository.save(user);
  }

  async remove(id: string): promise<void> {
    const user = await this.findone(id);
    await this.usersrepository.softremove(user);
  }
}

2. 查询构建器

// src/posts/posts.service.ts
import { injectable } from '@nestjs/common';
import { injectrepository } from '@nestjs/typeorm';
import { repository } from 'typeorm';
import { post } from './entities/post.entity';

@injectable()
export class postsservice {
  constructor(
    @injectrepository(post)
    private postsrepository: repository<post>
  ) {}

  async findpublishedposts() {
    return await this.postsrepository
      .createquerybuilder('post')
      .leftjoinandselect('post.author', 'author')
      .leftjoinandselect('post.tags', 'tags')
      .where('post.published = :published', { published: true })
      .orderby('post.createdat', 'desc')
      .getmany();
  }

  async searchposts(query: string) {
    return await this.postsrepository
      .createquerybuilder('post')
      .leftjoinandselect('post.author', 'author')
      .where('post.title ilike :query or post.content ilike :query', {
        query: `%${query}%`
      })
      .orderby('post.createdat', 'desc')
      .getmany();
  }

  async getpoststats() {
    return await this.postsrepository
      .createquerybuilder('post')
      .select('author.name', 'authorname')
      .addselect('count(*)', 'postcount')
      .leftjoin('post.author', 'author')
      .groupby('author.name')
      .getrawmany();
  }
}

事务处理

1. 事务装饰器

// src/common/decorators/transaction.decorator.ts
import { createparamdecorator, executioncontext } from '@nestjs/common';
import { getmanager } from 'typeorm';

export const transaction = createparamdecorator(
  async (data: unknown, ctx: executioncontext) => {
    const queryrunner = getmanager().connection.createqueryrunner();
    await queryrunner.connect();
    await queryrunner.starttransaction();
    return queryrunner;
  }
);

// 使用示例
@post('transfer')
async transfer(
  @transaction() queryrunner,
  @body() transferdto: transferdto
) {
  try {
    // 执行转账操作
    await queryrunner.manager.update(account, 
      transferdto.fromid, 
      { balance: () => `balance - ${transferdto.amount}` }
    );

    await queryrunner.manager.update(account, 
      transferdto.toid, 
      { balance: () => `balance + ${transferdto.amount}` }
    );

    await queryrunner.committransaction();
  } catch (err) {
    await queryrunner.rollbacktransaction();
    throw err;
  } finally {
    await queryrunner.release();
  }
}

2. 事务管理器

// src/common/services/transaction.service.ts
import { injectable } from '@nestjs/common';
import { connection, queryrunner } from 'typeorm';

@injectable()
export class transactionservice {
  constructor(private connection: connection) {}

  async executeintransaction<t>(
    callback: (queryrunner: queryrunner) => promise<t>
  ): promise<t> {
    const queryrunner = this.connection.createqueryrunner();
    await queryrunner.connect();
    await queryrunner.starttransaction();

    try {
      const result = await callback(queryrunner);
      await queryrunner.committransaction();
      return result;
    } catch (err) {
      await queryrunner.rollbacktransaction();
      throw err;
    } finally {
      await queryrunner.release();
    }
  }
}

// 使用示例
@injectable()
export class paymentservice {
  constructor(
    private transactionservice: transactionservice,
    private ordersservice: ordersservice
  ) {}

  async processpayment(paymentdto: paymentdto) {
    return await this.transactionservice.executeintransaction(async queryrunner => {
      const order = await this.ordersservice.findone(paymentdto.orderid);
      
      // 更新订单状态
      await queryrunner.manager.update(order, order.id, {
        status: 'paid'
      });

      // 创建支付记录
      const payment = queryrunner.manager.create(payment, {
        order,
        amount: paymentdto.amount
      });
      await queryrunner.manager.save(payment);

      return payment;
    });
  }
}

数据库迁移

1. 迁移配置

// ormconfig.js
module.exports = {
  type: 'postgres',
  host: process.env.db_host,
  port: parseint(process.env.db_port),
  username: process.env.db_username,
  password: process.env.db_password,
  database: process.env.db_database,
  entities: ['dist/**/*.entity{.ts,.js}'],
  migrations: ['dist/migrations/*{.ts,.js}'],
  cli: {
    migrationsdir: 'src/migrations'
  }
};

// package.json
{
  "scripts": {
    "typeorm": "ts-node -r tsconfig-paths/register ./node_modules/typeorm/cli.js",
    "migration:create": "npm run typeorm migration:create -- -n",
    "migration:generate": "npm run typeorm migration:generate -- -n",
    "migration:run": "npm run typeorm migration:run",
    "migration:revert": "npm run typeorm migration:revert"
  }
}

2. 迁移示例

// src/migrations/1642340914321-createuserstable.ts
import { migrationinterface, queryrunner, table } from 'typeorm';

export class createuserstable1642340914321 implements migrationinterface {
  public async up(queryrunner: queryrunner): promise<void> {
    await queryrunner.createtable(
      new table({
        name: 'users',
        columns: [
          {
            name: 'id',
            type: 'uuid',
            isprimary: true,
            generationstrategy: 'uuid',
            default: 'uuid_generate_v4()'
          },
          {
            name: 'name',
            type: 'varchar',
            length: '100'
          },
          {
            name: 'email',
            type: 'varchar',
            isunique: true
          },
          {
            name: 'password',
            type: 'varchar'
          },
          {
            name: 'created_at',
            type: 'timestamp',
            default: 'now()'
          },
          {
            name: 'updated_at',
            type: 'timestamp',
            default: 'now()'
          },
          {
            name: 'deleted_at',
            type: 'timestamp',
            isnullable: true
          }
        ]
      })
    );
  }

  public async down(queryrunner: queryrunner): promise<void> {
    await queryrunner.droptable('users');
  }
}

性能优化

1. 查询优化

// src/posts/posts.service.ts
@injectable()
export class postsservice {
  constructor(
    @injectrepository(post)
    private postsrepository: repository<post>
  ) {}

  // 使用分页和缓存
  async findall(page = 1, limit = 10) {
    const [posts, total] = await this.postsrepository.findandcount({
      relations: ['author', 'tags'],
      skip: (page - 1) * limit,
      take: limit,
      cache: {
        id: `posts_page_${page}`,
        milliseconds: 60000 // 1分钟缓存
      }
    });

    return {
      data: posts,
      meta: {
        total,
        page,
        lastpage: math.ceil(total / limit)
      }
    };
  }

  // 使用子查询优化
  async findpopularposts() {
    return await this.postsrepository
      .createquerybuilder('post')
      .leftjoinandselect('post.author', 'author')
      .addselect(subquery => {
        return subquery
          .select('count(*)', 'commentcount')
          .from('comments', 'comment')
          .where('comment.postid = post.id');
      }, 'commentcount')
      .orderby('commentcount', 'desc')
      .limit(10)
      .getmany();
  }
}

2. 索引优化

// src/posts/entities/post.entity.ts
@entity('posts')
@index(['title', 'content']) // 复合索引
export class post extends baseentity {
  @column()
  @index() // 单列索引
  title: string;

  @column('text')
  content: string;

  @column()
  @index()
  authorid: string;

  // ... 其他字段
}