Proposal: Manual database management

[Minivera]

Background

Database are currently handled by adding a migrations file in the migrations folder, once that's done, a database is created and you can access it in that system or other systems. This can cause some confusion, for example:

• I am starting to write code in a system, but didn't add a migration file. I might try to start connecting to that database with an external GUI client to experiment with my SQL, only to find the database doesn't exists and I have to create a migration file.
• I'm trying to setup the Ent guide and run into a weird problem where I need a database to generate the first migration file, but don't have a migration file. I need to create an "empty" migration for the database to be created.

This behavior can be confusing, one might expect the database to always exist if I create a system, or at least if I try to connect to a database, even without a migrations file. In addition, this might not work long term if Encore start support NoSQL database since those don't necessarily have migrations.

Proposal

@eandre had a proposal to give us a pattern similar to PubSub where we could create a new database object, which then returns a sql driver object, like this:

package users

import "encore.dev/storage"

var usersDB = storage.NewDatabase("users", storage.WithMigrations("path/to/migrations"), ...) // Maybe a "storage.WithDriver" to allow MySQL databases?

Which can then be used across the package, Named could potentially be replaced by this using a function like ConnectToDatabase.

This is a rough idea for this proposal, it very much could be improved.

Backwards compatibility

One big issue I can see with this approach is how to support existing applications and use cases that have gotten used to the current way of managing databases? I could see two path forward for supporting this feature for both old and new codebases.

• Opt-in: To get the new way of creating databases, you only have to start using it in a system. Once you use the NewDatabase function somewhere in that system, Encore disables the automatic database feature using migration files and lets you handle that yourself. When migrating a system with the old database logic, it could show a warning if creating a database with a different name than the existing automatic DB and could connect with the old DB automatically.
• Opt-out: The new system is the default once it ships, but the automatic db creation can be enabled on the system level with some kind of settings (Maybe in the encore app file or the dashboard). Old systems have this setting enabled by default and can be migrated by disabling this option and starting to use the new functions.