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

/*
 * @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

?

至此連接數據庫完成

?

目錄結構：