So basically for any software nowadays, best practice is to create REST
resources which handles all GET,POST,PUT,DELETE.
And it works for all clients be it mobile app or web app.
For these controllers are overwhelmed with lots of code.
Now in this approach, What you basically do is you organize your database
in the best way possible and then treat data as it comes in.
Consider a use case:
User A has 2 addresses and Institute A has 3 addresses
DB schema is:
User:
id(PK)
contactable_id(FK)
name(String)
Institute:
id(PK)
contactable_id(FK)
name(String)
Contactable:
id
Address:
pincode(String)
contactable_id(FK)
So now with this schema when a user is created, a record in contactable is
created and is associated with user's record, when the address for same
user is created, it get associated with this contactable id.
Now what we have done is we have a middleware built using sqlalchemy which
takes structural json as input and records data.
So for above example if I have to create a user with 2 addresses following
json should be sent as input.
{
"contactable": {
"user": {
"name": "Deepak"
},
"address": [
{
"pincode": "421202"
},
{
"pincode": "421203"
}
]
}
}
This approach has so far worked for all the relational structures we have.
And we have around 140 tables. :). Only condition is make sure the json
structure is as per schema.
For updates,
{
"contactable": {
"id": 1,
"user": {
"id": 1254,
"name": "Deepak"
},
"address": [
{
"id": 8,
"pincode": "421202"
},
{
"id": 9,
"pincode": "421203"
}
]
}
}
So fields will get updated if the json has ID's
Now when you have to GET records,
We have built a mechanism to query it over HTTP.
The URLs are the queries. Attempt is to make everything that you do in SQL
from browser.
This is the reference http://htsql.org/
Have made our own thing with a different design using sqlalchemy.
We are able to perform, queries with params, joins, conditional joins,
filters, paging, sorting, combining results from multiple tables, range,
date.
Still working on other stuff.
Authorization and Authentication are handled separately.
For all of these we have only 2 endpoints in the API.
One for GET and one for POST.
Hardly 700 lines of code and lot of Regex.
If it works fine, plan is to make it an open source.
So far we have solved all the issues we have faced.
I have received mixed reviews on this from different experts.
Would want a review from you guys.
What do you guys think of this approach?
What are the limitations?
Is it a stupid idea?
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
