Ir al contenido

SQLite vs. MySQL: Architectural Analysis

An in-depth comparative study between embedded database engines (SQLite) and client-server relational engines (MySQL) for design decisions in Portelcore.

0views

In previous sections, we understood that SQLite is a “serverless” embedded database. Now, we will perform a direct architectural comparison with one of the most widely used client-server relational database management systems globally: MySQL.

Understanding their differences and strengths is an essential design decision for selecting the appropriate engine according to the type of service.


2.1. The Fundamental Difference: Data Architecture

Sección titulada «2.1. La Diferencia Fundamental: Arquitectura de Datos»

The main difference does not lie in the standard SQL language, but in how the engine interacts and where the data is physically stored.

SQLite does not run as an independent system process or service. It is a library that your application (for example, our Python script or your local browser) invokes directly to read and write to a single physical file with the extension .db.

  • Workflow: Tu Aplicación (Python) ──> Motor SQLite (Biblioteca Local) ──> Archivo binario [portelcore.db]
  • Strengths: Zero configuration, exceptional performance in local searches, and absolute portability.
  • Limitations: Concurrent writes from multiple processes are not supported.

MySQL is a traditional RDBMS based on a standalone daemon or server. It runs persistently in the background within the server host and remains listening for network requests.

Your application acts as a "client" by connecting to this server via TCP/IP sockets using a dedicated network port (by default, 3306).

  • Workflow: Tu Aplicación (Cliente) ──> Red Segura (Portelcore LAN) ──> Servidor MySQL (Puerto 3306) ──> Archivo de Persistencia
  • Strengths: Native support for hundreds of simultaneous network connections and intelligent row-level locking.
  • Limitations: Requires provisioning, periodic administration (DBA), network policy control, and higher hardware consumption.

2.2. Comparative Table of Technical Capabilities

Sección titulada «2.2. Tabla Comparativa de Capacidades Técnicas»
Technical DimensionSQLiteMySQL
ProvisioningZero. Only requires defining the file path .db in the script.Complex. Requires starting the daemon, creating users, assigning roles, and configuring IPs.
Network SupportLocal. Does not interact directly via TCP/IP network protocols.Complete. Native remote access through multiple secure subnets.
Column TypingDynamic. Highly permissive. You can store text in a field declared as an integer.Strict. If the column is defined as INT, the engine guarantees integrity by rejecting other types.
Write ControlSingle-process. Locks the entire database during write operations.Multi-process. Allows concurrent writes by locking only the affected row.
ScalabilityVertical. Limited to the storage system size of the local node.Clustering and Replication. Supports load balancers, read replicas, and partitioning (sharding).
Role ManagementNon-existent. Access control relies entirely on operating system permissions.Excellent. Granular permissions at the table, column, and client source IP levels.

2.3. Comparison of SQL Dialects and Functions

Sección titulada «2.3. Comparativa de Dialectos y Funciones SQL»

Although both engines use the ANSI SQL standard, there are subtle but critical variations in their everyday syntax:

  • MySQL: Requires explicit definition:
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
  • SQLite: Is implicitly defined when declaring an integer as a primary key:
    id INTEGER PRIMARY KEY AUTOINCREMENT
  • In MySQL, you use interactive engine commands for global management, such as CREATE DATABASE portelcore_db; or SHOW TABLES;.
  • In SQLite, these global environment commands do not exist directly within SQL. The database is self-generated upon initializing the connection with the physical file, and tables are managed by interacting through special CLI tool commands (.tables, .databases).
  • MySQL provides full support for stored procedures (stored procedures), advanced (triggers), and native on-disk indexed views.
  • SQLite implements simplified (triggers) adapted to local environments and lacks stored procedures due to its nature of executing in the client process memory.

Before choosing a relational database engine for your data solution, validate these technical checklist criteria:

  • ✓ Do multiple clients write at the same time?: If the answer is yes (such as a portfolio intranet or a ticketing system), use a client-server (MySQL). If it is a single automated process or cache, use SQLite.
  • ✓ Does data cross the network?: If storage resides on a machine separate from the software backend, select MySQL.
  • ✓ Do you require immediate portability?: If you need to package the entire system state into a single downloadable file, use SQLite.
  • ✓ Granular table-level security?: If you need read roles limited to BI users and write roles restricted to the backend, use MySQL. SQLite has simpler Triggers and does not support Stored Procedures.