# Is it possible to create and maintain a SQLite database schema via the SQLProvider? **URL:** \ **Category:** General\ **Tags:** beginners\ **Created:** [May 11, 2023, 1:43pm UTC](https://forums.fsharp.org/t/is-it-possible-to-create-and-maintain-a-sqlite-database-schema-via-the-sqlprovider/3097 "2023-05-11T13:43:57Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![GarryP](https://avatars.discourse-cdn.com/v4/letter/g/da6949/32.png) [@GarryP](https://forums.fsharp.org/u/GarryP)\ **Post date:** [May 11, 2023, 1:43pm UTC](https://forums.fsharp.org/t/is-it-possible-to-create-and-maintain-a-sqlite-database-schema-via-the-sqlprovider/3097/1 "2023-05-11T13:43:57Z")
In my application I would like to be able to create the database file and maintain it – add/remove tables, add/modify/remove columns, etc. – within the F# library itself. Is this possible? I thought about using Entity Framework but that’s way too much ‘overkill’ for what I need. I’ve been trying Dapper but it’s too ‘finicky’ for me and SQLProvider looks much nicer to use (even though some of documentation is way over my head). However, I need to be able to create and maintain the database schema within the code because the application will be (hopefully) installed on different machines via the Microsoft Store and updates to the schema will need to be done ‘locally’ on demand as application updates are installed. I’ve had a look at the SQLProvider documentation [SQLProvider](https://fsprojects.github.io/SQLProvider/) but I can’t find anything about creation/maintenance. If it’s not possible to do the schema maintenance via SQLProvider, can anyone suggest how I can do it? **Note:** I am not an SQL expert in any way. I just want to do some simple things to get a database up-and-running and make some simple changes to it now and again. **Note:** The application will (as far as I know) be a WinUi3 application calling F# code. ---
**Author:** ![ntwilson](https://yyz2.discourse-cdn.com/flex030/user_avatar/forums.fsharp.org/ntwilson/32/984_2.png) [@ntwilson](https://forums.fsharp.org/u/ntwilson)\ **Post date:** [May 11, 2023, 2:30pm UTC](https://forums.fsharp.org/t/is-it-possible-to-create-and-maintain-a-sqlite-database-schema-via-the-sqlprovider/3097/2 "2023-05-11T14:30:09Z")
This isn’t really an answer, but I consulted [https://fsharp.org/guides/data-access/](https://fsharp.org/guides/data-access/) when I was trying to choose which package to use to work with databases for my project. I had different needs (connecting to a multi-team, already existing SqlServer database, so I needed to scaffold from an existing schema rather than build the DB through F#) so I ended up choosing a package which won’t work for you, but I thought it was a good resource for giving a nice lay-of-the-land. ---
**Author:** ![GarryP](https://avatars.discourse-cdn.com/v4/letter/g/da6949/32.png) [@GarryP](https://forums.fsharp.org/u/GarryP)\ **Post date:** [May 12, 2023, 1:17pm UTC](https://forums.fsharp.org/t/is-it-possible-to-create-and-maintain-a-sqlite-database-schema-via-the-sqlprovider/3097/3 "2023-05-12T13:17:35Z")
Thanks for the information. I hope someone can get in touch about my specific issue as I’m starting to go ‘up the wall’. When trying to use SQLProvider I’m following the instructions here: [SQLite Provider](https://fsprojects.github.io/SQLProvider//core/sqlite.html) Each time I go down a new route I come to a brick wall built from my own lack of experience mortared together with documentation written for experts. With the code: ```auto #r @"nuget: SQLProvider" open FSharp.Data.Sql let [] resolutionPath = __SOURCE_DIRECTORY__ + @"/../files/sqlite" let [] connectionString = "Data Source=" + __SOURCE_DIRECTORY__ + @"\testDatabase.sqlite;ReadOnly=false;FailIfMissing=True;" type sql = SqlDataProvider< ConnectionString = connectionString, DatabaseVendor = Common.DatabaseProviderTypes.SQLITE, SQLiteLibrary=Common.SQLiteLibrary.SystemDataSQLite, ResolutionPath = resolutionPath, IndividualsAmount = 1000, UseOptionTypes = Common.NullableColumnType.OPTION > let ctx = sql.GetDataContext() ``` I get the error: `"The type provider 'FSharp.Data.Sql.SqlTypeProvider' reported an error: Unable to load DLL 'SQLite.Interop.dll' or one of its dependencies: The specified module could not be found. (0x8007007E)"` I click on the error hyperlink and I get a “404 – Page not found” error from my browser – lovely. I read the SQLProvider documentation again and it’s telling me about some “interop” DLL that needs to be moved from ‘somewhere’ (wherever that is) to ‘somewhere else’ (wherever that is) for ‘some reason’, none of which is explained – lovely. I go down various alleys until I find [https://learn.microsoft.com/en-us/nuget/consume-packages/managing-the-global-packages-and-cache-folders](https://learn.microsoft.com/en-us/nuget/consume-packages/managing-the-global-packages-and-cache-folders) which tells me (I think) that the DLL I’m looking for is in a specific folder, but when I look, it’s not – lovely. There are other things in there but not what I’m being told I should be seeing. I simply cannot find this `SQLite.Interop.dll` that I am being told about, or its related folders. I later find that maybe it’s maybe something to do with the difference between using `PackageReference` and `packages.config` but I haven’t a clue about that sort of thing and am simply getting more confused – lovely. I go back to the book “Getting Started with F#” and there’s a section on using SQLProvider but that’s not working for me either – lovely. Maybe that’s because I haven’t done the SQL Server stuff in Visual Studio that’s before it but I don’t want to go there as I don’t want to use SQL Server – I don’t think the people using the application will be doing that either – and I’m not using Visual Studio. I could have ‘walked past’ the information I’m looking for a dozen times but since I don’t know what I’m looking for I wouldn’t recognise it if it came right up to me and slapped me round the chops with a large fish. All I want to do is use a simple little database that’s sitting in my project folder; surely that can’t be too difficult? Anyway, rant over. Thanks again for your continued assistance. (I’m guessing that people will see this post and ‘back the heck away’ from going near this ‘crazy fella’. I’m just totally confused is all.) ---
**Author:** ![ntwilson](https://yyz2.discourse-cdn.com/flex030/user_avatar/forums.fsharp.org/ntwilson/32/984_2.png) [@ntwilson](https://forums.fsharp.org/u/ntwilson)\ **Post date:** [May 12, 2023, 3:26pm UTC](https://forums.fsharp.org/t/is-it-possible-to-create-and-maintain-a-sqlite-database-schema-via-the-sqlprovider/3097/4 "2023-05-12T15:26:10Z")
Lol, FWIW, I’d suggest maybe trying it with an actual F# project and not via the Interactive window. Especially with those errors about DLLs not being found. I know I’ve never been able to figure out how to get database stuff working in interactive, specifically with Microsoft.Data.SqlClient (I think I kept hitting [this issue](https://github.com/dotnet/SqlClient/issues/292)). So it might be the case that everything works just fine if you just load it up in a normal fsproj. ---
**Author:** ![GarryP](https://avatars.discourse-cdn.com/v4/letter/g/da6949/32.png) [@GarryP](https://forums.fsharp.org/u/GarryP)\ **Post date:** [May 13, 2023, 8:51am UTC](https://forums.fsharp.org/t/is-it-possible-to-create-and-maintain-a-sqlite-database-schema-via-the-sqlprovider/3097/5 "2023-05-13T08:51:42Z")
Oh, I was really hoping that would do the trick but, unfortunately, it didn’t; I still get the same error: `"The type provider 'FSharp.Data.Sql.SqlTypeProvider' reported an error: Unable to load DLL 'SQLite.Interop.dll' or one of its dependencies: The specified module could not be found. (0x8007007E)”` Here’s my code as it is now (in a file called Library.fs): ```auto namespace FSharpSQLProviderTests module Library = open FSharp.Data.Sql let [] resolutionPath = __SOURCE_DIRECTORY__ + @"/../files/sqlite" let [] connectionString = "Data Source=" + __SOURCE_DIRECTORY__ + @"\testDatabase.sqlite;ReadOnly=false;FailIfMissing=True;" type sql = SqlDataProvider< ConnectionString = connectionString, DatabaseVendor = Common.DatabaseProviderTypes.SQLITE, SQLiteLibrary=Common.SQLiteLibrary.SystemDataSQLite, ResolutionPath = resolutionPath, IndividualsAmount = 1000, UseOptionTypes = Common.NullableColumnType.OPTION > ``` Here’s my FSPROJ file: ```auto net7.0 true ``` I’ve attached an image showing my Package References in VS Code (some of them are there for Dapper which I’m using to do the DB maintenance stuff). ![Screenshot 2023-05-13 094303](https://canada1.discourse-cdn.com/flex030/uploads/fsharp/original/2X/f/f1a5d3f1bc400f9347c8b3f0a7121fccb5c737de.jpeg) I’ve also attached an image showing the (fully-expanded) project folder in VS Code. ![Screenshot 2023-05-13 094326](https://canada1.discourse-cdn.com/flex030/uploads/fsharp/original/2X/6/67a90b142953648831efafab9e3b542dd4d09c4c.jpeg) The project was created using: `dotnet new classlib -lang f#` I added the nuget packages using: `dotnet add package ` I’ve tried closing and re-launching VS Code a few times, building and re-building the project, but that’s done nothing to help. I found this on Stack Overflow [f# - Problems with ResolutionPath using SQLProvider with SQLite - Stack Overflow](https://stackoverflow.com/questions/38776391/problems-with-resolutionpath-using-sqlprovider-with-sqlite) but what they are saying there – and in the links given – might as well be gibberish to me. (There’s mention of copying a file but they don’t say where to copy it from, which isn’t very helpful.) I’m assuming at the moment that either: 1. I’ve done something stupid; 2. I’ve missed something basic and important; 3. For some reason, whatever that may be, what I’m trying to do is impossible. (Or a mix of the above.) I’m at a complete standstill with this now and have no idea what to do next. I don’t even know where to turn for help because I don’t know what the root cause of the problem is so I don’t know which ‘team’ to ask about it. (Is it a problem with SQLProvider? Or with SQLite? Or with the project? Or with VS Code? Or with Ionide? Or with F#? Or with NUGET? Or with my machine? Or with me? Or something else?) Here’s hoping that someone can help because I am totally baffled but I don’t just want to give up. **P.S.** I have no problem going through the whole process again, step-by-step, with someone if that will help me to figure out what’s wrong. ---
**Author:** ![ntwilson](https://yyz2.discourse-cdn.com/flex030/user_avatar/forums.fsharp.org/ntwilson/32/984_2.png) [@ntwilson](https://forums.fsharp.org/u/ntwilson)\ **Post date:** [May 13, 2023, 7:16pm UTC](https://forums.fsharp.org/t/is-it-possible-to-create-and-maintain-a-sqlite-database-schema-via-the-sqlprovider/3097/6 "2023-05-13T19:16:49Z")
I feel bad saying this, but I think I’d recommend not using SQLProvider. Lots of folks seem to have trouble getting it setup in \>= .net6, with no real aid. (e.g., [Unable to load shared library 'SQLite.Interop.dll' · Issue #755 · fsprojects/SQLProvider · GitHub](https://github.com/fsprojects/SQLProvider/issues/755), but if you look through the issues list you see lots of similar ones). For some people it seems to work, but those people seem to have not written down how they got it working. I would suggest putting 15-30 minutes into several other alternatives and see if any of them “just work” when you try it out. I’d particularly look at [Rezoom](https://github.com/fsprojects/Rezoom.SQL), [Donald](https://github.com/pimbrouwers/Donald), [SqlFun](https://github.com/jacentino/SqlFun) and [EFCore.FSharp](https://efcore.github.io/EFCore.FSharp/index.html) just based on the use case you described. I’m pretty happy with [SqlHydra](https://github.com/JordanMarr/SqlHydra) myself, but that’s more for the case of connecting to and querying an existing database. I think you’re going to want either something with Migrations (I think Rezoom/EFCore.FSharp), or something that’s very very lightweight (Donald/SqlFun, or maybe even Dapper.FSharp) ---
**Author:** ![GarryP](https://avatars.discourse-cdn.com/v4/letter/g/da6949/32.png) [@GarryP](https://forums.fsharp.org/u/GarryP)\ **Post date:** [May 14, 2023, 8:56am UTC](https://forums.fsharp.org/t/is-it-possible-to-create-and-maintain-a-sqlite-database-schema-via-the-sqlprovider/3097/7 "2023-05-14T08:56:21Z")
Thanks for sticking with this; your help is very much appreciated. I’ve just had a look and, apparently, WinUi3 applications are only compatible with .NET 6 and later: [Windows UI Library (WinUI) - Windows apps | Microsoft Learn](https://learn.microsoft.com/en-us/windows/apps/winui/) So, if it’s so difficult to get SQLProvider working with .NET 6 and later, and WinUi3 only works with .NET 6 and later, it seems like I am ‘on a hiding to nothing’ with that – I’m glad you mentioned this before I went too far with it. I’m going to have a look at the alternatives you mentioned. I’ve already had a look at _ **Dapper** _ but some of the things I thought would be very basic seem to be very awkward. For example, to get the automatically-generated id of the record I’ve just created I have to then do a select on it to retrieve the value, which mean that the field(s) I select on also have to be unique otherwise I could get more than one record back, which sounds ‘wrong’ to me. (I could be using it incorrectly though, I’ve only been dabbling with it.) I’ve used Entity Framework before but, for what I want to do, it’s a bit like redirecting an asteroid to crash on a nut processing plant just to get a cracked nut, so maybe _ **EFCore.FSharp** _ isn’t for me this time. _ **Rezoom** _ doesn’t seem to have had any updates for over three years, so either it’s been ‘abandoned’ or it’s amazingly stable and has never needed a fix/change in all that time. _ **SqlFun** _ looks promising from the little of what I’ve read so I think I’ll give that a try. I don’t think _ **SqlHydra** _ is what I’m after but it’s a possibility that I can’t dismiss just yet. I’m unsure about _ **Donald** _ at the moment, maybe something to ‘keep in my back pocket’ though. It’s a shame that I might not be able to use SQLProvider – it looks so nice to use – but if it’s going to be too much pain to get it to work (at the moment) I can always abstract my database access functions away – which I was going to do anyway – and then look at swapping it in later, if possible. Anyway, thanks again for all of your help.