async pug

[woile]

Hello people! I'm starting a small project with starlette and I'd like to try pugsql, I found it super interesting, it's great project! But starlette is async and I'd like to benefit from it. I was wondering if there's a way to make pugsql async. I've been reading the source of pug and encode/databases, but still not sure where to begin. Any recommendations, idea?

Thanks

[polyrand]

For async you have a few options. I'm currently using encode/databases. It's been working great except for a few bugs that have been merged to master but not released yet. The difference is that you can't have you sql queries in separate .sql files, which I think is one of the strengths of pugsql. For that, you can check aiosql. It shares a lot with pugsql, apart from async, another difference is that it parses the queries using regex rather than relying on sqlalchemy.

SQLAlchemy got support for asyncio in 1.4: https://docs.sqlalchemy.org/en/14/orm/extensions/asyncio.html So maybe pugsql will support async when it updates? I don't know.

[woile]

Thanks for all the data! I'm thinking now, maybe do something just with encode/databases. Read files with queries and pass them to execute or execute_many smthing like:

queries = load_queries("resources/sql")
await database.execute(query=queries.find_user, user_id=42)
# or await database.execute_many(query=queries.find_user, user_id=42)

I'd get the highlight/lint from a .sql file. Do you see any downsides?

[polyrand]

That's actually an interesting way to do it. I don't know if you plan to load the queries manually or use aiosql. With aiosql you can access the ray sql string: https://nackjicholson.github.io/aiosql/advanced-topics/#accessing-prepared-sql-as-a-string

So one option may be having your queries in a .sql file to get highlight/linting. Load the queries with aiosql and then execute them with encode/databases.

This is a field I'm currently exploring myself, so I don't have a lot of experience. I am now porting an app from encode/databases to aiosql just to see how that would look like. One thing to take into account is that by using only aiosql you can remove the sqlalchemy dependency. If you are constrained by how big your app can be, that is something to consider (but with Python I'd say that's not the main challenge).

[Yeganloo]

I did not actually try this, but i hope this works for you: 1- Normally load queries with pugsql 2- Instead of using connect, use aiosql to build your engine 3- Use set engine method of your query object I hope after this all queries become await able! As i said, i didn't try this yet. If this doesn't work, inform me. I will try to make it work :D