12 Datenbank-Integration

npm install @nestjs/typeorm typeorm mysql2
# Oder für PostgreSQL:
npm install @nestjs/typeorm typeorm pg
# Oder für SQLite:
npm install @nestjs/typeorm typeorm sqlite3

import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { ConfigModule, ConfigService } from '@nestjs/config';

@Module({
  imports: [
    ConfigModule.forRoot(),
    TypeOrmModule.forRootAsync({
      imports: [ConfigModule],
      useFactory: (configService: ConfigService) => ({
        type: 'postgres',
        host: configService.get('DB_HOST'),
        port: +configService.get<number>('DB_PORT'),
        username: configService.get('DB_USERNAME'),
        password: configService.get('DB_PASSWORD'),
        database: configService.get('DB_NAME'),
        entities: [__dirname + '/**/*.entity{.ts,.js}'],
        synchronize: configService.get('NODE_ENV') !== 'production',
        logging: configService.get('NODE_ENV') === 'development',
        ssl: configService.get('NODE_ENV') === 'production' ? {
          rejectUnauthorized: false,
        } : false,
      }),
      inject: [ConfigService],
    }),
  ],
})
export class AppModule {}

import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  CreateDateColumn,
  UpdateDateColumn,
  DeleteDateColumn,
} from 'typeorm';

@Entity()
export class BaseEntity {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @CreateDateColumn()
  createdAt: Date;

  @UpdateDateColumn()
  updatedAt: Date;

  @DeleteDateColumn()
  deletedAt?: Date;
}

import { Entity, Column, OneToMany, Index } from 'typeorm';
import { BaseEntity } from './base.entity';
import { Post } from './post.entity';

@Entity('users')
@Index(['email'], { unique: true })
export class User extends BaseEntity {
  @Column({ length: 100 })
  firstName: string;

  @Column({ length: 100 })
  lastName: string;

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

  @Column({ select: false }) // Passwort wird standardmäßig nicht geladen
  password: string;

  @Column({ type: 'enum', enum: ['admin', 'user'], default: 'user' })
  role: 'admin' | 'user';

  @Column({ type: 'boolean', default: true })
  isActive: boolean;

  @Column({ type: 'json', nullable: true })
  preferences: Record<string, any>;

  @OneToMany(() => Post, post => post.author)
  posts: Post[];

  // Computed property
  get fullName(): string {
    return `${this.firstName} ${this.lastName}`;
  }
}

import {
  Entity,
  Column,
  ManyToOne,
  ManyToMany,
  JoinTable,
  Index,
} from 'typeorm';
import { BaseEntity } from './base.entity';
import { User } from './user.entity';
import { Tag } from './tag.entity';

@Entity('posts')
export class Post extends BaseEntity {
  @Column({ length: 200 })
  @Index() // Index für bessere Performance bei Suchen
  title: string;

  @Column({ type: 'text' })
  content: string;

  @Column({ length: 300, nullable: true })
  excerpt: string;

  @Column({ type: 'enum', enum: ['draft', 'published', 'archived'], default: 'draft' })
  status: 'draft' | 'published' | 'archived';

  @Column({ type: 'int', default: 0 })
  viewCount: number;

  @ManyToOne(() => User, user => user.posts, { eager: true })
  author: User;

  @ManyToMany(() => Tag, tag => tag.posts)
  @JoinTable({
    name: 'post_tags',
    joinColumn: { name: 'postId', referencedColumnName: 'id' },
    inverseJoinColumn: { name: 'tagId', referencedColumnName: 'id' },
  })
  tags: Tag[];

  @Column({ type: 'timestamp', nullable: true })
  publishedAt: Date;
}

import { Injectable, NotFoundException, ConflictException } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository, FindOptionsWhere } from 'typeorm';
import { User } from './entities/user.entity';
import { CreateUserDto } from './dto/create-user.dto';
import { UpdateUserDto } from './dto/update-user.dto';
import * as bcrypt from 'bcrypt';

@Injectable()
export class UsersService {
  constructor(
    @InjectRepository(User)
    private usersRepository: Repository<User>,
  ) {}

  async create(createUserDto: CreateUserDto): Promise<User> {
    // Prüfen ob E-Mail bereits existiert
    const existingUser = await this.usersRepository.findOne({
      where: { email: createUserDto.email },
    });

    if (existingUser) {
      throw new ConflictException('Email already exists');
    }

    // Passwort hashen
    const hashedPassword = await bcrypt.hash(createUserDto.password, 12);

    const user = this.usersRepository.create({
      ...createUserDto,
      password: hashedPassword,
    });

    return this.usersRepository.save(user);
  }

  async findAll(
    page: number = 1,
    limit: number = 10,
    search?: string,
  ): Promise<{ users: User[]; total: number; totalPages: number }> {
    const queryBuilder = this.usersRepository.createQueryBuilder('user');

    if (search) {
      queryBuilder.where(
        'user.firstName ILIKE :search OR user.lastName ILIKE :search OR user.email ILIKE :search',
        { search: `%${search}%` },
      );
    }

    queryBuilder
      .skip((page - 1) * limit)
      .take(limit)
      .orderBy('user.createdAt', 'DESC');

    const [users, total] = await queryBuilder.getManyAndCount();

    return {
      users,
      total,
      totalPages: Math.ceil(total / limit),
    };
  }

  async findOne(id: string): Promise<User> {
    const user = await this.usersRepository.findOne({
      where: { id },
      relations: ['posts'],
    });

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

    return user;
  }

  async findByEmail(email: string, includePassword: boolean = false): Promise<User> {
    const query = this.usersRepository.createQueryBuilder('user')
      .where('user.email = :email', { email });

    if (includePassword) {
      query.addSelect('user.password');
    }

    return query.getOne();
  }

  async update(id: string, updateUserDto: UpdateUserDto): Promise<User> {
    const user = await this.findOne(id);

    // E-Mail-Eindeutigkeit prüfen, falls E-Mail geändert wird
    if (updateUserDto.email && updateUserDto.email !== user.email) {
      const existingUser = await this.usersRepository.findOne({
        where: { email: updateUserDto.email },
      });

      if (existingUser) {
        throw new ConflictException('Email already exists');
      }
    }

    // Passwort hashen, falls es geändert wird
    if (updateUserDto.password) {
      updateUserDto.password = await bcrypt.hash(updateUserDto.password, 12);
    }

    Object.assign(user, updateUserDto);
    return this.usersRepository.save(user);
  }

  async remove(id: string): Promise<void> {
    const result = await this.usersRepository.softDelete(id);

    if (result.affected === 0) {
      throw new NotFoundException(`User with ID ${id} not found`);
    }
  }

  async getUserStats(): Promise<{
    totalUsers: number;
    activeUsers: number;
    newUsersThisMonth: number;
  }> {
    const [totalUsers, activeUsers, newUsersThisMonth] = await Promise.all([
      this.usersRepository.count(),
      this.usersRepository.count({ where: { isActive: true } }),
      this.usersRepository
        .createQueryBuilder('user')
        .where('user.createdAt >= :date', {
          date: new Date(new Date().getFullYear(), new Date().getMonth(), 1),
        })
        .getCount(),
    ]);

    return { totalUsers, activeUsers, newUsersThisMonth };
  }
}

@Injectable()
export class PostsService {
  constructor(
    @InjectRepository(Post)
    private postsRepository: Repository<Post>,
    @InjectRepository(User)
    private usersRepository: Repository<User>,
  ) {}

  // Komplexe Abfrage mit mehreren Joins
  async findPostsWithDetails(filters: {
    authorId?: string;
    status?: string;
    tagIds?: string[];
    search?: string;
    startDate?: Date;
    endDate?: Date;
  }): Promise<Post[]> {
    const queryBuilder = this.postsRepository
      .createQueryBuilder('post')
      .leftJoinAndSelect('post.author', 'author')
      .leftJoinAndSelect('post.tags', 'tags')
      .where('post.deletedAt IS NULL');

    if (filters.authorId) {
      queryBuilder.andWhere('author.id = :authorId', { authorId: filters.authorId });
    }

    if (filters.status) {
      queryBuilder.andWhere('post.status = :status', { status: filters.status });
    }

    if (filters.tagIds && filters.tagIds.length > 0) {
      queryBuilder.andWhere('tags.id IN (:...tagIds)', { tagIds: filters.tagIds });
    }

    if (filters.search) {
      queryBuilder.andWhere(
        '(post.title ILIKE :search OR post.content ILIKE :search)',
        { search: `%${filters.search}%` },
      );
    }

    if (filters.startDate) {
      queryBuilder.andWhere('post.createdAt >= :startDate', {
        startDate: filters.startDate,
      });
    }

    if (filters.endDate) {
      queryBuilder.andWhere('post.createdAt <= :endDate', {
        endDate: filters.endDate,
      });
    }

    return queryBuilder
      .orderBy('post.createdAt', 'DESC')
      .getMany();
  }

  // Aggregation mit GROUP BY
  async getPostStatsByAuthor(): Promise<any[]> {
    return this.postsRepository
      .createQueryBuilder('post')
      .select([
        'author.id as authorId',
        'author.firstName as firstName',
        'author.lastName as lastName',
        'COUNT(post.id) as postCount',
        'AVG(post.viewCount) as avgViews',
        'MAX(post.createdAt) as lastPostDate',
      ])
      .leftJoin('post.author', 'author')
      .where('post.status = :status', { status: 'published' })
      .groupBy('author.id, author.firstName, author.lastName')
      .having('COUNT(post.id) > :minPosts', { minPosts: 0 })
      .orderBy('postCount', 'DESC')
      .getRawMany();
  }

  // Subquery-Beispiel
  async findUsersWithMostPosts(limit: number = 5): Promise<User[]> {
    const subQuery = this.postsRepository
      .createQueryBuilder('post')
      .select('post.authorId')
      .addSelect('COUNT(*)', 'postCount')
      .where('post.status = :status', { status: 'published' })
      .groupBy('post.authorId')
      .orderBy('postCount', 'DESC')
      .limit(limit);

    return this.usersRepository
      .createQueryBuilder('user')
      .where(`user.id IN (${subQuery.getQuery()})`)
      .setParameters(subQuery.getParameters())
      .getMany();
  }
}

// ormconfig.ts
import { DataSource } from 'typeorm';
import { ConfigService } from '@nestjs/config';

const configService = new ConfigService();

export default new DataSource({
  type: 'postgres',
  host: configService.get('DB_HOST'),
  port: +configService.get('DB_PORT'),
  username: configService.get('DB_USERNAME'),
  password: configService.get('DB_PASSWORD'),
  database: configService.get('DB_NAME'),
  entities: ['src/**/*.entity.ts'],
  migrations: ['src/database/migrations/*.ts'],
  cli: {
    migrationsDir: 'src/database/migrations',
  },
});

# Migration generieren
npm run typeorm migration:generate -- src/database/migrations/AddUserPreferences

# Migration manuell erstellen
npm run typeorm migration:create -- src/database/migrations/CreateIndexes

# Migrations ausführen
npm run typeorm migration:run

# Migration rückgängig machen
npm run typeorm migration:revert

import { MigrationInterface, QueryRunner, TableIndex } from 'typeorm';

export class AddUserEmailIndex1642680000000 implements MigrationInterface {
  name = 'AddUserEmailIndex1642680000000';

  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.createIndex(
      'users',
      new TableIndex({
        name: 'IDX_USER_EMAIL',
        columnNames: ['email'],
        isUnique: true,
      }),
    );

    await queryRunner.createIndex(
      'posts',
      new TableIndex({
        name: 'IDX_POST_STATUS_CREATED',
        columnNames: ['status', 'createdAt'],
      }),
    );
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.dropIndex('users', 'IDX_USER_EMAIL');
    await queryRunner.dropIndex('posts', 'IDX_POST_STATUS_CREATED');
  }
}

npm install prisma @prisma/client
npm install @nestjs/config
npx prisma init

import { Injectable, OnModuleInit } from '@nestjs/common';
import { PrismaClient } from '@prisma/client';

@Injectable()
export class PrismaService extends PrismaClient implements OnModuleInit {
  async onModuleInit() {
    await this.$connect();
  }

  async enableShutdownHooks(app: any) {
    this.$on('beforeExit', async () => {
      await app.close();
    });
  }

  // Custom helper methods
  async cleanupDatabase() {
    const models = Reflect.ownKeys(this).filter(
      key => key[0] !== '_' && key[0] !== '$',
    );

    return Promise.all(
      models.map((modelKey) => (this as any)[modelKey].deleteMany()),
    );
  }
}

import { Global, Module } from '@nestjs/common';
import { PrismaService } from './prisma.service';

@Global()
@Module({
  providers: [PrismaService],
  exports: [PrismaService],
})
export class PrismaModule {}

import { Injectable, NotFoundException } from '@nestjs/common';
import { PrismaService } from '../prisma/prisma.service';
import { Prisma } from '@prisma/client';

@Injectable()
export class UsersService {
  constructor(private prisma: PrismaService) {}

  async create(data: Prisma.UserCreateInput) {
    return this.prisma.user.create({
      data,
      include: {
        profile: true,
        posts: {
          where: { status: 'PUBLISHED' },
          orderBy: { createdAt: 'desc' },
          take: 5,
        },
      },
    });
  }

  async findMany(params: {
    skip?: number;
    take?: number;
    cursor?: Prisma.UserWhereUniqueInput;
    where?: Prisma.UserWhereInput;
    orderBy?: Prisma.UserOrderByWithRelationInput;
  }) {
    const { skip, take, cursor, where, orderBy } = params;
    
    return this.prisma.user.findMany({
      skip,
      take,
      cursor,
      where,
      orderBy,
      include: {
        profile: true,
        _count: {
          select: {
            posts: {
              where: { status: 'PUBLISHED' },
            },
          },
        },
      },
    });
  }

  async findUnique(where: Prisma.UserWhereUniqueInput) {
    const user = await this.prisma.user.findUnique({
      where,
      include: {
        profile: true,
        posts: {
          include: {
            tags: {
              include: {
                tag: true,
              },
            },
          },
        },
      },
    });

    if (!user) {
      throw new NotFoundException('User not found');
    }

    return user;
  }

  async update(params: {
    where: Prisma.UserWhereUniqueInput;
    data: Prisma.UserUpdateInput;
  }) {
    const { where, data } = params;
    
    return this.prisma.user.update({
      data,
      where,
      include: {
        profile: true,
        posts: true,
      },
    });
  }

  async delete(where: Prisma.UserWhereUniqueInput) {
    return this.prisma.user.delete({
      where,
    });
  }

  // Komplexe Abfragen mit Prisma
  async getUsersWithMostPosts(limit: number = 10) {
    return this.prisma.user.findMany({
      take: limit,
      include: {
        _count: {
          select: {
            posts: {
              where: { status: 'PUBLISHED' },
            },
          },
        },
        profile: true,
      },
      orderBy: {
        posts: {
          _count: 'desc',
        },
      },
    });
  }

  async searchUsers(query: string) {
    return this.prisma.user.findMany({
      where: {
        OR: [
          {
            firstName: {
              contains: query,
              mode: 'insensitive',
            },
          },
          {
            lastName: {
              contains: query,
              mode: 'insensitive',
            },
          },
          {
            email: {
              contains: query,
              mode: 'insensitive',
            },
          },
        ],
      },
      include: {
        profile: true,
      },
    });
  }
}

import { User, Post, Prisma } from '@prisma/client';

// Generated Types für Relations
type UserWithPosts = Prisma.UserGetPayload<{
  include: { posts: true };
}>;

type PostWithAuthor = Prisma.PostGetPayload<{
  include: { author: true };
}>;

// Custom Service mit Generated Types
@Injectable()
export class PostsService {
  constructor(private prisma: PrismaService) {}

  async createPostWithTags(
    data: Prisma.PostCreateInput & {
      tagIds: string[];
    }
  ): Promise<PostWithAuthor> {
    const { tagIds, ...postData } = data;

    return this.prisma.post.create({
      data: {
        ...postData,
        tags: {
          create: tagIds.map(tagId => ({
            tag: {
              connect: { id: tagId },
            },
          })),
        },
      },
      include: {
        author: true,
        tags: {
          include: {
            tag: true,
          },
        },
      },
    });
  }

  async getPostAnalytics(): Promise<{
    totalPosts: number;
    publishedPosts: number;
    draftPosts: number;
    avgViewsPerPost: number;
    topAuthors: Array<{
      authorName: string;
      postCount: number;
    }>;
  }> {
    const [
      totalPosts,
      publishedPosts,
      draftPosts,
      avgViews,
      topAuthors,
    ] = await Promise.all([
      this.prisma.post.count(),
      this.prisma.post.count({ where: { status: 'PUBLISHED' } }),
      this.prisma.post.count({ where: { status: 'DRAFT' } }),
      this.prisma.post.aggregate({
        _avg: { viewCount: true },
      }),
      this.prisma.user.findMany({
        take: 5,
        include: {
          _count: {
            select: {
              posts: { where: { status: 'PUBLISHED' } },
            },
          },
        },
        orderBy: {
          posts: {
            _count: 'desc',
          },
        },
      }),
    ]);

    return {
      totalPosts,
      publishedPosts,
      draftPosts,
      avgViewsPerPost: avgViews._avg.viewCount || 0,
      topAuthors: topAuthors.map(author => ({
        authorName: `${author.firstName} ${author.lastName}`,
        postCount: author._count.posts,
      })),
    };
  }
}

npm install @nestjs/mongoose mongoose
npm install @types/mongoose

import { Module } from '@nestjs/common';
import { MongooseModule } from '@nestjs/mongoose';
import { ConfigModule, ConfigService } from '@nestjs/config';

@Module({
  imports: [
    MongooseModule.forRootAsync({
      imports: [ConfigModule],
      useFactory: async (configService: ConfigService) => ({
        uri: configService.get<string>('MONGODB_URI'),
        useNewUrlParser: true,
        useUnifiedTopology: true,
      }),
      inject: [ConfigService],
    }),
  ],
})
export class AppModule {}

import { Prop, Schema, SchemaFactory } from '@nestjs/mongoose';
import { Document, Types } from 'mongoose';

export type UserDocument = User & Document;

@Schema({
  timestamps: true,
  toJSON: {
    transform: (doc, ret) => {
      ret.id = ret._id;
      delete ret._id;
      delete ret.__v;
      delete ret.password;
      return ret;
    },
  },
})
export class User {
  @Prop({ required: true, maxlength: 100 })
  firstName: string;

  @Prop({ required: true, maxlength: 100 })
  lastName: string;

  @Prop({ required: true, unique: true, index: true })
  email: string;

  @Prop({ required: true, select: false })
  password: string;

  @Prop({ type: String, enum: ['admin', 'user'], default: 'user' })
  role: string;

  @Prop({ default: true })
  isActive: boolean;

  @Prop({ type: Object })
  preferences: Record<string, any>;

  @Prop([{ type: Types.ObjectId, ref: 'Post' }])
  posts: Types.ObjectId[];
}

export const UserSchema = SchemaFactory.createForClass(User);

// Index für bessere Performance
UserSchema.index({ email: 1 });
UserSchema.index({ firstName: 1, lastName: 1 });

// Virtual für fullName
UserSchema.virtual('fullName').get(function() {
  return `${this.firstName} ${this.lastName}`;
});

import { Injectable, NotFoundException } from '@nestjs/common';
import { InjectModel } from '@nestjs/mongoose';
import { Model } from 'mongoose';
import { User, UserDocument } from './schemas/user.schema';
import { CreateUserDto } from './dto/create-user.dto';
import { UpdateUserDto } from './dto/update-user.dto';

@Injectable()
export class UsersService {
  constructor(
    @InjectModel(User.name) private userModel: Model<UserDocument>,
  ) {}

  async create(createUserDto: CreateUserDto): Promise<User> {
    const createdUser = new this.userModel(createUserDto);
    return createdUser.save();
  }

  async findAll(
    page: number = 1,
    limit: number = 10,
    search?: string,
  ): Promise<{
    users: User[];
    total: number;
    totalPages: number;
  }> {
    const skip = (page - 1) * limit;
    
    const query = search
      ? {
          $or: [
            { firstName: { $regex: search, $options: 'i' } },
            { lastName: { $regex: search, $options: 'i' } },
            { email: { $regex: search, $options: 'i' } },
          ],
        }
      : {};

    const [users, total] = await Promise.all([
      this.userModel
        .find(query)
        .skip(skip)
        .limit(limit)
        .sort({ createdAt: -1 })
        .populate('posts')
        .exec(),
      this.userModel.countDocuments(query),
    ]);

    return {
      users,
      total,
      totalPages: Math.ceil(total / limit),
    };
  }

  async findOne(id: string): Promise<User> {
    const user = await this.userModel
      .findById(id)
      .populate('posts')
      .exec();

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

    return user;
  }

  async findByEmail(email: string): Promise<User> {
    return this.userModel.findOne({ email }).select('+password').exec();
  }

  async update(id: string, updateUserDto: UpdateUserDto): Promise<User> {
    const updatedUser = await this.userModel
      .findByIdAndUpdate(id, updateUserDto, { new: true })
      .exec();

    if (!updatedUser) {
      throw new NotFoundException(`User with ID ${id} not found`);
    }

    return updatedUser;
  }

  async remove(id: string): Promise<void> {
    const result = await this.userModel.findByIdAndDelete(id).exec();

    if (!result) {
      throw new NotFoundException(`User with ID ${id} not found`);
    }
  }

  // Aggregation Pipeline Beispiel
  async getUserStatistics(): Promise<any[]> {
    return this.userModel.aggregate([
      {
        $lookup: {
          from: 'posts',
          localField: '_id',
          foreignField: 'author',
          as: 'userPosts',
        },
      },
      {
        $addFields: {
          postCount: { $size: '$userPosts' },
          avgViews: { $avg: '$userPosts.viewCount' },
        },
      },
      {
        $project: {
          firstName: 1,
          lastName: 1,
          email: 1,
          postCount: 1,
          avgViews: { $round: ['$avgViews', 2] },
        },
      },
      {
        $sort: { postCount: -1 },
      },
      {
        $limit: 10,
      },
    ]);
  }
}

import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository, DataSource } from 'typeorm';
import { User } from './entities/user.entity';
import { Post } from './entities/post.entity';

@Injectable()
export class UserPostService {
  constructor(
    @InjectRepository(User)
    private usersRepository: Repository<User>,
    @InjectRepository(Post)
    private postsRepository: Repository<Post>,
    private dataSource: DataSource,
  ) {}

  // Query Runner Approach
  async createUserWithPosts(userData: any, postsData: any[]): Promise<User> {
    const queryRunner = this.dataSource.createQueryRunner();
    await queryRunner.connect();
    await queryRunner.startTransaction();

    try {
      // User erstellen
      const user = queryRunner.manager.create(User, userData);
      const savedUser = await queryRunner.manager.save(user);

      // Posts erstellen
      const posts = postsData.map(postData => 
        queryRunner.manager.create(Post, {
          ...postData,
          author: savedUser,
        })
      );
      
      await queryRunner.manager.save(posts);

      await queryRunner.commitTransaction();
      return savedUser;
    } catch (error) {
      await queryRunner.rollbackTransaction();
      throw error;
    } finally {
      await queryRunner.release();
    }
  }

  // Transaction Decorator Approach
  @Transaction()
  async updateUserAndPosts(
    userId: string,
    userData: any,
    postsData: any[],
    @TransactionManager() manager: EntityManager,
  ): Promise<User> {
    // User aktualisieren
    await manager.update(User, userId, userData);
    
    // Posts aktualisieren
    for (const postData of postsData) {
      await manager.update(Post, postData.id, postData);
    }

    return manager.findOne(User, {
      where: { id: userId },
      relations: ['posts'],
    });
  }

  // Programmatic Transaction
  async transferPostsBetweenUsers(
    fromUserId: string,
    toUserId: string,
    postIds: string[],
  ): Promise<void> {
    await this.dataSource.transaction(async manager => {
      // Validierung
      const [fromUser, toUser] = await Promise.all([
        manager.findOne(User, { where: { id: fromUserId } }),
        manager.findOne(User, { where: { id: toUserId } }),
      ]);

      if (!fromUser || !toUser) {
        throw new Error('User not found');
      }

      // Posts übertragen
      await manager.update(
        Post,
        { id: In(postIds), author: { id: fromUserId } },
        { author: { id: toUserId } },
      );

      // Log-Eintrag erstellen (optional)
      await manager.save(TransferLog, {
        fromUserId,
        toUserId,
        postIds,
        transferredAt: new Date(),
      });
    });
  }
}

@Injectable()
export class OrderService {
  constructor(private prisma: PrismaService) {}

  async createOrderWithItems(orderData: any, items: any[]): Promise<any> {
    return this.prisma.$transaction(async (prisma) => {
      // Bestellung erstellen
      const order = await prisma.order.create({
        data: orderData,
      });

      // Artikel erstellen
      const orderItems = await Promise.all(
        items.map(item =>
          prisma.orderItem.create({
            data: {
              ...item,
              orderId: order.id,
            },
          })
        )
      );

      // Lagerbestand aktualisieren
      await Promise.all(
        items.map(item =>
          prisma.product.update({
            where: { id: item.productId },
            data: {
              stock: {
                decrement: item.quantity,
              },
            },
          })
        )
      );

      return {
        ...order,
        items: orderItems,
      };
    });
  }

  // Interactive Transaction für komplexe Logik
  async processPayment(orderId: string, paymentData: any): Promise<any> {
    return this.prisma.$transaction(
      async (prisma) => {
        // Bestellung laden
        const order = await prisma.order.findUnique({
          where: { id: orderId },
          include: { items: true },
        });

        if (!order) {
          throw new Error('Order not found');
        }

        // Payment verarbeiten
        const payment = await prisma.payment.create({
          data: {
            ...paymentData,
            orderId: order.id,
            amount: order.total,
          },
        });

        // Bestellstatus aktualisieren
        const updatedOrder = await prisma.order.update({
          where: { id: orderId },
          data: { status: 'PAID' },
        });

        return { order: updatedOrder, payment };
      },
      {
        maxWait: 5000, // default: 2000
        timeout: 10000, // default: 5000
      }
    );
  }
}

@Module({
  imports: [
    TypeOrmModule.forRootAsync({
      useFactory: (configService: ConfigService) => ({
        type: 'postgres',
        host: configService.get('DB_HOST'),
        port: configService.get('DB_PORT'),
        username: configService.get('DB_USERNAME'),
        password: configService.get('DB_PASSWORD'),
        database: configService.get('DB_NAME'),
        
        // Connection Pool Settings
        extra: {
          max: 20, // Maximale Anzahl Verbindungen
          min: 5,  // Minimale Anzahl Verbindungen
          acquire: 30000, // Timeout für neue Verbindungen
          idle: 10000,    // Zeit bis idle Verbindungen geschlossen werden
          evict: 1000,    // Interval für Eviction-Check
        },
        
        // Additional Settings
        maxQueryExecutionTime: 1000, // Log slow queries
        logging: configService.get('NODE_ENV') === 'development',
        synchronize: false, // Nie in Production verwenden!
        
        // Connection Retry
        retryAttempts: 3,
        retryDelay: 3000,
      }),
      inject: [ConfigService],
    }),
  ],
})
export class DatabaseModule {}

// prisma/prisma.service.ts
@Injectable()
export class PrismaService extends PrismaClient implements OnModuleInit {
  constructor() {
    super({
      datasources: {
        db: {
          url: process.env.DATABASE_URL,
        },
      },
      log: [
        { level: 'query', emit: 'event' },
        { level: 'error', emit: 'stdout' },
        { level: 'info', emit: 'stdout' },
        { level: 'warn', emit: 'stdout' },
      ],
    });

    // Query logging für Development
    if (process.env.NODE_ENV === 'development') {
      this.$on('query', (e) => {
        console.log('Query: ' + e.query);
        console.log('Duration: ' + e.duration + 'ms');
      });
    }
  }

  async onModuleInit() {
    await this.$connect();
  }

  async onModuleDestroy() {
    await this.$disconnect();
  }

  // Health Check für Database Connection
  async isHealthy(): Promise<boolean> {
    try {
      await this.$queryRaw`SELECT 1`;
      return true;
    } catch {
      return false;
    }
  }
}

import { Injectable } from '@nestjs/common';
import { HealthIndicator, HealthIndicatorResult, HealthCheckError } from '@nestjs/terminus';
import { PrismaService } from '../prisma/prisma.service';

@Injectable()
export class DatabaseHealthIndicator extends HealthIndicator {
  constructor(private prismaService: PrismaService) {
    super();
  }

  async isHealthy(key: string): Promise<HealthIndicatorResult> {
    try {
      await this.prismaService.$queryRaw`SELECT 1`;
      return this.getStatus(key, true);
    } catch (error) {
      throw new HealthCheckError(
        'Database check failed',
        this.getStatus(key, false, { error: error.message }),
      );
    }
  }
}

// Health Check Controller
@Controller('health')
export class HealthController {
  constructor(
    private health: HealthCheckService,
    private db: DatabaseHealthIndicator,
  ) {}

  @Get()
  @HealthCheck()
  check() {
    return this.health.check([
      () => this.db.isHealthy('database'),
    ]);
  }
}

// TypeORM Indexes
@Entity('users')
@Index(['email'], { unique: true })
@Index(['lastName', 'firstName'])
@Index(['createdAt'])
export class User extends BaseEntity {
  // ...
}

// Prisma Indexes
model User {
  id        String   @id @default(cuid())
  email     String   @unique
  firstName String
  lastName  String
  createdAt DateTime @default(now())
  
  @@index([lastName, firstName])
  @@index([createdAt])
}

@Injectable()
export class OptimizedQueryService {
  constructor(private prisma: PrismaService) {}

  // Schlecht: N+1 Query Problem
  async getBadUserPosts(): Promise<any[]> {
    const users = await this.prisma.user.findMany();
    
    const usersWithPosts = await Promise.all(
      users.map(async user => ({
        ...user,
        posts: await this.prisma.post.findMany({
          where: { authorId: user.id },
        }),
      }))
    );
    
    return usersWithPosts;
  }

  // Gut: Single Query mit Include
  async getGoodUserPosts(): Promise<any[]> {
    return this.prisma.user.findMany({
      include: {
        posts: {
          where: { status: 'PUBLISHED' },
          orderBy: { createdAt: 'desc' },
        },
      },
    });
  }

  // Pagination mit Cursor
  async getPaginatedPosts(cursor?: string, take: number = 10) {
    return this.prisma.post.findMany({
      take,
      ...(cursor && {
        skip: 1,
        cursor: { id: cursor },
      }),
      include: {
        author: {
          select: {
            id: true,
            firstName: true,
            lastName: true,
          },
        },
      },
      orderBy: { createdAt: 'desc' },
    });
  }

  // Batch Operations
  async createManyUsers(users: any[]): Promise<void> {
    const batchSize = 1000;
    
    for (let i = 0; i < users.length; i += batchSize) {
      const batch = users.slice(i, i + batchSize);
      await this.prisma.user.createMany({
        data: batch,
        skipDuplicates: true,
      });
    }
  }
}

import { IsEmail, IsString, IsEnum, IsOptional, Length } from 'class-validator';
import { Transform } from 'class-transformer';

export class CreateUserDto {
  @IsString()
  @Length(2, 50)
  @Transform(({ value }) => value.trim())
  firstName: string;

  @IsString()
  @Length(2, 50)
  @Transform(({ value }) => value.trim())
  lastName: string;

  @IsEmail()
  @Transform(({ value }) => value.toLowerCase().trim())
  email: string;

  @IsString()
  @Length(8, 128)
  password: string;

  @IsOptional()
  @IsEnum(['admin', 'user'])
  role?: 'admin' | 'user';
}

import * as bcrypt from 'bcrypt';
import * as crypto from 'crypto';

@Injectable()
export class AuthService {
  private readonly saltRounds = 12;

  async hashPassword(password: string): Promise<string> {
    return bcrypt.hash(password, this.saltRounds);
  }

  async verifyPassword(password: string, hash: string): Promise<boolean> {
    return bcrypt.compare(password, hash);
  }

  generateResetToken(): string {
    return crypto.randomBytes(32).toString('hex');
  }

  async createUser(userData: CreateUserDto): Promise<User> {
    const hashedPassword = await this.hashPassword(userData.password);
    
    return this.prisma.user.create({
      data: {
        ...userData,
        password: hashedPassword,
      },
    });
  }
}

import { PrismaClientKnownRequestError } from '@prisma/client/runtime';

@Injectable()
export class DatabaseErrorHandler {
  handlePrismaError(error: any): never {
    if (error instanceof PrismaClientKnownRequestError) {
      switch (error.code) {
        case 'P2002':
          throw new ConflictException(
            `Unique constraint violation: ${error.meta?.target}`,
          );
        case 'P2025':
          throw new NotFoundException('Record not found');
        case 'P2003':
          throw new BadRequestException('Foreign key constraint violation');
        default:
          throw new InternalServerErrorException('Database error occurred');
      }
    }
    
    throw new InternalServerErrorException('Unexpected error occurred');
  }
}

// Usage in Service
@Injectable()
export class UsersService {
  constructor(
    private prisma: PrismaService,
    private errorHandler: DatabaseErrorHandler,
  ) {}

  async create(data: CreateUserDto): Promise<User> {
    try {
      return await this.prisma.user.create({ data });
    } catch (error) {
      this.errorHandler.handlePrismaError(error);
    }
  }
}

// test/test-database.module.ts
@Module({
  imports: [
    TypeOrmModule.forRoot({
      type: 'sqlite',
      database: ':memory:',
      entities: [User, Post, Tag],
      synchronize: true,
      logging: false,
    }),
  ],
})
export class TestDatabaseModule {}

// Integration Test Beispiel
describe('UsersService (Integration)', () => {
  let service: UsersService;
  let module: TestingModule;

  beforeEach(async () => {
    module = await Test.createTestingModule({
      imports: [TestDatabaseModule, UsersModule],
    }).compile();

    service = module.get<UsersService>(UsersService);
  });

  afterEach(async () => {
    await module.close();
  });

  it('should create a user', async () => {
    const userData = {
      firstName: 'John',
      lastName: 'Doe',
      email: 'john@example.com',
      password: 'password123',
    };

    const user = await service.create(userData);
    
    expect(user).toBeDefined();
    expect(user.email).toBe(userData.email);
    expect(user.password).not.toBe(userData.password); // Should be hashed
  });
});