欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-04-14 20:24:59
接上一篇,这里使用 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

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

 

至此连接数据库完成

 

目录结构:

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