Thoughts on dba stuff

dba.md

Notes on TypeORM and Lessons Learned DBA

---

• TypeORM seems to have some opinionations that are pretty unfamiliar. While it may seem advantageous to use them, it's not advantageous to from a 3 year view. When you build a DB it should always be prepared for inevitable change. It's not unforeseeable that we at some point get integrated into a bigger company.
• PostgreSQL has some awesome new features(plugins, jsonb aggregators); however, it's always my best practice to right as I possibly can to ther SQL-92 spec(SQL - ANSI (American National Standards Institute) SQL (Standard|Reference|Specification) - SQL (92|99|2003|2011))
• SQL-92 is primarily what most base database connectors connect to. There are subtle differences to each kind of architecture for example:
  • mssql TOP:

    SELECT TOP 10 * FROM users;

  • Postgres LIMIT

    SELECT * FROM users LIMIT 10;*

  Notice that neither of these are in the SQL-92, and it's completely okay to use them; however, if we can avoid using DB specific syntax, it's something we would like to do!
• I prefer all my migrations to be written in raw SQL, this allows us to run them in any atmosphere completely unopinionated. My reasoning for this is we may move from a unmanaged to an managed server(picture like Serverless Aurora).
• I like to use text over varchar() whenever possible. I do this for a couple reasons, and I'd like to share a lesson learned. I use this primarily because while maintaining a 500k list of products once we sorted them with unique UPC codes for each product. The DB Architect of the catalog database had set their upc field to varchar(12); however, UPC's can often have 14 characters. When I would ingest the catalog from our partner, there were UPC's truncated at 12 characters giving me duped data. This issue costed my company 1 week of developer time to find and fix. The other reason is simply size, our data should move fast. We architect fast, efficient for the future. An ounce of prevention removes future headaches. A varchar(255) will always retain 255 characters. A text column with proper backend validation will only hold what's in it.
• I personally have always named tables verbally, it was how i taught, and I've made it a pretty standard practice, and that seems to be standard spec practice at most companies I have have implemented DB architecture for.
• Relational tables are easy to name given the following scheme
  1. There are two tables: user_flags, users, where user_flags let's us know if the user has enabled things like push notifications, email alerts, or possible the last device they logged into, so we know if they need to re-verify.
  2. We want to link the the two tables, so we create a simple relational table: user_flags_users
• Relational tables are cheap.
• Enums are not forward thinking, we want to use enums for something like named binariesf(e.g. on/off). If a variable has a chance to expand in the future, use a text table.
• Enums are great, but they can be abused. If an Enum must be expanded, it's best to create a new Enum in migration, and switch the table to it.
• Using a predecessor name in a table name is an anti-pattern(e.g. the table name is prefix_users) and creates a bad smell on your tables. prefix is implied hopefully by the name of the database.
• When it comes to Postgres, we want to look into the option uuidExtension. UUId's are a powerful way of generating unique identifiers and have quickly become the industry standard in postgres.
• It looks like TypeORM has a handy way to implement this for us, otherwise you would have to do something like this in your migrations: /migragtions/queries/up/345655-add-extension.sql

  CREATE extension "uuid-ossp"; 

  /migragtions/queries/up/345655-add-extension.sql

  DROP EXTENSION "uuid-ossp";

  With the config option in TypeORM already this is easy money!

example migration setup

• even if there is no residual effect of an up, a down should correspond
• Example File structure:
  • /migrations/ - where we hold all migration data, this is isolated from the rest of the project, so if we need to move it between projects we can easily do it. This is where our base test runners Separation of concerns
  • /migrations/up/ - Anything going up
  • /migrations/down/ - Anything going down
  • /migrations/maintenance/ - Sometimes a migration requires that data be be uploaded(e.g. a catalog is inserted). You want to put that here, this is always up for debate, and should be thought out by the team be fore running maintenance.
  • /migrations/seeds/ - Seed data that can be loaded for ease of development
• Your initial migration /migrations/queries/up/208457-initial.sql:

  CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY NOT NULL,
    user_id UUID REFERENCES users(id) NOT NULL UNIQUE,
    name text DEFAULT '',
    address text DEFAULT '',
    city text DEFAULT '',
    state text DEFAULT '',
    zip_code text DEFAULT '',
    created_at DATE DEFAULT NOW(),
    updated_at DATE
  );

• Your initial drop statement should always reflect your up file. You should be able to run your migrations up and down 20 times without any race conditions at a bare minimum. /migrations/queries/down/208457-initial.sql:

    DROP TABLE users;

• This will load the sql as UTF8, and thusly run the migration /migrations/208457-initial.js

  const fs = require('fs');
  import {MigrationInterface, QueryRunner} from "typeorm";
  
  const up = fs.readFileSync('./migrations/queries/up/208457-initial.sql', 'utf8');
  const down = fs.readFileSync('./migrations/queries/down/208457-initial.sql', 'utf8');
  
  export class some name implements MigrationInterface {
    
    async up(queryRunner: QueryRunner): Promise<any> {
        await queryRunner.query(up);
    }
  
    async down(queryRunner: QueryRunner): Promise<any> { 
        await queryRunner.query(down); // reverts things made in "up" method
    }
  }
  `