How do you manage without an ORM?

Question

SQL

All the time I used ORM to communicate with the database, very rarely I had to write SQL to execute queries.
Nevertheless, I often heard that it is customary for someone to write pure SQL all the time, without using an ORM. If you do, can you provide a code example? How do you then work with strings, just like dictionaries? How do you protect against typos in table column names? Even for the simplest queries like `SELECT ... FROM table WHERE id = 1`, do you write such a long query all the time?
I'd appreciate some clarification on how this works for you.

Answer 1

depends on the language and size of the project
language - Python - God himself ordered (Alchemy or Django even more so)
language - Go - without sugar, what difference does it make what to write: it doesn’t matter what the cast will be and there will be many letters,
and the size of the project - very simply - is called "two times" ,
this is averaged (it seems that there was an article on Habré with numbers 120-500%),
while "twice" is not critical - you can be content with ORM

Answer 2

How do you then work with strings, just like dictionaries?

most of the tasks of such a plan need to be dumped on the subd. When there is a selection of 10-100-1k elements - iterates over as a dictionary or list

How do you protect against typos in table column names?

tests will fail, somewhere it will highlight, somewhere it will pull up constants from the code or from the database

Even for the simplest queries like `SELECT ... FROM table WHERE id = 1`, do you write such a long query all the time?

if such a request occurs three times, it is rendered and then called as a function The
request is "long", when it does not fit on one screen or it needs to be split in order to understand the logic of its execution
Thinking about abandoning orms, you, as a developer, need after a lot of experience work in the positions of an architect, if you have a team that is not much inferior to you, those in very rare cases

Answer 3

If the load on the database is not large and the structure of the database is simple, then ORM will do. But if there are many large tables (millions of records) and you need to build reports, then ORM will not pull it.
You need to manually build queries and analyze the execution plan in order to squeeze the maximum. The downside is that if you need to support multiple platforms, then you will have to write queries for each platform, taking into account the peculiarities of the syntax.

Answer 4

I like it better when you can write both ORM and manual requests. In python, for example, there is an excellent sqlalchemy library, you can use orm, sqlalchemy core (something in between) and write queries manually (but sqlalchemy is synchronous and this is a minus). Usually they write manually for optimization or some non-trivial things, like building a histogram.

users = await connection.fetch('''
                            SELECT * FROM core_user
                            WHERE is_updated = true and last_update_timestamp <= $1 - updating_period LIMIT $2
                            ''', int(time.time()), settings.BOT_CONCURRENT_TASKS)

Yes

user['password']

, there are any that allow you to handle it like this

user.password

So there will be an error.
Or here is an example, I have no idea how to do it with ORM

sql_request = '''
        WITH stats AS (
            SELECT MIN(:field_name) as min_value, MAX(:field_name) as max_value
            FROM :table_name
        )
        SELECT 
            width_bucket(:field_name, min_value, max_value, :window_size) as bucket,
            MIN(:field_name) as x,
            COUNT(*) AS y
        FROM 
            :table_name, stats
        GROUP BY
            bucket
        ORDER BY
            bucket;
    '''.replace(':field_name', field_name)\
        .replace(':table_name', table_name)\
        .replace(':window_size', str(window_size))

items = await asyncpgsa.pg.fetch(sql_request, *sql_arguments)

return [PostgreSQLSerializer(['x', 'y']).serialize(item) for item in items if item['y'] != 0]

Answer 5

1. One php-class is one system role for working with the database.
2. During initialization, the class receives its ACL: all tables, table fields with which it can work and with what accesses to each of these objects.
3. I write a query in any database tool and test it (query).
4. Copy and replace the parts I need with substitution variables:

$sql = 'SELECT * FROM [user] WHERE id=[$id:numeric] OR user=[$id:string] LIMIT 1';

5. After running through the function of checking variables, accessing, escaping and compiling a real query to the database, it is executed.

Answer 6

It all depends on the requests and the need for optimization.
when in the future a heavy request to the front that will be made by 10 clients at the same time, I will do it in pure SQL (and I will write the front itself in "C")
and if you write a backend - let it count for half an hour there - what do you care?