Intro¶
omni_python allows you to seamlessly integrate Python code within Omnigres, which are then used as stored procedures in the database. omni_python also allows you to integrate Flask framework and serve HTTP requests from directly within Omnigres.
Prerequisites¶
Create omni_python extension if it is not installed.
We would also need some helper extensions for loading relevant files from filesystem.
create extension if not exists omni_schema cascade;
create extension if not exists omni_vfs cascade;
Getting started¶
Create a directory on your host system containing the Python files, and corresponding requirements.txt, and mount the volume to the Docker container.
We need to add the following to requirements.txt file to support Python integration:
Create Python files. You can create more than one files, as long as they are in the directory that you would mount as a volume inside the Docker container for running Omnigres.
Create functions in Python as you would, and annotate them with @pg to make sure they are loaded into the database.
For example, let's make two Python files.
hello.py:
maths.py:
from omni_python import pg
@pg
def add(a: int, b: int) -> int:
return a + b
@pg
def subtract(a: int, b: int) -> int:
return a - b
python-files.
create or replace function demo_function() returns omni_vfs.local_fs language sql
as $$
select omni_vfs.local_fs('/python-files')
$$;
omni_python
Tip
We are working on a CLI tooling that will take care of directory mapping.
Load the filesystem files.
Optional Tip
You can set a reload command for reloading the filesystem changes.
Run Omnigres in a Docker container. Make sure to mount the local directory as a volume on the correct path.
docker run --name omnigres \
-e POSTGRES_PASSWORD=omnigres \
-e POSTGRES_USER=omnigres \
-e POSTGRES_DB=omnigres \
--mount source=omnigres,target=/var/lib/postgresql/data -v $(pwd)/python-files:/python-files \
-p 127.0.0.1:5433:5432 --rm ghcr.io/omnigres/omnigres-slim:latest
Let's try it out!
Flask¶
For Flask framework integration, we have a few more steps.
Ensure to create omni_httpd extension to be able to handle HTTP requests.
Add the following in requirements.txt file.
Let's say you have a table called employees.
create table employees (
id integer primary key generated always as identity,
name text not null,
department text not null,
salary integer not null
);
Now you can update your Python files (in the mounted volume) to include Flask functionality. For example, you can define endpoints to fetch list of all employees, fetch a particular employee, as well as create a new employee record.
from omni_python import pg
from omni_http import omni_httpd
from omni_http.omni_httpd import flask
from flask import Flask, jsonify, make_response, request
import uuid
app = Flask('myapp')
def employees_to_json(employees):
return json.dumps([dict(employee) for employee in employees])
@app.route('/employees', methods=['POST'])
def create_employee():
json_data = json.loads(request.data.decode('UTF-8'))
employee_name = json_data.get('name')
employee_department = json_data.get('department')
employee_salary = json_data.get('salary')
if not employee_name or not employee_department or not employee_salary:
return "Missing required fields", 400
employee = plpy.execute(plpy.prepare("insert into employees (name, department, salary) "
"values ($1, $2, $3) returning *", ["text", "text", "int"]),
[employee_name, employee_department, employee_salary])
return employees_to_json(employee)
@app.route('/employees', methods=['GET'])
def get_employees():
employees = plpy.execute(plpy.prepare("select * from employees"))
return employees_to_json(employees)
@app.route('/employees/<int:employee_id>', methods=['GET'])
def get_employee(employee_id):
employee = plpy.execute(plpy.prepare("select * from employees where id = $1", ["int"]), [employee_id])
return employees_to_json(employee)
handle = pg(flask.Adapter(app))
Flask integration with Omnigres
flask.Adapter(app) creates an instance of the flask.Adapter class, which is provided by the omni_http library.
This adapter allows you to integrate Flask with the omni_http framework.
The app object is your Flask application instance, and you pass it to flask.Adapter() to create an adapter
that can handle HTTP requests using your Flask app.
The handle function is the entry point for handling incoming HTTP requests.
It takes an HTTPRequest object as input and is expected to return an HTTPOutcome.
Inside the function, it forwards the req object to the app_ object, which is a Flask
application wrapped in the `flask.Adapter(). This allows the Flask application to handle
the incoming HTTP request and generate a response. Finally, the response is returned
as HTTPOutcome.
Tip
Note: We use plpy for now, but we should use DB API compatible APIs and/or other
frameworks (such as SQLAlchemy) which will be available very soon.
Make sure to add port mapping for 8080 (this is default omni_httpd is
configured with) when running Omnigres via Docker (using the same volume as
before).
docker run --name omnigres \
-e POSTGRES_PASSWORD=omnigres \
-e POSTGRES_USER=omnigres \
-e POSTGRES_DB=omnigres \
--mount source=omnigres,target=/var/lib/postgresql/data -v $(pwd)/python-files:/python-files \
-p 127.0.0.1:5450:5432 -p 127.0.0.1:8000:8000 \
--rm ghcr.io/omnigres/omnigres-slim:latest
Setup HTTP handler for out Flask application:
You can hit the endpoints defined in the Flask code above.
Fetch all employees:
$ curl http://localhost:8080/employees
[
{
"id": 1,
"name": "Akshat",
"department": "Engineering",
"salary": 100000
},
{
"id": 2,
"name": "Mohit",
"department": "Sales",
"salary": 50000
}
]
Create a new employee:
$ curl --request POST \
--url http://localhost:8080/employees \
--header 'Content-Type: application/json' \
--data '{
"name": "Daniel",
"department": "Marketing",
"salary": 70000
}'
[{"id": 3, "name": "Daniel", "department": "Marketing", "salary": 70000}]
Fetch a particular employee: