Knex: Cascade option when dropping table

[legomind]

It would be handy to have something like this:

knex.schema.dropTable('users', true);

//OR

knex.schema.cascade().dropTable('users');

Would generate:

DROP TABLE users CASCADE;

[elnaz]

Same for .truncate

[onurozkan]

+1

[chrisbroome]

Well this is dialect specific. What's even worse is that according to the MySQL Documentation, the CASCADE keyword is permitted but does absolutely nothing. (As an aside, MySQL also silently ignores CHECK constraints and this bit me in the past).

I think if we include this functionality (which I would like), there either needs to be a fallback implementation for dialects that lack a proper CASCADE implementation or, if there's no way to implement it with a fallback, and error should be thrown.

For example, in MySQL you can query INFORMATION_SCHEMA.KEY_COLUMN_USAGE and build a directed graph of the database with nodes for each table_name + '.' + column_name combination and edges from REFERENCED_TABLE_NAME.REFERENCED_COLUMN_NAME to TABLE_NAME.COLUMN_NAME. You then perform a topological sort on the graph and it gives you the drop order. I've done this, and it works pretty well. The good thing about using INFORMATION_SCHEMA also is that the implementation should be portable across SQL dialects. Whether or not that's true in practice is a whole different story.