State Pattern

Trying to refactor my old project by using design pattern. It really becomes simple to implement new functionality now! What I need to do is just to create additional state. And my code get less coupled! My class mainVM know nothing about my translation service now!

https://github.com/machingclee/ScreenCapDictionaryNoteApp_refactor/tree/2020-06-23-refactor-translation-by-state-pattern/ScreenCapDictionaryNoteApp/ViewModel/Helpers/TranslationHelper

Bash Script

 for f in *\ *; do mv "$f" "${f// /_}"; done

This change all spaces in file name by a "_".

Use Sequelize Migration with ES6 Syntax

After yarn add sequelize-cli it is clear from the --help command how to generate a migration folder and migration file. The only trouble is to use them with ES6 syntax. From the official document:

https://sequelize.org/master/manual/migrations.html#using-babel

we add

yarn add babel-register

and add a .sequelizerc runtime config with

// .sequelizerc
require("babel-register");

const path = require('path');

module.exports = {
  'config': path.resolve('config', 'config.json'),
  'models-path': path.resolve('models'),
  'seeders-path': path.resolve('seeders'),
  'migrations-path': path.resolve('migrations')
}

We can copy the implementation of altering, creating, deleting table from official documentation:

https://sequelize.org/master/manual/query-interface.html

Official document also says that in migration file we can export async function up and async function down instead of returning a chain of promises (i.e., a promise)! For example it happens that I want to add a column for users to implement mobile push notification, then I need to add a column called push_notification_token, I can do the following in our migration file:

"use strict";
import { modelNames } from "../src/enums/modelNames";
import { Sequelize, DataTypes } from "sequelize";

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.addColumn(modelNames.USER + "s", "push_notification_token", {
      type: DataTypes.STRING,
      allowNull: true
    });
  },

  async down(queryInterface, Sequelize) {
    await queryInterface.removeColumn(
      modelNames.USER + "s",
      "push_notification_token",
      {}
    );
  }
};

Now if you run the code, we encounter the following error

Loaded configuration file "config\config.js".
Using environment "development".
== 20200615141047-add-push-notification-token-to-users-table: migrating =======

ERROR: regeneratorRuntime is not defined

so we need the transform runtime plugin by babel,

yarn add babel-plugin-transform-runtime

and in our .babelrc add:

{
  "presets": ["env"],
  "plugins": [
    ["transform-runtime", {
      "regenerator": true
    }]
  ]
}

and we are done!

SQL injection 及 prepared statement already exists

雖然知道 sql injection 成功後果很嚴重，但因為我很少打 raw sql，而我知道很多 library (sequelize, knex 等) 都會避免 sql injection 所以沒有特別去深究。

今天突然想起這個問題，而我也在為自己的手機 app 寫一個 backend 及想有一個自己 customize 的 query 結果。翻查 sequelize 的 doc 這件事沒有比寫 raw query 簡單，所以就開始自己寫 raw sequel。嘗試 sql inject 自己一下。發現如果沒有做任何預防操施真的很危險，甚至把我整個 database 毀掉:

所以開始學習寫 prepare statement:

PREPARE get_notes (int) AS
  SELECT v."id", v."word", v."pronounciation", v."explanation", p."dateTime", p."croppedScreenshot"
  FROM vocabs v
  INNER JOIN pages p 
  ON v."sqlitePageId"=p."sqliteId"
  WHERE p."sqliteNoteId"=$1;
EXECUTE get_notes(${sqliteNoteId});

在 postman get request 了一次，一切都很美好，再 get request 多一次，誒?

error: prepared statement "get_notes" already exists

搜尋了一下解決方法，最後只要每一次完成 EXECUTE 後把儲存好的 prepared statement 移除就好，整句變成:

PREPARE get_notes (int) AS
  SELECT v."id", v."word", v."pronounciation", v."explanation", p."dateTime", p."croppedScreenshot"
  FROM vocabs v
  INNER JOIN pages p 
  ON v."sqlitePageId"=p."sqliteId"
  WHERE p."sqliteNoteId"=$1;
EXECUTE get_notes(${sqliteNoteId});
DEALLOCATE get_notes;

Sequelize API for CRUD operation

更多: https://dwatow.github.io/2018/09-24-sequelize/sequelize-R-of-CRUD/

module.exports = (app, db) => {
  app.get( "/posts", (req, res) =>
    db.post.findAll().then( (result) => res.json(result) )
  );

  app.get( "/post/:id", (req, res) =>
    db.post.findByPk(req.params.id).then( (result) => res.json(result))
  );

  app.post("/post", (req, res) => 
    db.post.create({
      title: req.body.title,
      content: req.body.content
    }).then( (result) => res.json(result) )
  );

  app.put( "/post/:id", (req, res) =>
    db.post.update({
      title: req.body.title,
      content: req.body.content
    },
    {
      where: {
        id: req.params.id
      }
    }).then( (result) => res.json(result) )
  );

  app.delete( "/post/:id", (req, res) =>
    db.post.destroy({
      where: {
        id: req.params.id
      }
    }).then( (result) => res.json(result) )
  );
}

Use docker-compose up instead of docker container run -v blablabla for local developement

version: "3.7"

services:
  app:
    container_name: docker-node-mongo
    restart: always
    build: .
    ports:
      - "80:3000"
    volumes:
      - type: bind
        source: ./
        target: /usr/src/app

  mongo:
    container_name: mongo
    image: mongo
    ports:
      - "27017:27017"