void db reset does not drop views: MySQL reset and seed fail when a migration creates one #421

Problem

The app has a migration that creates a view. void db migrate works. Then void db reset (or void db seed) fails when it re-applies that migration.

MySQL 8.4:

$ void db reset --yes
■ migration 0001_view.sql failed: Table 'user_names' already exists

PostgreSQL 17, with CREATE VIEW app_info AS SELECT 'v1'::text AS version;:

$ void db reset --yes
■ migration 0002_const_view.sql failed: relation "app_info" already exists

On MySQL the database is then left half reset. users and _void_migrations are created again, the old view is still there, and only 0000_init.sql is recorded. void db migrate and later resets fail with the same error until the view is dropped by hand.

This is older than #413: 6b47270 fails the same way (■ Table 'user_names' already exists).

Repro

void.config.ts has database: 'mysql', and .env has DATABASE_URL=mysql://root:root@127.0.0.1:3306/app.

-- db/migrations/0000_init.sql
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL);
-- db/migrations/0001_view.sql
CREATE VIEW user_names AS SELECT name FROM users;

void db migrate          # ✓ Applied 2 migrations.
void db reset --yes      # ■ migration 0001_view.sql failed: Table 'user_names' already exists

Cause

Reset lists only tables, drops them, and then runs every migration file again. Views are never listed:

void db reset (MySQL)
  list tables WHERE table_type = 'BASE TABLE'   -> users, _void_migrations
                                                   (user_names is a VIEW, so it is not listed)
  DROP TABLE users, _void_migrations              MySQL keeps the view
  CREATE TABLE IF NOT EXISTS _void_migrations     empty, so every file is pending
  0000_init.sql                                   ok
  0001_view.sql  CREATE VIEW user_names ...       -> ER 1050 already exists

Line numbers are on main, b58300f:

• MySQL: packages/void/src/cli/db.ts:606-618 runs
  SELECT table_name AS table_name FROM information_schema.tables WHERE table_schema = DATABASE() AND table_type = 'BASE TABLE',
  then DROP TABLE IF EXISTS `<name>` for each row. MySQL does not drop a view when its base table is dropped.
• PostgreSQL: packages/void/src/cli/db.ts:584-589 runs
  SELECT tablename FROM pg_tables WHERE schemaname = 'public', then DROP TABLE IF EXISTS "<name>" CASCADE.
  CASCADE removes views that depend on those tables. A view that does not depend on one stays.
• Reapply: db.ts:622 → packages/void/src/migrations/runner-mysql.ts:163-179. For PostgreSQL, db.ts:590 → runner-pg.ts:52-67.
• void db seed calls the same resetDatabase() (db.ts:654).
• D1 / SQLite deletes the database file instead (db.ts:631-633), so it is not affected.

Suggested fix

• MySQL: query information_schema.tables without the BASE TABLE filter. Run DROP VIEW IF EXISTS for rows of type VIEW, and DROP TABLE IF EXISTS for the rest. Keep skipping _void_migration_lock.
• PostgreSQL: also drop the views and materialized views in public (pg_views, pg_matviews) with CASCADE.

Workaround today: drop the view by hand, then run void db reset again.

🤖 Generated with Claude Code

https://claude.ai/code/session_0123r5HnanmW2VUrZ7VWDufH