@dpapejs/sql-mapper

Description

An Nodejs similar to Mybatis is used to map data to database.

Install

npm i @dpapejs/sql-mapper -S
# OR
yarn add @dpapejs/sql-mapper -S

Initial Configuration

import { init } from "@dpapejs/sql-mapper";
import { join } from "path";
init(
  {
    // Mysql database configuration
    mysql: {
      user: "root",
      password: "***********",
      database: "database name",
    },
    mongo: {
      user: "admin",
      password: "***********",
      database: "database name",
      authDatabase: "admin",
    },
  },
  // XML file exists path
  join(__dirname, "./mapper")
);

Use MySQL

user.xml

<?xml version="1.0" encoding="UTF-8"?>
<mapper>
  <item key="id" as="id"></item>
  <item key="username" as="username"></item>
  <item key="password" as="password"></item>
  <item key="nickname" as="name"></item>
  <item key="avatar_url" as="avatarUrl"></item>
  <item key="status" as="status"></item>
  <item key="create_time" as="createTime"></item>
  <item key="update_time" as="updateTime"></item>
  <item key="start" as="start"></item>
  <item key="end" as="end"></item>
  <item key="email" as="email"></item>

  <!-- SQL statement-->

  <!-- paging query  -->
  <select name="getUserList">
    SELECT id,username,nickname,avatar_url,`status`,create_time,update_time,email FROM users
    <where>
      <if rule="username !== null && username !== ''">
        username like #{username}
      </if>
      <if rule="status !== null && status !== ''">
        status = #{status}
      </if>
      <if rule="1===1">
        status != 'DELETE'
      </if>
    </where>
    limit #{start},#{end}
  </select>

  <!-- The details query returns only one data -->
  <detail name="getUserDetail">
    SELECT id,username,nickname,avatar_url,`status`,create_time,update_time,email FROM users WHERE id = #{id}
  </detail>

  <!-- Update data statement -->
  <update name="updateUserNickname">
    UPDATE users SET nickname=#{nickname} WHERE id = #{id}
  </update>

  <!-- Delete statement  -->
  <update name="deleteUser">
    DELETE FROM `users`  WHERE id = #{id}
  </update>

  <!-- Insert statement -->
  <insert name="createUser">
    INSERT INTO users (
      `username`,
      `password`,
      `nickname`,
      `avatar_url`,
      `create_time`,
      `update_time`,
      `email`
    ) VALUES (
      #{username},
      #{password},
      #{nickname},
      #{avatar_url},
      #{create_time},
      #{update_time},
      #{email}
    )
  </insert>
</mapper>

Javascript code

import { mysql } from "@dpapejs/sql-mapper";
const { md5 } = require("@dpapejs/node-utils");
const exec = mysql('user')

// Query User List
export async funtion getUserList(){
  return await exec("getUserList", {
    start: 0,
    end: 50,
  })
}

// Query User Detail
export async funtion getUserDetail(){
  return await exec("getUserDetail", { id:10 })
}

// Update the user nickname
export async function updateUserInfo(){
  return await exec("updateUserNickname", {
    id:10,
    nickname:"text value"
   })
}

// create user
export async function updateUserInfo(){
  return await exec("createUser", {
    username: "test_user",
    password: md5("123456"),
    name: "test nickname",
    create_time: new Date(),
    update_time: new Date(),
  })
}

// Delete User info
export async funtion deleteUser(){
  return await exec("deleteUser", { id:10 })
}

Use Mongodb

test_mapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<mapper>
  <item key="id" as="flowId"></item>
  <item key="remark" as="remark"></item>
  <item key="name" as="flowName"></item>
  <item key="status" as="flowStatus"></item>
  <item key="time" as="updateTime"></item>
</mapper>

Javascript code

import { mongodb } from "@dpapejs/sql-mapper";
const exec = mongodb("test_mapper", "collection");

// add data
exec("insert", {
  flowId: 1,
  remark: "remark value",
  flowName: "flow name",
  flowStatus: "flow status",
  updateTime: new Date(),
});

// Query data
exec("query", {
  // Query condition
  search: { flowId: 1 },
  // Sorting conditions
  sort: { updateTime: -1 },
  // Paging parameters
  paging: {
    page: 1,
    pageSize: 50,
  },
});

// Update the data
exec(
  "update",
  { flowId: 1 },
  {
    flowName: "update value",
  }
);

Notice

The mysql insert statement can be used as an array to insert multiple data

Frequently Asked Questions

Q: Connect MySQL Error

ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server;

A: Please execute the following command in MySQL

ALTER USER '[username]'@'[ip]' IDENTIFIED WITH mysql_native_password BY '[password]';