Improvements to onboarding developer experience

[petejodo]

Currently, creating/dropping databases seems to be outside the scope of drizzle-kit. I get this impression from drizzle's documentation where setting up a database is a manual job using docker run. I believe adding such functionality opens doors for improved developer experience, especially when new developers get onboarded to a project using drizzle.

First, here are some potential requirements a project might have:

• use more than 1 database (and therefore multiple calls to drizzle(conn))
  • this may be because of legacy reasons or a separation of concerns where the separate databases store different uncoupled data
• for a given database, local development of that project requires 2 separate database instances, one for dev and one for test
  • for example, given a project MyApp, a developer will want a my_app_dev database and a my_app_test database

Given those requirements and as a new developer on a project using drizzle, I want to be able to pull the code for that project, run docker compose up -d, npm i and then npm run setup where setup sets up my_app_dev and performs migrations. The setup script could look like "setup": "drizzle-kit create && drizzle-kit migrate" maybe with an additional seed script along w/ it. At this point, as a new dev, I can already run npm start and jump right in.

Then for testing, the setup script can also be reused but with a NODE_ENV=test that causes it to be configured to setup the my_app_test test database. This allows a reliable way to ensure tests are always run w/ the test database setup and fully migrated e.g. "test": "npm run setup && jest".

I've seen projects configure postgres containers w/ init scripts that spin up the necessary databases but that causes a duplication of knowledge since the databases get hardcoded into those scripts while all the information about a database is already contained in a drizzle.config.ts. Not only that it makes it inconvenient, like down/up the container to re-run the init script, for example. I think part of the issue is associating the postgres image as setting up a database when really it's setting up something higher level, a relation database management system. The setting up of databases specific to your project should be a separate step entirely that just depends on that step of setting up the RDBMS. This then also works for projects not using docker.

I should add that everything I described I got from Ecto, a similar tool as drizzle from the Elixir world which has some of the best DX I've ever come across.