Git corrupts SQLite database

Question

I use SQLite for websites I create and manage for my clients (I am a web developer). They are very small and straightforward databases.

This is now happened twice, so it is pretty clear that it wasn't a fluke. This last one was a clear set of steps that then corrupted the database:

1. Open the database in SQLite3 by linux command line
2. Make a mistake in the data
3. Ask Git to restore the released version of the file (this is reaching out to GitHub for the copy)
4. Open the restored version, and file is corrupt

I then struggle to get it back in order, nothing I do in git fixes it. I can luckily grab it from the live website (no updates there) and all is well.

My two questions:

1. Is there something I can do differently while getting the file from the git repository to revert, it is not clear to me why git would corrupt the file
2. Is there something I can do that is easier to restore the file other than get the copy from the live website

Answer 1

This was what I finally figured out, in case someone else comes along and hits the same problem. It was very confusing because the corruption was coming from git, so when I did something like rollback, it got corrupted then, not with my own activities.

I am running PHPStorm on Windows, with a shared directory on a virtual linux machine. I need line endings to be LF, but since PHPStorm is running in Windows, the CRLF and LF battles ensue.

I had long ago fixed it with .gitattributes. But when I switched from MySQL to SQLite, I had not added .db as a file not to be messed with when it came to line endings.

Git was replacing a CRLF with an LF in the .db binary file

In .gitattributes, you need:

# Set the default behavior to always be linefeed for linux
* text=auto

# Definitively text files
*.txt text eol=lf
*.php text eol=lf
*.html text eol=lf
...

# Ensure those won't be changed
*.jpg -text
*.png -text
*.db -text   

I added the last line and all was well, git is no longer trying to be helpful with the binary file and corrupting it with LFs.

One last thing I learned, you can set the gitattributes file, then go to your root directory and run the command:

git add --renormalize .
git status

You will then see any file that will be converted from CRLF to LF, a good way to fix an entire directory as well as making sure it is not doing to the SQLite database.