Recursion in SQL Explained Visually

Lets think about queries as a function. In a sense that a function takes an input and produces an output. Queries operates on relations or…

https://medium.com/swlh/recursion-in-sql-explained-graphically-679f6a0f143b

@DevMisc
#sql #misc

A Future for SQL on the Web

"I discovered something absurd recently and I’m very excited to tell you about it."

https://jlongster.com/future-sql-web

@DevMisc
#sql #web #extra

SQLite vs MySQL vs PostgreSQL — A Detailed Guide

...in this story I would be going through one of the major confusion which newbies have regarding various flavours of SQL...

https://medium.com/codex/sqlite-vs-mysql-vs-postgresql-a-detailed-guide

@DevMisc
#database #postgres #sql

SQL Join vs Subquery: The Game Changer

When building an SQL query that involves multiple tables, there is always a constant debate about joining the tables or using subqueries.

https://daily.dev/blog/sql-join-vs-subquery-the-game-changer

@DevMisc
#sql #learn #misc

What ORMs have taught me: just learn SQL

I’ve come to the conclusion that, for me, ORMs are more detriment than benefit.

https://wozniak.ca/blog/2014/08/03/1/index.html

@DevMisc
#orm #sql #meta

immudb

immudb is a database with built-in cryptographic proof and verification. It can operate both as a key-value store, and/or as relational database (SQL).

https://github.com/codenotary/immudb

@DevMisc
#database #sql #misc

Soft Deletion Probably Isn't Worth It

https://brandur.org/soft-deletion

@DevMisc
#sql #misc

"We ran out of columns" - The best, worst codebase

- The database was the central component of the system, with a table called "Merchants" that had over 500 columns due to running out of columns in the original "Merchants" table.
- The "SequenceKey" table was a single-column, single-row table used to generate IDs, demonstrating a creative but unconventional solution.
- The system had a manually maintained "calendar" table to track login access, which was a fragile and outdated approach.
- The employee data was reloaded from a CSV file every morning, with an email-based process to replicate the data to headquarters.
- There was a normalized copy of the database, but it required 7 joins to go from the "Merchants" table to a phone number, showing the complexity.
- The codebase was a mix of VB and C#, with a proliferation of JavaScript frameworks and custom modifications.
- The "shipping manager" application was built in a weekend by a single developer named Gilfoyle, who was known for not checking in his code.
- The author discovered a bug related to the shipping queue that was caused by a SOAP service client doing all the side effects instead of the service itself.
- The "Merchants Search" page was optimized by a senior developer named Justin, who was able to make significant improvements by decoupling the page into separate endpoints.
- The codebase, despite its flaws, allowed for a sense of freedom and creativity, with developers carving out their own "little worlds of sanity" within the larger monolithic application.

https://jimmyhmiller.github.io/ugliest-beautiful-codebase

@DevMisc
#sql #fun #badcode

Handling Concurrency Without Locks

- Concurrency issues can be difficult to recognize and often get overlooked, leading to hard-to-debug bugs.
- It's tempting to dismiss concurrency issues due to perceived low likelihood, but they can still crop up unexpectedly under high load.
- Locking is a common approach to handling concurrency, but locks can be overused and lead to performance issues.
- The database is the lowest common denominator for coordinating locks across multiple processes and servers.
- The "ask for forgiveness" (EAFP) approach, where you try an operation and handle exceptions, is often more Pythonic than checking conditions in advance.
- In PostgreSQL, when an exception occurs within a transaction, it can block further commands until the transaction ends, requiring special handling.
- Using SELECT FOR UPDATE can lock rows to prevent race conditions, but this can also cause performance issues with high concurrency.
- Incrementing counters directly in the database, using an F expression, can avoid race conditions without the need for explicit locking.
- Combining database-level updates with RETURNING to immediately fetch the updated object can optimize the process further.
- The key is to keep concurrency issues in mind, avoid dismissing them due to perceived low likelihood, and use the most appropriate concurrency control mechanisms for the specific situation.

https://hakibenita.com/django-concurrency

@DevMisc
#sql #reliability #learn #misc

Understanding and Preventing Race Conditions in Web Applications

- Race conditions can lead to data corruption, crashes, and security vulnerabilities if not properly addressed.
- Using a simple increment operation to update a shared counter is vulnerable to race conditions and can result in incorrect counts.
- Locking the database row using "SELECT FOR UPDATE" is not sufficient to prevent race conditions, as it does not guarantee atomicity.
- Transactions are essential to ensure atomicity and prevent race conditions, but simply using a transaction is not enough - the specific SQL query used must also be atomic.
- Updating the counter using an atomic SQL query (e.g. "UPDATE table SET views = views + 1 WHERE id = $1") is a more scalable and efficient solution compared to locking rows.
- When dealing with game economies and player balances, it's crucial to enforce constraints at the database level (e.g. CHECK constraints) to prevent negative balances.
- Transactions do not inherently prevent all race conditions - the specific implementation and isolation level used is important.
- Avoid using "LOCK TABLE" as it can lead to performance issues and scalability problems.
- Testing for race conditions by simulating concurrent requests is essential to identify and address such issues.
- There is no single silver bullet solution - each approach has trade-offs that must be carefully considered based on the specific requirements of the application.

https://gavide.hashnode.dev/prevent-race-conditions-in-your-api

@DevMisc [#Original ❤️]
#security #sql #backend #learn

@DevMisc
#meme #sql #extra