[DB__SQL] #articles

db-case.md

• Herding elephants: Lessons learned from sharding Postgres at Notion

db-tools.md

• RxDB: A realtime Database for JavaScript Applications
• Data Mapper vs. Active Record
• Top 11 Node.js ORMs, Query Builders & Database Libraries in 2021
• https://typeorm.io/#/active-record-data-mapper/what-is-the-active-record-pattern
• https://www.reddit.com/r/node/comments/j8i3ku/sequelize_vs_typeorm_vs_objection_for_nodejs/

db.md

• Zero-downtime schema migrations in Postgres using views
• How Does a Database Work?

sql.md

• 6 SQL Queries Every Data Engineer Should Be Aware of
  • Running Totals

    SELECT id,month
     , Amount
     , SUM(Amount) OVER (ORDER BY id) as total_sum
    FROM bill

  • Common Table Expressions

    SELECT *
    FROM bill
    WHERE id in 
      (SELECT DISTINCT id 
       FROM id 
       WHERE country = "US"
       AND status = "Y"
      )

    ->

    WITH idtempp as (
      SELECT id as id
      FROM id 
      WHERE country = "US"
      AND status = "Y"
    )
    
    SELECT *
    FROM bill
    WHERE id in (SELECT id from idtempp)

  • Temporary Functions

    CREATE TEMPORARY FUNCTION get_gender(type varchar) AS (
       CASE WHEN type = "M" THEN "male"
            WHEN type = "F" THEN "female"
            ELSE "n/a"
       END
    )
    SELECT 
      name,
      get_gender(Type) as gender
    FROM bill