Allow database metadata to be saved to a local file

[mvkara]

Would really like it if this were possible; I might be able to contribute if required. I would like the database to be a dependency only during the first build against it on my dev machine; having the database as a hard dependency on the build is a showstopper to using this library for us. Some other type providers allow this via a local schema file argument to the type provider. This helps with CI builds where you may not have access to the database to get the schema from (I.e. I can get it from a local data file instead). It also ensures builds can be repeatable by keeping versions metadata alongside the code.

Type dB = sqlcommandprovider < query, connectionString, LocalSchemaFile = "dbSchema.cache" > If the file does not exist then use the database and run the query to get the types to be generated else use the file.

[dmitry-a-morozov]

This is not a trivial thing to do. But I promise to think about it.

[Lenne231]

This would be really nice! Are there any other good solutions with CI builds today?

[dmitry-a-morozov]

The only solution as of today is to have actual database. Sql Express or LocalDB are not that bad to use for CI.

[Lenne231]

new SqlCommandProvider<"...", ConnectionStringEnvironmentVariable = "MY_DB_CONNECTIONSTRING">()

Is it possible to have something like this? This way it would be possible to use another connection string on the build server at compile time.

[dmitry-a-morozov]

I was thinking about it. Maybe it's not a bad idea. What do you use at design time: connection string literal or config file?

[Lenne231]

I'm using a connection string literal, but i thought about using a config file with a connection string and to replace it using a build script on the build server as a workaround.

[davidtme]

I would love to see this go in just because we use a build server that can't access a database so it's a bit of a show stopper.

[btrepp]

It would be super neat if this was even instead of the normal connection. Perhaps using the dmblfiletype provider as inspiration?

[erlis]

Any update on this? It's almost a year after the last comment.

Any suggestion on how are you guys (the users) using the TP with a CI that doesn't have connection to your database?

[vasily-kirichenko]

We deploy fresh copies of all needed DBs before building the solution.

[smoothdeveloper]

The build script is checking if database scripts changed against dev database running on sql localdb, and recreates the database if needed before building code using the type provider.

@erlis implementing this is basically an overhaul of the whole type provider, I think for most users it is doable and even recommendable to have a build step where the database is deployed.

[erlis]

thanks for the replies, I'll need to go the sql localdb route, I'm working with a database that I have on my machine but is not a localdb, I guess is not going to be that hard to implement.

[btrepp]

I shifted to not using this library because of the connected nature. My workflow ends up being

Write migrations using DbUp. Run them in my app (myapp.exe migrate) Run sql metal to get a dbml file. Commit the dbml file + use the dbml type provider.

I did play with the idea of making scripts to up localdb etc, but it all got way too difficult :(

[dmitry-a-morozov]

Why don't you use Docker to host Sql Server instance accessible during compilation phase? It's so easy to do these days.

[AndreasHassing]

@dmitry-a-morozov I don't think that everyone will agree on that being a great idea.

If I've done my testing right, I should not have to connect to the database at all.

My utmost respect to this implementation, but I feel that it's more sane to keep a cached version (DBML) checked in, as mentioned by @btrepp - except for the fact that you now have an additional artifact which you need to keep synced 😄.

In case anyone is interested: This Medium article goes into detail on using DBML files for CI.

[erlis]

Thanks for sharing! I couldn't find a solid solution for my CI issue with this TP, I'll check the DBML. For this TP I was thinking that creating a layer of abstraction could be a solution without that major rewrite. Instead of creating the types from the DB, what can be done is to connect to the DB and generate the metadata in a format that can be persisted, a JSON file maybe? Then generate the types from that file instead of the DB.

Then what we need is a flag to specify if you want the json regenerated or not.

I don't think this is a big change, but I'll have to dig into the source code to see how this idea can fit the design.

Thanks for all the updates... but CI today is a big deal. And in my scenario, the DB is provisioned and built with the project, so in my world. Build First then Deploy, but with this restriction I'm facing a bit of chicken-egg problem. I cannot build because I need the database, but to deploy the database I need to build...

[cadull]

Would love to be able to store cached results to be used when the source database is unavailable. These should be friendly to source control diffs and merges. For those looking to LocalDB as a solution, the following is what I used.

Pre-build event:

sqlcmd -S "(localdb)\MSSQLLocalDB" -i "$(ProjectDir)DatabaseSchema.sql" -l 30

DatabaseSchema.sql:

IF DB_ID('DatabaseName') IS NOT NULL
BEGIN
	ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE DatabaseName;
END
CREATE DATABASE DatabaseName;
GO

USE DatabaseName;

CREATE TABLE TableName (
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[etc]

[cmeeren]

This is my main pain point with SqlClient and has been for a while. Local checked-in metadata would solve a lot. The build servers have LocalDB, but I often use full-text indexes/queries, which means I can't use LocalDB (#384).