最近希望使用Nodejs
给我提供后端服务,但是数据库操作这块实在是麻烦,所以想着找一个线程的ORM框架来进行数据库映射、连接、操作功能。可能是真的因为Nodejs这块的使用比例不高的问题,ORM框架并不是很多,相应的开发文档信息也是少的可能,和其他主流后端语言没法比。我这里也只是找到几个ORM框架。
ORM框架
- sequelize :适配主流数据库,各种查询模式相对稳定,主流开源
node + js
框架(例如:egg)的数据库默认 orm 框架。 - Typeorm:同样适配主流数据库,另外有更好的类型推导,主流开源
node + ts
框架(例如:midway
)的数据库默认orm
框架。 - Prisma:有一套已经很成熟的
dsl
,用这个dsl
描述数据之间的关系,比用底层sql
要简单。相比 typeorm,类型推导上更加出色(属性查询的实体等),简洁的实体声明语法(语法高亮提示用 vscode 插件),还免费带有 可视化桌面端应用,整个生态相对比较完备。
TypeORM的使用
由于我的软件准备采用midway + TS
框架,所以我这边使用TypeORM做为ORM框架。这里记录总结一些TypeORM常用的操作。
安装
我这里使用mysql作为数据库,ts作为主要开发语言,所以本文所有使用基于mysql
数据库驱动之上。
$ npm install mysql typeorm reflect-metadata --save
$ npm install @types/node --save // ts类型声明
tsconfig.json配置
需要保证TypeScript的编译器版本在2.3之上,并在tsconfig.json
中启用如下两个模块:
// tsconfig.json
{
"compileOnSave": true,
"compilerOptions": {
"target": "es2018",
"module": "commonjs",
"moduleResolution": "node",
"experimentalDecorators": true, // typeorm需要启用
"emitDecoratorMetadata": true, // typeorm需要启用
"inlineSourceMap":true,
"noImplicitThis": true,
"noUnusedLocals": true,
"stripInternal": true,
"skipLibCheck": true,
"pretty": true,
"declaration": true,
"noImplicitAny": false,
"typeRoots": [ "./typings", "./node_modules/@types"],
"outDir": "dist"
},
"exclude": [
"dist",
"node_modules",
"test"
]
}
配置
新建ormconfig.json
文件,并添加数据库连接配置:
{
"type": "mysql",
"host": "localhost",
"port": 3306,
"username": "root",
"password": "root",
"database": "test",
"synchronize": true,
"logging": false,
"entities": ["src/entity/**/*.ts"], // 实体类位置
"migrations": ["src/migration/**/*.ts"], // 迁移
"subscribers": ["src/subscriber/**/*.ts"] // 订阅
}
我们也可以在代码中通过配置连接:
import { createConnection, Connection } from "typeorm";
const connection = await createConnection({
type: "mysql",
host: "localhost",
port: 3306,
username: "test",
password: "test",
database: "test",
entities: ["src/entity/**/*.ts"],
migrations: ["src/migration/**/*.ts"],
subscribers: ["src/subscriber/**/*.ts"]
});
同时,TypeORM
也支持主从复制配置和读写分离配置,参考:Nodejs使用typeorm实现mysql的读写分离
实体创建
TypeORM
中配置指定位置的实体类其实际回主动映射到对应的数据表中,我们创建一个实体类其实就是创建一个数据表。代码如下:
import { Entity, PrimaryGeneratedColumn, Column } from "typeorm";
// 通过entity注入创建数据表,可通过传入表名创建自定义表,若未传入则创建一个和类名相同的表
@Entity("sys_base_user")
export class User {
@PrimaryGeneratedColumn()
id: number;
@Column()
firstName: string;
@Column()
lastName: string;
@Column()
isActive: boolean;
}
创建的表结构如下:
+-------------+--------------+----------------------------+
| sys_base_user|
+-------------+--------------+----------------------------+
| id | int(11) | PRIMARY KEY AUTO_INCREMENT |
| firstName | varchar(255) | |
| lastName | varchar(255) | |
| isActive | boolean | |
+-------------+--------------+----------------------------+
基本实体由列和关系组成。 每个实体必须有一个主键(primary key
),每个实体都需要在连接时被注册,但由于我们上面已经配置注册完成了,这里可以不用管。如上可知,我们可以通过 @Column()
来定义我们的列数据,typeorm会根据列数据定义的类型自动在对应表中创建一个相同的列,其常见格式如下:
@Column("varchar", { length: 200 }) // 或者
@Column({ type: "int",comment })
Column
支持配置参数如下:
- type?: ColumnType:列数据类型,其支持参数如下
export type PrimaryGeneratedColumnType = "int" | "int2" | "int4" | "int8" | "integer" | "tinyint" | "smallint" | "mediumint" | "bigint" | "dec" | "decimal" | "smalldecimal" | "fixed" | "numeric" | "number";
/**
* Column types where spatial properties are used.
*/
export type SpatialColumnType = "geometry" | "geography" | "st_geometry" | "st_point";
/**
* Column types where precision and scale properties are used.
*/
export type WithPrecisionColumnType = "float" | "double" | "dec" | "decimal" | "smalldecimal" | "fixed" | "numeric" | "real" | "double precision" | "number" | "datetime" | "datetime2" | "datetimeoffset" | "time" | "time with time zone" | "time without time zone" | "timestamp" | "timestamp without time zone" | "timestamp with time zone" | "timestamp with local time zone";
/**
* Column types where column length is used.
*/
export type WithLengthColumnType = "character varying" | "varying character" | "char varying" | "nvarchar" | "national varchar" | "character" | "native character" | "varchar" | "char" | "nchar" | "national char" | "varchar2" | "nvarchar2" | "alphanum" | "shorttext" | "raw" | "binary" | "varbinary" | "string";
export type WithWidthColumnType = "tinyint" | "smallint" | "mediumint" | "int" | "bigint";
/**
* All other regular column types.
*/
export type SimpleColumnType = "simple-array" | "simple-json" | "simple-enum" | "int2" | "integer" | "int4" | "int8" | "int64" | "unsigned big int" | "float" | "float4" | "float8" | "float64" | "smallmoney" | "money" | "boolean" | "bool" | "tinyblob" | "tinytext" | "mediumblob" | "mediumtext" | "blob" | "text" | "ntext" | "citext" | "hstore" | "longblob" | "longtext" | "alphanum" | "shorttext" | "bytes" | "bytea" | "long" | "raw" | "long raw" | "bfile" | "clob" | "nclob" | "image" | "timetz" | "timestamptz" | "timestamp with local time zone" | "smalldatetime" | "date" | "interval year to month" | "interval day to second" | "interval" | "year" | "seconddate" | "point" | "line" | "lseg" | "box" | "circle" | "path" | "polygon" | "geography" | "geometry" | "linestring" | "multipoint" | "multilinestring" | "multipolygon" | "geometrycollection" | "st_geometry" | "st_point" | "int4range" | "int8range" | "numrange" | "tsrange" | "tstzrange" | "daterange" | "enum" | "set" | "cidr" | "inet" | "inet4" | "inet6" | "macaddr" | "bit" | "bit varying" | "varbit" | "tsvector" | "tsquery" | "uuid" | "xml" | "json" | "jsonb" | "varbinary" | "hierarchyid" | "sql_variant" | "rowid" | "urowid" | "uniqueidentifier" | "rowversion" | "array" | "cube" | "ltree";
/**
* Any column type column can be.
*/
export type ColumnType = WithPrecisionColumnType | WithLengthColumnType | WithWidthColumnType | SpatialColumnType | SimpleColumnType | BooleanConstructor | DateConstructor | NumberConstructor | StringConstructor;
name
:列名称,可以不指定,默认使用变量名length
:列长度,通常数据类型为varchar
时可用,如varchar(100)
width
:类型占用字节,如int(4)
nullable
:字段是否可为空,默认为false
(不可空)update
:字段只读,通常指是否支持save
操作,默认false
select
:是否支持执行QueryBuilder
操作,默认为true
insert
:是否支持insert操作,默认为truecomment
:列备注,不是所有类型都支持precision
: number类型小数点后的位数transformer
:数据库和实体类之前数据转换配置- 。。。等等
主键定义
- @PrimaryColumn():创建一个指定任何类型的数据为主键
import { Entity, PrimaryColumn } from "typeorm"
@Entity()
export class User {
@PrimaryColumn()
id: number
}
- @PrimaryGeneratedColumn():创建一个自增类型的主键,一般为
number
类型数据,我们不需要对其进行赋值,其会自动生成。
import { Entity, PrimaryGeneratedColumn } from "typeorm";
@Entity()
export class User {
@PrimaryGeneratedColumn()
id: number;
}
- @PrimaryGeneratedColumn(“uuid”):创建一个由uuid自动生成的主键列,Uuid 是一个独特的字符串 id。不需要我们手动分配数据,其会自动生成。
import { Entity, PrimaryGeneratedColumn } from "typeorm";
@Entity()
export class User {
@PrimaryGeneratedColumn("uuid")
id: string;
}
特殊列
@CreateDateColumn
:自动为实体插入日期。无需设置此列,该值将自动设置。- @UpdateDateColumn:自动更新实体日期。无需设置此列,该值将自动设置。
- @VersionColumn:每次调用实体管理器或存储库的
save
时自动增长实体版本(增量编号)。无需设置此列,该值将自动设置。