I was looking for an answer to this myself and found Running module migrations
It is basically saying you can only create the tables as part of your craft project - but not specific to your module. To create this project-wide migration run in your craft project folder (not your module folder):
php craft migrate/create [my_migration_name]
This will create the migration file and class in your craft's projects 'migrations' folder (it will display the exact path and file name after running the above command). For more info about the Migration class see https://craftcms.com/docs/3.x/extend/migrations.html#creating-migrations