SQLite Database Schema and Connection Management

The Pokédex Web App uses SQLite via Python’s built-in sqlite3 module — no external database server is required. The database file (pokedex.db) is created in the project root the first time the application starts. All four tables are created by init_db() inside models/database.py, which is called automatically inside an application context at startup in application.py.

Because SQLite stores the entire database in a single file, backing up the database is as simple as copying pokedex.db. For production deployments, consider adding this file to a regular backup schedule.

Database File Location

Setting	Value
Variable	`DB_PATH` in `models/database.py`
Resolved path	`Proyecto-UPC/pokedex.db`
Expression	`os.path.join(os.path.dirname(__file__), '..', 'pokedex.db')`

The path is computed relative to the models/ directory, placing the database file one level up in the project root. To move the database, update the DB_PATH variable — see the Configuration Reference.

Connection Utility

def get_connection():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn

get_connection() is the single point of entry for all database access across the application. Setting row_factory = sqlite3.Row means every row returned from a query can be accessed by column name (e.g. row["username"]) in addition to positional index — this is used throughout the model layer.

Every function in the model layer opens a connection, performs its query, and then explicitly calls conn.close(). There is no connection pooling; connections are short-lived and closed after each operation.

Initialization

init_db() uses executescript() to run a single SQL batch that creates all four tables with CREATE TABLE IF NOT EXISTS, making it safe to call on every startup without destroying existing data.

# application.py
with application.app_context():
    init_db()

This block runs before any request is served, guaranteeing the schema exists from the very first request.

Table Schemas

`usuarios`

Stores registered user accounts.

CREATE TABLE IF NOT EXISTS usuarios(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL
);

Column	Type	Notes
`id`	`INTEGER`	Primary key, auto-incremented.
`username`	`TEXT`	Must be unique across all users.
`password`	`TEXT`	A Werkzeug `generate_password_hash` hash — never plain text.

The password column stores a Werkzeug hash, not the raw password. Never store or log the plain-text value. Authentication is performed via check_password_hash in models/usuario.py.

`equipos`

Stores each user’s Pokémon team. Each row represents one Pokémon slot for one user.

CREATE TABLE IF NOT EXISTS equipos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    usuario_id INTEGER NOT NULL,
    pokemon_id INTEGER NOT NULL,
    pokemon_nombre TEXT NOT NULL,
    pokemon_imagen TEXT,
    pokemon_tipos TEXT,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id)
);

Column	Type	Notes
`id`	`INTEGER`	Primary key, auto-incremented.
`usuario_id`	`INTEGER`	Foreign key → `usuarios.id`.
`pokemon_id`	`INTEGER`	National Pokédex number.
`pokemon_nombre`	`TEXT`	Pokémon name as returned by PokéAPI.
`pokemon_imagen`	`TEXT`	Full URL to the Pokémon’s official sprite.
`pokemon_tipos`	`TEXT`	JSON-encoded list of type strings, e.g. `["fire", "flying"]`.

A single user may have the same Pokémon added more than once (there is no UNIQUE constraint on (usuario_id, pokemon_id)), but the team size is capped at 6 by Equipo.MAX_POKEMONES in application logic rather than at the database level.

The pokemon_tipos column must be decoded in templates using the parse_tipos filter.

`cache_pokemon`

Stores all Pokémon data fetched from the PokéAPI. Acts as a local mirror so the app does not re-fetch data on every request.

CREATE TABLE IF NOT EXISTS cache_pokemon (
    id INTEGER PRIMARY KEY,
    nombre TEXT NOT NULL,
    tipos TEXT,
    altura INTEGER,
    peso INTEGER,
    imagen TEXT,
    stats TEXT
);

Column	Type	Notes
`id`	`INTEGER`	National Pokédex number (not auto-incremented — set from API data).
`nombre`	`TEXT`	Pokémon name as returned by PokéAPI.
`tipos`	`TEXT`	JSON-encoded list of type strings.
`altura`	`INTEGER`	Height in decimetres (as returned by PokéAPI).
`peso`	`INTEGER`	Weight in hectograms (as returned by PokéAPI).
`imagen`	`TEXT`	Full URL to the Pokémon’s official sprite.
`stats`	`TEXT`	JSON-encoded object of base stats (hp, attack, defense, etc.).

Inserts into cache_pokemon use INSERT OR IGNORE so that re-running the cache load against a partially-populated table is safe — existing rows are never overwritten. To force a full refresh, delete all rows from this table and restart the application.

The cache is checked at startup via PokeAPI.validacion(). If the table contains data the app reads from it; otherwise it fetches all 1 025 Pokémon from https://pokeapi.co/api/v2 and populates the cache.

`pokedex_usuario`

Tracks which Pokémon each user has viewed on the detail page. Records the first view only.

CREATE TABLE IF NOT EXISTS pokedex_usuario (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    usuario_id INTEGER NOT NULL,
    pokemon_id INTEGER NOT NULL,
    pokemon_nombre TEXT NOT NULL,
    fecha_visto TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id),
    UNIQUE(usuario_id, pokemon_id)
);

Column	Type	Notes
`id`	`INTEGER`	Primary key, auto-incremented.
`usuario_id`	`INTEGER`	Foreign key → `usuarios.id`.
`pokemon_id`	`INTEGER`	National Pokédex number.
`pokemon_nombre`	`TEXT`	Pokémon name at the time of first view.
`fecha_visto`	`TIMESTAMP`	Set automatically to the current UTC time on insert.

The UNIQUE(usuario_id, pokemon_id) constraint ensures each Pokémon appears at most once per user in this table, regardless of how many times the detail page is visited. PokedexUsuario.registrar_visto() uses INSERT OR IGNORE to silently skip duplicate views.

This table powers the /mi-pokedex route, which displays the user’s personal “seen” log in chronological order by fecha_visto.

Entity Relationship Diagram

usuarios
  │
  ├──< equipos          (usuario_id → usuarios.id)
  ├──< pokedex_usuario  (usuario_id → usuarios.id)
  │
cache_pokemon           (standalone — no FK relationships)

cache_pokemon is independent of the user tables; it functions purely as an application-level cache for PokéAPI responses.

Architecture

Routes & Configuration

SQLite Database Schema and Connection Management

Database File Location

Connection Utility

Initialization

Table Schemas

`usuarios`

`equipos`

`cache_pokemon`

`pokedex_usuario`

Entity Relationship Diagram

Build docs developers (and LLMs) love

Architecture

Routes & Configuration

Documentation Index

​Database File Location

​Connection Utility

​Initialization

​Table Schemas

​usuarios

​equipos

​cache_pokemon

​pokedex_usuario

​Entity Relationship Diagram

Build docs developers (and LLMs) love

Database File Location

Connection Utility

Initialization

Table Schemas

`usuarios`

`equipos`

`cache_pokemon`

`pokedex_usuario`

Entity Relationship Diagram