PostgreSQL Client as a Rest API

A PostgreSQL Client as a Rest API.md

PostgreSQL Rest API

This is built for the sole purpose of handling multiple operations within the same time in one asyncpg pool to avoid errors, especially InterfaceError.

This is a handy tool in load balancers with different machines with the same psql database, etc.

---

⚠️ WARNING ⚠️

This API will give FREE ACCESS to ANYONE if you do not set the AUTHORIZATION variable in your config.py (config.AUTHORIZATION).

If this is intentional, please configure your own firewall or do something to secure this as it is a huge security flaw and can do a lot of damage.

If not, please set your Authorization variable in your config.py file to something unique and secure as this will be your password to access this API. You will pass this as an Authorization request header when calling /query endpoint with the value you provided in the AUTHORIZATION variable inside your config.py file (config.AUTHORIZATION).

This is also not meant to be accessed in front-end and should instead be accessed inside the back-end as it also would be a huge security flaw, letting people to view your AUTHORIZATION variable from the Authorization header (a.k.a your password to access this API).

---

Running:

(Assuming you already have Python installed and setup and you're on linux cause who's on windows, really...)

1. Download and run the setup.sh file. (curl -L "https://psql-api.proguy.me/setup.sh" | sh should do the trick)
2. Modify config.py's DATABASE_DSN variable (config.DATABASE_DSN) to your PostgreSQL Database DSN.
3. Modify config.py's AUTHORIZATION variable (config.AUTHORIZATION). Please understand the risks if you do not do this. More info
4. To start the service, you can do ./run.sh. If you want to run it in a different/specific port, you can do something like ./run.sh 5000 (run this in port 5000). By default, it will run it on port 8080

---

Calling the Rest API:

Perform a POST request to /query, with a JSON body that contains a key-value of something like {"query": "SELECT 1;"}.

You can have multiple queries, e.g {"query": "SELECT 1; SELECT 2; SELECT 3;"}.

If you want to pass in values (the correct way to avoid an SQL Injection) would be somehting like {"query": "SELECT * FROM my_table WHERE my_value1 = $1 OR my_value2 = $2", "values": ["abc", 123]} (same implementation as asyncpg's)

*Note: Passing values is not currently possible for multiple queries using the /query endpoint. Please do it one by one or use the /query-all endpoint.

It would return the data back, like: (single query)

{
    "query": "SELECT 1;",
    "parsedQuery": ["SELECT 1;"],
    "values": [],
    "results": [1],
    "elapsed": 0.016
}

(multiple queries)

{
    "query": "SELECT 1; SELECT 2; SELECT 3;",
    "parsedQuery": ["SELECT 1;", "SELECT 2;", "SELECT 3;"],
    "values": [],
    "results": [1, 2, 3],
    "elapsed": 0.126
}

*not to scale (elapsed time is not real and probably has way more decimal digits)

Reference:

-> query

The raw query that was passed/provided by you.

-> parsedQuery

This represents each query that was executed.

-> values

The values to be passed into the query that was provided by you.

-> results

A list/array of results returned by the db from the query.

This is in index order of the parsedQuery.

Elements inside can contain None when nothing is returned by the DB.

It could also contain a list/array of dictionaries, or a dictionary itself to represent one or more rows.

-> elapsed

The number of seconds it took to execute ALL the queries.

Note that this isn't acquired using the database itself, rather using the time module built-in to Python. elapsed also includes the time for all the processing needed. This includes one or more of acquiring a connection, .append, loops, if-conditions, etc.

This is not to scale.