Discussion on Database plans/ORM

[adenner]

For ease of development, any objections to using Entity Framework and sqlsql lite? By using ef it will make it far easier to switch to a "real" database like mysql, mssql et.al.

[benrick]

No objections at all. In fact, I was going to recommend we start with SQLite during development regardless of how we store data long-term. In-memory and SQLite have basically no up-front cost or setup, which is perfect for us, since we don't have dev containers, etc. configured yet.

[kirkbrauer]

I think long-term we should probably look into PostgreSQL and Npgsql + EF Core. I'm currently using this in production, it's a super powerful solution for most general use cases. However, SQLite is a great place to start development, single file DB for the win!

[edmistond]

I'll reiterate my somewhat more ephemeral Mastodon comments here - I think SQLite's great for getting some code down to start, but I'm not sure it would work well if we need to implement background workers outside the main process... which, just based on what I know so far about ActivityPub and Mastodon generally, I am assuming we will.

I agree that Postgres is a great choice for long-term, though. I wouldn't want to tie this to Azure (or AWS) since I suspect that would be a disincentive for a lot of folks, which kind of argues against something like CosmosDB.

[benrick]

Yes, I think SQLite is a good starting point, but the background workers will require some kind of storage that isn't as tied to the file system.

And I agree that we don't want to add anything that requires any specific provider (Azure, AWS, etc.). Someone should be able to host this on a PC in their house if they want to (no idea why they would).

[adenner]

Both azure's cosmos and aws's aurora have compatibility and support for postgres in one way or another. For the homelab situation there is always k8s or docker compose.

[kirkbrauer]

@adenner there's also other options available like Azure Database for PostgreSQL and Amazon RDS for PostgreSQL. Looking at the docs for Mastodon, it appears that they use Postgres and Redis for their own data store and cache.

Perhaps it would be a good idea to make this project schema-compatible with any original Mastodon Postgres databases?

Ruby on Rails has a similar ORM to EF Core, so it wouldn't be too difficult to replicate their migrations

[benrick]

Yeah, I think there's a good chance postgres will be our answer. Obviously, it's also the choice mastodon uses.

[benrick]

Yeah, I was figuring that we'd either need to support a mastodon database directly, or have some kind of conversion to/from in order for us to be compatible.

[benrick]

Have a look at #18 @adenner and @kirkbrauer . I'm happy to have us start with SQLite or Postgres. Whichever someone wants to put in first, we can use.

[kirkbrauer]

@benrick you know what, the more I think about it, I think we should just start with Postgres. That would make our lives a lot easier and makes it much easier to run regression tests comparing it to the original DB when ready.

[edmistond]

I'm fine with either. Postgres runs great in a Docker container for dev purposes so it shouldn't be a heavy lift to set it up.

[benrick]

Yeah @kirkbrauer , I was leaning the same way. I had Postgres installed in PR 18, but removed it before sending the PR. 😆

[benrick]

The other aspect of this is ORM discussion. I do like Entity Framework, but I'm thinking about how quick a lighter solution can be. Any of you work with Dapper?

[edmistond]

@benrick I've used Dapper on and off since it was a single file you just could pull into your project... some might say I've been doing this stuff for too long now. 😂 Big fan of it though, it made my life a lot easier.

I had kind of the same thought about performance; the one option I could see is taking a more CQRS-y approach where you have separate read/write EF contexts and all the reads go through AsNoTracking()... but that gets to be a lot to set up and maintain, and if I'm honest I feel like taking a CQRS approach also brings in Mediatr and then you end up going down a huge rabbit hole of indirection. Nice library, just feels like overkill sometimes. :)