API Service for Query Statistics

This is a Golang API service that allows you to connect to any PostgreSQL database and retrieve query statistics. The service supports filtering by query type, pagination, and sorting by slowest or fastest queries. The service is designed to handle high loads of API calls and uses Redis for caching to improve performance.

You can easily analyze your all query performance and identify areas for optimization with single API call. And with Redis caching, the service can handle high loads of API calls without sacrificing performance.

Getting Started

To run the code, use the following command:

go run ./cmd/dbquery/app.go

Before using the API service, make sure that the PostgreSQL database you are connecting to has the pg_stat_statements extension loaded correctly. You also need to configure postgresql.conf with shared_preload_libraries = ‘pg_stat_statements’. Additionally, you need to have a local running Redis server.

Usage

To use the API service, you can send HTTP requests to the API endpoint with the appropriate parameters.

Connecting any PostgreSQL Database

To connect to any PostgreSQL database, use the following endpoint:

POST /connect

you will get database_instance_id and you need it for query api.

The request body should be in JSON format and include the following fields:

{
    "DBHost": "127.0.0.1",
    "DBUserName": "postgres",
    "DBUserPassword": "password123",
    "DBName": "dbname",
    "DBPort": "5432"
}

Retrieve Query Statistics

To retrieve query statistics, use the following endpoint:

GET /querystate?page=1&page_size=50&sort=slowest&filter_query=all&database_instance_id=d96fceb7-c576-404d-b8ab-c46db4fd1422

You can use query parameters to filter and paginate the results. The available query parameters are:

• page: The page number to retrieve (default is 1).
• page_size: The number of query statistics to retrieve per page (default is 50).
• sort: The sorting order for the query statistics. You can sort by slowest or fastest (default is slowest).
• filter_query: The query type to filter by. You can filter by all, select, insert, update, or delete (default is all).
• database_instance_id: The database connection. You will get this id from database connect api.

Testing

To run the tests, you will need to have Redis and PostgreSQL databases running. You can use Docker to easily set up these databases:

Start a Redis container

docker run --name my-redis -d -p 6379:6379 redis

Start a PostgreSQL container

docker run --name my-postgres -e POSTGRES_PASSWORD=password123 -d -p 5432:5432 postgres

Once the databases are running, you can run the tests with the following command:

go test ./...

Conclusion

This API service provides a fast and efficient way for clients to retrieve query statistics from any PostgreSQL database. With support for filtering, pagination, and sorting, clients can easily analyze their query performance and identify areas for optimization. And with Redis caching, the service can handle high loads of API calls without sacrificing performance.

CI-CD Jenkies

Jenkins pipeline is configured to build and deploy the application.

Screenshot

To do soon

frontend 🤩️