and how to batch to optimize DELETEs

The perfect prisma alternative?

A (somewhat opinionated) list of SQL tips and tricks that I've picked up over the years in my job as a data analyst.

Okay

A query builder. It can be coupled to Prisma schema for better performance.

As much as I dig into it, I find raw SQL better (or with a SQL builder).

Le NoSQL est également plus adaptée pour le développement agile car les données ne sont pas structurées pendant les itérations.

Le SQL a réussi à traverser les âges car: il est simple, il est rapide et il est stable.

Les avantages de SQLite:

• facilité d'administration: tout le contenu peut être visualisé par lecture d'un fichier.
• facilité de déploiement: il est possible de pousser les BDD en production et de les migrer
• simplicité: sqlite est intégré avec tous les langages de programmation
• capacité: une BDD peut contenir plus d'un million de TB

Ses désavantages:

• requêtes concurrentes:
• les types de données limitées: NULL, INTEGER, REA L, TEXT, BLOB. Il n'y a pas de BOOL ni de dates.
• les données sont faiblement validées: "abcd" dans INTEGER o 20 charactères dans VARCHAR(5) passent.
• la sauvegarde des données est à faire soit même
• l'hébergement n'est pas possible avec tous les hébergeurs

Create webpages from SQL requests

13 examples or applications

Eḿbed SQL examples in a web page. It uses a browser-compatible compiled sqlite under the hood .

The deficiencies of SQL to use it as frontend databases:

1. Standard SQL doesn’t support nesting
2. SQL syntax is verbose and non-uniform.
3. SQL’s scalar expression language is weird and limited
4. SQL doesn't have good tools for metaprogramming and changing the shape of a query at runtime

Maybe useful someday

The use of SQL within streaming systems opens up a new chapter in the story of SQL within the data domain.

Machine learning needs data and a lot of processing. Thus the data needs to be efficiently stored and retrieved

“Every decade, another hyped-up database technology comes along that claims SQL is terrible, slow, or impractical,” Pavlo says. “Over time, the conventional wisdom comes back to realizing that [SQL] is a good idea, and everyone returns to it.”

Reference to NoSQL, document-based database, graphs with nodes and edges.

SQL table expressions are somewhat similar to functions in a regular programming language — they reduce the overall complexity.
You can write an unreadable sheet of code, or you can break the code into understandable individual functions and compose a program out of them.
You can build a tower of nested subqueries, or you can extract them into CTEs and reference from the main query.

There is a myth that “CTEs are slow”. It came from old versions of PostgreSQL (11 and earlier), which always materialized CTE — calculated the full result of a table expression and stored it until the end of the query.

Ok. There are some rules:

• CTE runs on every request
• CTE splits the query code into multiple chunks
• instead of subquery, always use CTE for clarity

Installer pv, puis passer de
zcat data-export.gz | mysql -u measuser -p -h db_host -P 3306 mydatabase
à
pv -pret --name ' Importing.. ' data-export.gz | zcat | mysql -u measuser -p -h db_host -P 3306 mydatabase