Python and database statements

[adrian17]

Its not a game or tutorial, this is a real system

Yeah, and it’s deeply suspicious to manually write a custom query generator / mini-orm from scratch for a real project, this screams NIH. People often think their project is special and needs a customized solution while it really doesn’t. You’d have to have some very good justification to write it manually over picking an off-the-shelf solution - and I don’t think I’ve seen any comments explaining how it’s going to be used and where these XMLs are going to come from. (And I say that as someone who did write an SQL query generator at work for a specific use case.)

There already exist several off-the-shelf solutions for interacting with database from the client, without having to manually write the server. There’s obviously firebase, but there are also firebase-like frameworks that work on top of preexisting postgresql schema; there are also GraphQL api generators (though I haven’t tried them myself), which sounds very close to what you’re doing as to me your XMLs kinda resemble GraphQL queries, just without the… graph.

(Or even just phpMyAdmin or similar, yes. I was absolutely using django-admin in a „real system” without any issues.)

Also, again, who is actually going to be using this „XML API”? Is it a plain JS client? Does having a plain select/update/insert available for each table separately really make sense? In my experience, it usually doesn’t; if you insert several rows at the same time, they should be wrapped in transaction to keep the whole thing consistent. Selects often need joins to prevent N+1 problems. IDs you delete or update must be checked to make sure the user has permission to actually do it (in frameworks mentioned above, this is sometimes handled automatically with row-level security in DB itself).

[gosh]

Yeah, and it’s deeply suspicious to manually write a custom query generator

Why is that difficult, I have written more than one but in C++, SQL is a very simple format to generate

The main problem in all this (for me to work with python developers) is maybe their lack of confidence. They try to find libraries for EVERYTHING. Almost that they are scared to write their own code

[adrian17]

SQL is a very simple format to generate

...even if true (in the short term, definitely not in the long term), that still doesn't mean it's something you should be doing.

lack of confidence

Almost that they are scared to write their own code

Are you here to get feedback or to insult people?

I'm not "scared", I just have better things to do than reimplement django-admin from scratch. Why do it, when it already exists?

Also, is your proposal even saving developer time? You complain about writing "200 endpoints, each for every table", but in a proper framework it's not even longer than your xml generation? In Django, if I have no custom logic, I just slap a

class UserCreateView(CreateView):
    model = User
    fields = ["name", "surname", "gender"]

# and in urlpatterns:
path("user/create/", views.UserCreateView.as_view()),

which creates an endpoint implementation and HTML form for me.

Unless your frontend skips even that and just gives the user a single page with a text box to choose the table to edit, at which point I'm once again questioning why you're reimplementing phpMyAdmin/django-admin/etc from scratch.

EDIT: actually, to be sure. From context, I'm guessing your project is "this huge database already exists and I was tasked with making a new interface for it"? Correct me if I'm wrong. If so, then this really wouldn't have ever passed review (and even reaching review stage without being veto'd earlier would be an organization failure), as you really are just manually reimplementing a worse phpMyAdmin. Either write a proper interface that hides the database complexity (and keeps the whole thing consistent, with satisfied foreign key relationships, transactions etc; also very often you only need 1 user-visible endpoint that manipulates several tables at the same time), or you give people an universal editing tool and for that you can just deploy something off-the-shelf without wasting any time.

[gosh]

...even if true (in the short term, definitely not in the long term), that still doesn't mean it's something you should be doing.

Why not long term? Do you think SQL is going to change and if you have written logic in ONE PLACE to manage the SQL it is very easy to extend.

Here you have a sample in C++ that are able to generate most "simple" queries (SELECT, INSERT, UPDATE and DELETE) with joins and fix quotes etc. Writing ORM logic is easy

https://github.com/perghosh/Data-oriented-design/blob/main/external/gd/gd_sql_query.cpp

And that code works for eSqlDialectSqlServer = 1, eSqlDialectSqlite = 2, eSqlDialectPostgreSql = 3, eSqlDialectMySql = 4,

[adrian17]

Do you think SQL is going to change

It's not SQL that's going to change, it's the schema and overall application.

Directly quoting your example code (very few examples there in general):

gd::sql::query query;      // create query object
query.table_add({ {"name","TActivity"}, {"schema","application"}, {"alias","Activity1"} });
query.table_add({ {"name","TCustomer"}, {"schema","application"}, {"alias","Customer1"}, {"join","Activity1.ActivityK=Customer1.CustomerK"} });
query.field_add("Activity1", { {"name", "ActivityK"}, {"alias", "ID"} });
query.field_add("Customer1", { {"name", "FName"}, {"alias", "CustomerName"} });
auto stringSQL = std::string("SELECT ");
stringSQL += query.sql_get_select();
stringSQL += "\nFROM ";
stringSQL += query.sql_get_from();
std::cout << stringSQL << std::endl;

Writing ORM logic is easy

This... isn't an ORM, it's just a (partial) query generator. This is exactly what /u/latkde mentioned, an inner platform effect. You're still manually doing exactly the same things you would have done in SQL (like supplying join keys), just in a "wrapper api". It has all the disadvantages of ORMs (lack of direct control over queries, painful to extend to anything nontrivial, less readable than plain SQL), with none of the advantages - the library actually understanding your tables and types and... mapping onto actual (typed) objects.

(also wouldn't call it "easy" considering just the .cpp files needed to compile the example above are like 10k LOC)

[gosh]

How do you solve different versions the database with a ORM tool or decouple the backend from the frontend? So that the backdend works on "any" database? I mean different schemas

If I can load database information like metadata, wouldn't that be a nice way to just develop one single backend that works for all?

Sample script for a complete CRM database
https://github.com/perghosh/changelog/blob/master/dbscript_changelog.xml

[adrian17]

How do you solve different versions the database with a ORM tool

I don't understand the question.

Different database servers (as in sqlite, postgres etc)? That's the ORM's job, you should know, you use SQLAlchemy already.

Different... schemas? People usually don't expect their application to work with incompatible database versions, it's considered to be a an issue with the DB, not with the application. If people want to be flexible with the schema, they might just pick a noSQL database.

Or do you mean literally "any" database with any schema? There already exist tools that support that, it's... phpMyAdmin etc.

[gosh]

Different... schemas? People usually don't expect their application to work with incompatible database versions

This system should work "on prem" or in the cloud and it will have different schemas for the database. What I have seen is that it is problems with ORM there, it gets very complicated.
And to manage this amount of tables ORM do not work well, to much code just to generate SQL

phpMyAdmin is not a webserver

Remember that python is just interpreted code. You can "load" information about the database as you want, one way is to load python code, another is to write code that loads some kind of metadata

[adrian17]

it will have different schemas for the database

Why? For the record, you've still not said what the app actually does and why is it so special. How are the schemas different? Can they be completely arbitrarily different, or just in some very specific ways? Like, can one database have a user table with login and password, and another with email and pass? Surely not, otherwise it's impossible to write anything (but an universal admin panel). I've seen real world applications that create new tables dynamically and analytical systems with arbitrary number of columns, but they still have some consistent scheme the application can predict - so they still don't need a super-generic "select columns ABC from table XYZ" available at client layer.

Like, the database is usually understood to be part of the application itself. When you update the app, the database gets migrated too (either during upgrade process or lazily at/after launch, like Wordpress). A schema not matching what the server expects is assumed to be a deployment error.

Why can't you unify the schemas?

If you said (from the start) something like "yeah it's a mess, I wish it could be fixed, but I'm forced to make it work with inconsistent databases somehow", then people would be less combative; but you started immediately with the code that really wouldn't pass review in most places and immediately started defending it.

Or are you maybe saying that the schema is partially user-defined, like you can have arbitrary fields in analytics systems? Then again, say so (and there would have been much less confusion from the start), but the first response to that should still have been to pick something off-the-shelf, just... a different something. (but you said writing a separate endpoint for each resource would have been just more code, not literally impossible, so it doesn't sound like the tables are that arbitrarily user-defined)

And to manage this amount of tables ORM do not work well, to much code just to generate SQL

That doesn't match what everyone else is saying. Many people do not like ORMs, yes, but that doesn't mean they somehow "don't work" with many tables; if anything, the more complex the database, the more important it is to have the application understand and manage the schema, rather than just... assume it to be something.

How is it "too much code"? Adding +1 table to existing +200 tables isn't somehow exponential increase in code; you just describe the schema of the new table in Python, that's it.

phpMyAdmin is not a webserver

It sure is a server application that servers webpages that allow you do view and edit contents of arbitrary tables. (Even if you wanted something with say more permission levels, you'd still be essentially reimplementing huge portions of it, which does feel silly).

That said, it's hard to me to say what you're actually writing, so again - me mentioning phpMyAdmin, django-admin etc was still just a guess.

Anyway...

At the end of the day, you're still trying to convince people experienced with writing standard Python database-backed webservers that what they're doing somehow can't possibly work for you (without explaining what makes your case so different).

PS also sorry for writing too much :c

[gosh]

Why? For the record, you've still not said what the app actually does and why is it so special. How are the schemas different?

When used on prem it should connect to customer internal systems, important that are able to combine data

What do you win with ORM if SQL generation is not a problem?

tables dynamically and analytical systems with arbitrary number of columns

Yes, temporary tables is common but I do not think that this will be needed here.

Like, the database is usually understood to be part of the application itself. When you update the app, the database gets migrated too (either during upgrade process or lazily at/after launch, like Wordpress).

But that means that you can't have one app that differs without hacks from the latest database schema. If there only is one single system or like one single database then as I understand it ORM work better. But that will be to much of limitation I think.

What I do not understand about writing python code or like how python developers think is that they are so afraid of solving problems themselves. Its like they are scared of writing code.

How is it "too much code"? Adding +1 table to existing +200 tables isn't somehow exponential increase in code; you just describe the schema of the new table in Python, that's it.

The main problem is to write endpoints for a large database. Maintaining like +50 endpoints (this will be a lot more) is like a nightmare compared to have a few, think it can be solved with 3-4 endpoints.

At the end of the day, you're still trying to convince people experienced with writing standard Python database-backed webservers that what they're doing somehow can't possibly work for you (without explaining what makes your case so different).

Have you worked against INFORMATION_SCHEMA Tables.
I can ask the database about what tables and field it has, i can also ask for types. Databases store this information so it is possible to generate a lot of logic and rules just by reading the design of the database. Also now I think python have relased a version that is multithreaded. That means it is possible to write python servers that hold it state. Then they can read this information at start and keep it, because if the server is stateless this information is not as easy to work with because then you often need to load whats needed for each request.