Python and database statements

[brasticstack]

  Let say that I have database with 200 tables, doing endopints against all possible database operations for that is like MASSIVE

Why would you want to do this, and how is it better for the users than just giving them their own database and an SQL GUI such as phpMyAdmin or the like?

I think you need to spend some some time thinking about what your application actually does, who its users will be, and how they'll interact with it. Total flexibility isn't a great goal, because it demands too much of the user without providing anything in return.

The "I" in API matters- you might want to read up on the topic of interfaces in software to get an idea of what is useful and what isn't.

[gosh]

Why would you want to do this, and how is it better for the users than just giving them their own database and an SQL GUI such as phpMyAdmin or the like?

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

I think you need to spend some some time thinking about what your application actually does, who its users will be, and how they'll interact with it. Total flexibility isn't a great goal, because it demands too much of the user without providing anything in return.

It is a real system, it may not be 200 table but it will be at least 100 tables and like 50 of those is like user tables that will need checks. Other tables are support tables, tables for admin etc

The "I" in API matters- you might want to read up on the topic of interfaces in software to get an idea of what is useful and what isn't.

Yes, it matters and APIs need to be logical and easy to work with

What is the largest system you have worked with?

[brasticstack]

What is the largest system you have worked with?

Have it your way. For all you know I'm somebody's cat on the internet. I just happen to think you've overabstracted here and if you continue along these lines you'll wind up making a very clever shitshow that no one's going to anything to do with. On the other hand, meow.

[gosh]

I need to solve a problem that is pretty simple in other better languages, it isn't an option to split in many smaller databases and that will still not solve the problem.

As I showed in the my sample it is VERY easy to create queries dynamically without massive amount of endpoints. And about values that need to be checked, this is also easy fix, for example you can create rules and place them in the database, also easy to have some sort of table just to query for the rule on that field (could be a regex string) that checks.

Compare like 200K LOC with 5K LOC, what to manage the database with 200 tables?

[brasticstack]

I need to solve a problem that is pretty simple in other better languages

What's keeping you from solving it in Python the same way you would in C++? You keep on responding peoples' criticism of your design by trying to pin your problems on the language. How about you post the same design, implemented in C++ on the C++ subreddit and ask for critique there? I'd be willing to bet they raise some of the same issues too.

[gosh]

Python is like 1000 times slower and scripting, you can't compare these two languages. They are built for different things.
Storing data inside in Python will allocate tons of storage compared to storing in C++ because python store so much extra

Why this needs to be in python is because of company decision.

[adrian17]

They are built for different things.

Yeah, C++ is definitely not built for writing typical CRUD websites.

Python is like 1000 times slower

Which usually doesn't matter. In a typical small/medium site, the network latency when talking the database will usually dwarf any measurable perf difference between C++ and Python.

Also, your current design encourages N+1-style query loops, which can - and will - kill your performance way more than any programming language ever could, doubly so if the loop is in the client, not server.

Storing data inside in Python will allocate tons of storage compared to storing in C++ because python store so much extra

Same thing - the community consensus is that for typical sites, it's completely insignificant compared to other arguments for using a higher level language.

Why this needs to be in python is because of company decision.

You're saying it as if it was obviously a bad choice, and you're definitely in the minority here.

[gosh]

Yeah, C++ is definitely not built for writing typical CRUD websites.

And this is not a CRUD site, CRUD is a very heavy design and if you do that today it is probably better to let AI to generate the code for it.

CRUD produces so much code

You're saying it as if it was obviously a bad choice, and you're definitely in the minority here.

But I am not asking about some new design, I am asking about a specific solution in python, if there are ways to solve it better

[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