typescript-koa-postgresql 实现一个简单的rest风格服务器 —— 连接 postgresql 数据库
程序员文章站
2022-08-09 08:48:50
接上一篇,这里使用 sequelize 来连接 postgresql 数据库 1、安装 sequelize,数据库驱动 pg 2、新建配置文件夹 conf 及 配置文件 db.conf.ts 3、连接数据库,新建文件夹 db 及 配置文件 db.ts 4、数据库实体类,新建文件夹 models 及文 ......
接上一篇,这里使用 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: sequelize 11 constructor() { 12 const { host, database, dialect, username, password } = dbconfig 13 this.sequelize = new sequelize({ 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.sequelize 39 } 40 getinstance(): sequelize { 41 return this.sequelize 42 } 43 isinit(): boolean { 44 return !!this.sequelize 45 } 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<user> { 11 @column({ 12 comment: '自增id', 13 primarykey: true, 14 autoincrement: true, 15 }) 16 id: number 17 18 @column 19 username: string 20 21 @column 22 password: string 23 }
5、编写业务逻辑接口,在 src 目录下新建文件夹 dao、service,在 dao 目录下新建 userdao.ts 及子目录 impl,在 service 目录下新建 userservice.ts 及子目录 impl
1 /* 2 * @description: 数据库表操作基础接口 userdao.ts 3 */ 4 export interface userdao { 5 /** 6 * @name: 查询 7 * @param : 8 * @return : array<user> 9 */ 10 findall(); 11 /** 12 * @name: 查询 13 * @param : 14 * @return : array<user> 15 */ 16 findbyname(username:string); 17 /** 18 * @name: 新增 19 * @param : undefined 20 * @return : undefined 21 */ 22 create(entity:userinfo); 23 24 /** 25 * @name: 删除 26 * @param : undefined 27 * @return : undefined 28 */ 29 delete(id:number); 30 } 31 export interface userinfo { 32 username:string; 33 password:string; 34 }
1 /* 2 * @description: service接口 userservice.ts 3 * @version: 4 */ 5 6 export interface userservice{ 7 /** 8 * @name: 查询 9 * @param : undefined 10 * @return : undefined 11 */ 12 findall(); 13 14 /** 15 * @name: 查询 16 * @param : undefined 17 * @return : undefined 18 */ 19 findbyname(username:string); 20 21 /** 22 * @name: 新增 23 * @param : undefined 24 * @return : undefined 25 */ 26 create(username:string,password:string); 27 28 /** 29 * @name: 删除 30 * @param : undefined 31 * @return : undefined 32 */ 33 delete(id:string); 34 }
6、编写业务逻辑实现类 userdaoimpl.ts、userserviceimpl.ts
1 /* 2 * @description: 数据库表操作基础实现类 userdaoimpl.ts 3 */ 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 : undefined 16 * @return : undefined 17 */ 18 public async findall(){ 19 const results = await user.findall({ 20 raw: true 21 }) 22 return results; 23 } 24 25 /** 26 * @name: 查询 27 * @param : undefined 28 * @return : undefined 29 */ 30 public async findbyname(username:string){ 31 const results = await user.findone({ 32 where:{ 33 username:username 34 } 35 }) 36 return results; 37 } 38 39 /** 40 * @name: 新增 41 * @param : entity 42 * @return : undefined 43 */ 44 public async create(entity:userinfo) { 45 const results = await user.create(entity) 46 return results; 47 } 48 49 /** 50 * @name: 删除 51 * @param : undefined 52 * @return : undefined 53 */ 54 public async delete(id: number) { 55 const results = await user.destroy({ 56 where:{ 57 id:{ 58 $eq:id 59 } 60 } 61 }); 62 return results; 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.ts 7 */ 8 9 10 export class userserviceimpl implements userservice{ 11 private userdao:userdao; 12 13 constructor(){ 14 this.userdao = new userdaoimpl(); 15 } 16 17 /** 18 * @name: 查询 19 * @param : undefined 20 * @return : undefined 21 */ 22 public findall() { 23 return this.userdao.findall(); 24 } 25 /** 26 * @name: 查询 27 * @param : undefined 28 * @return : undefined 29 */ 30 public findbyname(username:string) { 31 return this.userdao.findbyname(username); 32 } 33 /** 34 * @name: 新增 35 * @param : entity 36 * @return : undefined 37 */ 38 public create(username: string, password: string) { 39 return this.userdao.create({username,password}); 40 } 41 42 /** 43 * @name: 删除 44 * @param : undefined 45 * @return : undefined 46 */ 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.0 4 */ 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 = new router(); 11 const userservice:userservice =new userserviceimpl(); 12 13 router.get('/*', async (ctx) => { 14 ctx.body = await userservice.findall(); 15 }) 16 17 export { router }
8、浏览器输入 http://localhost:8080
至此连接数据库完成
目录结构: