PostgreSqlJSONB plugin

PostgreSqlJSONB plugins features:

1. Allows working with JSONB PostgreSql fields similarly to a regular model on the client side. get requests are filtered and ordered by first-level JSONB fields.

2. Integrates with ApiSpecPlugin in swagger for get requests (for ResourceList views). New fields are added:

   • filter[<JSONB field name in the model>.<upper level JSONB field>] - simple filter;

   • filter = [{"name": "<JSONB field name in the model>.<upper level JSONB field>", "op": "eq", "val": "<значение>"}] - in complex filters, we can request JSONB fields similarly to regular fields in the model.

   • sort=<JSONB field name in the model>.<upper level JSONB field> - used as deep sort.

3. Integrates with PermissionPlugin, so you can use upper level JSONB fields in permission cases.

How to use

To use the plugin in your schemas with JSONB fields do the following:

1. In schema, describe JSONB field (from model) as Nested, linking to a schema with upper level fields in model JSONB field.

2. JSONB schema with upper-level fields must be inherited from combojsonapi.postgresql_jsonb.schema.SchemaJSONB class.

And you’re done.

Plugin usage example

Let’s take a look at example implementation of a plugin, where we store user settings in JSONB field. This example requires a postgresql database connection.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import Column, Integer, String
from sqlalchemy.dialects.postgresql.json import JSONB
from sqlalchemy.orm import Query, load_only, scoped_session
from flask_combo_jsonapi.marshmallow_fields import Relationship
from flask_combo_jsonapi import Api, ResourceList, ResourceDetail
from flask_combo_jsonapi.querystring import QueryStringManager
from combojsonapi.postgresql_jsonb.schema import SchemaJSONB
from combojsonapi.postgresql_jsonb import PostgreSqlJSONB
from combojsonapi.spec import ApiSpecPlugin
from marshmallow_jsonapi.flask import Schema
from marshmallow_jsonapi import fields


app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = '<подключение к Postgresql базе данных>'
app.config['SQLALCHEMY_ECHO'] = True
db = SQLAlchemy(app)

"""Models description"""

class User(db.Model):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    email = Column(String)
    password = Column(String)
    settings = Column(JSONB)


db.create_all()

"""Models' schemas description"""

class SettingsSchema(SchemaJSONB):
    active = fields.Boolean()
    age = fields.Integer()

class UserSchema(Schema):
    class Meta:
        type_ = 'user'
        self_view = 'user_detail'
        self_view_kwargs = {'id': '<id>'}
        self_view_many = 'user_list'
        ordered = True
    id = fields.Integer(as_string=True)
    name = fields.String()
    fullname = fields.String()
    email = fields.String()
    password = fields.String()
    settings = fields.Nested('SettingsSchema')

"""API resource managers description"""

class UserResourceDetail(ResourceDetail):
    schema = UserSchema
    events = UserEventsForResourceDetail
    methods = ['GET']
    data_layer = {
        'session': db.session,
        'model': User,
    }

class UserResourceList(ResourceList):
    schema = UserSchema
    methods = ['GET', 'POST']
    data_layer = {
        'session': db.session,
        'model': User,
    }

"""Initializing the API"""

app.config['OPENAPI_URL_PREFIX'] = '/api/swagger'
app.config['OPENAPI_SWAGGER_UI_PATH'] = '/'
app.config['OPENAPI_SWAGGER_UI_VERSION'] = '3.22.0'

api_spec_plagin = ApiSpecPlugin(
    app=app,
    # Declaring tags list with their descriptions, so API gets organized into groups. This is optional: when there's no tags,
    # api will be grouped automatically by type schemas names (type_)
    tags={
        'User': 'API для user'
    }
)

api_json = Api(
    app,
    plugins=[
        api_spec_plagin,
        EventPlugin(),
        PostgreSqlJSONB()
    ]
)
api_json.route(UserResourceDetail, 'user_detail', '/api/user/<int:id>/', tag='User')
api_json.route(UserResourceList, 'user_list', '/api/user/', tag='User')


if __name__ == '__main__':
    for i in range(10):
        u = User(name=f'name{i}', fullname=f'fullname{i}', email=f'email{i}', password=f'password{i}')
        db.session.add(u)
    db.session.commit()
    app.run(port='9999')

Requests example

With views described in example above, we can use new filtering and ordering features.

Request all active users with a simple filter:

/api/user/?filter[settings.active]=True

Request all users aged 18 and up with a complex filter, ordered by age desc, then name:

/api/user/?filter=[{"name":"settings.age","op": "gt","val": "18"}]&sort=-settings.age,name