When not using an ORM: how do you marry SQL with the main programming language of the application (be it web, or data science etc.)?

On the one hand, it feels right to abstract the SQL database access into some kind of centralised API - but that feels like reinventing an ORM. Of course the difference remains that the hand-written queries are more specific than what the ORM can produce, but still.

The alternative seems messy - there is one shared database, and different parts of the application manage their own little slice of it. If you then want to, say, join tables from different logical domains, are you at risk of your queries getting out of date as different parts of the application evolve?

What about creating tables and indices - do you do that as a centralised, "format" process, or do different parts of the application create them as and when? Or do you even do it by hand? Where do any such creation scripts live - a centralised bootstrap script, or again, dispersed throughout the application?

Do you hard-code queries as strings in the application, or do they live as separate files? How do you keep table creation and insert/update queries in sync?

The context is, I'm setting up some SQL for a data-science-y project, that might lead to something bigger. I'm scraping various data sources and sticking them into a DB (SQLite for now), and I'm trying to think ahead, so as not to create one big ball of spaghetti.I'm trying to avoid using an ORM, to both better learn SQL, and after some critical comments found on the good old t'Internet.

All thoughts and ideas welcome - and thanks!