/sql-notes

SQL what I like about it, but mostly, the horror 😱

Some reflections about the SQL language and "ecosystem".

What I like 👍

  • Set logic
  • Relations
  • Joins
  • Input validation on write
  • Transactions
  • Support for exploratory queries
  • Indexes
  • Legacy, the good kind
  • "Works everywhere"

Dialects 🤔

Classics 🗿

  • SQLite (select)

    SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is the most used database engine in the world. SQLite is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day. More Information...

  • MySQL (select)
    The "M" in "LAMP".
  • Postgres (select)

    The World's Most Advanced Open Source Relational Database

Hosted 😙

  • Aurora
    👆 + AWS secret sauce.

    Amazon Aurora is a MySQL and PostgreSQL-compatible relational database built for the cloud that combines the performance and availability of traditional enterprise databases with the simplicity and cost-effectiveness of open source databases.

Scaled 🤯

  • Vitess

    A database clustering system for horizontal scaling of MySQL

    • PlanetScale
      Hosted Vitess.

      The MySQL-compatible serverless database platform.

  • Spanner (select)
    Google secret sauce + GPS clocks.

    Fully managed relational database with unlimited scale, strong consistency, and up to 99.999% availability.

  • Snowflake (select)
  • CockroachDB (select)

    A distributed SQL database designed for speed, scale, and survival. Trusted by thousands of innovators around the globe.

Weird 🤪

  • Cassandra

    Open Source NoSQL Database Manage massive amounts of data, fast, without losing sleep CQL instead of SQL. CQL does not execute joins or sub-queries and a select statement only apply to a single table.

  • Athena/S3 Select?

    Amazon S3 Select and S3 Glacier Select queries currently do not support subqueries or joins.

  • BigQuery (select)

    Serverless, highly scalable, and cost-effective multicloud data warehouse designed for business agility.

  • Fauna (read)
    Relational database without SQL support.

    Fauna is a flexible, developer-friendly, transactional database delivered as a secure and scalable cloud API with native GraphQL

What I dislike 👎

"Specification".

Specification of user interface, not an interoperable protocol.

  • Queries as strings (not data structure).
    • Which (every?) implementation changes.
    • List inputs/outputs.
  • Java and Go have standardized APIs which helps. But an adapter is always required.

Language

Queries as strings (not data structure). The format of the strings.

Read order

https://en.wikipedia.org/wiki/SQL_syntax#Queries

SELECT <columns>              5.
FROM <table>                  1.
WHERE <predicate on rows>     2.
GROUP BY <columns>            3.
HAVING <predicate on groups>  4.
ORDER BY <columns>            6.
OFFSET                        7.
FETCH FIRST                   8.
  • Two different <predicate> syntaxes.

Non similar queries

SELECT column1, column2
FROM example
WHERE column2 = 'N';
INSERT INTO example
  (column1, column2, column3)
VALUES
  ('test', 'N', NULL);
UPDATE example
  SET column1 = 'updated value'
WHERE column2 = 'N';
DELETE FROM example
WHERE column2 = 'N';

Name bindings

Only somewhat hacky name bindings (variables are separate).

SELECT
  (
    SELECT COUNT(*)
    FROM example
    WHERE column2 = ?
  ) AS match_count,
  (
    SELECT COUNT(*)
    FROM example
    WHERE column2 <> ?
  ) AS non_match_count
;
WITH
  bindings(col2_val) AS (VALUES (?))
SELECT
  (
    SELECT COUNT(*)
    FROM example
    WHERE column2 = bindings.col2_val
  ) AS match_count,
  (
    SELECT COUNT(*)
    FROM example
    WHERE column2 <> bindings.col2_val
  ) AS non_match_count
;

Multi network call transactions

  • Can't submit full query at once -> N+1 queries.
  • See name binding 👆
BEGIN TRANSACTION;

  INSERT INTO talks
    (title, description)
  VALUES
    ('Sql notes', 'Ramblings');

  UPDATE user
    SET most_recent_talk_id = '???'
  WHERE user_name = 'Emil';

COMMIT TRANSACTION;
await sqlClient.query(`begin transaction;`);

const createdTalk = await sqlClient.query(
  `
  INSERT INTO talks
    (title, description)
  VALUES
    ('Sql notes', 'Ramblings')
  RETURNING
    talks_id;
  `
);

const updatedUser = await sqlClient.query(
  `
  UPDATE user
    SET most_recent_talk_id = '?'
  WHERE user_name = 'Emil';
  `,
  [createdTalk]
);

await sqlClient.query(`commit transaction;`);

Misc

Also, "coolest SQL hack I learned this week?"

Final note

If you want to know more, I highly recommend reading/listening to https://dataintensive.net/.