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

And that’s the characteristic problem with the normalized approach: In exchange for the simplicity of working exclusively with normalized data, you have to write queries that don’t scale.

With denormalization, there is so much to think about, so much edge cases that needs to be handled !

A reason to use sqlite for small projects.

SQLite is not client/server, however. The SQLite database runs in the same process address space as the application. Queries do not involve message round-trips, only a function call. The latency of a single SQL query is far less in SQLite. Hence, using a large number of queries with SQLite is not the problem.