OK, so I'm running into some challenges with my previous post, but wanted to ask a clear question apart from that. Can a database be shared between Meltano "projects?" It's as simple as that. If we have separate projects in Meltano, can we share a database? Will they step on each other, as far as job_id's, schedules, or anything else? Essentially the important data that is saved in the db as far as I can tell is the state, in the job table. The rest of the db appears essentially blank. The schedule is not even in the db, it is taken from the meltano.yml file. So I think it is currently "safe" as long as different projects don't have conflicting job_ids, but don't know the future direction and use of the DB.

I think you can just specify a different postgresql database (same server, different db) in the

MELTANO_DATABASE_URI

environment variable at runtime, and there would be no chance of stepping on each other https://meltano.com/docs/settings.html#database-uri

True, but I don't necessarily want 50 databases on my postgres server either. The data stored is so small that I'm actually thinking about mapping a directory in an EFS filesystem to a mount point and just storing the database there as a SQLite DB. I'm thinking, generally, Meltano needs a higher-level concept and pull the system database up to that higher level, above individual projects. The conundrum is that some people need to be able to break out projects per pipeline / internal company project, BU, or other structure, while there should be an infrastructure layer where all projects can run. Unless I'm missing something that does currently not exist.

@fred_reimer unless I'm missing something, it's perfectly possible to have different meltano projects connect to the same database (same instance, same db) using the same connection string, and even run pipelines concurrently. As long as you don't use the same job ids in different projects you should be fine. Autoincrement keys are handled server-side, so it should be like any user-facing OLTP application. I'm not sure SQLite can handle the concurrency, though, so I'd opt for Postgres instead.

The SQLite would be per-project, different database in different directory if needed. I agree it looks like there should not be a problem currently with using the same postgres db for multiple projects, but I'm not sure what the plans are for the db and how it is used. There is only one table currently being used.

@fred_reimer thank you for being mindful of any future plans for the db 😄

There is only one table currently being used.

That's correct. Makes it two tables if you store plugin settings in there (i.e.

meltano config my-tap set my_setting 123 --store=db

). The other tables are mostly used by the UI for authentication, etc.

I'm not sure what the plans are for the db and how it is used.

Looking at our roadmap, I don't see anything that would impact your use case. cc @taylor