ORM使用 #

一、ORM概述 #

1.1 什么是ORM？ #

ORM（Object-Relational Mapping）是一种将对象模型映射到关系型数据库的技术。它允许开发者使用面向对象的方式操作数据库。

1.2 常用ORM #

ORM	数据库支持	特点
Sequelize	MySQL, PostgreSQL, SQLite	成熟稳定
Prisma	MySQL, PostgreSQL, MongoDB	类型安全
TypeORM	MySQL, PostgreSQL, SQLite	TypeScript原生
Mongoose	MongoDB	MongoDB专用

二、Sequelize #

2.1 安装 #

bash

npm install sequelize mysql2

2.2 连接数据库 #

javascript

const { Sequelize } = require('sequelize');

const sequelize = new Sequelize('myapp', 'root', 'password', {
    host: 'localhost',
    dialect: 'mysql',
    logging: false
});

const testConnection = async () => {
    try {
        await sequelize.authenticate();
        console.log('数据库连接成功');
    } catch (error) {
        console.error('数据库连接失败:', error);
    }
};

module.exports = sequelize;

2.3 定义模型 #

javascript

const { DataTypes } = require('sequelize');
const sequelize = require('../config/database');

const User = sequelize.define('User', {
    name: {
        type: DataTypes.STRING(50),
        allowNull: false,
        validate: {
            notEmpty: true,
            len: [2, 50]
        }
    },
    email: {
        type: DataTypes.STRING(100),
        allowNull: false,
        unique: true,
        validate: {
            isEmail: true
        }
    },
    password: {
        type: DataTypes.STRING(255),
        allowNull: false
    },
    role: {
        type: DataTypes.ENUM('user', 'admin'),
        defaultValue: 'user'
    },
    isActive: {
        type: DataTypes.BOOLEAN,
        defaultValue: true
    }
}, {
    tableName: 'users',
    timestamps: true,
    createdAt: 'created_at',
    updatedAt: 'updated_at',
    hooks: {
        beforeCreate: async (user) => {
            if (user.password) {
                user.password = await bcrypt.hash(user.password, 10);
            }
        }
    }
});

module.exports = User;

2.4 关联关系 #

javascript

const User = require('./User');
const Post = require('./Post');

User.hasMany(Post, { foreignKey: 'authorId', as: 'posts' });
Post.belongsTo(User, { foreignKey: 'authorId', as: 'author' });

2.5 CRUD操作 #

javascript

const users = await User.findAll();
const user = await User.findByPk(id);
const user = await User.findOne({ where: { email } });

const user = await User.create({ name, email, password });

await User.update({ name: '新名字' }, { where: { id } });

await User.destroy({ where: { id } });

2.6 查询进阶 #

javascript

const users = await User.findAll({
    where: {
        role: 'admin',
        isActive: true
    },
    attributes: ['id', 'name', 'email'],
    include: [{
        model: Post,
        as: 'posts',
        attributes: ['id', 'title']
    }],
    order: [['created_at', 'DESC']],
    limit: 10,
    offset: 0
});

const { count, rows } = await User.findAndCountAll({
    limit: 10,
    offset: 0
});

2.7 事务 #

javascript

const result = await sequelize.transaction(async (t) => {
    const user = await User.create({ name, email, password }, { transaction: t });
    await Profile.create({ userId: user.id, bio }, { transaction: t });
    return user;
});

三、Prisma #

3.1 安装 #

bash

npm install prisma --save-dev
npx prisma init

3.2 Schema定义 #

prisma/schema.prisma：

prisma

datasource db {
    provider = "mysql"
    url      = env("DATABASE_URL")
}

generator client {
    provider = "prisma-client-js"
}

model User {
    id        Int      @id @default(autoincrement())
    name      String   @db.VarChar(50)
    email     String   @unique @db.VarChar(100)
    password  String   @db.VarChar(255)
    role      Role     @default(USER)
    isActive  Boolean  @default(true) @map("is_active")
    posts     Post[]
    createdAt DateTime @default(now()) @map("created_at")
    updatedAt DateTime @updatedAt @map("updated_at")

    @@map("users")
}

model Post {
    id        Int      @id @default(autoincrement())
    title     String   @db.VarChar(200)
    content   String?  @db.Text
    authorId  Int      @map("author_id")
    author    User     @relation(fields: [authorId], references: [id])
    createdAt DateTime @default(now()) @map("created_at")
    updatedAt DateTime @updatedAt @map("updated_at")

    @@map("posts")
}

enum Role {
    USER
    ADMIN
}

3.3 客户端使用 #

javascript

const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

const users = await prisma.user.findMany();
const user = await prisma.user.findUnique({ where: { id } });
const user = await prisma.user.findUnique({ where: { email } });

const user = await prisma.user.create({
    data: { name, email, password }
});

const user = await prisma.user.update({
    where: { id },
    data: { name: '新名字' }
});

await prisma.user.delete({ where: { id } });

3.4 关联查询 #

javascript

const users = await prisma.user.findMany({
    include: {
        posts: true
    }
});

const posts = await prisma.post.findMany({
    include: {
        author: {
            select: {
                id: true,
                name: true,
                email: true
            }
        }
    }
});

3.5 分页查询 #

javascript

const page = 1;
const limit = 10;
const skip = (page - 1) * limit;

const [users, total] = await Promise.all([
    prisma.user.findMany({
        skip,
        take: limit,
        orderBy: { createdAt: 'desc' }
    }),
    prisma.user.count()
]);

3.6 事务 #

javascript

const result = await prisma.$transaction([
    prisma.user.create({ data: { name, email, password } }),
    prisma.profile.create({ data: { userId, bio } })
]);

const user = await prisma.$transaction(async (tx) => {
    const user = await tx.user.create({ data: { name, email, password } });
    await tx.profile.create({ data: { userId: user.id, bio } });
    return user;
});

四、完整示例 #

4.1 Sequelize完整示例 #

models/index.js：

javascript

const Sequelize = require('sequelize');
const config = require('../config/database');

const sequelize = new Sequelize(config.database, config.username, config.password, {
    host: config.host,
    dialect: config.dialect,
    logging: false
});

const db = {};

db.Sequelize = Sequelize;
db.sequelize = sequelize;

db.User = require('./User')(sequelize, Sequelize);
db.Post = require('./Post')(sequelize, Sequelize);
db.Comment = require('./Comment')(sequelize, Sequelize);

db.User.hasMany(db.Post, { foreignKey: 'authorId', as: 'posts' });
db.Post.belongsTo(db.User, { foreignKey: 'authorId', as: 'author' });
db.Post.hasMany(db.Comment, { foreignKey: 'postId', as: 'comments' });
db.Comment.belongsTo(db.Post, { foreignKey: 'postId' });
db.Comment.belongsTo(db.User, { foreignKey: 'authorId' });

module.exports = db;

services/userService.js：

javascript

const { User, Post } = require('../models');

const userService = {
    async findAll(options = {}) {
        const page = parseInt(options.page) || 1;
        const limit = parseInt(options.limit) || 10;
        const offset = (page - 1) * limit;
        
        const { count, rows } = await User.findAndCountAll({
            attributes: { exclude: ['password'] },
            include: [{
                model: Post,
                as: 'posts',
                attributes: ['id', 'title'],
                required: false
            }],
            order: [['createdAt', 'DESC']],
            limit,
            offset
        });
        
        return {
            users: rows,
            pagination: {
                page,
                limit,
                total: count,
                totalPages: Math.ceil(count / limit)
            }
        };
    },
    
    async findById(id) {
        return await User.findByPk(id, {
            attributes: { exclude: ['password'] },
            include: [{
                model: Post,
                as: 'posts'
            }]
        });
    },
    
    async create(userData) {
        return await User.create(userData);
    },
    
    async update(id, userData) {
        const [updated] = await User.update(userData, {
            where: { id }
        });
        return updated > 0;
    },
    
    async delete(id) {
        return await User.destroy({ where: { id } });
    }
};

module.exports = userService;

五、ORM对比 #

特性	Sequelize	Prisma
学习曲线	中等	较低
类型安全	一般	优秀
查询语法	链式调用	对象语法
迁移工具	内置	内置
文档	完善	优秀
性能	良好	优秀

六、总结 #

ORM使用要点：

概念	说明
模型定义	定义数据结构和验证
关联关系	一对一、一对多、多对多
CRUD	增删改查操作
查询	条件、排序、分页
事务	保证数据一致性

下一步，让我们学习高级特性！