2

I'm building my first open source project. It's an API build with C#.

My project has layers

  • Api itself to receive requests
  • Services library, for logic.
  • Database library, for db operations.

I'm using Dapper as orm and i'm creating, altering tables in the database manually. If anyone ever downloads and runs my app, they'll get exception because their own database doesn't have tables i created locally.

What's the proper way of providing up to date db schema, info in opensource and/or c# project?

Christophe
  • 74,672
  • 10
  • 115
  • 187
Sefa Ümit Oray
  • 249
  • 2
  • 8
  • https://en.wikipedia.org/wiki/Data_definition_language – Robert Harvey Jul 30 '16 at 21:30
  • 1
    _i'm creating, altering tables in the database manually_ So you want us to figure out solution your own inability to use your tools properly? There is reason why SQL allows creation and modification of tables. – Euphoric Jul 31 '16 at 13:05
  • 1
    @Euphoric I'm asking for correct way to do things. This is called learning and this is what this site is for. – Sefa Ümit Oray Jul 31 '16 at 13:54
  • 1
    @Sefa consider adjusting your expectation of what this site is for. [As explained eg here](http://meta.stackexchange.com/a/243649/165773) "It is **not** a learning environment. It never was. If it were, you would see tutorials, recommendations and more here. You don't see them as what we are looking for is not to educate people - it is to give them direct answers to their specific questions..." – gnat Jul 31 '16 at 14:18

2 Answers2

1

I understand that your API works with a database that is outside the scope of your project but that has to comply with your API requirements.

It's not clear to me if:

  1. you "own" the schema and just leave the responsibility of database management (including choice of DBMS) to the users of your API.
  2. your API is supposed to work on a third party schema and just has some requirements on this third party schema.

In addition, you're using Drapper. So you access the database via an IDbConnection but you may not be sure about the client used (e.g. Sql Client, ODBC, OleDB ...).

Case 1: You own the schema

If you have an SQL schema (you speak of "tables" and "columns"), the best approach would be to distribute an SQL script. Eventually you could provide several scripts to support major DBMS and their native clients;

As you're running an open source project, I'd not be surprised if some of your users would provide you with scripts adapted to other DBMS that you have not thought of.

Case 2: you don't own the schema

I understand then that your API is supposed to be used by third party developers, that will use it to add functionality to their own database.

There are two options:

  • document your assumptions and requirements (i.e. the manual steps that you had to do) and let your users adapt their schema. After all it's their responsibility, if they want to use your API.
  • provide some API functions or an admin tool to adapt the schema automatically, analyzing the objects in the database and sending commands to update it.

The second would be much nicer. But as you only have and IDbConnection, you could be somewhat constraint about the access you have to the database. I'd therefore suggest to start with the documentation approach, and provide as experimental faclility the admin tool using an SqlClient. Again, with the princpile of open source, you could expect some feedback and adaptations of this tool for other DBMS.

Christophe
  • 74,672
  • 10
  • 115
  • 187
0

You could try RoundHousE: "a Database Migration Utility using sql files and versioning based on source control". It runs under .NET in Windows and "currently works with Oracle, SQL Server (2000/2005/2008/Express), Access, MySQL, SQLite and PostgreSQL".

There are similar programs out there.

Juanga Covas
  • 119
  • 2