Backend brainstorming

[mohe2015]

From the standpoint of making it easy for contributors, there are basically three choices: node.js / javascript backend, php (but saner) or python. I do like .net core and have some relationship with mono and the .net ecosystem, so I might be biased there. But I also know that it is turning some people off. So my final tradeoff would be python3/django which makes things easier for more contributors but is not statically typed or c# .net core MVC API with Entity Framework which makes all calculations that need to be done on specific types much easier. Or stick to php, but I really, really, really dislike practically all php ORMs.

I'm really against python3 because it's not statically typed and I believe this to be the major advantage when reimplementing the database interactions. I haven't written database code in python3 yet though so maybe it's not that bad.
For node.js I only found one ORM that I think I would like: https://typeorm.io/#/ also because types are really important in my opinion.
For PHP I didn't find one that I liked.
For c# .net core MVC API with Entity Framework I've never used it before but if it's https://docs.microsoft.com/en-us/aspnet/core/data/ef-mvc/intro?view=aspnetcore-5.0 (and following pages) it seems to look okay but i'm not a particular fan of "forcing" us into one vendor.
Still this might be the best option especially if you know it already.

But in all reality: Before attempting to fundamentally change the backend, we need to document and understand the current database schema, and all API endpoints. I really want to avoid changing existing API endpoints while re-doing the frontend, and even if we redo the core at some point, I don't really want to throw away the current API all at once. At some point it would be sensible to re-do some of the endpoints, but that will include versioning the new api to /api/v2/<endpoints>. So, I think we should open a discussion outside of the PR of what to do with the backend (it's a bit off topic here), because I'm sure there are more things to consider that I don't think of right now.

Originally posted by @mistressofjellyfish in #25 (comment)

[hackathi]

For c# .net core MVC API with Entity Framework I've never used it before but if it's https://docs.microsoft.com/en-us/aspnet/core/data/ef-mvc/intro?view=aspnetcore-5.0 (and following pages) it seems to look okay but i'm not a particular fan of "forcing" us into one vendor.

Yes, that is the docs for EF core + MVC (non-API, traditional pages).

re: typeorm - it still does and will forever suffer from the inherent type uncertainty that comes with javascript. Even typescript doesn't help there too much, because it needs to be compiled down to plain javascript at some point. Compared to what is possible with LINQ queries in c#, the query Interface seems to rely on strings for the most part. I'm not too fond of that, because it introduces new overhead while developing to keep static analyzers happy.

For PHP I didn't find one that I liked.

I agree. They all suck, and that is mostly PHPs fault. Then again, most of php's tooling sucks.

I'm really against python3 because it's not statically typed and I believe this to be the major advantage when reimplementing the database interactions.

While python is dynamically typed, it doesn't have type coercion, so you need to explicitly manage your types / type conversions. djangos ORM does do that for all backends and Request bodies while deserializing; so we should be fine there as long as our models are typed correctly. However, there are still certain situations where the result type might change:

Python 3.9.5 (default, May  4 2021, 03:29:30) 
[Clang 11.0.0 (clang-1100.0.33.17)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> a = 5
>>> b = 2
>>> c = a / b
>>> type(a)
<class 'int'>
>>> type(b)
<class 'int'>
>>> type(c)
<class 'float'>
>>> type(4/2)
<class 'float'>
>>> 4/2
2.0

So we still need to be careful there. Other than that, I have done some web projects with python that handle large-ish traffic, and it does run OK for the most part. The django developing experience could be a bit better, but for the most part it works. Still I have to admit that I don't feel as productive as with net core.

Re C#: Please bear in mind my past personal involvement with mono, ASP.net MVC and that it is still my preferred language and tooling for personal or work projects where I need something something API. The development speed, the robust migration handling and the general tooling are stellar, and devloping on not-windows with VS Code, the roslyn language server and stuff just works. Many repeated tasks scaffold very easily, a Code-First approach to databases and ORM mapping ensures minimal work when adding migrations. It can also automatically generate an openapi.json spec. And last but not least there is SignalR, which is a framework for realtime server-to-client communitcation (via websockets or whatever). This could be useful for not-grocy in handling database reloads, so the server can tell the client that some data has changed and it needs to reload (Or to handle some of the 2D barcode recognition server side, because it just doesn't work in the browser). And it's statically typed, and - believe it or not - fast.

So, from a correctness / ease of use / write minimal code for maximum result, c# yes please. And also, it seems to play nice with a desktop version.

So, I know that this probably sounded like a sales pitch and I definitely don't want to pressure you into learning this because I think it's cool.

i'm not a particular fan of "forcing" us into one vendor.

To be completely honest: I have never seen a project changing their ORM, templating engine, or other core component start-to-finish not ending up with a full rewrite. The situation is about the same in the djangoverse, and there are other ORMs than EF core for .net. Stack Exchange for example currently uses Dapper.

[mohe2015]

So, I know that this probably sounded like a sales pitch and I definitely don't want to pressure you into learning this because I think it's cool.

I'm totally fine with learning some new stuff. Also I actually think it looks quite good.

Let's see if somebody else has an opinion but otherwise I think this will be our choice otherwise.

i'm not a particular fan of "forcing" us into one vendor.

To be completely honest: I have never seen a project changing their ORM, templating engine, or other core component start-to-finish not ending up with a full rewrite. The situation is about the same in the djangoverse, and there are other ORMs than EF core for .net. Stack Exchange for example currently uses Dapper.

I was thinking more about companies dropping their products because they're not interested in them any more or something but ASP.NET Core seems to way more popular than i thought.
https://insights.stackoverflow.com/survey/2020#technology-web-frameworks

[hackathi]

https://github.com/CodySchrank/MTT

Also this.

Edit: should've added some context: basically, MTT is a msbuild plugin that autogenerates typescript interface definitions from C# Data Transfer Classes. That would ensure that the frontend type definitions always stay up to date with what's in the backend. Would be nice.

[mohe2015]

That's also something I've thought about and it has been a reason for me in the past to use typescript on the server side. But that's nice if there is already a tool for that.

[mohe2015]

https://github.com/mohe2015/not-grocy-server/blob/schema-doc/docs/schema.md is what I have in mind for the initial schema documentation. The format is a bit lengthy whitespace-wise. What do you think?

[hackathi]

Format-wise I think it's okay. Maybe a markdown table would be better for the entities.

Do you want some feedback on the content too?

[hackathi]

Also, re: data import: EF Core can reverse-engineer a given database schema to entities, so that would be a good starting point for a DB importer.

[mohe2015]

Format-wise I think it's okay. Maybe a markdown table would be better for the entities.

Mmh a table - will try it out and see how it works with longer descriptions. But should work well.

Do you want some feedback on the content too?

Not yet except if you have something that I should know for the future. The entities* tables are still really WIP and I don't fully understand them yet. But I will continue this later today.

[mohe2015]

Also, re: data import: EF Core can reverse-engineer a given database schema to entities, so that would be a good starting point for a DB importer.

Can it also reverse engineer relations? Probably not?

But yeah the rust one could also do the basics of that (although you have to do the mapping to Rust types by hand then as there are multiple choices and probably also different ways (as it's a lower level and you could probably e.g. use a pointer or "inline" a relation entity)

The problem is that some types are wrong (as it doesn't introspect the actual values) and foreign keys are completely missing.

[hackathi]

Really, the only thing that's burning under my nails is a remark to your comment about the API key table:

remove and make api_key the primary key

While api_key needs a UNIQUE / NOT NULL constraint, I'm hesitant to make it the primary key. They are strings, fundamentally; and if API keys should have different scopes (for example: read-only, read/write, read/write but not edit master data, ...) there could be a scenario where a join to this primary key is needed. And string keys don't mix with joins too well. So, in my opinion, an integer primary key doesn't really hurt, but does help with performance. It probably won't be an issue within not-grocy, though.

Can it also reverse engineer relations? Probably not?

If foreign key constraints are set, it does. If not, no.

The problem is that some types are wrong (as it doesn't introspect the actual values) and foreign keys are completely missing.

No surprises there. Really, I see it more as a shortcut for an importer. As long as the old database is selectable, everything else can be a one-off-conversion. If that takes some time and isn't particularly efficient, so be it. It's a thing you'll probably do once, after all.

[mohe2015]

Really, the only thing that's burning under my nails is a remark to your comment about the API key table:

remove and make api_key the primary key

While api_key needs a UNIQUE / NOT NULL constraint, I'm hesitant to make it the primary key. They are strings, fundamentally; and if API keys should have different scopes (for example: read-only, read/write, read/write but not edit master data, ...) there could be a scenario where a join to this primary key is needed. And string keys don't mix with joins too well. So, in my opinion, an integer primary key doesn't really hurt, but does help with performance. It probably won't be an issue within not-grocy, though.

I would've probably thought about changing the type to uuid (which only postgresl supports so wouldn't work) or bytea (which is likely inefficient on sqlite3 and I don't know the performance on postgresql as it's variable length which is not what we want). Also there are hash indexes which may perform better than binary tree indexes.

But I agree - this is probably not a good change depending on how much we need to join on it.

[mohe2015]

Tables in markdown (not rendered) are really ugly - I will look into writing an OpenAPI 3.0 specification (so just the models for now). It's probably not perfectly fitting but do you think that's a good idea?

Current state is: https://github.com/mohe2015/not-grocy-server/blob/schema-doc/docs/original-schema.yaml
You can look at it by pasting it in https://editor.swagger.io/

[hackathi]

I haven’t worked with that, so I don‘t have an opinion, but anything where we can get an overview of what is there is fine by me. Once EF Models are written, they are the source of truth anyways (or at least should be)