Nikeza Server Data Access Strategy

[odytrice]

Data Access Options for Nikeza

Technology	ADO.NET	E.F Core	SQLClient TP	Entity TP
Automated SQL	No	Yes	No	Yes
Type Safety	No	Yes	Yes	Yes
Entities Free	Yes	No	Yes	Yes
Database Migrations	No	Yes	No	No
Database Free Builds	Yes	Yes	No*	No*
Atomic IO	Yes	No	Yes	No

*There is a way to prevent this but it requires manually building a dbml file and check-in that into source control

Automated SQL - This means that the library writes the SQL on your behalf leading to simpler queries thats easy to read
Type Safety - This means that the compiler can check for errors in queries
Entities Free - This means that the library does not require that you create classes/records for Each table in the database.
Database Migrations - This means that the library manages schema changes and can automatically do updates to the schema when the application is deployed to a new environment without having to run scripts for each deployment Environment
Database Free Builds - This means that during build time on a build server, you don't need to have a live connection string to a database server for builds to work
Atomic IO - This means that a single IO can be scoped to exactly one function. without needing to pass some sort of context around

No SQL Option

There is also the no-sql option. They solve most of these problems but there is a bigger discussion to be had there and I don't have much experience with them especially from F#

[yelluw]

What's the next step forward?

[bizmonger]

As discussed, we are going to go MS SQL Server and ADO.Net with the option to swap out ADO.Net as the Data Access framework as tooling improves for .Net Core over time.

As a result, let's throw up MS SQL Server and add some tables that will support Nikeza/client/app/server.elm.

Examples include:
Followers
Subscriptions
ContentProviders
Sources
Topics
Link
ContentProviderLinks
LinkTopics
Platforms
ContentProviderPlatforms
Profile

Let's close this issue once we have an instance of SQL Server running with the relevant tables.

[yelluw]

I have no working knowledge of ADO and last time I touched MSSQL.

Can we define who will setup the database instance?

Are there any references that you recommend to get started with ADO?

[yelluw]

Ok, so Im setting up MSSQL locally with the official docker image from MS found here: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-docker

Then I'll use that to work with ADO with @bizmonger :)

[bizmonger]

Pablo, I recommend your journey of leading this effort to start with TDD.
Specifically, consider building a suite of failing integration tests.
Then overtime, getting all of those tests to finally pass.

Because, we are initially using ADO.Net, integration tests are essential to identifying schema changes.

[bizmonger]

I will start building this out tonight in hopes that I can begin integrating the client and server on Monday.

[yelluw]

Created this gist to flush out the Schema

https://gist.github.com/yelluw/d44bd2456c75c931051402414bfd20fb

[bizmonger]

I created the database.
Still need to wire-up the relationships.

[bizmonger]

[odytrice]

Is it possible to get the schema file so that I start work on this today?

[bizmonger]

I can publish the schema later today after I get off work.
Might need some help figuring out how to though.
Hence, I'm not a DB person.

[bizmonger]

Nikeza backup attached.
Nikeza_db.zip

[odytrice]

Database Version for the Backup is a bit too high, You can either redo the backup for a lower version or preferably you can just generate the SQL

[bizmonger]

Attached: SQL Script for database.

Nikeza_db.zip