Manage short urls on db level (postgres, sequelize)

test_sequelize_migration.js

// initial idea is taken here https://blog.andyet.com/2016/02/23/generating-shortids-in-postgres/
// this version is more general as we can pass the name of the column while setting up the trigger
// can be even more general if we pass the number of random bytes as the second argument (different length of short urls)
module.exports = {
  up: async (queryInterface, Sequelize) => [
    await queryInterface.sequelize.query('CREATE EXTENSION IF NOT EXISTS pgcrypto;'),
    await queryInterface.sequelize.query('CREATE EXTENSION IF NOT EXISTS hstore;'),
    await queryInterface.sequelize.query(`
-- Create a trigger function that takes no arguments.
-- Trigger functions automatically have OLD, NEW records
-- and TG_TABLE_NAME as well as others.
CREATE OR REPLACE FUNCTION unique_short_id()
RETURNS TRIGGER AS $$

 -- Declare the variables we'll be using.
DECLARE
  _key TEXT;
  _qry TEXT;
  _found TEXT;
  _col TEXT;
BEGIN

  _col := TG_ARGV[0];
  -- generate the first part of a query as a string with safely
  -- escaped table name, using || to concat the parts
  _qry := 'SELECT ' || _col || ' FROM ' || quote_ident(TG_TABLE_NAME) || ' WHERE ' || _col || ' = ';

  -- This loop will probably only run once per call until we've generated
  -- millions of ids.
  LOOP

    -- Generate our string bytes and re-encode as a base64 string.
    -- hex is not a good alternative as it uses more chars to encode
    _key := encode(gen_random_bytes(9), 'base64');

    -- Base64 encoding contains 2 URL unsafe characters by default.
    -- The URL-safe version has these replacements.
    _key := replace(_key, '/', '_'); -- url safe replacement
    _key := replace(_key, '+', '-'); -- url safe replacement

    -- Concat the generated key (safely quoted) with the generated query
    -- and run it.
    -- SELECT id FROM "test" WHERE id='blahblah' INTO found
    -- Now "found" will be the duplicated id or NULL.
    EXECUTE _qry || quote_literal(_key) INTO _found;

    -- Check to see if found is NULL.
    -- If we checked to see if found = NULL it would always be FALSE
    -- because (NULL = NULL) is always FALSE.
    IF _found IS NULL THEN

      -- If we didn't find a collision then leave the LOOP.
      EXIT;
    END IF;

    -- We haven't EXITed yet, so return to the top of the LOOP
    -- and try again.
  END LOOP;

  -- NEW and OLD are available in TRIGGER PROCEDURES.
  -- NEW is the mutated row that will actually be INSERTed.
  -- We're replacing id, regardless of what it was before
  -- with our key variable.
  -- This nice idea is take from https://stackoverflow.com/a/38722477/1274503
  NEW := NEW #= hstore(_col, _key); 

  -- The RECORD returned here is what will actually be INSERTed,
  -- or what the next trigger will get if there is one.
  RETURN NEW;
END;
$$ language 'plpgsql';
    `),
    await queryInterface.createTable('short_url', {
      short_url_id: {
        type: Sequelize.UUID,
        unique: true,
        allowNull: false,
        primaryKey: true,
        defaultValue: Sequelize.literal('gen_random_uuid()'),
      },
      // we allow NULL values here, so we can replace expired/completed links with NULLs and have less collisions
      url: {
        type: Sequelize.TEXT,
        unique: true,
      },
      status: {
        type: Sequelize.ENUM,
        values: [
          'SENT_TO_USER',
          'EXPIRED',
          'VISITED',
          'COMPLETED',
        ],
        allowNull: false,
      },
      meta: {
        type: Sequelize.JSONB,
      },
      visited_at: {
        type: Sequelize.DATE,
      },
      expire_at: {
        type: Sequelize.DATE,
      },
      created_at: {
        type: Sequelize.DATE,
        allowNull: false,
      },
      updated_at: {
        type: Sequelize.DATE,
        allowNull: false,
      },
      deleted_at: {
        type: Sequelize.DATE,
      },
    }),
    // here we pass 'url' as a param to unique_short_id() function
    await queryInterface.sequelize.query(`
      CREATE TRIGGER short_url_generate_url BEFORE INSERT ON short_url FOR EACH ROW EXECUTE PROCEDURE unique_short_id('url');
    `),
  ],
  down: async (queryInterface) => [
    await queryInterface.dropTable('short_url'),
    await queryInterface.sequelize.query(`
      DROP TRIGGER IF EXISTS short_url_generate_url ON short_url;
    `),
  ],
};