SQL functions to build the OpenAPI output of a PostgREST instance.
- The first step in the roadmap is to migrate the OpenAPI spec from the PostgREST core repository (version 2.0 to 3.1):
- Info object
- Server object (replaces host, basePath and schemes from OAS 2.0)
- Components object
- Schemas (definitions in OAS 2.0)
- Security scheme (security definitions in OAS 2.0)
- Parameters
- Paths object
- Tables and Views
- GET
- POST
- PATCH
- DELETE
- Functions
- GET
- POST
- Tables and Views
- External Documentation Object
- Handle relevant OpenAPI elements according to user permissions
- The next step is to fix the issues tagged with
OpenAPI
in the core repo.
make && sudo make install
Check the code documentation at /docs to familiarize yourself with the project structure and function usage.
For testing on your local database:
# this will load fixtures in a contrib_regression db on your local postgres
make fixtures
# run the tests, they can be run repeatedly
make installcheck
# to clean the fixtures you can use
make clean
For an isolated and reproducible enviroment you can use Nix.
# to run tests
nix-shell --run "with-pg-15 make installcheck"
# to interact with the local database with fixtures loaded
nix-shell --run "with-pg-15 psql contrib_regression"
# you can choose the pg version
nix-shell --run "with-pg-13 make installcheck"
For those who insist on Docker:
# To build a docker image and run the tests in it
make docker-build-test
# To build a docker image for actual use
make docker-build
- OpenAPI 3 Specification Documentation: The official documentation of the spec.
- OpenAPI Specification Explained: Introductory explanation of the spec.
- OpenAPI Guide: Detailed explanation for each concept of the spec, useful to build it from scratch.
- OpenAPI Visual Map: Visual representation of the spec using an interactive GUI to navigate through its components.
postgrest-openapi's People
postgrest-openapi's Issues
Add "items" property to "array" types
Currently it detects the array type correctly but does not add "items".
Add nix environment for testing purposes
To easily test and verify SQL output for different OpenAPI specifications.
Repeated "Prefer" header overrides the previous one in SwaggerUI
I had the idea to repeat the Prefer
header for each preference that PostgREST has (count, tx, etc.). This would be useful to allow more than one Prefer Header at a time (which is not possible right now in the core repo).
But in SwaggerUI, the other Prefer headers are overridden and have the same value as the first one (since they all have the same name: Prefer
). A solution would be to just add the valid transactions in the description e.g. "Valid values are: tx=commit
, return=minimal
, ...
" or find if it's possible to allow many enum
s (couldn't find a way yet).
This is the relevant function:
postgrest-openapi/sql/postgrest.sql
Line 615 in 81f5b64
Missing instruction for integrating into PostgREST
Problem
Related to PostgREST/postgrest#1698 (comment).
Solution
Instructions need to be added to the README.
I assume we need a wrapper function that calls get_postgrest_openapi_spec? Then define it in https://postgrest.org/en/stable/references/configuration.html#db-root-spec as:
db-root-spec = "wrapper_func"
Validate the Json Schema and/or OpenAPI 3.1 Schema
In the core repository we use gavinwahl/postgres-json-schema to validate the resulting OpenAPI document for testing purposes (see jsonschema.sql). It's doesn't look mantained anymore so we need to use another one, preferably in SQL, to start validating the output (I'm doing it manually in the Swagger Editor right now). I think the pg_jsonschema extension is a good alternative.
Edit: see extra info on OpenAPI validation.
Add CI using GitHub actions
Needs CI for tests. Should trigger on every push/PR to main.
Verify how to parse some special default values for arrays, timezones, etc.
Need to verify how to parse some special default values, e.g. arrays have two types of defaults: {1,2,3}::integer[]
and ARRAY[1,2,3]
(which can be parsed) but composite types or even timestamps sometimes return complex defaults which are not accepted by the OpenAPI spec (e.g.: timezone('utc'::text, now())
). Maybe add another x-pgrst-sql-default
for complex types and leave default
for integers, text, etc.
Originally posted by @laurenceisla in https://github.com/laurenceisla/postgrest-openapi/pull/3#discussion_r1198483329
Add summary to the info object
In OAS 2.0, only the title and description are specified. Maybe a comment on the schema could be interpreted as:
'title
summary
description line 1
description line 2..'
Currently, it only detects title and description.
Can I Restrict Generated Methods?
I have created a view and granted a role SELECT privileges access the view. OpenAPI has generated docs including GET, POST, DELETE, and PUT methods for this endpoint. Only GET works (executing from swagger), the others are denied due to lack of permissions.
Is this the expected behavior? The docs seemed to suggest that only methods that are permitted will be generated. I'm wondering if this is working as expected, or if I haven't quite configured permissions correctly.
I'm using v11.2.
Use a extension property to define PostgreSQL formats
Maybe add x-pg-format
in the same way that x-mysql-type
is used in this example: https://github.com/swagger-api/validator-badge/blob/75bfd0b70ad4bc4543ca6f83e0f51baba664966a/src/test/resources/valid_oas3.yaml#L21-L23
Currently PostgREST adds the PostgreSQL format to the "format" key, which does not accept values like "double precision", "bigint", "smallint" etc. Although, more study on the format keyword and custom format attributes should be made to determine the best option.
Support as TLE
Handling domains created as custom types or other domains
Right now, a domain created as a data type defined in pg_catalog
returns the name of the base type. No problem there, since:
create domain mydomain as int;
will be shown in OpenAPI as an integer
. The problem starts when the domain is created as a custom type or as another domain. Then, postgrest-openapi
treats it as a string
instead of the schema defined by the base type.
Version detection
We need to find a way on how to detect the PostgREST version. Maybe using this PostgREST/postgrest#2647 could help, but if it fails it needs to be retrieved from another source.
Detect Primary Keys and Foreign Keys for Component Objects
Completion roadmap:
- Add extension for PKs and FKs (e.g.
x-pgrst-pks
) - Prepend
PK
andFK
to the "description" property. Simplify it from the verbose "This is a primary key" to simply "PK
" and "FK → table.col
(there should be an option to not show it if the user does not want to)
Enum array does not include enum property in OpenApi response
Hey,
The OpenApi response when the column type is an enum includes an enum
property which lists all the values of the enum
. This property is missing if the column is an array type. The expected output would be
{
"format": "schema.typename[]",
"type": "array",
"items": {
"type": "string",
"enum": ["first-value", "second-value"] // This property is currently missing
}
}
It would be very helpful if this was resolved as our app needs it. If this is a simple enough issue and if you could direct me to the correct starting point I can attempt raising a fix for this as well.
Thanks!
Verify if the nullable columns that have a default value are "required"
Are not nullable columns that have a default value required? For instance, if a column is_completed
has a default of false
, then even if it's not included in the body, then the request still completes, thus not required. There's a special case for generated id
s too, they shouldn't be included in the body.
Originally posted by @laurenceisla in https://github.com/laurenceisla/postgrest-openapi/pull/3#discussion_r1198485530
Only support OpenAPI 3.1
The roadmap mentions:
The first step in the roadmap is to migrate the OpenAPI spec from the PostgREST core repository (version 2.0 to 3.1):
https://github.com/PostgREST/postgrest-openapi#roadmap
Originally we thought it would be possible to replace the core openapi output in Haskell by somehow inlining the functions of this extension into a single query. But that seems too difficult (maybe impossible).
So I think we can just focus on openapi 3.0, to reduce the scope of the project.
With this we should still be able to fix all postgrest core issues. Since this lib is pure SQL, we can just instruct users to install it. They have been doing that for pgjwt
for a long time anyway.
Add an initial implementation of the Schema field of the Components Object
Basically follow what is specified in the OpenAPI spec.
Basic completion roadmap:
- Create the function with the schema structure
- Adapt the PostgREST SQL query to get all the tables and columns
- Add PostgreSQL composite types as objects in the exposed schema
- Show at least description, type and format as component properties
Add unit tests for functions
Mostly for the ones inside utils.sql
since they are reused in many places.
My version
Hi all!
As per the discussion at PostgREST/postgrest#1698 (comment) , I'd started working on something similar before I knew this repo existed.
I was going a slightly different direction, in that I was using PL/pgsql (happy with the direction this project's going though).
Unfortunately, I seem to have implemented just about the exact same features, with a few differences
- I added a table for defining servers, so that if people want to define extra servers as per https://spec.openapis.org/oas/v3.1.0#server-object-example (the multi-server example), they can. However, this assumes that we want config in the database.
- I assumed that the API version was not the version of PostgREST, but the version of the API itself -- every time the JSON text for the API changed, the version number should change. I hadn't really implemented that side, but I had implemented an "x-software" block that contained the version of PostgREST.
If you're interested in having either of those pieces in this, let me know and I'll see what I can do.
Thanks!
Detect missing properties for array types inside the items property
Currently missing:
- Maximum character size
- Detect enums
Order is lost when generating the OpenAPI document
The ordering of endpoints and other elements seem to be lost here and there when generating the OpenAPI document. For example, even though it's specified like this:
postgrest-openapi/sql/components.sql
Lines 544 to 567 in e0bc07c
The text/csv
entry always appears as first element of the object, even though it's defined as the last. This is because:
jsonb does not preserve white space, does not preserve the order of object keys, and does not keep duplicate object keys.
Source
A solution would be to use json
instead of jsonb
with the drawback of losing the ability to use some operations like concatenation ||
that is heavily used.
Include more details on table definition including types and foreign keys
I was wondering if you plan to include more details on table definitions in the openapi document so that one could parse it to generate e.g. html forms. Currently under the "Models" section there is already some relevant information but it is a bit unstructured e.g.
I am just wondering if it makes sense to implement views/rpc myself or if this project will provide this later so I just need to wait a bit.
Thank you,
Jan
Provide a SQL script as release artifact
Problem
While we're a pg extension, doing make && sudo make install
is still more cumbersome than running a SQL script (psql -f script.sql
).
Solution
Include a single SQL file as a release artifact. Newer versions can have a single script plus an upgrade script too (like postgrest-openapi--0.1.0--0.1.1.sql
).
The functions from `postgrest.sql` are not very reusable
The function postgrest_get_all_tables
in particular, returns columns
as a JSONB object, using functions from openapi.sql
.
postgrest-openapi/sql/postgrest.sql
Lines 5 to 18 in b5827bf
I think the returning table should be normalized (by columns
and its properties, not a JSONB object) and not use openapi.sql
functions if necessary. Then we could use an intermediate function(s) to convert to different OpenAPI objects. This function could be the one called (instead of postgrest_get_all_tables
) by other modules to build parameters, schemas, etc.
This would make it easier to reuse and even more performant (does not need to unnest or keep repeating unnecessary queries).
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.