Using databases with deploys on each commit

Question

It's become popular with modern cloud deployment services like Vercel, Netlify, Linc, and so on to deploy web apps on every commit for pull requests. This makes a lot of sense for frontend code.

It's also become popular, however, with frameworks like NextJS, to deploy one's API in the same codebase and infrastructure as their frontend code. But APIs often require a database to function, and databases often change schemas with migrations. To me, this means that preview deploys in a frontend/API monolith could often fail if the data model changes on a branch.

How have others handled this "Preview Deploys" development pattern, when databases get involved? Is there an elegant way to spin up separate database instances per preview deploy, that match the schemas/migrations defined per-branch, and work well with these very distributed, often serverless hosting providers?

Some initial thoughts

• is there a fast and cheap way to spin up a DB from a template on deploys (based on a project's main branch), run migrations of a particular branch on it, and have a Vercel preview deploy somehow discover that new DB instance? What if such a database has a lot of data, can it still be kept snappy? Maybe with Docker images?
• maybe a checked-in sqlite database might make sense to easily support preview deploys, so long as it's assumed that preview deploys don't experience much parallel access? but unless you're using sqlite as your prod DB, it creates a significant difference between preview and production environments
• Or perhaps coupling one's data model to preview deploys is a bad idea altogether and data access ought to be kept separate from this kind of frontend code.

Curious to hear what other people have done.

Answer 1

This was posted quite some time ago but now you have more and more db providers that provide branching automation for that exact reason.

neon.tech or planetscale.com

are 2 of them

Answer 2

A couple ways I could think of (and use)

Environment variables

In vercel for example, you could configure individual production, staging and development environment variables.

You could set the staging database_uri environment variable to an in memory (no filesystem access for serverless at vercel) sqlite3 database for any manual testing/ demonstration and and use a different variable in the production environment

Branches

Another way is to use main or master as the development branch and have a separate production branch. Configure vercel (or your platform) to build only when pushed to this production branch.

While you won't actually push to this branch, you'll be pulling from the main (or any other default) branch and merging into the prod one.