Current project uses sqlite3 for local storage of stuff for various reasons.  This was our first time working with it.  We’ve learned a few things that are not obvious.. coming from a SqlServer world —

Tooling

SQLite Expert for the win.  It has a chocolatey package as well, although that failed to install once for me (but worked twice.  Who knows).

Note that SQLite is multi-process-open-able; data inserted by your program shows up automatically in the Data tab in SQLite Expert.  However, if SQLite Expert has the .db3 file open, can’t delete it to scrub it clean.

All the same: varchar nvarchar and text

SQLite doesn’t care.   It has 5 types of “data” it can store, depending on what the data is.  Handy reference: http://www.tutorialspoint.com/sqlite/sqlite_data_types.htm.

So when we were doing “uniqueidentifier” it was actually doing BLOB under the hood.

Its entirely dependent on what you’re trying to store, as to what gets stored.   So you can store a string in an int field.   https://www.sqlite.org/faq.html#q3  — its a feature, not a bug.

GUIDs as binary, beware Endian.

Most programmatic forms of storing Guid data end up storing it as Binary/Blob.   You know its binary if you do:

select id, hex(id)

And you get two things that look about the same size in length, but are shuffled around:

{E589C188-2575-4AC6-BD41-A66A00A1AF22}

88C189E57525C64ABD41A66A00A1AF22

You might say “hey! bad Endian!”  but actually its Guid.ToByteArray() that’s doing the re-shuffling.

So, if you see the value {E589C1… in a grid result, and you want to select it, and you say

select .... where id=x'e589c1...'

That doesn’t work!  you have to give the bytes in the other order (as returned from hex(id)).

Foreign Keys Not On By Default from C#

This one was a shocker.  Its a pragma to turn foreign key checking on.  However, tools such as SQLite Expert do this automatically for you.

What we ended up with is a GetOpenDbConnection() call that did both .Open() and executed the pragma.

Logging Generated SQL from Dapper

A lot of this got figured out after we could see the SQL Generated by the various libraries we were working with. Turns out, that’s 2 statements after your connection is opened:

con.Flags |= SQLiteConnectionFlags.LogAll;
con.Trace += (o, e) => { Console.WriteLine("SQL: " + e.Statement); };

In Conclusion

These are the bombshells we’ve experienced over the last week.  Hopefully, all the bombshells are done with now.

Other than this learning curve — very solid, very fast, very nice.  2 Thumbs Up. Plus One. Heart It.