ā EFCore SQLITE Migrations on Server
I am developing with EFCore locally, however I copied the .db over to the server and on the server its filling the database with data.
Now I have the issue that I need to change some table, so I was changing the corresponding model locally. Now my question, what would be the proper way to apply these changes to the .db that is on the server?
62 Replies
on the server I have only the build, not the source
You can use runtime migrations, aka running them via code inside your app as part of your startup
oh ok. The migration files I need to move them on the server too I assume?
Using Efcore they are part of your code already
Assuming code first, at least
If you are using DB first, idk
Ok wait I will try to explain what I did so far first.
What I did:
- Programmed locally
- Set up the database locally (with EnsureCreated method, not with any command)
- Copied the database to the server
- The server runs a program which inserts data into the database copied.
The problem:
- Now I found a bug in the implementation, I want to change a table.
- I change the model locally in the respective class (add a property).
- How do I put the new database on the server without losing the existing data that is already in the database on the server?
Like what is the best way to handle it, whats the best workflow now?
Maybe my setup was from the beginning on wrong, not sure.
Since you used EnsureCreated, then you won't be able to use migrations (you should def read on that).
Right now your only solution is to manually write the SQL queries and apply them to your database
Got it, its not a big deal to drop the current one. Just want to know what the best way to proceed.
I will use migrations next time, but how would I update it on the server each time. Kinda seems annoying
Migrations are either applied by accessing into the server and running
dotnet ef database update
or there is a Migrate() function on the context (or context.Database, i dont remember)for that I would need to always move the migration files that I generated locally
so instead of EnsureCreated(), you can always use
Migrate()to the server?
Migration files are within the project
They're .cs files
and they're autogenerated
yeah but on server end I have only the build files
the build would include the migrations
oh got it now
so running .Migrate() will be fine
I guess
dotnet ef database update wouldn't work, since you don't have the source project in the serverso each time I do a migration on my local, I can just build the project after doing it, then on server app calling just ".Migrate()" would update the DB on the server?
But i'm not entirely sure
yeah
yep
is it like the best way ?
Maybe sqlite is like not the best for this purpose, maybe a cloud DB would do better
but not sure
like with a cloud db
I can do the migration
and "push" it on the cloud DB
and then its auto synchronized on the server aswell
as it uses the same cloud db
Yeah but if you wanna prototype, or use different data you can't...
if you add a column or change a column locally, the running server will explode
it's not a good idea
you should have different local and production databases
yeah thats true
it's not specific to sqlite
it's an architectural problem
and it's a bad idea to use the production database
about this command I had a question too, how does it know the DB Path for updating the DB? is it parsing the context file and searching for data source string?
afaik it's using appsettings.json
or yeah if oyu have it inside the context
it uses that
i don't remember the priority
Ok anyways.
I will just do what you suggested. Do the migration locally, rebuild the project, put it on the server and use Migrate method at startup
or what else I could do probably is just syncing via git, once migration done I push on git and then pull on server and then update the DB via that command on server
would work too right?
yeah
Ok setted it up now.
So basically my workflow currently is:
- Changing stuff locally if needed
- Generating migration files (via Add-Migration)
- Push
- Pull on Server
- Update command on server
is this correct?
not really
you dont need the source on the server, you just need the published release
migrations are literally code that is compiled just like your application code
yeah ok got it, though I prefer to have the code on server too for some reasons. My approach would still work I assume
sure
just add in a "build/publish app" after "pull on server" then š
If I only have the builds (not the src) and I do the update command how does it search for the db path ? via reflection?
it sounds weird to me
no, it uses the config file
just like it does when you run it "with the source"
it always compiles
and resolves your context
ĀÆ\_(ć)_/ĀÆ
sorry but which one are you refering to
I assumed you used config files to store your connection string
it doesnt matter thou
it resolves the context
No I didnt actually
the
Add-Migration and Update-Database (or dotnet ef ...) commands all use your actual real contextoh
they are instantiating the context via reflection probably
they actually "start" part of your application to resolve the context
yes
that might also explain the error I had when I didnt have the default constructor
could not instantiate
ok now I understand it better.
here I hardcoded the path, however multiple projects use this DB library and the context, now I assume if I run it from A.exe it will not actually use the bot.db from the DB project path but it will search for it from A.exe directory. Whats the best way to go about this? I want all to use the same .db that is located in the DB project folder
it will.
or actually, what?
your db file shouldnt exist in a "project"
the db file is a result of running the program/migrations
yeah right
I mean I just do the migration & update stuff in the Database project
if you have multiple consuming projects (think things that run, non-class libs) that use the same database, you will have issues with SQLite
as it should be
due to concurrency?
yes
whats my best bet
using a real database
sqlite is an in-process database
SQL server?
Postgres, SQL Server
are my two recommendations
okay. Yeah I thought about that already. Thanks for claryfing again.
MYSQL also fine?
not really
in theory, yes, but its by far the worst SQL database ever written
I see literally no reason to use it over postgres
kk xD
because the legacy app is written using mysql
(cries internally)
with EF Core, its usually quite easy to switch š
but since you said legacy, its probably not ef core š
it's a huge erp app and i just migrated it to dotnet after being on net framework. All of the SQL queries are handwritten (no ORM). So I reverse engineered the database using efcore tools and we're partially replacing the app part by part
if we were to switch the whole application instantly to EF-Core it would take a year lol
unfortunately not a path we can take, but at least mysql is usable ...
anyways, that being said postgres is the best way to go forward
bruh handwritten sql
never again
I just added a new DBSet and added the migration and this is what happened (in a completely unrelated model) when I tried to update it. As I said this column is completely independent from what is new, it worked before.
using postgres
thats not an EF error
so it must be a postgres error
Yep
Was this issue resolved? If so, run
/close - otherwise I will mark this as stale and this post will be archived until there is new activity.