Linting MySQL

docker-compose.yml

# Docker Compose file Reference (https://docs.docker.com/compose/compose-file/)

version: '3.7'

# Define services
services:
  # App backend service
  index-digest:
    image: macbre/index-digest:latest
    # Configuration for building the docker image for the backend service
    #command: 
    entrypoint: index_digest mysql://root:password@db:3307/index_digest --sql-log=/tmp/user.log
    #restart: always
    depends_on: 
      - db # This service depends on mysql. Start that first.
    environment: # Pass environment variables to the service
      MYSQL_URL: "mysql://root:password@db:3307/index_digest"
      MYSQL_USERNAME: root
      MYSQL_PASSWORD: password
    volumes:
      - .:/tmp     
    networks: # Networks to join (Services on the same network can communicate with each other using their name)
      - backend

  # Database Service (Mysql)
  db:
    image: mysql:5.7
    ports:
      - "3307:3306"
    restart: always
    environment:
      MYSQL_DATABASE: index_digest
      MYSQL_USER: root
      MYSQL_PASSWORD: password
      MYSQL_ROOT_PASSWORD: password
    volumes:
      - db-data:/var/lib/mysql
    networks:
      - backend  
  
# Volumes
volumes:
  db-data:

# Networks to be created to facilitate communication between containers
networks:
  backend:

report.txt

index-digest_1  | ------------------------------------------------------------
index-digest_1  | Found 9 issue(s) to report for "index_digest" database
index-digest_1  | ------------------------------------------------------------
index-digest_1  | MySQL v5.7.29 at 807f70f74852
index-digest_1  | index-digest v1.2.1
index-digest_1  | ------------------------------------------------------------
index-digest_1  | empty_tables → table affected: user
index-digest_1  |
index-digest_1  | ✗ "user" table has no rows, is it really needed?
index-digest_1  |
index-digest_1  |   - schema: CREATE TABLE `user` (
index-digest_1  |       `id` bigint(10) NOT NULL AUTO_INCREMENT,
index-digest_1  |       `age` smallint(3) NOT NULL,
index-digest_1  |       `city` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
index-digest_1  |       `createdAt` datetime DEFAULT NULL,
index-digest_1  |       `updatedAt` datetime DEFAULT NULL,
index-digest_1  |       PRIMARY KEY (`id`)
index-digest_1  |     ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
index-digest_1  |
index-digest_1  | ------------------------------------------------------------
index-digest_1  | generic_primary_key → table affected: user
index-digest_1  |
index-digest_1  | ✗ "user" has a primary key called id, use a more meaningful name
index-digest_1  |
index-digest_1  |   - schema: CREATE TABLE `user` (
index-digest_1  |       `id` bigint(10) NOT NULL AUTO_INCREMENT,
index-digest_1  |       `age` smallint(3) NOT NULL,
index-digest_1  |       `city` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
index-digest_1  |       `createdAt` datetime DEFAULT NULL,
index-digest_1  |       `updatedAt` datetime DEFAULT NULL,
index-digest_1  |       PRIMARY KEY (`id`)
index-digest_1  |     ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
index-digest_1  |
index-digest_1  | ------------------------------------------------------------
index-digest_1  | not_used_columns → table affected: user
index-digest_1  |
index-digest_1  | ✗ "id" column was not used by provided queries
index-digest_1  |
index-digest_1  |   - column_name: id
index-digest_1  |   - column_type: bigint(10)
index-digest_1  |
index-digest_1  | ------------------------------------------------------------
index-digest_1  | not_used_columns → table affected: user
index-digest_1  |
index-digest_1  | ✗ "createdAt" column was not used by provided queries
index-digest_1  |
index-digest_1  |   - column_name: createdAt
index-digest_1  |   - column_type: datetime
index-digest_1  |
index-digest_1  | ------------------------------------------------------------
index-digest_1  | not_used_columns → table affected: user
index-digest_1  |
index-digest_1  | ✗ "updatedAt" column was not used by provided queries
index-digest_1  |
index-digest_1  |   - column_name: updatedAt
index-digest_1  |   - column_type: datetime
index-digest_1  |
index-digest_1  | ------------------------------------------------------------
index-digest_1  | queries_not_using_index → table affected: user
index-digest_1  |
index-digest_1  | ✗ "select * from `user` where city IN ('Bangalore', '..." query did not make use of any index
index-digest_1  |
index-digest_1  |   - query: select * from `user` where city IN ('Bangalore', 'Mumbai', 'Delhi') ORDER BY age DESC
index-digest_1  |   - explain_extra: Using where; Using filesort
index-digest_1  |   - explain_rows: 1
index-digest_1  |   - explain_filtered: 100.0
index-digest_1  |   - explain_possible_keys: None
index-digest_1  |
index-digest_1  | ------------------------------------------------------------
index-digest_1  | queries_using_filesort → table affected: user
index-digest_1  |
index-digest_1  | ✗ "select * from `user` where city IN ('Bangalore', '..." query used filesort
index-digest_1  |
index-digest_1  |   - query: select * from `user` where city IN ('Bangalore', 'Mumbai', 'Delhi') ORDER BY age DESC
index-digest_1  |   - explain_extra: Using where; Using filesort
index-digest_1  |   - explain_rows: 1
index-digest_1  |   - explain_filtered: 100.0
index-digest_1  |   - explain_key: None
index-digest_1  |
index-digest_1  | ------------------------------------------------------------
index-digest_1  | queries_using_full_table_scan → table affected: user
index-digest_1  |
index-digest_1  | ✗ "select * from `user` where city IN ('Bangalore', '..." query triggered full table scan
index-digest_1  |
index-digest_1  |   - query: select * from `user` where city IN ('Bangalore', 'Mumbai', 'Delhi') ORDER BY age DESC
index-digest_1  |   - explain_rows: 1
index-digest_1  |
index-digest_1  | ------------------------------------------------------------
index-digest_1  | select_star → table affected: user
index-digest_1  |
index-digest_1  | ✗ "select * from `user` where city IN ('Bangalore', '..." query uses SELECT *
index-digest_1  |
index-digest_1  |   - query: select * from `user` where city IN ('Bangalore', 'Mumbai', 'Delhi') ORDER BY age DESC
index-digest_1  |
index-digest_1  | ------------------------------------------------------------
index-digest_1  | Queries performed: 11

schema.sql

CREATE TABLE `user` (
	`id` bigint(10) NOT NULL AUTO_INCREMENT,
	`age` smallint(3) NOT NULL,
	`city` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
	`createdAt` datetime DEFAULT NULL,
  	`updatedAt` datetime DEFAULT NULL,
  	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

user.log

select * from `user` where city IN ('Bangalore', 'Mumbai', 'Delhi') ORDER BY age DESC