接上一篇,这里使用 sequelize 来连接 postgresql 数据库
1、安装 sequelize,数据库驱动 pg
yarn add sequelize sequelize-typescript pg reflect-metadata
2、新建配置文件夹 conf 及 配置文件 db.conf.ts
/**
* @name: 数据库配置
* @param : undefined
* @return : undefined
*/
export const dbConfig = {
host: 'localhost',
database: 'demo',
dialect: 'postgres',
username: 'postgres',
password: '123456'
}
3、连接数据库,新建文件夹 db 及 配置文件 db.ts
/*
* @Description: 数据库连接类
*/ import * as path from 'path'
import { Sequelize } from 'sequelize-typescript'
import { dbConfig } from '../conf/db.conf' class DbContext {
private sequelize: Sequelize
constructor() {
const { host, database, dialect, username, password } = dbConfig
this.sequelize = new Sequelize({
host: host,
database: database,
dialect: dialect,
username: username,
password: password,
define: {
timestamps: true, //开启时间戳 create_at delete_at update_at
paranoid: true, //开启假删除
underscored: true, //下划线
charset: 'utf8',
freezeTableName: true //固定表名为单数 默认表名是xxxs
},
pool: {
max: 10,
min: 0,
acquire: 30000,
idle: 10000
},
timezone: '+08:00',
modelPaths: [path.resolve(__dirname, `./models`)]
})
this.sequelize.sync()
}
init(): Boolean {
return !!this.sequelize
}
getInstance(): Sequelize {
return this.sequelize
}
isInit(): Boolean {
return !!this.sequelize
}
}
export const dbContext = new DbContext()
4、数据库实体类,新建文件夹 models 及文件 user.ts
/*
* @Description: 数据库实体类
*/ import { Table, Column, Model } from 'sequelize-typescript' @Table({
tableName: 'user'
})
export default class User extends Model<User> {
@Column({
comment: '自增ID',
primaryKey: true,
autoIncrement: true,
})
id: number @Column
username: string @Column
password: string
}
5、编写业务逻辑接口,在 src 目录下新建文件夹 dao、service,在 dao 目录下新建 UserDao.ts 及子目录 impl,在 service 目录下新建 UserService.ts 及子目录 impl
/*
* @Description: 数据库表操作基础接口 UserDao.ts
*/
export interface UserDao {
/**
* @name: 查询
* @param :
* @return : Array<User>
*/
findAll();
/**
* @name: 查询
* @param :
* @return : Array<User>
*/
findByName(username:string);
/**
* @name: 新增
* @param : undefined
* @return : undefined
*/
create(entity:UserInfo); /**
* @name: 删除
* @param : undefined
* @return : undefined
*/
delete(id:number);
}
export interface UserInfo {
username:string;
password:string;
}
/*
* @Description: service接口 UserService.ts
* @version:
*/ export interface UserService{
/**
* @name: 查询
* @param : undefined
* @return : undefined
*/
findAll(); /**
* @name: 查询
* @param : undefined
* @return : undefined
*/
findByName(username:string); /**
* @name: 新增
* @param : undefined
* @return : undefined
*/
create(username:string,password:string); /**
* @name: 删除
* @param : undefined
* @return : undefined
*/
delete(id:String);
}
6、编写业务逻辑实现类 UserDaoImpl.ts、UserServiceImpl.ts
/*
* @Description: 数据库表操作基础实现类 UserDaoImpl.ts
*/ import { dbContext } from '../../db/db'
import { UserDao, UserInfo } from '../UserDao';
import User from '../../db/models/user'; export class UserDaoImpl implements UserDao{
constructor(){
dbContext.init();
}
/**
* @name: 查询
* @param : undefined
* @return : undefined
*/
public async findAll(){
const results = await User.findAll({
raw: true
})
return results;
} /**
* @name: 查询
* @param : undefined
* @return : undefined
*/
public async findByName(username:string){
const results = await User.findOne({
where:{
username:username
}
})
return results;
} /**
* @name: 新增
* @param : entity
* @return : undefined
*/
public async create(entity:UserInfo) {
const results = await User.create(entity)
return results;
} /**
* @name: 删除
* @param : undefined
* @return : undefined
*/
public async delete(id: number) {
const results = await User.destroy({
where:{
id:{
$eq:id
}
}
});
return results;
}
}
import { UserService } from "../UserService";
import { UserDao } from "../../dao/UserDao";
import { UserDaoImpl } from "../../dao/impl/UserDaoImpl"; /*
* @Description: service实现类 UserServiceImpl.ts
*/ export class UserServiceImpl implements UserService{
private userDao:UserDao; constructor(){
this.userDao = new UserDaoImpl();
} /**
* @name: 查询
* @param : undefined
* @return : undefined
*/
public findAll() {
return this.userDao.findAll();
}
/**
* @name: 查询
* @param : undefined
* @return : undefined
*/
public findByName(username:string) {
return this.userDao.findByName(username);
}
/**
* @name: 新增
* @param : entity
* @return : undefined
*/
public create(username: string, password: string) {
return this.userDao.create({username,password});
} /**
* @name: 删除
* @param : undefined
* @return : undefined
*/
public delete(id: String) {
return this.userDao.delete(~~id);
} }
7、查看成果,修改 router/index.ts
/*
* @Description: 后台路由组件
* @version: 0.1.0
*/
import * as Router from 'koa-router';
import { UserInfo } from '../dao/UserDao';
import { UserService } from '../service/UserService';
import { UserServiceImpl } from '../service/impl/UserServiceImpl'; const router = new Router();
const userService:UserService =new UserServiceImpl(); router.get('/*', async (ctx) => {
ctx.body = await userService.findAll();
}) export { router }
8、浏览器输入 http://localhost:8080
至此连接数据库完成
目录结构: