with database ORM

Author：Chuan Chen 阅读数：21409人阅读分类： TypeScript

Understanding the Basic Concepts of ORM

ORM (Object-Relational Mapping) is a programming technique that establishes a mapping relationship between object-oriented programming languages and relational databases. It allows developers to interact with databases in an object-oriented manner without writing SQL statements directly. In TypeScript, ORM tools typically provide type-safe APIs, making database operations more intuitive and secure.

// Traditional SQL query
const users = await db.query('SELECT * FROM users WHERE age > 18');

// ORM query
const users = await UserRepository.find({ where: { age: MoreThan(18) } });

Popular ORM Libraries in TypeScript

The TypeScript ecosystem features several mainstream ORM solutions, each with unique characteristics and advantages:

TypeORM: The most comprehensive TypeScript ORM, supporting both Active Record and Data Mapper patterns
Prisma: A next-generation ORM providing a type-safe database client
Sequelize: A mature ORM with improving TypeScript support
MikroORM: A modern ORM based on the Data Mapper pattern

// TypeORM entity definition example
@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column()
  age: number;
}

Entity Definition and Relationship Mapping

In TypeScript ORMs, entities are classes corresponding to database tables. Through decorators or configuration files, we can define table structures, field types, and inter-table relationships.

Basic Entity Definition

// Using TypeORM to define an entity
import { Entity, PrimaryGeneratedColumn, Column } from 'typeorm';

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

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

  @Column('decimal', { precision: 10, scale: 2 })
  price: number;

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

Relationship Types

ORMs typically support the following relationship types:

One-to-One (OneToOne)
One-to-Many (OneToMany)
Many-to-One (ManyToOne)
Many-to-Many (ManyToMany)

// One-to-Many relationship example
@Entity()
export class Author {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @OneToMany(() => Book, book => book.author)
  books: Book[];
}

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

  @Column()
  title: string;

  @ManyToOne(() => Author, author => author.books)
  author: Author;
}

Query Builder and Repository Pattern

ORMs provide various ways to query data, from simple find methods to complex query builders.

Basic Queries

// Using Repository for simple queries
const userRepository = connection.getRepository(User);

// Find all users
const allUsers = await userRepository.find();

// Query with conditions
const adultUsers = await userRepository.find({ where: { age: MoreThan(18) } });

Query Builder

For complex queries, the query builder offers more flexibility:

// Using QueryBuilder
const users = await userRepository
  .createQueryBuilder('user')
  .where('user.age > :age', { age: 18 })
  .andWhere('user.isActive = :isActive', { isActive: true })
  .orderBy('user.name', 'ASC')
  .skip(10)
  .take(5)
  .getMany();

Transaction Management and Data Operations

ORMs simplify transaction management, ensuring atomicity of data operations.

Basic Transactions

// Using transactions
await connection.transaction(async manager => {
  const user = new User();
  user.name = 'John';
  user.age = 30;
  await manager.save(user);

  const profile = new Profile();
  profile.user = user;
  profile.bio = 'Software Developer';
  await manager.save(profile);
});

Batch Operations

// Batch insert
const users = [
  { name: 'Alice', age: 25 },
  { name: 'Bob', age: 30 },
  { name: 'Charlie', age: 35 }
];

await userRepository
  .createQueryBuilder()
  .insert()
  .into(User)
  .values(users)
  .execute();

Migrations and Database Synchronization

ORMs typically provide migration tools to help manage database schema changes.

Automatic Synchronization

// TypeORM automatic synchronization
await connection.synchronize(); // Used in development environment

Manual Migrations

// Creating migration files
import { MigrationInterface, QueryRunner } from 'typeorm';

export class AddUserTable1620000000000 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      CREATE TABLE user (
        id int PRIMARY KEY AUTO_INCREMENT,
        name varchar(100) NOT NULL,
        age int
      )
    `);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`DROP TABLE user`);
  }
}

Performance Optimization and Advanced Features

Lazy Loading vs Eager Loading

// Eager loading
const userWithPosts = await userRepository.findOne({
  where: { id: 1 },
  relations: ['posts']
});

// Lazy loading
@Entity()
export class User {
  // ...
  @OneToMany(() => Post, post => post.user, { lazy: true })
  posts: Promise<Post[]>;
}

const user = await userRepository.findOne(1);
const posts = await user.posts; // Actual query executed here

Caching Strategies

// Query caching
const users = await userRepository.find({
  where: { isActive: true },
  cache: true
});

Advantages of TypeScript Type Safety

The greatest advantage of TypeScript ORMs is type safety, which significantly reduces runtime errors.

// Type-safe queries
const user = await userRepository.findOne({
  where: { 
    name: 'John',
    age: Between(20, 40)
  }
});

// user is automatically inferred as User | undefined
if (user) {
  console.log(user.name); // Type-safe access
  console.log(user.nonexistent); // Type error
}

Integration with GraphQL

Modern ORMs often provide good integration with GraphQL.

// TypeORM + TypeGraphQL example
@Entity()
@ObjectType()
export class Recipe {
  @Field(type => ID)
  @PrimaryGeneratedColumn()
  id: number;

  @Field()
  @Column()
  title: string;

  @Field(type => [Ingredient])
  @OneToMany(() => Ingredient, ingredient => ingredient.recipe)
  ingredients: Ingredient[];
}

Testing and Mocking

When testing database-related code, ORMs provide convenient mocking methods.

// Testing with in-memory database
beforeEach(async () => {
  const connection = await createConnection({
    type: 'sqlite',
    database: ':memory:',
    entities: [User, Post],
    synchronize: true
  });
  
  // Test code...
});

// Mocking Repository
const mockUserRepository = {
  find: jest.fn().mockResolvedValue([{ id: 1, name: 'Test User' }])
};

// Using mock in tests
const users = await mockUserRepository.find();

Common Issues and Solutions

N+1 Query Problem

// Code with N+1 problem
const users = await userRepository.find();
for (const user of users) {
  const posts = await postRepository.find({ where: { userId: user.id } });
  // ...
}

// Solution: Use relation preloading
const users = await userRepository.find({ relations: ['posts'] });

Complex Query Optimization

// Optimizing with subqueries
const activeUsers = await userRepository
  .createQueryBuilder('user')
  .where(qb => {
    const subQuery = qb.subQuery()
      .select('post.userId')
      .from(Post, 'post')
      .where('post.createdAt > :date', { date: '2023-01-01' })
      .getQuery();
    return 'user.id IN ' + subQuery;
  })
  .getMany();

Custom Repository Pattern

Extending base Repository functionality helps better organize code.

// Custom Repository
@EntityRepository(User)
export class UserRepository extends Repository<User> {
  findByName(name: string) {
    return this.find({ where: { name } });
  }

  findAdults() {
    return this.find({ where: { age: MoreThan(18) } });
  }
}

// Using custom Repository
const userRepository = connection.getCustomRepository(UserRepository);
const adults = await userRepository.findAdults();

Multi-Database Support and Sharding

Some ORMs support multiple databases and sharding strategies.

// Multi-database configuration
const connection = await createConnection({
  type: 'mysql',
  name: 'writeConnection',
  host: 'write.db.example.com',
  // ...
});

const readConnection = await createConnection({
  type: 'mysql',
  name: 'readConnection',
  host: 'read.db.example.com',
  // ...
});

// Selecting connection based on operation type
function getConnection(isReadOperation: boolean) {
  return isReadOperation ? readConnection : connection;
}

Mixing Raw SQL with ORM

Sometimes direct SQL is needed, and ORMs support this approach.

// Executing raw SQL
const rawData = await connection.query(`
  SELECT u.name, COUNT(p.id) as post_count
  FROM user u
  LEFT JOIN post p ON p.userId = u.id
  GROUP BY u.id
`);

// Mapping raw results to entities
const users = await connection
  .createQueryBuilder(User, 'user')
  .select('user.name')
  .addSelect('COUNT(post.id)', 'postCount')
  .leftJoin('user.posts', 'post')
  .groupBy('user.id')
  .getRawMany();

Event Subscriptions and Hooks

ORMs typically provide lifecycle hooks for executing code before/after specific operations.

// Entity listeners
@Entity()
export class User {
  // ...

  @AfterInsert()
  logInsert() {
    console.log(`User inserted: ${this.id}`);
  }

  @BeforeUpdate()
  updateTimestamp() {
    this.updatedAt = new Date();
  }
}

// Global event subscription
connection.subscribe('afterInsert', event => {
  if (event.entity instanceof User) {
    console.log('New user created:', event.entity);
  }
});

Integration with Frontend Frameworks

ORMs can integrate with frontend frameworks for end-to-end type safety.

// Using TypeORM in Angular service
@Injectable()
export class UserService {
  constructor(private connection: Connection) {}

  async getUsers(): Promise<User[]> {
    return this.connection.getRepository(User).find();
  }
}

// Using Prisma in React component
function UserList() {
  const [users, setUsers] = useState<User[]>([]);

  useEffect(() => {
    prisma.user.findMany().then(setUsers);
  }, []);

  return (
    <ul>
      {users.map(user => (
        <li key={user.id}>{user.name}</li>
      ))}
    </ul>
  );
}

ORM Usage in Microservice Architecture

In microservice architectures, ORMs help manage databases across services.

// Database isolation in microservices
@Service()
export class UserService {
  private userRepository: Repository<User>;

  constructor(@InjectConnection('userDB') connection: Connection) {
    this.userRepository = connection.getRepository(User);
  }

  async createUser(userData: CreateUserDto) {
    const user = this.userRepository.create(userData);
    return this.userRepository.save(user);
  }
}

Security Best Practices

Security considerations are important when using ORMs.

// Preventing SQL injection
// Wrong way (vulnerable to injection)
const users = await userRepository.query(
  `SELECT * FROM user WHERE name = '${userInput}'`
);

// Correct way (using parameterized queries)
const users = await userRepository.query(
  `SELECT * FROM user WHERE name = ?`,
  [userInput]
);

// ORM way (automatically handles parameters)
const users = await userRepository.find({
  where: { name: userInput }
});

Logging and Debugging

ORMs typically provide detailed logging to help debug database operations.

// Configuring logging
const connection = await createConnection({
  // ...
  logging: true,
  logger: 'advanced-console',
  maxQueryExecutionTime: 1000 // Log slow queries
});

// Custom logger
connection.logger = {
  log(level: 'log' | 'info' | 'warn', message: any) {
    myCustomLogger.log(level, `[DB] ${message}`);
  },
  // ...
};

Database Connection Pool Management

Proper connection pool configuration is crucial for performance.

// Connection pool configuration
const connection = await createConnection({
  // ...
  extra: {
    connectionLimit: 10, // Maximum connections
    queueLimit: 0, // Unlimited queue
    acquireTimeout: 30000 // 30-second connection acquisition timeout
  }
});

// Monitoring pool status
setInterval(() => {
  const pool = connection.driver.pool;
  console.log(`Pool stats: ${pool.active} active, ${pool.idle} idle`);
}, 5000);

Multi-Tenant Architecture Implementation

ORMs can help implement multi-tenant systems.

// Schema-based tenant isolation
@Entity()
@Table({ schema: 'tenant_${tenantId}' })
export class Product {
  // ...
}

// Dynamically setting tenant
function setTenant(tenantId: string) {
  const originalFind = connection.getRepository(Product).find;
  connection.getRepository(Product).find = function(options) {
    const schema = `tenant_${tenantId}`;
    return originalFind.call(this, {
      ...options,
      where: { ...options?.where, __schema__: schema }
    });
  };
}

Interacting with NoSQL Databases

Some ORMs also support NoSQL databases.

// TypeORM with MongoDB
@Entity()
export class Product {
  @ObjectIdColumn()
  id: ObjectID;

  @Column()
  name: string;

  @Column()
  price: number;
}

// Usage similar to relational databases
const products = await connection.getMongoRepository(Product).find({
  where: { price: { $gt: 100 } }
});

Database Version Compatibility Handling

Handling differences between database versions.

// Conditional SQL
async function getUsers() {
  if (connection.options.type === 'postgres') {
    return connection.query('SELECT * FROM users FOR UPDATE SKIP LOCKED');
  } else {
    return connection.query('SELECT * FROM users WITH (NOLOCK)');
  }
}

// Using ORM abstraction
const users = await userRepository.find({
  lock: { mode: 'optimistic', version: 1 }
});

Batch Operations and Stream Processing

Optimization strategies for handling large datasets.

// Batch insert
const batchSize = 1000;
for (let i = 0; i < largeData.length; i += batchSize) {
  const batch = largeData.slice(i, i + batchSize);
  await userRepository.insert(batch);
}

// Stream reading
const stream = await userRepository
  .createQueryBuilder('user')
  .stream();

stream.on('data', user => {
  // Process each user
});

stream.on('end', () => {
  // Processing complete
});

Custom Data Types and Transformations

Handling special data types and custom transformations.

// Custom column type
@Column({
  type: 'json',
  transformer: {
    to(value: string[]): string {
      return JSON.stringify(value);
    },
    from(value: string): string[] {
      return JSON.parse(value);
    }
  }
})
tags: string[];

// Spatial data type
@Column({
  type: 'geometry',
  spatialFeatureType: 'Point',
  srid: 4326
})
location: Point;

Database Health Checks and Monitoring

Implementing database health monitoring.

// Health check endpoint
app.get('/health', async (req, res) => {
  try {
    await connection.query('SELECT 1');
    res.status(200).json({ status: 'healthy' });
  } catch (error) {
    res.status(500).json({ status: 'unhealthy', error: error.message });
  }
});

// Performance monitoring
connection.queryEvents.on('query', ({ query, duration }) => {
  if (duration > 1000) {
    logSlowQuery(query, duration);
  }
});

Database Seeding and Test Data

Automating test data generation.

// Using faker to generate test data
async function seedDatabase() {
  const userRepository = connection.getRepository(User);
  
  for (let i = 0; i < 100; i++) {
    const user = new User();
    user.name = faker.name.findName();
    user.email = faker.internet.email();
    user.age = faker.datatype.number({ min: 18, max: 80 });
    await userRepository.save(user);
  }
}

// Factory pattern
class UserFactory {
  static create(overrides?: Partial<User>): User {
    return {
      name: faker.name.findName(),
      email: faker.internet.email(),
      age: faker.datatype.number({ min: 18, max: 80 }),
      ...overrides
    } as User;
  }
}

Database Index Optimization

Managing database indexes through ORM.

// Defining indexes
@Entity()
@Index(['firstName', 'lastName'])
@Index(['email'], { unique: true })
export class User {
  @Column()
  firstName: string;

  @Column()
  lastName: string;

  @Column()
  email: string;
}

// Using index hints in queries
const users = await userRepository
  .createQueryBuilder('user')
  .useIndex('IDX_USER_NAME')
  .where('user.name LIKE :name', { name: '%John%' })
  .getMany();

Multilingual and Internationalization Support

Handling multilingual data.

// Multilingual entity
@Entity()
export class Product {
  @PrimaryGeneratedColumn()
  id: number;

  @Column('json')

做个网站！

本站部分内容来自互联网,一切版权均归源网站或源作者所有。

如果侵犯了你的权益请来信告知我们删除。邮箱：cc@cccx.cn

上一篇：与Deno运行时

下一篇：与测试框架(Jest/Mocha)