typescript连接mysql_typescript-koa-postgresql 实现一个简单的rest风格服务器 —— 连接 postgresql 数据库...

接上一篇，这里使用 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

1 /*

2 * @Description: 数据库连接类3 */

4

5 import * as path from 'path'

6 import { Sequelize } from 'sequelize-typescript'

7 import { dbConfig } from '../conf/db.conf'

8

9 class DbContext {10 private sequelize: Sequelize11 constructor() {12 const { host, database, dialect, username, password } =dbConfig13 this.sequelize = newSequelize({14 host: host,15 database: database,16 dialect: dialect,17 username: username,18 password: password,19 define: {20 timestamps: true, //开启时间戳 create_at delete_at update_at

21 paranoid: true, //开启假删除

22 underscored: true, //下划线

23 charset: 'utf8',24 freezeTableName: true //固定表名为单数 默认表名是xxxs

25 },26 pool: {27 max: 10,28 min: 0,29 acquire: 30000,30 idle: 10000

31 },32 timezone: '+08:00',33 modelPaths: [path.resolve(__dirname, `./models`)]

34 })35 this.sequelize.sync()36 }37 init(): Boolean {38 return !!this.sequelize39 }40 getInstance(): Sequelize {41 return this.sequelize42 }43 isInit(): Boolean {44 return !!this.sequelize45 }46 }47 export const dbContext = new DbContext()

4、数据库实体类，新建文件夹 models 及文件 user.ts

1 /*

2 * @Description: 数据库实体类3 */

4

5 import { Table, Column, Model } from 'sequelize-typescript'

6

7 @Table({8 tableName: 'user'

9 })10 export default class User extends Model{11 @Column({12 comment: '自增ID',13 primaryKey: true,14 autoIncrement: true,15 })16 id: number17

18 @Column19 username: string20

21 @Column22 password: string23 }

5、编写业务逻辑接口，在 src 目录下新建文件夹 dao、service，在 dao 目录下新建 UserDao.ts 及子目录  impl，在 service  目录下新建 UserService.ts 及子目录  impl

1 /*

2 * @Description: 数据库表操作基础接口 UserDao.ts3 */

4 export interface UserDao {5 /**6 * @name: 查询7 * @param :8 * @return : Array9 */

10 findAll();11 /**12 * @name: 查询13 * @param :14 * @return : Array15 */

16 findByName(username:string);17 /**18 * @name: 新增19 * @param : undefined20 * @return : undefined21 */

22 create(entity:UserInfo);23

24 /**25 * @name: 删除26 * @param : undefined27 * @return : undefined28 */

29 delete(id:number);30 }31 export interface UserInfo {32 username:string;33 password:string;34 }

1 /*

2 * @Description: service接口 UserService.ts3 * @version:4 */

5

6 export interface UserService{7 /**8 * @name: 查询9 * @param : undefined10 * @return : undefined11 */

12 findAll();13

14 /**15 * @name: 查询16 * @param : undefined17 * @return : undefined18 */

19 findByName(username:string);20

21 /**22 * @name: 新增23 * @param : undefined24 * @return : undefined25 */

26 create(username:string,password:string);27

28 /**29 * @name: 删除30 * @param : undefined31 * @return : undefined32 */

33 delete(id:String);34 }

6、编写业务逻辑实现类 UserDaoImpl.ts、UserServiceImpl.ts

1 /*

2 * @Description: 数据库表操作基础实现类 UserDaoImpl.ts3 */

4

5 import { dbContext } from '../../db/db'

6 import { UserDao, UserInfo } from '../UserDao';7 import User from '../../db/models/user';8

9 export class UserDaoImpl implements UserDao{10 constructor(){11 dbContext.init();12 }13 /**14 * @name: 查询15 * @param : undefined16 * @return : undefined17 */

18 public async findAll(){19 const results =await User.findAll({20 raw: true

21 })22 returnresults;23 }24

25 /**26 * @name: 查询27 * @param : undefined28 * @return : undefined29 */

30 public async findByName(username:string){31 const results =await User.findOne({32 where:{33 username:username34 }35 })36 returnresults;37 }38

39 /**40 * @name: 新增41 * @param : entity42 * @return : undefined43 */

44 public async create(entity:UserInfo) {45 const results =await User.create(entity)46 returnresults;47 }48

49 /**50 * @name: 删除51 * @param : undefined52 * @return : undefined53 */

54 public async delete(id: number) {55 const results =await User.destroy({56 where:{57 id:{58 $eq:id59 }60 }61 });62 returnresults;63 }64 }

1 import { UserService } from "../UserService";2 import { UserDao } from "../../dao/UserDao";3 import { UserDaoImpl } from "../../dao/impl/UserDaoImpl";4

5 /*

6 * @Description: service实现类 UserServiceImpl.ts7 */

8

9

10 export class UserServiceImpl implements UserService{11 private userDao:UserDao;12

13 constructor(){14 this.userDao = newUserDaoImpl();15 }16

17 /**18 * @name: 查询19 * @param : undefined20 * @return : undefined21 */

22 public findAll() {23 return this.userDao.findAll();24 }25 /**26 * @name: 查询27 * @param : undefined28 * @return : undefined29 */

30 public findByName(username:string) {31 return this.userDao.findByName(username);32 }33 /**34 * @name: 新增35 * @param : entity36 * @return : undefined37 */

38 public create(username: string, password: string) {39 return this.userDao.create({username,password});40 }41

42 /**43 * @name: 删除44 * @param : undefined45 * @return : undefined46 */

47 public delete(id: String) {48 return this.userDao.delete(~~id);49 }50

51 }

7、查看成果，修改 router/index.ts

1 /*

2 * @Description: 后台路由组件3 * @version: 0.1.04 */

5 import * as Router from 'koa-router';6 import { UserInfo } from '../dao/UserDao';7 import { UserService } from '../service/UserService';8 import { UserServiceImpl } from '../service/impl/UserServiceImpl';9

10 const router = newRouter();11 const userService:UserService =newUserServiceImpl();12

13 router.get('/*', async (ctx) =>{14 ctx.body =await userService.findAll();15 })16

17 export { router }

8、浏览器输入 http://localhost:8080

至此连接数据库完成

目录结构：