A framework to build simple business apps (filemaker/access complexity) entirely out of PostgreSQL (PG) that auto generates the web (desktop? mobile?) UI, with minimal configuration.
🚪 Auth
User authentication (e.g. log-ins) and Authorization are done done through
PG's ROLE
s and GRANT
s.
C.R.U.D.
✨ Creating
The form for creation of a single entry is based on the names and types of the
fields in the table's schema. Each PG type has its own "write-mode"
implementation. Required form fields (i.e., NOT NULL
fields) are marked
with an asterisk. Similarly, validation is driven by CHECK
s and underlying
data types.
Here are some examples for "write-mode" widgets:
date
— date picker- foreing-key —
lfsd
(see below.) - file — dropzone
Inserting into multiple tables at once
TODO:
views + PGRULE
s?
👓 Reading
Listing and visualizing tables is similar to
pgweb
. A convention (i.e., starting
names with _
) is adopted for hiding tables/views.
The visibility of objects is also dictated by PG's access permissions.
Even finer-grained access to data is achieved through
PG's Row Security Policies.
Each PG type has its "read-mode" visual representation. Implementing one for custom types is easy.
🔎 Searching
Trivial cases are inferred by the indexes that exist on a table:
- index on a
boolean
corresponds to a checkbox on the search form - index on an
enum
field is a dropdown - index on
text
enables full text search - index on
date
corresponds to- date range widgets; or
- datepicker + before/after now.
More complex cases are handled by writing a PG function
from which the
search form is generated. The form fields are inferred from the types and
names of the function
's input arguments. Performance optimization (e.g.,
indexing) is left to the developer.
✏️ Updating
Similar to Creating
❌ Deleting
From the list, view, if you're authorized to do so. Likewise in singular view form.
TODO:
how to deal with cascades?
↝ Foreign Keys
Fields that can act as foreing-keys also need to specify a corresponding
"description" field. This requires an entry in postgres-web
's config.
The "description" is used instead of the actual value when displaying it in a table/view.
This would also allow for a lazy?-fuzzy?-searchable-dropdown
(lfsd
)
(a la Bootstrap Select) to be
generated for searching, creating and updating.
🗄️ Blob storage
Integration with blob storages (for e.g. photos, documents) is easily configured and completely transparent to user.
Example usage:
select ("key"
, mime_type
, content_length
, content :: bitea)
from bucket
TODO:
encription?
TODO:
streaming?!?
Cool storages to look into:
❓ Open questions
- How many connections can PG handle? Is there any other way to do RLS other than each user logging in separately?