Support running multiple instances on v3

[GerryWilko]

Is your feature request related to a problem? Please describe

Currently we are unable to reliably use Nuxt Content v3 with Nuxt Test Utils with multiple parallel workers as Nuxt Content adds content to a static set of SQL tables.

The following reproduction demonstrates the issue:
https://github.com/GerryWilko/content-sql-repro

Describe the solution you'd like

I would like to be able to safely run Nuxt Content v3 with multiple Nuxt instances connecting to the same DB.

During module initialization all existing content tables are dropped:

content/src/module.ts

Lines 253 to 254 in 96a9c4a

	// Remove all existing content collections to start with a clean state
	db.dropContentTables()

If we are already dropping all the content tables on module initialization we could just use a set of scoped tables within the same DB with a unique ID generated on module init. This would allow for each Nuxt instance to only interact with its own tables.

This solution may be problematic for users that are not using the built in SQLite server as they will end up with many duplicate copies of their content files. However at present if they are running multiple instances then each one is perhaps unnecessarily clearing the content dbs and they may be hitting this race condition of instances clashing with each other on start.

Describe alternatives you've considered

Alternatively we instead stop calling dropContentTables on module initialization and instead perform a clean-up operation of orphaned content files in each content table after content has been injected into and overwritten what is already present.

Additional context

[GerryWilko]

I was thinking more about this over the weekend. Is the main purpose of the dropContentTables call during module initialization to ensure that content files are always up to date?

If so would it not be better to hash the collection before creating the tables and then add the hash to the end of the table name so it would end up looking something like _content_something_xxxx. Then you can drop all collections not matching the current expected table name with hash and create any missing tables from the source files.

This could provide a much more efficient startup and would resolve the issue with multiple running Nuxt instances.

[case-fastly]

@GerryWilko I think I'm running into this as well ("Nuxt Test Utils with multiple parallel workers"), wondering if you've found any workarounds?