Provide poetry based script to initialise the database

[devraj]

While initialising my first project from the template I had to do the following to initialise the database using the following steps:

root@af3b478425ca:/opt# python
Python 3.10.4 (main, May 11 2022, 10:32:02) [GCC 8.3.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> from acacia import db, models
>>> db.init_models()
<coroutine object init_models at 0xffff993e5a10>
>>> init = db.init_models()
>>> init()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
TypeError: 'coroutine' object is not callable
>>> import asyncio
>>> asyncio.run(init)
2022-07-23 01:35:04,927 INFO sqlalchemy.engine.Engine select pg_catalog.version()
2022-07-23 01:35:04,927 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-07-23 01:35:04,928 INFO sqlalchemy.engine.Engine select current_schema()
2022-07-23 01:35:04,928 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-07-23 01:35:04,929 INFO sqlalchemy.engine.Engine show standard_conforming_strings
2022-07-23 01:35:04,929 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-07-23 01:35:04,930 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-07-23 01:35:04,931 INFO sqlalchemy.engine.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%s
2022-07-23 01:35:04,931 INFO sqlalchemy.engine.Engine [generated in 0.00014s] ('user',)
2022-07-23 01:35:04,932 INFO sqlalchemy.engine.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%s
2022-07-23 01:35:04,932 INFO sqlalchemy.engine.Engine [cached since 0.001741s ago] ('user',)
2022-07-23 01:35:04,933 INFO sqlalchemy.engine.Engine 
CREATE TABLE "user" (
	id UUID DEFAULT gen_random_uuid() NOT NULL, 
	created_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, 
	updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, 
	email VARCHAR, 
	password VARCHAR, 
	verified BOOLEAN DEFAULT false NOT NULL, 
	mobile_number VARCHAR, 
	first_name VARCHAR, 
	last_name VARCHAR, 
	is_admin BOOLEAN DEFAULT false NOT NULL, 
	otp_secret VARCHAR, 
	PRIMARY KEY (id), 
	UNIQUE (email)
)


2022-07-23 01:35:04,933 INFO sqlalchemy.engine.Engine [no key 0.00007s] ()
2022-07-23 01:35:04,939 INFO sqlalchemy.engine.Engine COMMIT

which was essentially me running the init_db async method to create the tables. It would be preferable to wrap this up into a poetry script and made available as a task, so the user could do something

task db:init

and the initial database schema would be present. This would go hand in hand with #23 and could possible combine the two steps.

[devraj]

with asyncpg we have to run the init methods in a async loop

import asyncio
from labs import db, models
init = db.init_models()
asyncio.run(init)

Verify that the tables were created:

\c database_name
\dt

[devraj]

While the above example works, this does not appear to work via the poetry script:

Steps to reproduce

• Drop the database completely
• run task db:init
• inspect the tables to see that there was nothing created

The database engine has echo=True hence you should see the SQL statements if the tables are created, instead you see:

task: [db:init] docker compose exec api sh -c "poetry run initdb"
Skipping virtualenv creation, as specified in config file.
2023-03-22 23:04:36,312 INFO sqlalchemy.engine.Engine select pg_catalog.version()
INFO:sqlalchemy.engine.Engine:select pg_catalog.version()
2023-03-22 23:04:36,312 INFO sqlalchemy.engine.Engine [raw sql] ()
INFO:sqlalchemy.engine.Engine:[raw sql] ()
2023-03-22 23:04:36,313 INFO sqlalchemy.engine.Engine select current_schema()
INFO:sqlalchemy.engine.Engine:select current_schema()
2023-03-22 23:04:36,313 INFO sqlalchemy.engine.Engine [raw sql] ()
INFO:sqlalchemy.engine.Engine:[raw sql] ()
2023-03-22 23:04:36,314 INFO sqlalchemy.engine.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.Engine:show standard_conforming_strings
2023-03-22 23:04:36,314 INFO sqlalchemy.engine.Engine [raw sql] ()
INFO:sqlalchemy.engine.Engine:[raw sql] ()
2023-03-22 23:04:36,315 INFO sqlalchemy.engine.Engine BEGIN (implicit)
INFO:sqlalchemy.engine.Engine:BEGIN (implicit)
2023-03-22 23:04:36,315 INFO sqlalchemy.engine.Engine COMMIT
INFO:sqlalchemy.engine.Engine:COMMIT

Note that following the above steps does work.

Possible cause
The method that poetry calls wraps the async function in a method. I have not investigated this yet, but it's well possible that asyncio.run might have to be called outside of a function scope which is what would be happening when it's run on the python shell.

If this is the case then we might have to refactor the db.py package to be a folder and then have a __main__.py that has the initialise function.

This raises alerts towards updating the documentation around this.

[devraj]

The bug turns out to be that the initialise method did not have models imported into context:

def initialise():
    """ Async IO containers to run the init_models function

    This is called from the command line via poetry scripts.
    """
    import asyncio
    asyncio.run(init_models())

importing it into context fixes this issue:

def initialise():
    """ Async IO containers to run the init_models function

    This is called from the command line via poetry scripts.

    Note: while import the models package seems useless, it is
    infact crucial that the models are in context before the
    create_all is called, otherwise the context has no models
    defined and none will be created.    
    """
    import asyncio
    from . import models
    asyncio.run(init_models())

I had left the models import out thinking that it had no effect, but clearly not importing does not give SQLAlchemy the context of the models.

Note that while to avoid circular dependency we should reference models from the packages directly, but it's easier to aggregate the models to the top package for this sort of utility.