--- date: created: 2025-05-07 authors: [mrxsierra] categories: - Database Architecture tags: - SQL - PostgreSQL - MySQL - SQLite - Docker - CLI slug: beyond-the-schema-a-practical-guide-to-querying-and-interacting-with-sqlite-mysql-postgresql description: > A practical guide to querying, CLI administration, Docker container networking, and auto-increment sequence resets across SQLite, MySQL, and PostgreSQL. --- # Beyond the Schema: A Practical Guide to Querying and Interacting with SQLite, MySQL, & PostgreSQL Building on our analysis of cross-engine schema definitions, this guide focuses on daily database operation: query execution mechanics, CLI diagnostic commands, script piping, and Dockerized networking nuances across SQLite, MySQL, and PostgreSQL. This reference is grounded in practical scripts from the [**Examination Management System (EMS DB)**](https://github.com/mrxsierra/ems-db/) project repository. --- ## 1. CLI Shell Access & Connection Flags Each RDBMS provides a dedicated terminal client with specific formatting and debugging flags: === "PostgreSQL (`psql`)" ```bash # Direct local connection with input echo (-a) and error display (-b) psql -a -b -d ems -U postgres # Connect to a containerized instance from an application service psql -h db -U postgres -d ems ``` > **Tip:** Use `~/.pgpass` (`hostname:port:database:username:password`) with `chmod 600` for secure, passwordless authentication in local development. === "MySQL (`mysql` / `mysqlsh`)" ```bash # Tabular output (-t) with verbose execution (-v) mysql -t -v -u root -psecret ems # Modern multi-protocol MySQL Shell mysqlsh root@db:3306/ems --sql ``` === "SQLite (`sqlite3`)" ```bash # File-based connection with column table mode and command echo sqlite3 ems.db -table -echo ``` --- ## 2. Executing SQL Scripts from Files Running batch DDL migrations or query test benches from external `.sql` files: === "PostgreSQL" ```bash # From inside the psql prompt: \i ./queries.sql # Via shell stdin piping: psql -a -b -d ems -U postgres < ./queries.sql ``` === "MySQL" ```bash # From inside the mysql prompt: source ./queries.sql # Via shell stdin piping: mysql -tv -u root -psecret ems < ./queries.sql ``` > **Note:** Because `mysql-connector-python` lacks native support for the `DELIMITER` directive required by complex trigger blocks, executing schema migrations via the CLI client is the recommended production practice. === "SQLite" ```bash # From inside the sqlite3 prompt: .read ./queries.sql # Via shell stdin piping: sqlite3 ems.db -table -echo < ./queries.sql ``` --- ## 3. Resetting Auto-Increment Sequences When wiping test tables (`DELETE FROM students;`), resetting the primary key counter requires engine-specific operations: === "PostgreSQL" ```sql -- PostgreSQL manages primary keys via dedicated sequence objects ALTER SEQUENCE students_id_seq RESTART WITH 1; ``` === "MySQL" ```sql -- MySQL stores the counter as a table property ALTER TABLE students AUTO_INCREMENT = 1; ``` === "SQLite" ```sql -- SQLite tracks AUTOINCREMENT counters in the internal sqlite_sequence table DELETE FROM sqlite_sequence WHERE name = 'students'; ``` --- ## 4. Shell Diagnostic & Inspection Commands Inspecting catalog objects (tables, indexes, views) from within interactive database shells: === "PostgreSQL" ```text \dt -- List all tables in current schema \di -- List all indexes \dv -- List all views \d+ -- Inspect detailed table definition, triggers, and constraints ``` *Information Schema Alternative:* ```sql SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'; SELECT indexname, indexdef FROM pg_indexes WHERE schemaname = 'public'; ``` === "MySQL" ```sql SHOW TABLES; SHOW INDEX FROM students; SHOW FULL TABLES WHERE TABLE_TYPE = 'VIEW'; SHOW CREATE TABLE students; ``` *Information Schema Alternative:* ```sql SELECT table_name FROM information_schema.tables WHERE table_schema = 'ems'; SELECT index_name, column_name FROM information_schema.statistics WHERE table_schema = 'ems'; ``` === "SQLite" ```text .tables -- List all tables .schema students -- Show DDL for a specific table .fullschema -- Show entire database DDL ``` *Master Catalog Alternative:* ```sql SELECT name, sql FROM sqlite_master WHERE type = 'table'; SELECT name FROM sqlite_master WHERE type = 'index'; ``` --- ## 5. Dockerized Multi-Database Orchestration In reproducible testing environments, database services run inside isolated Docker networks. ```yaml # Sample Multi-RDBMS Docker Compose Architecture services: app: image: python:3.12-slim depends_on: - postgres-db - mysql-db volumes: - ./:/workspace postgres-db: image: postgres:17-alpine environment: POSTGRES_DB: ems POSTGRES_USER: postgres POSTGRES_PASSWORD: password mysql-db: image: mysql:8.4 environment: MYSQL_DATABASE: ems MYSQL_ROOT_PASSWORD: secret ``` ### CLI Container Exec Patterns === "PostgreSQL" ```bash # Direct container execution docker compose exec postgres-db psql -U postgres -d ems # Access from application container across internal DNS docker compose exec app psql -h postgres-db -U postgres -d ems ``` === "MySQL" ```bash # Direct container execution docker compose exec mysql-db mysql -u root -psecret ems # Access from application container across internal DNS docker compose exec app mysql -h mysql-db -u root -psecret ems ``` === "SQLite" ```bash # Access local shared volume file inside app container docker compose exec app sqlite3 /workspace/ems.db ``` --- ## Quick Reference Summary | Operation | PostgreSQL | MySQL | SQLite | |:---|:---|:---|:---| | **CLI Binary** | `psql` | `mysql` / `mysqlsh` | `sqlite3` | | **Run Script (Prompt)** | `\i queries.sql` | `source queries.sql` | `.read queries.sql` | | **Reset Sequence** | `ALTER SEQUENCE ... RESTART WITH 1;` | `ALTER TABLE ... AUTO_INCREMENT = 1;` | `DELETE FROM sqlite_sequence ...` | | **Inspect DDL** | `\d+ table_name` | `SHOW CREATE TABLE table_name;` | `.schema table_name` | | **Execution Plan** | `EXPLAIN ANALYZE SELECT ...;` | `EXPLAIN SELECT ...;` | `EXPLAIN QUERY PLAN SELECT ...;` | | **Docker Hostname** | DNS service name (`postgres-db`) | DNS service name (`mysql-db`) | Local file path / mount | --- ## Conclusion & Series Navigation Understanding both the schema syntax (**Part 1**) and the operational tooling (**Part 2**) ensures seamless database migrations and resilient CI/CD pipelines across different relational engines. --- ## Reference Documentation - [PostgreSQL 17 Client Documentation](https://www.postgresql.org/docs/17/reference-client.html) - [MySQL 8.4 Reference Manual](https://dev.mysql.com/doc/refman/8.4/en/) - [SQLite CLI Reference](https://sqlite.org/cli.html) - [Examination Management System DB (EMS DB)](https://github.com/mrxsierra/ems-db/)