Enhancement: Support save of SQL

TonyGravagno commented 2 months ago

It can be helpful to see the Create Table SQL used to generate the Zod. I propose a new option to export this as tableName.sql in the same folder as the .ts files.

I can do this.

araera111 commented 2 months ago

Thanks for the suggestion! I think we could add something like this in getTableDefinition.ts:

const [table] = await connection.query("show create table ??", tableName);

And then use appendFileSync to write it to ./outputDir/tableName.sql. We might need to add outputDir to the function's config parameters.

TonyGravagno commented 1 month ago

I modified a local version of getTableDefinition.ts to save the SQL. It's very helpful.
I also had a situation where the node SQL parser was failing on my SQL. (I've been testing with new constraints.) So I further enhanced this read from the SQL file rather than from the database. This allows us to modify the SQL to find out exactly what is causing the SQL parsing to fail.

For example, I have a table with 10 fields and each has constraints - this table is failing to parse. So after saving the .sql file I removed 9 fields, and the parsing was successful. As time permits I will now add a new field until there is a failure. It's much more difficult to do that when the SQL is in a database.

Summary: We can support new options to save, and read SQL. I have already done this with ugly code. When we finish with the maxLength feature I can post a new branch with this save/read feature.

What do you think?

araera111 commented 1 month ago

Thank you for sharing this idea.

The SQL save/read feature sounds very interesting. Once you post the new branch, I would be happy to test it in my own test branch and consider integrating it into the develop branch.

Looking forward to it!

TonyGravagno commented 1 month ago

I have completed this enhancement based on the current main/v2.2.4. I am testing with 2.2.4 and will add it into develop after you have made an update there.

Notes:

This feature adds a new options.output field, sqlFileName.
For now, the prettier-plugin-sql package is imported to format the SQL. This is used in formatByPrettier.ts.
I've incorporated the save of SQL into the outputToFile code, to keep file output in one place. This required a number of other small changes that I need to test.
I tried to keep this code "clean". It's OK but not great. If you like the initial implementation, I'll clean it up in another ticket/update.
Right now, all tables are written into a single file, like tables.sql, in the outputDir. I'd like to enhance this to optionally use tablename.sql with another config setting. I'll look at this in another ticket/update.

TonyGravagno commented 1 month ago

This feature is now available in a dedicated branch that is based on core/main.
https://github.com/TonyGravagno/mysql-to-zod/tree/tg-save-sql-2

Summary: In the config.js options.output, activate the output of .sql files with saveSql:true. When the sqlFileName option is set to a name like "alltables.sql", all SQL is written to that file. If that option is set to "tablename", the SQL for each table is written to its own file. Most of the changes in this branch were made to make this enhancement and others easier to implement.

Details

1. New options: - option.schema.saveSql : Boolean, default false - option.output.sqlFileName : filename for SQL file - Only valid when saveSQL=true - Default "tables.sql" - "tablename" causes table name to substitute in file name: person.sql, company.sql ... 2. formatByPrettier.ts - Now accepts parser value, limited to SupportedFormatters. - babel-ts for TypeScript - sql for ... SQL - prettier-plugin-sql is used for SQL 3. outputToFile.ts (refactored) - Now includes general purpose writeLocalFile(). - Now includes dedicated function - writeformattedFile(). - New outputSchemaToFile() - replaces outputToFile, passes responsibility for formatting to writeFormattedFile(). - Added code to use tablename.ts as filename, or name from config, or default. Not complete, tablename is not passed into this function yet. - New outputSqlToFile() - also passes responsibility for formatting to writeFormattedFile(). - Types SchemaOutputParams and SqlOutputParams not necessary but help to provide context. 4. runCommand.ts : now uses outputSchemaToFile() rather than outputToFile() 5. output.ts : Now provides defaults to other modules to eliminate hardcoded defaults elsewhere. 6. options.ts : Removed hardcoded output defaults, now uses defaults from output.ts. 7. mysqlToZod.config.js : Includes new output.sqlFileName. 8. getTableDefinition.ts: params on getTableDefinition() are now defined as a type. (housekeeping)

Small commits were progressively added to the branch to make it easy to follow the progress and changes.

This is not being PR'd for merge to core because this does not include the 'develop' branch and I don't want to confuse the branches. It is safe to merge this tg-save-sql-2 branch with main, and it will fit with other changes later. For now, please comment on commits in the branch, or merge here when comfortable.

Repo documentation has not been updated yet. I want to make sure that the changes are accepted before documenting them for others.

araera111 / mysql-to-zod

Enhancement: Support save of SQL #157