Coping With a Bad Sequel

SQL has been around longer than most of us have been writing code—and it shows. Originally built for tidy tables and straightforward joins, it’s now being stretched to handle JSON documents, arrays, and even composite types. Meanwhile, so-called “NoSQL” databases are sneaking SQL in through the back door. In this talk, we’ll take a slightly ranty tour through the history of SQL, the rise of document databases like MongoDB and CouchDB, and the awkward state of modern query languages. If you’ve ever tried updating a nested JSON field in MySQL and thought, “there has to be a better way”—this one’s for you.

Transcript

4. /ˈsiːkwəl/ /ˌɛsˌkjuˈɛl/
5. The History of SQL
8. CODASYL Conference on Data Systems Languages 1960
9. Navigational Database Model 1964
10. Parent 1st Child 2nd Child Nth Child
11. Edgar F. Codd A relational model of data for large shared data banks 1970
15. Skepticism • Impractical • Too Slow • Too Abstract • “Unfit for real-world workloads”
16. SEQUEL Structured English Query Language SELECT C.name, E.email FROM Contacts C JOIN Emails E ON C.id = E.contact_id WHERE C.id = 2; 1974
18. Structured Query Language 1977
19. Structured French Query Language What If? SÉLECTIONNER C.name, E.email DE Contacts C JOINDRE Emails E SUR C.id = E.contact_id AVEC C.id = 2;
20. Structured Emoji Query Language What If? ! C.name, E.email " Contacts C # Emails E $ C.id = E.contact_id ❓ C.id = 2;
21. SQL Takes Over Database Wars • IBM System R as Prototype • Oracle v2 released in 1979 • IBM DB2 released in 1983 1979-1983
22. Structured, not Standardised
23. SQL-86 • Data Definition Language • Data Manipulation Language • Queries • Constraints • Transactions • Vendor Extensions 1986
24. SQL-92 • New Types • Unions, Outer Joins • CASE • DROP 1992
25. Too Ambitious? SQL-92 Feature Oracle DB2 SQL Server PostgreSQL MySQL FULL OUTER JOIN Different Syntax ✅ 2005 ✅ 2018 INTERSECT Different Syntax ✅ Later ✅ 2019 Assertions ❌ ❌ ❌ ❌ ❌ Domains ❌ ❌ ❌ ✅ ❌ Deferrable Constraints Partial Limited ❌ ✅ ❌
26. SQL-86 SQL-89 SQL-92 SQL:1999 SQL:2003 SQL:2006 SQL:2008 SQL:2011 SQL:2016 SQL:2019 SQL:2023
27. Standardised? Server SQL-92 SQL:1999 SQL:2003 SQL:2008 SQL:2016 PostgreSQL ✅ ✅ ✅ ✅ ✅ Oracle ✅ ✅ ✅ ✅ ✅ SQL Server ✅ 㾒 ✅ ✅ 㾒 MySQL ✅ 㾒 㾒 㾒 㾒 SQLite ✅ 㾒 ❌ ❌ ❌
28. Good In Theory… Standards SELECT * FROM t ORDER BY id LIMIT 10 OFFSET 20; SELECT * FROM t ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
29. But In Databases… Standards SELECT 'a' || 'b'; SELECT 'a' + 'b'; SELECT CONCAT('a','b');
30. Standards Become… Standards INSERT INTO t (id,val) VALUES (1,'x') ON CONFLICT (id) DO UPDATE SET val = EXCLUDED.val; INSERT INTO t (id,val) VALUES (1,'x') ON DUPLICATE KEY UPDATE val = VALUES(val); MERGE INTO t USING (SELECT 1 id, 'x' val FROM dual) s ON (t.id = s.id) WHEN MATCHED THEN UPDATE SET t.val = s.val WHEN NOT MATCHED THEN INSERT (id,val) VALUES (s.id,s.val);
31. Useless Standards CREATE TABLE t (b BOOLEAN); CREATE TABLE t (b NUMBER(1)); CREATE TABLE t (b BIT);
32. Welcome To Hell DBAL public function getBooleanTypeDeclarationSQL(array $column): string; public function getIntegerTypeDeclarationSQL(array $column): string; public function getBigIntTypeDeclarationSQL(array $column): string; public function getSmallIntTypeDeclarationSQL(array $column): string; public function getAsciiStringTypeDeclarationSQL(array $column): string; public function getStringTypeDeclarationSQL(array $column): string; public function getBinaryTypeDeclarationSQL(array $column): string; public function getEnumDeclarationSQL(array $column): string;
33. One More Feature…
34. SQL/PSM CREATE PROCEDURE give_raise (IN emp_id INT, IN pct DECIMAL(5,2)) LANGUAGE SQL BEGIN DECLARE old_salary DECIMAL(10,2); DECLARE new_salary DECIMAL(10,2); SELECT salary INTO old_salary FROM employees WHERE id = emp_id; SET new_salary = old_salary * (1 + pct/100); UPDATE employees SET salary = new_salary WHERE id = emp_id; END; 1999
36. First Normal Form Is What? Arrays CREATE TABLE pixels ( id INT PRIMARY KEY, data INT[3][3] ); INSERT INTO pixels VALUES (1, ARRAY[[1,2,3],[4,5,6],[7,8,9]]); SELECT data[2][3] FROM pixels WHERE id = 1; 1999
37. The Enterprise Strikes Back XML CREATE TABLE books ( id INT PRIMARY KEY, metadata XML ); INSERT INTO books VALUES (1, '<book><title>Dune</title><author>Frank Herbert</author></book>'); SELECT XMLELEMENT( NAME "library", XMLAGG( XMLELEMENT(NAME "book", title) ) ) AS library_xml FROM books; 2003
38. The Enterprise Discovers Node.js JSON CREATE TABLE books ( id INT PRIMARY KEY, metadata JSON ); INSERT INTO books VALUES (1, '{"title": "Dune", "author": "Frank Herbert"}'); SELECT JSON_AGG( JSON_BUILD_OBJECT('title', title) ) AS library_json FROM books; 2016
39. Wonderful in SQL JSON UPDATE mytable SET foo = 'bar', bar = 'baz', comments = ( SELECT to_jsonb(sub) FROM ( SELECT elem FROM jsonb_array_elements( comments || '{"foo":"bar","date":1}'::jsonb ) elem ORDER BY (elem->>'date')::int DESC LIMIT 5 ) sub ) WHERE id = 1; 2016
40. The NoSQL Revolution
41. Strozzi NoSQL Early Sparks • Shell-based RDBMS • Does not use SQL • Limited functionality 1998
42. NoSQL = No SQL
43. NoSQL Flavours • Key-Value Stores: Redis, DynamoDB • Wide-Column Stores: Cassandra, HBase, ScyllaDB • Document Stores: MongoDB, CouchDB • Graph Databases: Neo4j
44. NoSQL != non-relational
45. MySQL Document Store var myColl = session.getSchema("test").getCollection("books"); myColl.add({ title: "Dune", author: "Frank Herbert" }).execute();
46. Please Don’t Do This Poor Person’s Document Database CREATE TABLE users ( id INT PRIMARY KEY, data JSON );
47. Putting the “SQL” in “NoSQL” Microsoft DocumentDB SELECT documentdb_api.insert_one( 'documentdb', 'patient', '{ "patient_id": "P001", ... }' );
48. NoSQL != “Not Only SQL”
49. What About The Future?
50. All Hail AI What’s Next CREATE TABLE items (id serial, embedding vector(1536)); SELECT * FROM items ORDER BY embedding <-> '[0.1, 0.2, 0.3, ...]' LIMIT 5;
51. Don’t use a “SQL database” but Choose based on features
52. Hide Your Database Logic but Don’t Abstract Your Database