Why do you need the table structure before? and what exactly is ‘before’? we’ve been talking about adding this feature to the designer so that you get the structure as soon as you have your questionnaire, but I think for all practical purposes export file is enough - since we’re talking about automating things, the database schema will / should be created automatically, right? so you could generate export file as soon as you start your survey and even if there are no interviews collected yet, you will get those files with all the column headers in there.
@arthurshaw2002 already responded on the variable types, questionnaire document does contain all this info.
I’ve played with this a little and even have a (definitely not polished or final) working prototype, if you work with python you can take a look at https://github.com/vavalomi/surveysolutions_utils for some ideas - using export file structure to form the table/variable names, and by parsing the document fine-tuning the variable types.
Great thing of using sqlalchemy orm is that there is a layer of abstraction in front of the actual database engine - the same code should work to export to sqlite file, as well as postgresql, oracle, etc…
There is an interesting project that also does csv to sqlite conversion with dynamic schema generation (https://github.com/simonw/csvs-to-sqlite) in their case they just use the exported csv (tab) files and convert variables into either numeric or string variables, with optional conversion of datetime fields as well.