Skip to content

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.

2-Part Engineering Series Part 2 of 2
Part 1: Navigating the Nuances: SQL Dialects (SQLite, MySQL, PostgreSQL)
Part 2: Querying, CLI Interaction, & Docker Nuances (Current)

This reference is grounded in practical scripts from the Examination Management System (EMS DB) project repository.

1. CLI Shell Access & Connection Flags

Each RDBMS provides a dedicated terminal client with specific formatting and debugging flags:

# 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.

# 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

# 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:

# From inside the psql prompt:
\i ./queries.sql

# Via shell stdin piping:
psql -a -b -d ems -U postgres < ./queries.sql

# 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.

# 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 manages primary keys via dedicated sequence objects
ALTER SEQUENCE students_id_seq RESTART WITH 1;

-- MySQL stores the counter as a table property
ALTER TABLE students AUTO_INCREMENT = 1;

-- 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:

\dt          -- List all tables in current schema
\di          -- List all indexes
\dv          -- List all views
\d+ <table>  -- Inspect detailed table definition, triggers, and constraints

Information Schema Alternative:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
SELECT indexname, indexdef FROM pg_indexes WHERE schemaname = 'public';

SHOW TABLES;
SHOW INDEX FROM students;
SHOW FULL TABLES WHERE TABLE_TYPE = 'VIEW';
SHOW CREATE TABLE students;

Information Schema Alternative:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'ems';
SELECT index_name, column_name FROM information_schema.statistics WHERE table_schema = 'ems';

.tables             -- List all tables
.schema students    -- Show DDL for a specific table
.fullschema         -- Show entire database DDL

Master Catalog Alternative:

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.

# 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

# 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

# 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

# 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.

Series Complete ← Review Part 1

Part 1: Navigating the Nuances: A Developer's Guide to SQL Dialects
Deep dive into schema definitions, trigger syntax, timestamp functions, and type systems across PostgreSQL, MySQL, and SQLite.

Reference Documentation