Support for other databases, specifically Postgres.

[tombert 17]

Hello!

 

I love Emby, but the limitation of SQLite makes it impossible to scale this across multiple nodes.  I think it would be better for anyone if there were an option to use Postgres (or MySQL or Mongo or whatever) so that we could scale it to multiple nodes, as I think this would put less strain the devices when multiple users are streaming and transcoding.

 

I thought about adding this myself (I'm a software engineer), and I think I could, but from what I'm seeing it looks like it would be a relatively big ripping of the `ManagedConnection` by making it use some kind of `IDatabase` or something. 

 

I'm not opposed to doing it, but currently the only language I use for work is F#, and while I would be happy to write a Postgres driver in a super-duper-functional style, I don't know that that would be approved upstream, and my OOP experience is a bit out of date. 

 

-Thomas Gebert.

 

 

[Luke 37094]

Hi, yes this is something we've talked about in the past but have never been able to look at it. 

[tombert 17]

@@Luke

 

How receptive would you be to a pull request that does the IDatabase setup that I mentioned before?  I started some preliminary work on that last night; if you are ok with allowing the inclusion of the Postgres .NET driver (and only having it used when an environment variable is supplied), I think I can have something ready by Thursday or Friday.

Edited April 3, 2018 by tombert

[Luke 37094]

I'm ok with it, although we'd probably isolate postgres support into a plugin rather than integrating into the core.

[tombert 17]

I'm not entirely sure how you would use it in a plugin; I want to use Postgres as a configuration database, similar to what SQLite is accomplishing now; aren't plugins installed after the server is already configured? If it's installed after the fact, I do not believe that that would address my scalability-across-many-nodes problems.

[Luke 37094]

It would mean that you'd initially run through the setup wizard based on sqlite, install the plugin, then enable the alternative database.

 

That's the pattern we've been following for pretty much all of our modular features where you can swap parts in an out.

[tombert 17]

I'm not opposed to that, but it looks like SQLite is hard-coded in a lot of the project; would I still need to have an upstream merge for some kind of database abstraction to make this work? Or is there some mechanism to which I could tell it to ignore Sqlite once the Postgres DB has been initialized?

[tombert 17]

Also, if it will be an external plugin, would there be any rule saying I couldn't write it using F#?  I'll make sure it builds with .NET Core of course.

[Luke 37094]

Yes exactly, abstraction in the core, implementation in the plugin. The entire database classes already have an interface so you could already make use of that. It means you'd essentially have to start with a complete copy of the DB class and then modify for the new db engine.

 

The database indexes and joins have all been designed based on what sqlite supports so this approach would allow those to be modified for the new db engine rather than trying to do a one size fits all.

[tombert 17]

I'm ok with all of this, though I'm not entirely sure how I would go about telling the server to use my Postgres implementation of the database instead of the Sqlite one. 

 

Also, even if I get all of this working correctly, if you still have the hard dependency on Sqlite for the initial loading of the configuration, wouldn't I still get locking issues upon start with a multi-node deployment?  Would it be prudent to add some kind of check for an environment variable on boot to bypass Sqlite if it's true? 

[Luke 37094]

There would be have to be a server setting added to identify the alternate db to load instead.

[tombert 17]

What would be the best way in which to handle that?  Once the plugin is loaded, maybe read in an option environment variable like `DB_ENGINE=postgres` and `DB_CONNECTION_STRING=blah`?  If the variable isn't there, fall back on Sqlite?

[Luke 37094]

No, it would just be a regular server config setting. That part is better for me to handle. For your purposes you can just hardcode it to load postgres.

[tombert 17]

Ok, forgive a tad bit of ignorance on this front, but aren't the server config settings still using Sqlite, and again, wouldn't it lead to the same locking problems with multi-node deployment?  Are the configs stored in some kind of text file or something that I'm not aware of?

[Luke 37094]

No, the server config settings are not tied to sqlite. They are in config files.

[tombert 17]

Oh, ok, then I think I have enough to get started.  Thank you for your help.

[tombert 17]

Sorry, I have one more question on this; again, since I am doing an external, presumably-decoupled project here, is there any objection to doing it in F#? The only reason being that I am substantially more familiar with the idioms of the language and I think I can develop with it faster.

[Luke 37094]

I would think that should be fine.

[tombert 17]

Awesome, again, thank you for the help.

[ebr 14921]

https://emby.media/community/index.php?/topic/31103-mysql-support/?hl=mysql

[Dibbes 431]

@@tombert is there any particular reason you chose Postgres over others?

[mastrmind11 717]

@@tombert is there any particular reason you chose Postgres over others?

I'd imagine because it's leagues above MySQL from both performance and scalability standpoints, and yet remains open source.

[tombert 17]

@Dibbes  I have no issue with MySQL or Mongo or Couch or any database as long as it can handle more than one user at a time. 

 

That said, Postgres has the advantage of a) being open source, relatively fast, c) having JSON as a first-class type, which, since Emby stores its configurations as JSON within SQLite, makes Postgres an especially nice fit. 

 

That said, if I manage to pull this off (which I think I will; I'm making good progress implementing the classes in F#), I think it will be relatively easy to fork it and add support for any database you'd like.

[Luke 37094]

 

 

since Emby stores its configurations as JSON within SQLite

 

Not exactly. Configuration settings are stored in config files. Library data is stored in the database, and most objects have individual columns for all data members rather than json blob.

[tombert 17]

Not exactly. Configuration settings are stored in config files. Library data is stored in the database, and most objects have individual columns for all data members rather than json blob.

 

I admit I was speaking a bit too broadly; I was referring to https://github.com/MediaBrowser/Emby/blob/1e9b6c17fe6423f37b1a347f3257718a4395c383/Emby.Server.Implementations/Data/SqliteUserRepository.cs#L95