.Net 8 Database Interfaces
I'm working on my budget app and have come to the point where I need to use a database (SQLite specifically). I'm debating between using
Microsoft.Data.Sqlite or just biting the bullet and using Entity Framework Core.
The database tables are not very complex so the SQL statements won't be that verbose, but most places (from my research) use EFCore. But I don't like/understand EFCore (maybe some correlation/causation there :p).
Thoughts?
And this is what my database will look like (WIP but I think this is the final form)
16 Replies
In my opinion should give a try to EF Core if there is any possibility that your app gets an upgrade (schema change).
And extra column or type change would mean that you need to scan all your queries to be sure that you are not introducing breaking change(s).
From my experience its quite easy and not much extra code involved to get EF Core going, set up dbContext, create Entity class for each table, and create migration(s) and apply them to the database.
Also that would mean that your queries won't be coupled with SQLite and you can switch it up at some point easily.
As this is more a prototype app to add to my portfolio (really, start the portfolio :p) I won't be growing it to the point where I won't be using SQLite anymore.
Would it be silly of me to use EF Core to handle the db migrations and
Microsoft.Data.Sqlite for the db transactions so I can keep my SQL skills sharp? Or is that too much?There are ways to do that in EFCore too, but I guess it would be too weird to use EFCore just to write raw SQL š .
Maybe go without EFCore with this project to see how it is without a framework to do some heavy-lifting.
And you can use EFCore for some other more complicated project if you wish so in some time.
For the migrations I am not sure how you can achieve that without having defined entity classes and efcore, haven't used it that way ever.
If you want to hone your SQL skills you can even write your own table definitions and store them in a schema.sql file, so every time you run it you get an empty database with all tables created, and maybe seed.sql or seed statments appended in the schema.sql.
something i always do, with the database, is to put all foreign keys at the top
for example,
group_id on the categories table would go under category_id
i also move all timestamps to the bottom, like unix_date in transactionsthis makes it easy to understand what's what without having to look for the column: you look into the general area and you find what you need
enjoy my professional anatomy of a table, how i usually do it
by the way, instead of using an integer for
unix_date, use unix_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
then you can just use unixepoch(unix_date) to get a numberSQLite doesn't havea timestamp data type, hence the use of integer
it has an alias to it
or should have
SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values: * TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS"). * REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar. * INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.āhttps://sqlite.org/datatype3.html#date_and_time_datatype
Though I'm going to be changing that column to be just
date and it'll be a number in YYYYMMDD format. Transactions don't need a time, just a day it happened. Same for the categories table, the effective_unix_date column will be renamed something like effective_date and be a number in YYYYMM format.
So a budget for September 2024 will be 202409. A transaction that happened on 3 September 2024 would be 20240903. That way it should be easy to scan the DB for all transactions in September 2024 by doing a range from 20240901 to 20240931 and BOOM! They're all there.the
timestamp type is an alias to integer
that's not a good idea. if you need to format the data in a specific way, format it on retrieving, not when inserting the data
if you have to convert it to something else, for any reason, you will be very thankful for having the full timestamp instead of a custom non-standard date format
also, a timestamp is just an integer, which will use just as much storage as your schemeTrue. Unix timestamp is a standard for a reason
exactly
by the way, is the email unique, in the
users table?Yeah. I'm using MS OAuth for login so email will be unique
nice, that's good. the diagram doesn't make it obvious