[BatteryMountain]

I feel the whole debate is overkill: 99% of businesses/systems will never have so much data that they NEED to use uuid's. I personally don't like using integers for keys either as I've been burnt by them before. I also doubt any software I build today or have built in the last 10 years will be used 100 years from now.

Recently I built a new system (typical business-type backend) and forced to use sqlite + C# + dapper. Using this combination I cannot use guid/uuid as dapper cannot properly map it back to c# from sqlite, and my dislike of int's got me thinking. I have a random string generator (have used it for years for things like OTP's and other reference numbers), where I give it an alphabet + length of the desired string. Using 8 to 12 characters, I can get a few million unique permutations. That is, if used as a primary key, few million per database table. Then I hear in the back of my head, guys from work who would argue I would run out of unique combinations or would have to do lookups to see if they exist. So I decided slap the year and month on it as a prefix, so a key might look like this: 2105HSUAMWPA. This gets indexed really well too and there is some inherent information that can be seen from looking at the key: Year 21, Month 5 and then the unique bits.It's basically 4 lines of code that gets called on every new database entity. I think it will be easy to shard/partition the data too if the need arise in the future, by simply looking at the first 4 digits.

Thus to summarize:

Data is sliced by entity type (customer, invoice, etc), then by date (2105 for May 2021) then by unique string.

What do you guys think about this approach? Anyone been burnt by something like this?

[hardwaresofton]

> Recently I built a new system (typical business-type backend) and forced to use sqlite + C# + dapper. Using this combination I cannot use guid/uuid as dapper cannot properly map it back to c# from sqlite

Are you sure about this? This is pretty poor of a well known solution in the ORM world, SQLite or not.

If you were going for sortability/understandability then I understand slapping your own together, but why not generate v1/v4/v6[0] UUIDs in your application and then send them along to teh database, possibly prefixed with whatever you want it to be sorted by (though IMO you should just add that metadata to the thing being saved and sort on that properly)?

[0]: http://gh.peabody.io/uuidv6/

[BatteryMountain]

The problem is when reading the data back with Dapper (writing works fine) and it has to parse the guid stored in sqlite back into a .net Guid. Thus I resorted to making the column type text and ToString() all the Guid's before writing, but that's pretty gross. With a normal string field, sqlite can set it as primary key, make unique indexes on it, use it as foreign keys. Thus my attempt to use my own generator the generate id's. So far it works great but not sure if it will be fine with 5 years of usage (basically on tables that keep growing over time, like a comments table).

But of a rabbit hole: https://github.com/DapperLib/Dapper/pull/1082

[hardwaresofton]

Ahh thanks for noting this -- I've written off writing C# and PHP for the rest of my life for reasons, but this is a fascinating read.

[BatteryMountain]

On the other hand, C# + postgres is a super sweet combo. I don't use EntityFramework, so I don't know how well they play together, but if you write sql queries and run them from c#, it's super nice. I typically have a generic repository class that generates all the statements and it works great (I recently made the query generator able to generate inner join if I pass it filters that are not found on the table/object, based on a naming convention (any property that end in "Id" that is not the main entity Id and not a direct property, use the part before the "Id" part as the join table name) - it works great too!).

I also cache certain prepared statements in a static concurrent dictionary, so I can short circuit if the it the query/insert/update has been run before for the specific object type. That way all of the string manipulation can be avoid for each query - I just use a previous statement but with different sql parameters. On every deployment the cache gets cleared because the app basically get restarted, so it won't go stale. It's very cool (at least in my mind). The only indirect rule I have is that nothing else should modify the main schema besides my migrations and not while the app is using it (using FluentMigrator code that execute on app startup).

If I may ask, why don't you like C#? I totally get the php thing - I cannot stand it at all either.

[hardwaresofton]

> If I may ask, why don't you like C#? I totally get the php thing - I cannot stand it at all either.

I'm just not good at it -- I came in expecting it to be better Java (better async ergonomics, slightly less verbose, etc) -- but after working with it (while working with a client no less) I found that I disliked it just as much as Java and had a relatively rough go of it. There are a lot of things that I think contributed:

- .NET4 was becoming .NET core / .NET standard while this project was happening

- I needed a windows VM to work on the project (dotnet was not ready for prime time yet, so I couldn't depend on it to build the legacy code)

- Packaging was uncomfortable and a source of pain (also had to do with the core/standard transition) -- after you've used NPM/yarn/cargo/stack(haskell) nuget+chocolatey (IIRC?) felt like a huge step backwards, basically had to be IDE driven.

- EF was so incomplete (due in part to the core/standard transition), and so much worse than my then-and-now favorite TypeORM.

- The async paradigm and how they handle results are a little weird, IIRC awaiting a task would mean you received the task, not the result of the computation of the task?

- IIRC there was no Option type. Java learned this lesson in 1.8 IIRC (and it rocked my world a bit, in a good way), but C# not taking it up is weird to me.

- The codebase was just like you'd expect an old Java codebase to be -- i.e. terrible notfun.

- Everything was heavily IDE/visual studio driven which was not fun for me. Visual studio is an amazing tool no doubt, but a lot of it is unintuitive to me at this point after years of straight emacs/vim and occasionally sublime/atom/vscode. This is more personal than any other reason.

I just felt like it was so much worse than Typescript/Javascript for questionable gain -- I had worked with this same client to deliver a JS codebase that was easy for them to work on, on time and at budget. The C# side felt much worse to work on, and I just took it to mean I'm not good at C#, and I have no desire to be. The ecosystem you're almost forced to accept (appveyor, windows machines + powershell, etc) is just not my cup of tea.

I'm spoiled for choice these days -- if I need performance for a backend thing (and need to hand the project off to someone) I can choose Go. If I really need performance I can pick Rust. If I don't need performance-per-say I pick Typescript (it's still generally better perf than Ruby/Python). If I want to get it right (and really craft software) I pick Haskell (and of course it's near impossible to hand that off to most companies).

In the end C# is a great language (whether I like it or not), but it fell short of my expectations as a "better java". If I'm going to do Java I'll just do Java/Kotlin/Scala/Clojure. I think I could go my whole life without ever touching C# again, so was an easy rule to make for myself.

[BatteryMountain]

Got ya, thanks for explaining.

Legacy C# projects can be a mess, I've stepped away after a month or from one or two projects in the past simply because it was beyond fixable - I think there is clean way to use c# and there is an over-engineered way (typically java devs that try to use the exact same patterns in c#, which bloats it).

You are right, the past 3 years or with .net core and .net standard was messy too! They should've just named it something different. Same with Entity Framework. I'm still steaming about them using the same names for everything, as there are plenty cases when things don't work the same way as before. It completely screwed googling things.

And any desktop development was broken too for a time (still bugging out with new .net core xaml editor) - so yeah, skip.

I primarily use C# for api's and tooling (my own build server is code I wrote myself in C#, hosting my own git repo's (not using gitlab/bitbucket etc; rather bare repositories!), and my own nuget server). But for anything web/desktop related, meh skip. In C# api land, you can get such clean/unbloated code with full type safety and compiler checks, I love it. Most C# devs use/throw exceptions but they really muddy the water and make it feel like Java. My own code only have try/catch when talking to the outside world (harddrive, network, etc), the rest of the layers are clean and with minimal null checks (cause at those layers I never have nulls). Exceptions are expensive/slow too, so rather catch it as early as possible and return a Result object (not built in, my own envelope) instead of rethrowing multiple times and doing a crazy amount of null checks. So it is possible to write "clean" C# but most commercial projects I've seen is pretty darn ugly.

Just an fyi, I don't use appveyor/windows/powershell/Azure at all. I work primarily from Fedora with Jetbrains products, with most of my tooling being hand rolled. I use the Digital Ocean Api to manage all my boxes and use SSH.NET (nuget package that can open ssh tunnels in C#, I use it for all sorts of things, incl reaching my db servers (ssh.net + Npgsql)) and FluentFtp if needed. So I don't really touch Microsoft's ecosystem at all. My builder code calls the dotnet sdk and I copy the build artifacts around as needed - no third parties involved. In the beginning I stressed about going this way but I see now how great it is - no need to worry about CI minutes or paying Gitlab or Bitbucket, or dealing with 10 different api keys or dealing with Azure etc. So it's totally possible to have sanity - but to be honest I cannot take "my way" to work as everything is hand rolled - people want to use Gitlab/Bitbucket etc else they lose their minds (other developers seems to be my biggest enemy these days - everything needs to match to their exact pattern else they quit). So yeah, own clients = own stack.

On the visual studio side, the IDE has gotten slower and slower... I have a 6 core cpu, 32Gb ram and a Samsung nvme drive, RX580.. yet visual studio lags like crazy, intellisense take 2 minutes to update etc.. that's with only VS open and no extra extensions installed. It also crashes sometimes while doing nothing out of the ordinary. So not sure what the heck they are doing to the project at Microsoft. On the otherhand, the jetbrains products have super slow startup times (try 10 to 30 seconds for Rider and Datagrip) but once in, they work great!

Another cool thing to note, Gitkraken sees my bare git repositories as valid remotes (provided ssh keys are in place) and pushes to them without issue (and near instantly, bitbucket push takes 20s no matter what I do).

Sorry for dumping all of this on you!