Simple “Search” with LIKE in MySQL Sequelize

A simple way to implement a “search” feature into your node app is using the database engine to check for presence of tokenized search query. In my case, I’m using MySQL with the Sequelize ORM and needed to add a e-commerce like search form with product results for a client.


  // sanitize plain text query to only keep alphanumeric lowercase
  const sanitizedQuery = query.trim().toLowerCase().replace(/[\W_]+/, '')
  // split by space as basic tokenization
  const queryTokens = sanitizedQuery.split(/\s+/)

  const options = {
    where: {
      [Op.and]: queryTokens.map(token =>
        // check for presence of each token in lowercased product `title`
        Sequelize.where(Sequelize.fn('lower', Sequelize.col('title')), 'LIKE', `%${token}%`)
      )
    }
  }
  const results = await db.Product.FindAll(options)

The main `cleverness` of this snippet is in mapping the queryTokens to generate Sequelize.where() queries that compare the lowercased title with our token. Sequelize has some powerful features to interact with the database at a low-level, and this is a great example of that used in a real-world project

Discuss on Twitter

Better Vue SEO with Prerender SPA Plugin and vue-cli

Vue loads content asynchronously, which means that Google’s crawlers won’t pick up your site for indexing. That is, until you give them a rendered version to see. We’re going to discuss a common way to serve content properly for crawlers here, called “Prerendering”.

One of the existing, common solutions is Server-side Rendering or SSR. They render all content on your server and then send it to the client along with JS for interaction. There’s even a new wave of easy to use SSR-compatible tooling, like Nuxt.js (Vue) and Next.js (React). However, SSR does have downsides — it moves rendering load from client to server, and requires a node backend to be running.

A solid middle option is occupied by what’s called Prerendering, where you run a headless browser (either locally or on the server) to cache some of your site’s pages to disk. The Prerender SPA Plugin for Webpack makes it extremely easy to do all this.

Installation

(I’m assuming you’re using the Webpack template for vue-cli, where all your configuration files are in the ./build and you compile the final files to ./dist.)

We’re going to create a new Webpack configuration file for the prerendering. We’ll also add a script to our package.json as an alias.

First, install and save by running npm i prerender-spa-plugin or yarn add prerender-spa-plugin.  Then create a new file under ./build named webpack.prerender.conf.js.

We can only prerender once we have a built index.html for us in the ./dist/ folder, so we need to run the existing build script to ensure we have that done correctly. Do so by requiring path, merge, and the production webpack config like so:

const path = require('path')
const merge = require('webpack-merge')
const baseWebpackConfig = require('./webpack.prod.conf')
const PrerenderSpaPlugin = require('prerender-spa-plugin')

const webpackConfig = merge(baseWebpackConfig, {

Drew Lustro and Chris Fritz have done a great job abstracting away the difficult work of prerendering, and so we simply add and configure their plugin like so:

const webpackConfig = merge(baseWebpackConfig, {
  plugins: [
    // prerender the important pages
    new PrerenderSpaPlugin(
      path.join(__dirname, '../dist'),
      ['/', '/about', '/faq' /* and others... */],
      {
        /**
          * A dirty hack: setting a very specific viewport size 
          * makes it very easy to check for the prerenderer in Vue's
          * `created()' via `window.innerWidth' and `window.innerHeight',
          * giving a way to server custom content for search engines
          */ 
        phantomPageViewportSize: {
          width: 1242,
          height: 742
        },
        postProcessHtml: function (context) {
          // `context.html' will contain the HTML returned by the
          // headless browser, and `context.route' will be the path
          // use this place to replace or fix the contents.
        }
      }
    )
  ]
})

module.exports = webpackConfig

I’m using some of the plugins options since fine-tuning prerendering is often needed, but only the first two arguments are actually required — the path to ./dist and an array of the routes you wish to prerender. Try to pick only routes that don’t change often to minimize time spent running the prerendering script.

The little hack I’ve added allows us to detect the viewport size can then be used in JS or CSS (via media queries) and then present a slightly different version of the page for crawlers. Setting a viewport size is needed if you’ve got responsive website anyway, to choose the version you wish to use for prerendering.

I’ve also needed to strip away all <style> tags from the page for a project since there was CSS from rarely-used third party modules being included in the pages that didn’t even use the modules. If you wish to do so too, use this one-liner in postProcessHtml which uses a simple RegEx:

return context.html.replace(/<style type="text\/css">[\s\S]*?<\/style>/gi, '')

To make things easier, I copied the ./build/build.js file to ./build/build.prerender.js and made a few changes to get a pretty spinner when I prerender:

'use strict'
require('./check-versions')()

process.env.NODE_ENV = 'production'

const ora = require('ora')
const chalk = require('chalk')
const webpack = require('webpack')
const webpackConfig = require('./webpack.prerender.conf')

const spinner = ora('building for production...')
spinner.start()

webpack(webpackConfig, (err, stats) => {
  spinner.stop()
  if (err) throw err
  process.stdout.write(stats.toString({
    colors: true,
    modules: false,
    children: false, // If you are using ts-loader, setting this to true will make TypeScript errors show up during build.
    chunks: false,
    chunkModules: false
  }) + '\n\n')

  if (stats.hasErrors()) {
    console.log(chalk.red('  Build failed with errors.\n'))
    process.exit(1)
  }

  console.log(chalk.cyan('  Build complete.\n'))
  console.log(chalk.yellow(
    '  Tip: built files are meant to be served over an HTTP server.\n' +
    '  Opening index.html over file:// won\'t work.\n'
  ))
})

Finally, the following line in your package.json under scripts will give you an easy way to run the prerender from the command line: "prerender": "node build/build.prerender.js"

Run the prerender using npm run prerender. See files generated in ./dist. It’s like magic.

Testing for Google

To check all the pages, make sure you run a local server for dist and browse every pre-rendered page with JavaScript turned off (Chrome: Inspector > Settings > “Disable Javascript” under Debugger). Things don’t always work the way you want them to, especially when you’re dealing with headless browsers.

Once you’ve gotten things looking visually perfect, do inspect the generated HTML for excess code that you could trim away to further decrease your filesize.

Finally, once you push and run the prerender on server, use the Google Webmaster’s Console to see if everything is working well; since your ultimate reason for prerendering is probably proper SEO anyway. Click “Request Indexing” under “Fetch as Google” to then get your app to show up on Google.

Further Reading

Discuss on Twitter

A personal injury claim is a legal case you can open if you’ve been hurt in an accident and it was someone else’s fault. According to a personal injury attorney, an injury claim is based on medical evidence. Sometimes a doctor will have to take the stand, and sometimes the evidence will be in writing, as when you file a lawsuit.

If you’re injured in a car crash and you’re the only one who was injured, you’re entitled to the following:

The cost of the injury

The amount of compensation you received The amount of money you owe for the damages, including those you could have recovered if you’d been in the car, such as the cost of an ambulance ride.

Other legal rights you may have in an accident If you’ve suffered a medical emergency in an accident and it was caused by a car accident or other injury, you may be entitled to other legal rights, such as: A doctor’s opinion on how long you have to recover (often called a medical prognosis)

Your own doctor’s opinion on your condition and treatment

Financial compensation.

What happens if the car you drove is the same one in the accident?

In the case of a collision in which the same car was involved, your lawyer can take one of the following actions: File a lawsuit to recover the property damages.

Sue the other driver’s insurance company to recover the damages the other driver should have already paid (which will be listed as “other than vehicle damage” on your insurance card). If you were to get a reasonable settlement (or your attorney negotiates a good one for you), your lawyer could: Take this money and put it in your pocket so you don’t have to work to make up the difference.

Give this money to you to use. If you had a car wreck and were badly hurt, you may qualify for medical benefits.

In some states, your state’s DMV can verify your identity, including your Social Security number and date of birth. Make sure that you have received an approval letter before you can request medical benefits. If you have, then you’re eligible to receive medical benefits under federal law. You can use the following form (or a different form that your insurance company approves). This form is in addition to all of the other documents listed under the “How to Use the Request For Payment Form” section of this article. There are several types of payments that you can request, and each one is explained further below.

Discuss on Twitter

Bye Ghost, hello again WordPress!

Ghost… I tried. Trust me, I really did. I stuck with you for over 3 years, developed a custom theme on you, hacked around any shortcomings you had. But yesterday, I had to give up. Trying to upgrade from 0.11.x to 1.x had to be one of the most annoying experiences I’ve had recently and enough is enough. It’s not me, it’s you.

So I login to /ghost to pen a new post and I’m greeted with 2 bars at the top asking me to upgrade. I agree and check out that Ghost is finally version 1, woohoo! Then I check out the migration guide

Prepare your theme in advance

Backup your content using the JSON exporter

Create a brand new Ghost v1.0.0 site following our install guide

Use Ghost v1.0.0 Importer to import existing content

Copy your images to your new site.

Upload your theme – your theme will not be uploaded from the importer

Alright then — I start by preparing my custom theme. Upload it to gscan and do a few find and replaces and I’m good. The backup is pretty quick, too. I then get to the part where everything got stuck — installing Ghost CLI.

Ghost CLI is opinionated and broken

Ghost CLI seems to be focused more to make Ghost Pro easier to maintain for the team, simply because of how opinionated it is. Support only for Ubuntu + Nginx? Node v6 (I’ve read the reasons and it seems like laziness, but I understand that they’re a business and need to be pragmatic with priorities)? I hit n install lts to downgrade to Node 6 from v8.4.0 and try to npm i -g ghost-cli.

All’s good. I do ghost install in my ghost directory and everything breaks. I’m greeted with 3 screens of error logging and so I browse around a couple of Github issues to make sense of it. I double, then triple, check MySQL and my MySQL credentials. I create a new user and new database to check if it helps. Nothing. After spending an hour here and growing tired of the fact that Ghost, in fact, didn’t make my writing any easier or faster; I give up on upgrading, go write and publish my post, and figure out how to move to WordPress.

We meet again, WordPress

WordPress might be old, clunky, and PHP (heh) — but it works, has basically the largest ecosystem with amazing plugins, and it makes it easier to work with pages and posts. I use the _s base theme, add a way to work with browser-sync and Stylus files, and whip up the pyaar theme (with lots of code copied from the last design). Turns out that WP and Ghost have pretty similar template hierarchies, just add a class with “entry” changed to “post” wherever you see it (e.g., add .post-content to .entry-content) and you’ll get the theme more or less working.

Used Ghost to WP XML converter to transfer all content from posts, moved /content/images to /wp-content/uploads and added an .htaccess 301 Redirect.

I’ve yet to test the theme properly (let me know if you see anything broken) and have a lot more content planned that I need to integrate before I can make Symmetrycode what I envision it to be. I’m slightly sad that I lost Ghost, but I guess it made me appreciate WP’s stability a lot more. And it’s good to be back.

Discuss on Twitter

Instant, real-time, RSS to email

I’ve been looking for a free option for instant RSS to email subscriptions, and while Zapier comes close; the free tier does not support the volume or speed that I require.

That’s why I mocked up Sangh. Using Sangh with Gmail’s Filters allows you to have a powerful and well-regulated inbox with a real-time subscription to whatever feeds you want to follow.

1. Formatting RSS

The rss-parser looks to be a solid bet in terms of simplicity.

I grab the 3 latest entries from the feed and pass them to the parsing function which returns content in an object that can be consumed by nodemailer that we’ll set up later. The formatEntry function is where you’ll make changes and perform string interpolation to fit your content.

const formatEntry = entry => ({  
  to: TO_EMAIL,
  subject: `${ entry.title }`,
  html: `${ entry.content }`
})

parser.parseURL(RSS_URL, (err, parsed) => {  
  let entries = parsed.feed.entries.splice(0, 3) // only latest 3

  for (entry of entries) {
    sendEmail(formatEntry(entry))
  }
})

2. Configuring sendmail

I’m hosting things on a $5 DO droplet which I’ve configured with Postfix and can use sendmail on, which makes it really easy to use Nodemailer‘s sendmail transport.

const nodemailer = require('nodemailer')

const transporter = nodemailer.createTransport({  
  sendmail: true,
  newline: 'windows',
  path: '/usr/sbin/sendmail'
})

3. Preventing repeats

Now here’s the challenge — we need to ensure that no repeat emails are sent to us in case the RSS feed doesn’t actually update. I solved this with a simple sqlite database that records RSS item’s ID and doesn’t send out the email if it’s already sent.

I use Sequelize + sqlite here. An ORM just makes simple read/write operations easier, and sqlite‘s flat file datbase is perfect for this approach.

Initialize the database like so:

const Sequelize = require('sequelize')

const sequelize = new Sequelize({  
  host: 'localhost',
  dialect: 'sqlite',
  storage: './posts.sqlite'
})

const Post = sequelize.define('post', {  
  id: {
    type: Sequelize.STRING,
    notNull: true,
    primaryKey: true
  }
}, {
  timestamps: false
})

Post.sync()  

We then need to modify our ‘parser’ function to check if post already exists in the DB or not.

parser.parseURL(RSS_URL, async (err, parsed) => {  
  let entries = parsed.feed.entries.splice(0, 3)

  for (entry of entries) {
    await Post.findOrCreate({
      where: { id: entry.id }
    }).spread((_, created) => {
      if (created) {
        // if a new entry had to be created, send an email
        sendEmail(formatEntry(entry))
      }
    })
  }
})

Note my use of async and await here. This is for performance reasons, since it’s better for sqlite to run synchronous create operations rather than async ones, it often breaks on async operations. awaiting the findOrCreate promise makes the loop run synchronously, which is exactly what we want.

4. Run it every minute

I use cron to run the node script every minute to check for updates. Check your node install location by running which node (mine is /usr/local/bin/node) and run crontab -e to open the crontab editor.

Add the line * * * * * /usr/local/bin/node /path/to/your/script/index.js to check and email updates every minute. And you’re done!

Wrapping up

A quick, small project highlighted the importance of using various tools in modular ways to come up with something great. sqlite is perfect for such applications, and ‘offloading’ the actual checks to cron is much better and optimized than running a node script via forever/pm2 etc. Get the final version of Sangh at Github.

Discuss on Twitter

How to do X using Sequelize

I don’t know about you guys, but I always have an annoying time trying to figure out how to make Sequelize work — the documentation seems to have a dearth of examples. Here’s a few examples for “common” functionality.

Get plain objects instead of Sequelize Instances after bulkCreate

models.Item.bulkCreate(values)  
.then(results => results.map(result => result.get({ plain: true }))) // Convert to plain
.then(items => console.log(items)) // Plain object array [{...}, {...}, ...]

Pretty easy, use .get({ plain: true }) on any Sequelize instance to convert it to a plain JavaScript object.

Sequelize bulkCreate but ignore any duplicates

models.Item.bulkCreate(values, {  
    ignoreDuplicates: true 
})

Just use the ignoreDuplicates option and pass it as the second argument.

Deleting a row

Delete is called destroy, use it as so:

models.Items.destroy({  
    where: {
        id: 42
    }
})

Discuss on Twitter

Why is Gift Card Rebel everywhere on YouTube?

Recently I’ve been noticing a bunch of spam comments all over YouTube — with different text and links, but all leading to one single site: giftcardrebel.co. Why, how, or for what purpose — I have no idea.

The site in question looks like:

Clicking through finally gets you to a page where you need to fill a survey to continue. Boo. I assume the spammer earns a fixed amount per completed survey (cost per action.)

The spammer is very thorough, they have a few different domains leading to the exact same template.

The spam

This is a comment I saw on a very recent DJ Khaled’s Behind The Scenes

The video in question:

This link seems to be masked via the Google Cloud Platform, possibly to prevent the account from being shadow-banned. Clicked on the link redirects to the Gift Card Rebel site that I’ve been seeing everywhere (the link in plaintext https://storage.cloud.google.com/49282391/index.html).

Here’s another comment on one of Stephen Colberts’ videos, #3 on Trending at the time of this comment.

A few days earlier, I noticed links to Google Drive and pointed it out to a friend. However, the comment is now deleted.

Another type of comments say “Guys Look this up on youtube ==> markolife10x incredible” (the screenshot is from Convenience Store Cooking Challenge, #28 on Trending at the time of posting)

Searching for the exact term, curiously, gives us a single result:

Which basically is:

We’re getting this video and this video only because the description of the video contains “#markolife10x” (and of course, the term is unique enough that it’s not used anywhere else.)

Summing up

  • These are new accounts with real sounding names, usually without profile pictures.
  • Each comment gets around 200-400 likes and is ranked in top 3 under the video.
  • The spam comment is deleted after a few hours, approximately 24-48 hours of posting (or once the comment loses it’s position in the page).
  • A comment is posted very soon after a video is uploaded, around 30-60 minutes on average. It seems like the spammer is keeping an eye on some the regularly (semi?) viral channels.
  • The spammer seems to be trying new tricks regularly. Earlier it started with t.co links, then it was google drive links, and currently it is google cloud storage links along with “search for x on YouTube.”

In short — the spammer seems to have a comprehensive and automated method and a hoard of different Google accounts that they use and cycle through regularly.

My curiosities

I’m wondering:

  • Why is the spammer doing this?
  • What is their daily investment like?
  • How many people are required to pull this off?

Any discussion or answers will be appreciated — let’s discuss it on HackerNews. I’ll try updating this post as I see new spam/methods.

Discuss on Twitter

Super easy image uploads with Multer and Express

Multer makes file uploads via <form enctype="multipart/form-data"></form> really simple. If you’re looking to host user images on your filesystem and are wondering about the best practices involved, here’s what works for me.

1. The form

For the purposes of the article, I’m going to have a really simple form that submits a POST request to our endpoint and has a single <input type="file"> field.

form(method="post" action="/img" enctype="multipart/form-data")  
  input(type="file" name="photo")

2. The endpoint

Since I want to show this works with the default Express boilerplate, we’re going to put our routing in the default routes/index.js file.

First, we need to include multer.

const multer = require('multer')  
const upload = multer({  
  dest: './public/images/users',
  limits: {
    fileSize: 10000000
  }
})

I recommend uploading the images to a subfolder of ./public/images, in this case users, because this is the default directory where you’ll be storing all your images anyway. Note that the path described in dest is relative to the project directory (i.e. where your app.js is stored). I’ve also set a limit of 10mb but that’s optional.

I’ve got a simple set up going on with passport for user logins and sequelize as my ORM. Using either, however, is optional — you can handle image submissions without any logins at all and can use mongoose or some other DB system for storing the data.

Using upload.single('<name of input>') middleware adds req.file to the req object which contains metadata about the file.

router.post('/img',  
  upload.single('photo'), 
  (req, res) => {

  // Return user if they're not logged in
  if (!req.isAuthenticated()) {
    return res.redirect('/')
  }

  // Just a safety check
  if (!req.file) {
    return res.redirect('/')
  }

  // Your filename is stored in `req.file.filename`, which then goes
  // to your database

  const newUserData = {
    photo: req.file.filename,
  }
})

What does this code do?

You should now see files with 16 letter hex filenames being stored to your public/images/users directory. Multer does not add any file extensions as a safety precaution, and we now need to write some simple code to send the files with the appropriate mime-type so they’re served appropriately.

3. Serving Files

We’re going to use read-chunk and image-type modules to check the actual format of the file and serve it with the correct mime type. Install them with npm --save read-chunk image-type and include them in your routes/index.js as so. We also need path and fs default modules.

const readChunk = require('read-chunk')  
const imageType = require('image-type')  
const path = require('path')  
const fs = require('fs')  

Now here’s the clever part — we’re going to detect what file type the image is by reading their ‘magic bytes’ and allow only png, jpg, or jpeg formats.

We’re using Express’ route parameters so we get the filename dynamically. Then, we store the UPLOAD_PATH and FILE_PATH for the image in appropriate constants.

Using the read-chunk and image-type modules, we read the first 12 bytes of the file and see what file format do they correspond to. We then check for the file formats we’re allowing and serve a default image (that is actually a 1×1 transparent png that I’ve put in the folder) that is sent over if the file is anything fishy.

Otherwise, we set the appropriate mime type and pipe a stream of file data through res via the fs.createReadStream() method.

router.get('/images/users/:img', (req, res) => {  
  const UPLOAD_PATH = './public/images/users/'
  const FILE_PATH = path.join(UPLOAD_PATH, req.params.img)

  let imageData = imageType(readChunk.sync(FILE_PATH, 0, 12))

  if (!(imageData.ext == 'png' 
    || imageData.ext == 'jpg' 
    || imageData.ext == 'jpeg')) fs.createReadStream(path.join(UPLOAD_PATH, 'default.png')).pipe(res)

  res.set('Content-Type', imageData.mime)
  fs.createReadStream(FILE_PATH).pipe(res)
})

Wrapping up

Express makes image uploads slightly complicated, but once you understand the philosophy behind their approach you can get it going really fast and easily. Multer is a very extensible library, even having modules for memory and Amazon S3 storage, among others. The router.get approach makes our application extremely robust and secure against any attacks that are usually otherwise missed.

Discuss on Twitter

Setting Up a Local Shopify Theme Development Environment in Seconds

A quick way to get a local shopify dev environment going:

Installation and Setting Up

The first step is to install ThemeKit.

Then, from your Shopify admin, Apps > ‘View Private Apps’ > Generate API Credentials with “Theme Templates and Theme Assets” to “Read and write” (doesn’t matter what the rest are) > Save > Copy the ‘password’ and store it.

The Environment

If you want a new theme, then:

theme bootstrap --password=[password] --store=[yourstore.myshopify.com]  

If you want to use an existing theme, get the theme ID from the shopify admin. Go to Online Store > Themes and select “Edit HTML/CSS” on the theme you want a local environment of. Copy the digits at the end of the URL — this is your theme ID.

Then, configure and download with:

theme configure --password=[password] --store=[youstore.myshopify.com] --themeid=[your-theme-id]  
theme download  

Running

Use theme watch to run with a watcher on all the theme files and live-upload to the site.

Discuss on Twitter

CBSE 2016 Board Result All India Data Analysis

I recently presented you with a frequency distribution of CBSE board marks for DPS Vasant Kunj, and now I’m back with an analysis of 4052 CBSE schools across India, with results of over 476898 students.

Here are the interactive subject-wise charts plotted between number of students and number of marks scored.

I found many interesting trends, hope you enjoy sifting through the results as well. I might detail how exactly I obtained the data in another post, let me know if you’re looking forward to it. In the meanwhile, if NIC requires any sort of real security on their websites, tell them to hire me — my contact details are on the right.

Special thanks to Aayush Bhasin, Aditya Garg, Tanay Rajoria, Divyansh Sharma, Bhuvesh Mehendiratta.



Catch me on Facebook

Discuss on Twitter