API Reference (beta)

docs.holistic.dev
Search…
The holistic.dev API is organized around REST with JSON request and responses and uses standard HTTP response codes.
The current API status is BETA. Minor changes are possible before leaving this status. After fixing the state, JSON-schemas will be published, with a description of the formats of requests and responses
Authentication
The holistic.dev API uses API key to authenticate requests. You can view and manage your API key in the Account Settings.
The holistic.dev APIs is a REST-based service. Subsequently, all requests to the APIs require this HTTP header:
1
x-api-key: Your API key
Copied!
Content type
The holistic.dev APIs is also a JSON-based service. You have to add Content-Type HTTP header to all your requests:
1
Content-Type: application/json
Copied!
URL and API versioning
Only one API version is operating at this time. Use this base URL for your requests:
https://api.holistic.dev/api/v1
Responses
All responses are JSON-based and follow one of these formats:
1
// successful response
2
{
3
  "status": "OK",
4
  "data": {...}
5
  }
6
}
Copied!
1
// error response
2
{
3
  "status": "ERROR",
4
  "data": {
5
    "code": <http-code>,
6
    "message": "<error-message>",
7
    "details": <mixed-object(optional)>,
8
  }
9
}
Copied!
Http codes and error message you can find at Errors section
Projects
The project represents one database. Each project has a name and database type. Each project can contain multiple environments (development, test, stage, production) and various version control system branches.
Multiple environment and multiple branches will be avalible soon.
Create project
post
https://api.holistic.dev/api/v1/
project
project
Example:
JSON
Bash
1
{
2
	"project": {
3
		"name": "project name",
4
		"db": "pg"
5
	}
6
}
Copied!
1
HOLISTICDEV_API_KEY="<your-api-key>" HOLISTICDEV_PROJECT_NAME="<project-name>"; \
2
echo "{\"project\":{\"name\":\"$HOLISTICDEV_PROJECT_NAME\",\"db\":\"pg\"}}" | \
3
curl \
4
  --header "x-api-key: $HOLISTICDEV_API_KEY" \
5
  --header "Content-Type: application/json" \
6
  --request POST --data @- https://api.holistic.dev/api/v1/project/
Copied!
Rename project
patch
https://api.holistic.dev/api/v1/
project
project
Example:
JSON
Bash
1
{
2
	"project": {
3
		"name": "project name",
4
		"uuid": "00000000-0000-0000-0000-000000000000"
5
	}
6
}
Copied!
1
HOLISTICDEV_API_KEY="<your-api-key>" HOLISTICDEV_PROJECT_NAME="<project-name>"; \
2
echo "{\"project\":{\"name\":\"$HOLISTICDEV_PROJECT_NAME\",\"db\":\"pg\"}}" | \
3
curl \
4
  --header "x-api-key: $HOLISTICDEV_API_KEY" \
5
  --header "Content-Type: application/json" \
6
  --request PATCH --data @- https://api.holistic.dev/api/v1/project/
Copied!
List projects
get
https://api.holistic.dev/api/v1/
project
project
Example:
Bash
1
HOLISTICDEV_API_KEY="<your-api-key>"; \
2
curl \
3
  --header "x-api-key: $HOLISTICDEV_API_KEY" \
4
  --header "Content-Type: application/json" \
5
  --request GET https://api.holistic.dev/api/v1/project/
Copied!
Project details
get
https://api.holistic.dev/api/v1/
project/:uuid/details
project/:uuid/details
Example:
Bash
1
HOLISTICDEV_API_KEY="<your-api-key>" HOLISTICDEV_PROJECT_UUID="<project-uuid>"; \
2
curl \
3
  --header "x-api-key: $HOLISTICDEV_API_KEY" \
4
  --header "Content-Type: application/json" \
5
  --request GET "https://api.holistic.dev/api/v1/project/$HOLISTICDEV_PROJECT_UUID/details"
Copied!
Project DDL details
get
https://api.holistic.dev/api/v1/
project/:uuid/ddl
project/:uuid/ddl
Example:
Bash
1
HOLISTICDEV_API_KEY="<your-api-key>" HOLISTICDEV_PROJECT_UUID="<project-uuid>"; \
2
curl \
3
  --header "x-api-key: $HOLISTICDEV_API_KEY" \
4
  --header "Content-Type: application/json" \
5
  --request GET "https://api.holistic.dev/api/v1/project/$HOLISTICDEV_PROJECT_UUID/ddl"
Copied!
Project DMLs list
get
https://api.holistic.dev/api/v1/
project/:uuid/dml/list
project/:uuid/dml/list
Example:
Bash
1
HOLISTICDEV_API_KEY="<your-api-key>" HOLISTICDEV_PROJECT_UUID="<project-uuid>"; \
2
curl \
3
  --header "x-api-key: $HOLISTICDEV_API_KEY" \
4
  --header "Content-Type: application/json" \
5
  --request GET "https://api.holistic.dev/api/v1/project/$HOLISTICDEV_PROJECT_UUID/dml/list"
Copied!
SQL Syntax 
At this time, holistic.dev support only PostgreSQL syntax. We have implemented the last original parser from PostgreSQL 13. So, we support modernist PostgreSQL features.
PostgreSQL's syntax includes all standard SQL syntax, so, quite possibly, you can parse queries written for other RDBMS - Mysql, MSSQL, ORACLE, and more. But all analyzer rules aim to find specific PostgreSQL behavior.
DDL can contain multiple files. Each of them can include a lot of DDL statements. All unknown or syntactically incorrect statements will be ignored.
DML can contain only one DML statement. All other statements will be ignored. DML can contain one of the following parameter syntaxes:
1
-- SUPPORTED:
2
SELECT $1, ${obj.name} , $/obj.name/, $[obj.name], $(obj.name)
Copied!
Warning! 
Parameter as question mark not supported! 
1
-- NOT SUPPORTED !!!
2
SELECT ?
Copied!
Database schema (DDL)
DDL, aka Data Definition Language, is an SQL subset that includes CREATE, ALTER, and DROP statements. It uses to define database structure. Also, can include DML statements with extension's commands like create_hypertable() from TimescaleDB. All supported extensions you can find in the extensions list.
Knowledge about database structure is the critical requirement for SQL-queries static analysis. We require to upload the database structure described in DDL syntax before process any DML queries.
Extract DDL from database
If you store database schema in your version control system, you can directly upload it for any project. 
DDL can contain multiple files, and we store it as is for better navigation.
In case you have not DDL synchronized with production database in your version control system, you should extract DDL directly from target database:
Bash
1
 PGPASSWORD=<pg-password> pg_dump -h <pg-host> -p <pg-port> -U <pg-username> \
2
 -d <pg-db-name> --schema-only --no-owner --no-privileges --no-security-labels \
3
 > ddl.sql
Copied!
database parameters:
<pg-password>
<pg-host>
<pg-port>
<pg-username>
<pg-db-name>
pg_dump utility knows nothing about extension's necessary routines. E.g., commands like create_hypertable() from TimescaleDB you should add by yourself. We recommend storing it in a separate file and upload both.
Upload DDL
post
https://api.holistic.dev/api/v1/
ddl
ddl
Example:
JSON
Bash
1
{
2
    "project": {
3
        "name": "default"
4
    },
5
    "ddl": {
6
        "version": null
7
    },
8
    "files": [
9
        {
10
            "name": "filename-1.sql",
11
            "source": "CREATE TABLE aircrafts_data (\n    aircraft_code character(3) NOT NULL,\n    model jsonb NOT NULL,\n    range integer NOT NULL,\n    CONSTRAINT aircrafts_range_check CHECK ((range > 0))\n);\n\nCREATE VIEW aircrafts AS\n SELECT ml.aircraft_code,\n    (ml.model ->> lang()) AS model,\n    ml.range\n   FROM aircrafts_data ml;\n\n\n\nCREATE TABLE airports_data (\n    airport_code character(3) NOT NULL,\n    airport_name jsonb NOT NULL,\n    city jsonb NOT NULL,\n    coordinates point NOT NULL,\n    timezone text NOT NULL\n);\n\n\n\nCREATE VIEW airports AS\n SELECT ml.airport_code,\n    (ml.airport_name ->> lang()) AS airport_name,\n    (ml.city ->> lang()) AS city,\n    ml.coordinates,\n    ml.timezone\n   FROM airports_data ml;\n\n\n\nCREATE TABLE boarding_passes (\n    ticket_no character(13) NOT NULL,\n    flight_id integer NOT NULL,\n    boarding_no integer NOT NULL,\n    seat_no character varying(4) NOT NULL\n);"
12
        },
13
        {
14
            "name": "filename-2.sql",
15
            "source": "CREATE TABLE bookings (\n    book_ref character(6) NOT NULL,\n    book_date timestamp with time zone NOT NULL,\n    total_amount numeric(10,2) NOT NULL\n);\n\n\nCREATE TABLE flights (\n    flight_id integer NOT NULL,\n    flight_no character(6) NOT NULL,\n    scheduled_departure timestamp with time zone NOT NULL,\n    scheduled_arrival timestamp with time zone NOT NULL,\n    departure_airport character(3) NOT NULL,\n    arrival_airport character(3) NOT NULL,\n    status character varying(20) NOT NULL,\n    aircraft_code character(3) NOT NULL,\n    actual_departure timestamp with time zone,\n    actual_arrival timestamp with time zone,\n    CONSTRAINT flights_check CHECK ((scheduled_arrival > scheduled_departure)),\n    CONSTRAINT flights_check1 CHECK (((actual_arrival IS NULL) OR ((actual_departure IS NOT NULL) AND (actual_arrival IS NOT NULL) AND (actual_arrival > actual_departure)))),\n    CONSTRAINT flights_status_check CHECK (((status)::text = ANY (ARRAY[('On Time'::character varying)::text, ('Delayed'::character varying)::text, ('Departed'::character varying)::text, ('Arrived'::character varying)::text, ('Scheduled'::character varying)::text, ('Cancelled'::character varying)::text])))\n);\n\n\nCREATE VIEW flights_v AS\n SELECT f.flight_id,\n    f.flight_no,\n    f.scheduled_departure,\n    timezone(dep.timezone, f.scheduled_departure) AS scheduled_departure_local,\n    f.scheduled_arrival,\n    timezone(arr.timezone, f.scheduled_arrival) AS scheduled_arrival_local,\n    (f.scheduled_arrival - f.scheduled_departure) AS scheduled_duration,\n    f.departure_airport,\n    dep.airport_name AS departure_airport_name,\n    dep.city AS departure_city,\n    f.arrival_airport,\n    arr.airport_name AS arrival_airport_name,\n    arr.city AS arrival_city,\n    f.status,\n    f.aircraft_code,\n    f.actual_departure,\n    timezone(dep.timezone, f.actual_departure) AS actual_departure_local,\n    f.actual_arrival,\n    timezone(arr.timezone, f.actual_arrival) AS actual_arrival_local,\n    (f.actual_arrival - f.actual_departure) AS actual_duration\n   FROM flights f,\n    airports dep,\n    airports arr\n  WHERE ((f.departure_airport = dep.airport_code) AND (f.arrival_airport = arr.airport_code));\n\n\n\nCREATE VIEW routes AS\n WITH f3 AS (\n         SELECT f2.flight_no,\n            f2.departure_airport,\n            f2.arrival_airport,\n            f2.aircraft_code,\n            f2.duration,\n            array_agg(f2.days_of_week) AS days_of_week\n           FROM ( SELECT f1.flight_no,\n                    f1.departure_airport,\n                    f1.arrival_airport,\n                    f1.aircraft_code,\n                    f1.duration,\n                    f1.days_of_week\n                   FROM ( SELECT flights.flight_no,\n                            flights.departure_airport,\n                            flights.arrival_airport,\n                            flights.aircraft_code,\n                            (flights.scheduled_arrival - flights.scheduled_departure) AS duration,\n                            (to_char(flights.scheduled_departure, 'ID'::text))::integer AS days_of_week\n                           FROM flights) f1\n                  GROUP BY f1.flight_no, f1.departure_airport, f1.arrival_airport, f1.aircraft_code, f1.duration, f1.days_of_week\n                  ORDER BY f1.flight_no, f1.departure_airport, f1.arrival_airport, f1.aircraft_code, f1.duration, f1.days_of_week) f2\n          GROUP BY f2.flight_no, f2.departure_airport, f2.arrival_airport, f2.aircraft_code, f2.duration\n        )\n SELECT f3.flight_no,\n    f3.departure_airport,\n    dep.airport_name AS departure_airport_name,\n    dep.city AS departure_city,\n    f3.arrival_airport,\n    arr.airport_name AS arrival_airport_name,\n    arr.city AS arrival_city,\n    f3.aircraft_code,\n    f3.duration,\n    f3.days_of_week\n   FROM f3,\n    airports dep,\n    airports arr\n  WHERE ((f3.departure_airport = dep.airport_code) AND (f3.arrival_airport = arr.airport_code));\n\n\nCREATE TABLE seats (\n    aircraft_code character(3) NOT NULL,\n    seat_no character varying(4) NOT NULL,\n    fare_conditions character varying(10) NOT NULL,\n    CONSTRAINT seats_fare_conditions_check CHECK (((fare_conditions)::text = ANY (ARRAY[('Economy'::character varying)::text, ('Comfort'::character varying)::text, ('Business'::character varying)::text])))\n);\n\n\nCREATE TABLE ticket_flights (\n    ticket_no character(13) NOT NULL,\n    flight_id integer NOT NULL,\n    fare_conditions character varying(10) NOT NULL,\n    amount numeric(10,2) NOT NULL,\n    CONSTRAINT ticket_flights_amount_check CHECK ((amount >= (0)::numeric)),\n    CONSTRAINT ticket_flights_fare_conditions_check CHECK (((fare_conditions)::text = ANY (ARRAY[('Economy'::character varying)::text, ('Comfort'::character varying)::text, ('Business'::character varying)::text])))\n);\n\n\n\nCREATE TABLE tickets (\n    ticket_no character(13) NOT NULL,\n    book_ref character(6) NOT NULL,\n    passenger_id character varying(20) NOT NULL,\n    passenger_name text NOT NULL,\n    contact_data jsonb\n);\n\n\nALTER TABLE ONLY aircrafts_data\n    ADD CONSTRAINT aircrafts_pkey PRIMARY KEY (aircraft_code);\n\n\nALTER TABLE ONLY airports_data\n    ADD CONSTRAINT airports_data_pkey PRIMARY KEY (airport_code);\n\n\nALTER TABLE ONLY boarding_passes\n    ADD CONSTRAINT boarding_passes_flight_id_boarding_no_key UNIQUE (flight_id, boarding_no);\n\n\nALTER TABLE ONLY boarding_passes\n    ADD CONSTRAINT boarding_passes_flight_id_seat_no_key UNIQUE (flight_id, seat_no);\n\n\nALTER TABLE ONLY boarding_passes\n    ADD CONSTRAINT boarding_passes_pkey PRIMARY KEY (ticket_no, flight_id);\n\nALTER TABLE ONLY bookings\n    ADD CONSTRAINT bookings_pkey PRIMARY KEY (book_ref);\n\n\nALTER TABLE ONLY flights\n    ADD CONSTRAINT flights_flight_no_scheduled_departure_key UNIQUE (flight_no, scheduled_departure);\n\n\nALTER TABLE ONLY flights\n    ADD CONSTRAINT flights_pkey PRIMARY KEY (flight_id);\n\n\nALTER TABLE ONLY seats\n    ADD CONSTRAINT seats_pkey PRIMARY KEY (aircraft_code, seat_no);\n\n\nALTER TABLE ONLY ticket_flights\n    ADD CONSTRAINT ticket_flights_pkey PRIMARY KEY (ticket_no, flight_id);\n\n\nALTER TABLE ONLY tickets\n    ADD CONSTRAINT tickets_pkey PRIMARY KEY (ticket_no);\n\n\nALTER TABLE ONLY boarding_passes\n    ADD CONSTRAINT boarding_passes_ticket_no_fkey FOREIGN KEY (ticket_no, flight_id) REFERENCES ticket_flights(ticket_no, flight_id);\n\nALTER TABLE ONLY flights\n    ADD CONSTRAINT flights_aircraft_code_fkey FOREIGN KEY (aircraft_code) REFERENCES aircrafts_data(aircraft_code);\n\n\nALTER TABLE ONLY flights\n    ADD CONSTRAINT flights_arrival_airport_fkey FOREIGN KEY (arrival_airport) REFERENCES airports_data(airport_code);\n\n\nALTER TABLE ONLY flights\n    ADD CONSTRAINT flights_departure_airport_fkey FOREIGN KEY (departure_airport) REFERENCES airports_data(airport_code);\n\n\nALTER TABLE ONLY seats\n    ADD CONSTRAINT seats_aircraft_code_fkey FOREIGN KEY (aircraft_code) REFERENCES aircrafts_data(aircraft_code) ON DELETE CASCADE;\n\n\nALTER TABLE ONLY ticket_flights\n    ADD CONSTRAINT ticket_flights_flight_id_fkey FOREIGN KEY (flight_id) REFERENCES flights(flight_id);\n\n\nALTER TABLE ONLY ticket_flights\n    ADD CONSTRAINT ticket_flights_ticket_no_fkey FOREIGN KEY (ticket_no) REFERENCES tickets(ticket_no);\n\n\n\nALTER TABLE ONLY tickets\n    ADD CONSTRAINT tickets_book_ref_fkey FOREIGN KEY (book_ref) REFERENCES bookings(book_ref);"
16
        }
17
    ]
18
}
Copied!
1
HOLISTICDEV_API_KEY="<your-api-key>" HOLISTICDEV_PROJECT_NAME="<project-name>"; \
2
DATA=$(cat ddl.sql | base64 -w0)
3
echo "{\"project\":{\"name\":\"$HOLISTICDEV_PROJECT_NAME\"},\"ddl\":{\"version\":null},\"files\":[{\"name\":\"ddl.sql\",\"source\":\"$DATA\"}]}" | \
4
curl \
5
  --header "x-api-key: $HOLISTICDEV_API_KEY" \
6
  --header "Content-Type: application/json" \
7
  --request POST --data @- https://api.holistic.dev/api/v1/ddl/
Copied!
base64 argument -w0 need to prevent formatting result at Linux-based os
All files of the previous version will be replaced, even if their number does not match the number of files of the new version.
We store the history of all schema changes for future features.
Also, you can copy bash script with credentials from project card from project list inside client area:
Project card -> "Copy info" button
​
Patch DDL
patch
https://api.holistic.dev/api/v1/
ddl
ddl
Example:
JSON
1
{
2
    "project": {
3
        "name": "default"
4
    },
5
    "ddl": {
6
        "version": null
7
    },
8
    "source": "CREATE INDEX ON aircrafts_data (aircraft_code)"
9
}
Copied!
Patch existing DDL with new DDL commands. This source will be attached at the end of the existing DDL in a separate file. DDL and ALL project DMLs will be re-parsed after this.
Get DDL source
get
https://api.holistic.dev/api/v1/
ddl/:uuid/source
ddl/:uuid/source
Example:
Bash
1
HOLISTICDEV_API_KEY="<your-api-key>" HOLISTICDEV_DDL_UUID="<ddl-uuid>"; \
2
curl \
3
  --header "x-api-key: $HOLISTICDEV_API_KEY" \
4
  --header "Content-Type: application/json" \
5
  --request GET "https://api.holistic.dev/api/v1/ddl/$HOLISTICDEV_DDL_UUID/source/"
Copied!
Get DDL abstract syntax tree
get
https://api.holistic.dev/api/v1/
ddl/:uuid/ast
ddl/:uuid/ast
Example:
Bash
1
HOLISTICDEV_API_KEY="<your-api-key>" HOLISTICDEV_DDL_UUID="<ddl-uuid>"; \
2
curl \
3
  --header "x-api-key: $HOLISTICDEV_API_KEY" \
4
  --header "Content-Type: application/json" \
5
  --request GET "https://api.holistic.dev/api/v1/ddl/$HOLISTICDEV_DDL_UUID/ast/"
Copied!
Get DDL compiled object
get
https://api.holistic.dev/api/v1/
ddl/:uuid/object
ddl/:uuid/object
Example:
Bash
1
HOLISTICDEV_API_KEY="<your-api-key>" HOLISTICDEV_DDL_UUID="<ddl-uuid>"; \
2
curl \
3
  --header "x-api-key: $HOLISTICDEV_API_KEY" \
4
  --header "Content-Type: application/json" \
5
  --request GET "https://api.holistic.dev/api/v1/ddl/$HOLISTICDEV_DDL_UUID/object/"
Copied!
SQL query (DML)
DML, aka Data Manipulation Language, is an SQL subset that includes SELECT, INSERT, UPDATE, and DELETE statements.
Upload DML
post
https://api.holistic.dev/api/v1/
dml
dml
Example:
JSON
Bash
1
{
2
	"project": {
3
	    "name": "default"
4
	},
5
	"ddl": {
6
	    "version": null
7
	 },
8
	 "dml": {
9
		"name": "booking-info.sql",
10
		"version": null,
11
		"source": {
12
			"sql": "SELECT   \n  b.book_ref,\n  t.ticket_no,\n  t.passenger_id,\n  t.passenger_name,\n  tf.fare_conditions,\n  tf.amount,\n  f.scheduled_departure_local,\n  f.scheduled_arrival_local,\n  f.departure_city || '(' || f.departure_airport || ')' as departure,\n  f.arrival_city || '(' || f.arrival_airport || ')' as arrival,\n  f.status,\n  bp.seat_no\nFROM\n  bookings b\n  JOIN tickets t ON b.book_ref = t.book_ref\n  JOIN ticket_flights tf ON tf.ticket_no = t.ticket_no\n  JOIN flights_v f ON tf.flight_id = f.flight_id\n  LEFT JOIN boarding_passes bp ON tf.flight_id = bp.flight_id AND tf.ticket_no = bp.ticket_no\nWHERE\n  b.book_ref = '_QWE12'\nORDER BY\n  t.ticket_no,\n  f.scheduled_departure"
13
		}
14
	 }
15
}
Copied!
1
HOLISTICDEV_API_KEY="<your-api-key>" HOLISTICDEV_PROJECT_NAME="<project-name>"; \
2
DATA=$(cat ddl.sql | base64 -w0)
3
echo "{\"project\":{\"name\":\"$HOLISTICDEV_PROJECT_NAME\"},\"ddl\":{\"version\":null},\"dml\":{\"name\":\"dml.sql\", \"version\": null, \"source\":{ \"sql\":\"$DATA\"}}}" | \
4
curl \
5
  --header "x-api-key: $HOLISTICDEV_API_KEY" \
6
  --header "Content-Type: application/json" \
7
  --request POST --data @- https://api.holistic.dev/api/v1/dml/
Copied!
base64 argument -w0 need to prevent formatting result at Linux-based os
We store the history of all schema changes for future features.
 Also, you can copy bash script with credentials from project card from project list inside client area:
Project card -> "Copy info" button
Get DML source
get
https://api.holistic.dev/api/v1/
dml/:uuid/source
dml/:uuid/source
Example:
Bash
1
HOLISTICDEV_API_KEY="<your-api-key>" HOLISTICDEV_DML_UUID="<dml-uuid>"; \
2
curl \
3
  --header "x-api-key: $HOLISTICDEV_API_KEY" \
4
  --header "Content-Type: application/json" \
5
  --request GET "https://api.holistic.dev/api/v1/dml/$HOLISTICDEV_DML_UUID/source/"
Copied!
Get DML abstract syntax tree
get
https://api.holistic.dev/api/v1/
dml/:uuid/ast
dml/:uuid/ast
Example:
Bash
1
HOLISTICDEV_API_KEY="<your-api-key>" HOLISTICDEV_DML_UUID="<dml-uuid>"; \
2
curl \
3
  --header "x-api-key: $HOLISTICDEV_API_KEY" \
4
  --header "Content-Type: application/json" \
5
  --request GET "https://api.holistic.dev/api/v1/dml/$HOLISTICDEV_DML_UUID/ast/"
Copied!
Get DML compiled object
get
https://api.holistic.dev/api/v1/
dml/:uuid/object
dml/:uuid/object
Example:
Bash
1
HOLISTICDEV_API_KEY="<your-api-key>" HOLISTICDEV_DML_UUID="<dml-uuid>"; \
2
curl \
3
  --header "x-api-key: $HOLISTICDEV_API_KEY" \
4
  --header "Content-Type: application/json" \
5
  --request GET "https://api.holistic.dev/api/v1/dml/$HOLISTICDEV_DML_UUID/object/"
Copied!
pg_stat_statements and pg_stat_monitor
The pg_stat_statements and pg_stat_monitor modules provides a means for tracking execution statistics of all SQL statements executed by a server.
The holistic.dev API can process whole pg_stat_statements/pg_stat_monitor snapshot at one request.
Exporting the pg_stat_statements/pg_stat_monitor content is the most comfortable, most flexible, and secure way to organize the automatic export of SQL-queries from Postgresql. This extension is easy to configure for both on-premise installations and cloud providers: AWS, GCP, AZURE, ALIBABA CLOUD, DIGITAL OCEAN, YANDEX CLOUD, SBER CLOUD and others.
pg_stat_statements and pg_stat_monitor can be disabled by default. You should execute the following command to activate it:
1
-- pg_stat_statements
2
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
3
​
4
-- pg_stat_monitor
5
CREATE EXTENSION pg_stat_monitor;
Copied!
Privacy
pg_stat_statements and pg_stat_monitor extensions normalizes query entries. Normalization is a process whereby similar queries, typically differing only in their constants (though the exact rules are somewhat more subtle than that) are recognized as equivalent, and are tracked as a single entry.  This is particularly useful for non-prepared queries.

The normalization process intercepts constants in SQL statements run by users and replaces them with a placeholder (identified as a dollar mark with number).

For this reason, all data values that were used in SQL-statement will be blacked out and will not be visible at holistic.dev

But normalization has another side.

When normalizing the parameters of functions and expressions, it is quite often impossible to define the type of result unambiguously. For example, the addition operator has 42 types of its arguments, and the normalized querySELECT$1 + $2 can return 20 different response types. Such ambiguity negatively affects the analyzer's accuracy. The result of this will be less accurate analysis results - some rules will not be applied.
post
https://api.holistic.dev/api/v1/
pg_stat_statements
pg_stat_statements
Bash
1
HOLISTICDEV_API_KEY="<your-api-key>" HOLISTICDEV_PROJECT_NAME="<project-name>"; \
2
PG=$(PGPASSWORD=<pg-password> psql -t -A -h <pg-host> -p <pg-port> -U <pg-username> -d <pg-db-name> -c "SELECT json_agg(u) FROM (SELECT DISTINCT ON (queryid) queryid :: varchar as name, query as source, SUM(calls) :: varchar AS calls, SUM(total_time) :: varchar AS total_time, MIN(min_time) :: varchar AS min_time, MAX(max_time) :: varchar AS max_time, AVG(mean_time) :: varchar AS mean_time, SUM(rows) :: varchar AS rows FROM pg_stat_statements pgss JOIN pg_database pgd ON pgss.dbid = pgd.oid WHERE pgd.datname = current_database() AND queryid IS NOT NULL GROUP BY queryid, query) u" | base64 -w0); \
3
echo "{\"pgss\":\"$PG\", \"project\":{\"name\":\"$HOLISTICDEV_PROJECT_NAME\"}}" | \
4
curl \
5
  --header "x-api-key: $HOLISTICDEV_API_KEY" \
6
  --header "Content-Type: application/json" \
7
  --request POST --data @- https://api.holistic.dev/api/v1/pg_stat_statements/
Copied!
base64 argument -w0 need to prevent formatting result at Linux-based os
holistic.dev parameters:
<your-api-key> - your account api key
<project-name> - project name
database parameters:
<pg-password>
<pg-host>
<pg-port>
<pg-username>
<pg-db-name>
You need to upload DDL (database schema) before upload pg_stat_statements snapshot!
Make sure that the <pg-username> has enough permissions to retrieve the query results. If there are problems with the output, the easiest way to fix it is DROP EXTENSION and CREATE EXTENSION again.
Query in sample script aggregat
Check Results
After adding DDL or DML source, we try to parse and analyze it. It can take some time, especially for big DDL. Because of these reasons parsing and analyzing doing asynchronous way. 

When we ship new parser or analyzer, we rebuild all internal objects and analyze them with new rules.

You can reach check results for the last DDL by <project-uuid> or <ddl-uuid>. 
DML check result can be reached only by <dml-uuid>. 

All of them have similar results format.

UUID point to exact DDL/DML version, not the last one.
get
https://api.holistic.dev/api/v1/
ddl/:uuid/check-result/
ddl/:uuid/check-result
get
https://api.holistic.dev/api/v1/
project/:uuid/ddl/check-result/
project/:uuid/ddl/check-result
get
https://api.holistic.dev/api/v1/
dml/:uuid/check-result/
dml/:uuid/check-result
Errors
The holistic.dev API uses conventional HTTP response codes to indicate the success or failure of an API request. In general: Codes in the 2xx range indicate success. Codes in the 4xx range indicate an error that failed given the information provided (e.g., a required parameter was omitted, a charge failed, etc.). Codes in the 5xx range indicate an error with holistic.dev's servers.
Error statuses
200 - OK    Everything worked as expected.
400 - Bad Request    The request was unacceptable, often due to missing a required parameter.
401 - Unauthorized    No valid API key provided.
403 - Forbidden    The API key doesn't have permissions to perform the request.
404 - Not Found    The requested resource doesn't exist.
409 - Conflict    The request conflicts with another request (perhaps due to using the same idempotent key).
429 - Too Many Requests    Too many requests hit the API too quickly. We recommend an exponential backoff of your requests.
500 - Server Error     Something went wrong on holistic.dev's end.
Error response messages
coming soon
Cloud Service Integrations
Last modified 1yr ago