mergeconflict227
James: [00:00:00] Let's talk about databases. 
Frank: [00:00:13] These things keep coming up. Well, they're useful. Aren't they? I mean, you're writing an app. You got to put the data somewhere. File systems are so 1970s let's do databases. 
James: [00:00:23] Databases. And we've talked about databases on the podcast before, but our good friend, Gary, who we've talked about on this podcast before Rhoda send with a glorious, super long email that we're not going to read, but it, it had me thinking about that.
Normal. Normally we think about databases. It's easy. Like we're using like a no SQL solution or a SQL solution. And then we normally think about the server stuff. And when we come to mobile or desktop applications, It seems as though there's a lot of different options that are out there. All of them do something a little bit different.
And what I mean by that is that you got the, you know, the SQL Lite under, they got the SQL light under the hood, right. The raw, but yeah. Then there's stuff on top of it. You got the light DBS, you got the sequel light, you got the SQL light dash nets, you got the ATO stuff, you got the, this thing. And then that thing, and some are using SQL Lite and some are using their own things.
As well, and that could be kind of a confusing, um, you know, wilderness out there, wilderness of databases, if you will. And we could de-mystify demystify that. 
Frank: [00:01:34] Yeah, we took a shot at it when we were first talking about, I had a question I think for you was, uh, what, what are your favorite ORMs because all throughout my career, it feels like I'm always kind of picking and choosing ORMs.
I have my own ORM. Um, I still ride them for some reason. I think it's a little plague that goes on in the back of my head. I, but it all comes down for me. Um, serialization is one of the big problems and programming. It's a little annoying because although databases are taught in school, they're often taught separate from the programming language.
So they'll teach you this amazing programming language, like C or C with objects or Java or Python or C sharp, who knows what you're taught in school. But teachers love to give like trivial. Data input problems like, uh, use read line and have some on type in their name or their date of birth or something, and then write some of that data to a file.
That's all nice when you're first learning to program and you're learning those basics, but basically none of that applies to the real world. Like all of that. It's very unsafe code to be writing in a real app. And the truth is most programming languages. Don't give you any good serialization support.
They used to, it used to be a feature of programming languages, but these days, the, the programming language people are like, we're not touching that you want data, use a database or figure it out on your own. And I think that that's kind of the root cause of the problem is just the programming language people have really just.
Punted on this one. They're like data. Good luck. Good luck with that. And, 
James: [00:03:15] and it's hard when you're in control of the data. Right? I think that's another thing that I've realized I've gone through, you know, different mechanisms of my monkey cache library, which my favorite is just writing files. The disc that seems to work does great.
Um, yeah. That's I mean, granted, you know what a database is, 
Frank: [00:03:32] Frank. It's often represented as files on a disc. I see where you're going. 
James: [00:03:38] Yeah. Different information in there. And they're, they're all a little bit different, but you know, th there's that, there's that aspect of it. Um, but I've gone through, you know, preferences, secure storage, like dif store.
I store a lot of different information in a lot of different places. Not everything needs to go into a database. However, you know, when you're going through an abstraction or you're just using an API and when something. Goes awry. You're like, well, it must be over well, iOS updated. And then this thing stopped working.
Are you sure it stopped working or did someone go do something, you know, did someone clear the data now? You're, you know, so when you are a library creator, a language creator, it then becomes, it is your responsibility for the data, which is why I hate, by the way, my inept billing plugin. I love it. And I hate it because now I made it for myself and I knew people were going to use it.
But now I'm just like, Um, somehow in between you and money, and that makes me very uncomfortable. 
Frank: [00:04:33] Oh yeah. I, any of those, like, uh, I remember an open source people would be selling, um, open source storefronts, and they're like, here's some PHP code, just put in your credit card, authorization code here, and we're all set to go and we have a storefront and I'm just like, I don't know how much can you trust anything these days, but, well, you have to trust at some level.
I. Yes. Um, so I made fun of saving things to files earlier. Not because the file system itself is unreliable, but generally the way that we access it as lazy programmers. Is unreliables there are provisions and file systems like file system support locks. Like you can, you can have right. Only locks on a file that way you can make sure or, you know, read only whatever you have.
All these locks, you can do all that stuff. So SQL Lite databases are nice because. They're generally written by good engineers who know how to talk to the file system properly and can pull off all that synchronization stuff. Broadly speaking, I think we've brought this up a million times. The big reason not to use the file system for data storage is corruption of that data.
If your process, and while you're in the middle of writing, you're going to have a half half-written file. Tricks around that are, um, whenever you're going to write a file, write it to a temporary file first and then just switch the file name at the very end, like do a quick move or even a copy, but even a copy is bad.
You just want to do that little file name, trick or use a database. Those are kind of your options. 
James: [00:06:17] Yeah. And I think, I think the thing is when. You start to get any data that starts to become a little bit more complex. That's not a cash, right? I think Cassius, when I think of monkey cash, the reason cash is in the name is because it's a cash.
It should be okay if your data is corrupt and it goes away and something happens because it's a cash, your application should be able to work without a cash. That's low. The name of it, where a database is like, Hey, I have this data and I really want it to be around for a prolonged amount. So time in fact, maybe years, maybe decades of time, you know, and yeah.
And that should continue to work. And the underlying system that I think is so important of how a lot of databases work is sequel light, which is, which is provided by the operating systems. Is that correct? Frank? 
Frank: [00:07:06] Yeah, it wasn't written by any of the major platform people out there it's written by these two insane hackers that are awesome smart people, but they're in full control of it.
The cool thing is it's released under a very permissive license and that's why you're seeing all the big platforms out there adopt it. Microsoft has had a sequel Xpress. Have you ever used that one? 
James: [00:07:32] I think a long time ago there was, I use compact SQL. I think that was one of the things was windows phone back in the day.
Frank: [00:07:39] I wasn't sure if they're related different or the same name for the same product or what, but, uh, that was trying to take parts of the. Big SQL engine, this thing that was meant to run on big servers and trying to get those so that they could run and more of the app world. The beautiful thing about SQL Lite.
Is it scales from the app up to somewhat up to the server? Yeah, I don't think, I don't know if it supports like the advanced server things like, uh, replication. It's usually the big one that you want on a server. So you can have 10 computers hosting this one database. I'm not sure if it does that, but you know, it's still it's so feature rich at this point.
It just might. 
James: [00:08:19] It just may. I mean, it, it there's a lot to it. I mean, the whole thing was SQL Lite is it's a C language library. That's the base fundamental underneath. Right. And the cool parts about it are that you're right. It's, it's, um, completely stable. It's been around for a long time. Cross-platform.
Backwards compatible. And by the way, I love that on the website, they send the developer that the developers pledged to keep, but that way through at least the year 2050. So you got like, you got a good number. They're 30 years out of this database, but it's also completely open source. In both public domain and is free to everyone, to, for everyone to use for any purpose.
That's what it says. And I think that's that that is really big and momentous on just SQL Lite itself. Um, however, like I've never used sequel light directly itself. We've already just said like four names. Yeah. All our like SQL Lite. 
Frank: [00:09:12] Okay. So yeah. Yeah. The problem is we all are very unoriginal with names, especially me.
I take, I take some credit for having one of the worst named open source libraries out there. So we'll try to break this down. So sequel, light. Is a library of code that acts as a database engine. It has many levels of abstractions. So the database can be hosted in memory. It can be hosted on the file system, but in the end, it is a proprietary C API.
So very simple function calls kind of interface for talking to the database. Now. I actually coded it in that fashion. When I very first started out in iOS programming, I was writing in objective C and I wanted a database. So I use SQL Lite. And so I use the C interface to it. And that's why I'll never do that ever.
I can ever, 
James: [00:10:14] it doesn't sound like fun at all, by the way. 
Frank: [00:10:16] So. It's not bad, but when you're coming from a world, where was I coming from? I was coming from like VB and JavaScript where, uh, you had ATO and visual basic where you could just say database connect. You give it this mysterious connection string.
But after that it's a database and you'd run commands against it. Compared to that the C interface. It's a bit, it's a bit nastier. I don't want to say that too bad though, because the reason it's so popular is that their API is easy to consume. So I would say it's just more intimidating than anything else.
It's not so much that it's bad. It's just definitely intimidating and definitely, um, Um, you know, classic, see needlessly complex, every statement can fail. So you have to put an if statement after every statement. So your code just starts to, you know, get a little bit ugly. 
James: [00:11:15] Yeah. And the very first times that I ever used the SQL Lite was that there were sort of SQL Lite.
Things for windows phone. And then I ended up using sequel light dash net. And these are both ORMs are object relational mappers. Is that correct? ORM. 
Frank: [00:11:34] Yup. Optimum sessional mapper. 
James: [00:11:37] On top of it, that, that, that took the complexities of what you were just talking about and turned it into for me as a C sharp refied.
Beautiful. API that could give me that sort of ado feel in a way, uh, I could, I could create a connection to the database, you know, pass it. I would pass it instead of passing a bunch of things. I would pass it a file name to, to basically that's where y'all want the file to live. And. It would just figure out the, the quirks or how to implement SQL Lite.
Um, for me, for, you know, I can make those calls and do those things automatically. Um, and that was something that I really lived with because I had all the other option too, which is, um, early on. I, I felt like there weren't a lot of options on mobile. It was mostly use the SQL light. Stuff. If there is a SQL combat or something, try to figure out how to use that for one platform.
And then when it came to Xamarin, at least it was you cul I dash net, which was your implementation of the ORM or use the mano data sequel. Is that what a mano data 
sequel 
Frank: [00:12:47] light. Yeah. Yes. Yep. And it's funny. So just a little history, mano.data, SQL Lite did not work on mobile in the very beginning. And that's why I created my RM because we just had no data access layer at all.
And because I had spent the previous year writing a bunch of objective C code to talk to SQL Lite, I just happened to know how to do it. It was easy for me. And so that's how I ended up writing that library. But so. There are kind of two problems and ORM has to solve. One is provide a nice programmatic API so that it's really easy to put data into the database, change things around query the database, get database out of it.
But on the flip side to that, people also want performance. Perfect. Perfect, perfect, perfect. Uh, the way to get sequel light to be fast and the way to get sequel light to be multithreaded has just. Change over the years, like the recommendations have swerved all over the place as features are added, as the platform versions are increased as people's, um, preference for how to access the data.
Right. When I first wrote the RM async than exists in.net. It's not that I hadn't had time to implement it. It just hadn't been invented yet. So it's just, it didn't have that feature for a long time. And, but nowadays people expect a sink and they expect a sink to be kind of the fast version, oddly enough.
James: [00:14:28] Yeah. Which, uh, not necessarily, I don't know if that's true. And no other way. 
Frank: [00:14:36] I, well, what I find no one wants to use the synchronous API anymore. Everyone wants to use the asynchronous because it's generally the recommendation Microsoft gives for building user interface applications. Whenever you're.
Something changes in your data model, kickoff, an async task to do everything else. Uh, it's just different from how we used to write apps. And I think so not so much that it should be faster or anything than the Cypress version, but it should be at least as fast or you shouldn't have a performance penalty for it.
James: [00:15:09] Yeah, that makes sense. That makes sense. And then there could be a lot of things that it can do and you can do as a developer, like grouping and doing things in multiple, you know, in a single transaction and, you know, just ensuring that the things are happening there, but that's a good point. Is that. For me, it was always about the simplicity of the API, because that's what I like.
You know, I had messed around with the mano, um, data SQL, which isn't an ado.net ORM around it. So they're actually very different, even though I believe that they both just sit on top of SQL Lite itself, but they have a different interface. There are different ORMs, which is something to think about at the end of the day of what you're trying to accomplish.
And I don't really think that the, the mano data SQL Lite. Ever took into consideration the, a sync stuff from my understanding. 
Frank: [00:16:01] Yeah. It's, it's hard for me to say exactly because I don't know that code base inside and out, but I will say definitely in the past, um, when we're implementing ado stuff for other databases, definitely thought about things like connection pools.
Because those would be really important in service scenarios. Uh, you can't open a new TCP connection to a database server for every request because you'll just run out of operating system resources immediately. So you'd always have some kind of pooling connection concept, but none of that was required by.
80. Oh, and in case we say an ado, I don't even know what ADL stands for. Access data, objects, abstract data objects. It's just an API that Microsoft established a generic API for talking to a database. And that's, what's kind of cool about it is you can theoretically talk to any database through it, but your query is we'll probably have to change a little 
James: [00:16:58] is it is not the active X data objects as it.
Frank: [00:17:01] Oh, got it. It might be, it might, it's old enough that it might actually, cause I keep saying ado, but I should be saying ado.net. 
James: [00:17:12] Yes. That's very because that's the new version 
Frank: [00:17:14] of it. Yeah. So it is active X data objects. Whoa. Oh, the past is catching up with 
James: [00:17:20] us and then here's, what's really important to think about too.
And I really liked that on the sequel light. Um, The SQL Lite website, they really spell out something that's very important because if you're a server person coming over to mobile or to desktop, you have to remember that sequel light itself has embedded, right? It's not client server. It's not supposed to be right.
When you think about, see it a SQL database engines like SQL server PostgreSQL, a QL, my SQL or Oracle or other things like that, you know, those are. Like you were talking about, they could be distributed of a bunch of other things. SQL Lite is not intended to be the same thing as that it's inherently serverless, you know, it's embedded into the application, uh, and it's for just that application.
And that's it, you know, it's, it's, it's weird to me because often I would think about a database that I would be calling would be like, I have my, I have a. A desktop application or something. Right. And I'm creating a connection to a physical database that is somewhere else on, on the network, right.
Somewhere else on the network. It's never on my machine. Usually it's like, it's somewhere else. But like inherently, this it's the SQL engine running inside of your app. Like the connection that it's making is like, To the, to the disc it's like it's local, it's all there. Right? It's it's you kind of have to remember that in, in a way.
I think when you come to programming the set, it is very inherently different than, um, those normal client server database engines. 
Frank: [00:18:55] It's it's very different and that has impacts and how you use it. Uh, the best example there is this problem, when you have RRMS, when you're trying to talk between an object model, like an object Varian programming language, like C-sharp.
And you're trying to talk to a relational model, like a database. There is this common problem that comes up called the, uh, end plus one query problem. So it's kind of your join. I don't want all the items from this table. I want all the items through this table. And so you would come up with a join. If you're used to writing SQL, you would do a join, or if you're lazy at SQL, you would do a multi table and you would do aware and you would do the join on the where clause.
That's what I always do because I can never remember the stupid. Syntax and most database engines are smart enough to optimize that. However, that's a trick that came out of the distributed database world that end plus one problem. The problem is I can't just do one query. I have to do one query to find out how many objects do you get?
And then I have to do N more queries. So that's why it's called the end. Plus one. Problem cause you end up doing N plus one queries that is really bad in a distributed database world, because you have latency every time I want to talk to that database, uh, there is a delay you will also have bandwidth.
However, networks are usually faster. Bandwidth is usually really high, but your latency is terrible. And so this end plus one, your latency is your latency times. And plus one. Okay. On embedded systems where this database is almost always running memory, mapped files off the desk, you don't have that late and see, or at least that latency is 1000000th of what it is on a network.
Uh, yeah, literally a million of what it is on the network. And so you don't have to worry. About the end plus one problem. And it sometimes frustrates me a little bit because, um, SQL Lite dash net does not have good joint support for 10 years. I've been meaning to add good joint support to it, but it doesn't have it, but at the same time, it doesn't need it.
Like that style of querying is not needed in the embedded world, but people are just so accustomed to it from the server world that they think that they're being bad coders, but really. You gotta, you gotta kind of think about your environment and how performance actually functions in that environment.
James: [00:21:25] Yeah. Joins always kind of confused me just in life in general, uh, when it came to the databases. So I remember with my meta manager app, which I haven't forbid someone's all running that somewhere, but I had, I had, I had kept adding tables because I was adding new features and, um, What I ended up doing right.
Is like I would query an event. And then I had another table that I just had a, you know, you know, an event ID that was on, on that object. And then of doing some magical join, I would just do two different queries, I would say, well, give me, give me all the information about this event and then okay. On this other table, like, go get me this other thing.
And like all then create the data based off these, like. If loops inside this thing, like, Oh, well, I'll go figure it out. Right. It was in the merge them into my, my higher level object. That's not in the database, it's doing other stuff that's in my view model and whatnot. Um, and that, that seemed to be just fine.
I literally had no issues at all. It wasn't the cleanest looking code and I think you're right, is like, Oh, I want my code to look clean, but it totally worked just fine. 
Frank: [00:22:28] Yeah. And the trick with databases is because you have this problem of, you can either put stuff in the query or you can put it into your code is you don't really know which one to put it into because they're both kind of the right answer, especially in the world of SQL Lite where you're not paying that latency cost.
It's whatever you prefer me whenever I was in large groups and we, we always had the database person in the group. And in this case I'm not using it as a compliment. I'm using it as they wrote stored procedures, they wrote. Complicated loops. They used cursors. Anyone who does database work and uses cursors is either a database professional or they are prematurely optimizing everything.
James: [00:23:14] Oh, on cursors. By the way, that is literally how nearly everything low-level on. Android works is everything. Everything has occurred. So you want to iterate through the, the they're all like, um, what are they called? Haven't used them forever. Cause they're so complicated. Like, let's say you want to query the.
The contacts you have to go through like this and the cursor in this. And you're like, Oh my, Oh my, my head, my head just hurts thinking about it in general. It's terrible. So 
Frank: [00:23:43] they took the worst feature of relational databases and put that all throughout the UI layer, fundamental layer. 
James: [00:23:49] It's a fundamental layer of how things, but like the thing is if you implement it, correct it, they did the cursors to allow cross app communication, to query things and.
And I'll enable you. If you had a bunch of data in your app, you could use this, this whole database query model. So it was very standardized. Like this is the bare bones level, how you do it across APA. And then you use this, you do this query and you get this cursor back and you iterate through it. But if you do it correct, and if you understand it, it can be like the most highly performance craziest thing ever.
The problem is like, I don't know. It's so complicated that I don't want to, I don't want to do it. 
Frank: [00:24:27] Yeah, it sounds like. Um, if I innumerable was I async innumerable and IAA synchrony, numerable worked across processes. That's kind of what it's sounding like to me from over here. Yeah, uh, tough. It's hard to implement that stuff.
Um, and I think that's why people tend to use these higher level libraries because honestly, life's too short to deal with that kind of stuff. I just have some data I want to pop in here and be able to query it easily. And my preference is still a little bit towards relational databases. I feel like I'm showing my age a little bit here because certainly.
Object databases are a key value stores are certainly useful. The thing that I like about a relational databases is I know exactly which columns are indexed and I'm just a performance weirdo in my head, even though I'm always telling people not to prematurely optimize, I'm always prematurely optimizing.
I want to know for a fact that there is an index on that column so that I can do that query. Efficiently. And that's why I still tend to stick to this old school method. 
James: [00:25:37] Yeah. Yeah. I've used them late DB before, which is a very nice new SQL solution. It doesn't run on SQL. It has its own engine. It has its own thing.
Right. And that has it. It just works out of the box, which is very nice. Um, and it has a very nice. SQL Lite ASCA vibe to it in a way, but yeah, I'm just more, I've just always been relational data. I don't know what it is. I think because when I worked at Canon, we were writing a lot of SQL code and that's how I learned my first databases.
And that's just what I've known and loved at least coming from, from this world, uh, which is fascinating. The one thing that always tripped me. Regardless of what ORM or database system I was using was the threading parts of it along with async await. Right? So you often have like a connection to the database.
And for me, I'll be honest with you. I use the synchronous SQL Lite dash net. I don't need the asynchronous stuff. I just put locks around everything. Frank, I just locked it to me because the very first time I used it, Uh, the SQL Lite dash net in my applications, this is pre all the async always have and pre everything.
And I just, you know, to make sure I wasn't writing or doing multiple things to my, my connection, I would just put a lock around it, lock an object and dang it at works, you know? Um, and it was fine. Is that okay? 
Frank: [00:26:55] You know, it, it kind of is don't tell anyone I said that, but it kind of is here's the deal. Uh, if you want to keep the UI thread responsive, you have what, 16 milliseconds to do your work.
That's one 60th of a second. If you're on an iPad pro you have, uh, Oh 0.08, whatever you got less. Um, this database is running memory mapped files. Most of it's already hot in the cache. Chances are your query is going to take significantly less than one or two milliseconds, and you're going to be fine.
It's only when you try to pull out. You know, 30 columns, all that are strings, uh, 10,000 rows in a query. That's when you're going to start hitting your perfect issues. But I think we've always talked in the past, like you have to design your UI, so you're not presenting that much information to the user.
It's not a good UI to show someone 10,000 rows. So like just don't do that. And so you'll probably be fine. That's that's my initial answer to you. Yeah, you're fine. But I had more, I promise. 
James: [00:28:04] Yeah. I just sent you like, basically a link to my monkey cash and in every single method, it's just like lock DB and then it's here's I run the query and then I'm like, okay.
And then go through the results and do this song. You know, it's a very short-lived lock. You don't have to lock everything, but you, you just go through it and just make sure that that one database connection is good to go. Um, but. Even beyond that, the one thing that I haven't ever thought about and that Gary brought up in this email was around, uh, threading itself.
So single threaded multithreading and then serialization models and SQL Lite itself supports many of those things. I've never thought about it or, or really. It's a thought about why I would need this because it has a lock on it. It's like if I was kicking up different threads, it should just be okay. I don't know.
What do I have to be concerned about here, Frank? 
Frank: [00:28:54] Well, let's start by saying, um, you're locking is all to avoid, not a multithreading bug. Let me say this first SQL Lite is thread safe. You do not have to do any locks anywhere. It will never corrupt data. It is good software written by smart people. What you are trying to avoid is the infamous database busy error.
And yeah. So this is an error that you can get. If you managed to, uh, attempt to write to the database while you're actively reading it, there's a million other scenarios, but basically it's a read-write kind of conflict and you will get this error back from the database called. Busy from our stair, because when I first started running into this with SQL, when I started using, I had no idea how to handle that error.
Like, what are you supposed to do when you get a busy, what do you think you're supposed to do? 
James: [00:29:55] Well, if you're busy, I mean, you should have, you should probably roll back whatever you were going to do. And then try again. 
Frank: [00:30:03] Yep. That's what you gotta do. So think about that for literally every query in your code, you have to put a, for a loop with a tri counter.
Well, the try-catch looking for that error. It's all ugly. No one wants to do that. So, uh, there's a few tricks they put into SQL Lite. Uh, one is an automatic time out. So you could say, wait for up to. Mm, I don't know, three seconds. If the database happens to be locked right now, don't give up. Don't give up, keep trying for three seconds to, uh, get the lock to that database.
So I would say that's the very first thing you should look into. If you're running into this infamous busy error that SQL I can give you just the default for that timer, I think is a little bit random, depending on which platform you're using it from. So you can always set the busy timeout. To something more manageable, like one second.
Cause like very few of your queries are ever going to take more than a few milliseconds. So one second is plenty. I like 
James: [00:31:08] that. That's a good pro tip from the Frank Krueger. So, so do I really need to think you said, I don't really need to think about multi threading. And in general, I mean, I guess if I have a single connection to the thing, it should be okay.
I mean, how do you normally in your application get access to your database? Do you have it as a single 10? Do you have now? Is it, uh, you know, it's not a factory, right? You shouldn't have multiple connections to the database. Correct. How, how does, how do you Frank structure your SQL Lite database? That's what I actually wanted to know.
Frank: [00:31:40] Okay. Uh, it's actually changed over time, but I'll, I'll start with kind of your last question there. If you're talking about a true connection to SQL Lite, no, you do not benefit from multiple connections to it. If these are true connections, if these are true opening SQL Lite, if these are somehow pooled, you know, there's shenanigans happening in the background, then the rules are different, but fundamentally that's that?
Okay. When I first faced this busy error as I was describing before, this is when I started doing exactly what you're doing, where I. Forced single single-threaded access to the database through the use of a lock, um, and C sharp. You just use the lock statement and those locks are very efficient in C sharp.
So again, you're not, you're not achieving any level of multithreaded furnace here because there's a global lock you're having you're specifically not multithreaded at this point. Yeah. But what you're really doing is avoiding that annoying error that no one knows how to deal with. Cause it's too hard to deal with that error.
So that that's what you're achieving there. And that's where I started out also. Uh, so put these locks on everything. Uh, I often recommended the repository kind of patterns. So you don't really expose the public methods of your database, but you kind of put more, um, what do you call it? Domain specific access methods on top of it.
The problem is that that advice. Often doesn't it's good advice, but people tend to follow it by creating a bunch of create hat, delete hat functions. And at that point you might as well just expose the database. Cause like you're not really buying anything there, but th that's where you're getting into the territory with the RM.
But anyway, to start over, uh, that lock thing that you're talking about, that's a great place to be. Um, You're going to be very successful doing that 
James: [00:33:36] and to have been Frank for nine years. I mean, yeah. I mean, it's, it's, it's, I wish maybe I'll link to a few apps that we're in, which I wish I use all of this different sorts of things and, and you're right.
The, when I. Worked at Canon. A lot of, lot of his big architectural rise. I learned a lot, but we did very similar things. Like we reuse the repository pattern, we actually had an interface and then we implemented that interface and then used and use the eye for it. I think that that's overkill because nowadays for me, Um, you know, I'm never swapping out my database, um, uh, in general and every time I add or remove something I have to go in and just two plates doesn't make any sense that the interface makes no sense in general for it.
Um, and then, yeah, I think you're right. I think there's the common thing that people want to do, which is like get generic tier, you know, like I like to say, you know, get all RSVPs for this. I, you know, and then passing an ID or it's very. Very specific. So I can tune that specific method. Four in that query and the transactions, right.
For it, or it's like delete, you know, X or whatever, instead of just saying like delete, I would say, delete blah, blah, blah. So I could fine tune it for the specific use case. And that's something that I've always done as well. I've had these very high level concepts of the thing I'm trying to do to the database pass in the data and then I'll handle whatever the database is and the combine, because that does give me the way of.
Technically swapping it out in the future. If I did want to change it to a different database, well, my view models or whatever, whatever, my, my access layer, whoever's calling the database, isn't calling the database directly. So I've always enjoyed that pattern no matter what database I'm using. 
Frank: [00:35:24] Yeah. Yeah.
You're just protecting yourself, keeping the code clean. However you want to think about key clean separation between layers. There's a lot of good reasons for the repository hiding the database that said almost all of my repository patterns have like a public. Uh, database property on them as like an escape hatch, because you know, I know how databases work and I, I love software engineering.
Let's do abstraction. Yay. Encapsulation. Good. But at the end of the day, I'm trying to write an app here. And if I need to talk to the database, I need to talk to the database and get in your way. 
James: [00:36:02] When you need to get to the database, Frank and nee to get to the database. I, I agree. I mean, there's, there's only so many best practices you can do.
And so little time, I mean, for a lot, for a lot of this, some people will be like, Oh, you're not thinking about unit testing and this and that. Like, yeah. You know what I mean? Sometimes I just got to ship an app at the end of the day and that's okay if I don't test this method or do this thing, uh, you know, that's, uh, that's the, the motto I've ever lived by, but I am technically in a nice place.
And so are you in which I am writing software for myself and you're writing software for not just yourself, but I mean, you're for your living, right? You are. You're the boss. So you get to, you get to control that not everybody gets to control. You know, the, your architecture at your company. So of course, these are sort of James and Frank, what we like to do, but you know, I think these patterns at least can give you the flexibility to be as, as strenuous or relaxed as you prefer.
Frank: [00:36:58] Yeah. In my defense though, um, I am a huge critic and I'm a huge critic of my own code. So I'm trying to always have good code for future Frank to deal with. So that future Frank, isn't such a jerk all the time. 
James: [00:37:12] You always want. Future you to be happy with present to you. And that is a good motto to live by.
And, uh, and that, that has, has really helped me in, in past applications where I started with, uh, you know, one type of UI pattern swap to another one. It was super simple because it's, again, I just throw away this UI, build this UI. My MVVM does. It depends on 
Frank: [00:37:38] how 
James: [00:37:39] much you're comfortable, sort of. Tweaking a little bit of code.
Right. And, and, you know, are you just going to be like, Oh man, I just, I just wish it pass off just this little thing, but it's okay. It's going to take me an extra 10 minutes compared to, Oh, that's going to take me 10 days. 
Frank: [00:37:55] Yeah. Yeah. Uh, so, uh, let's get back to, uh, we're. We're not done with multithreaded and SQL Lite.
Oh no, we have just scratched the surface. 
James: [00:38:04] Right? 
Frank: [00:38:04] Kenny. All right. So all that stuff I said about SQL Lite being thread safe is a lie. Yeah, I'm sorry. At least it was a lie in the very early days. In the very early days, a SQL Lite had modes and you could switch between single-threaded mode and multithreaded mode.
And the big difference there was internal locks or not. You can imagine. Accessing the single-threaded mode of SQL light through multithreading created lots of problems. So, you know, what a smart thing was was they decided to, um, make multithreaded mode the default. So that's why I'm saying it's not so much an issue these days, because almost everyone is shipping the multithreaded version of the library with multithreading turned on by default.
So that's that I just, it occurred to me while we were talking that I forgot to mention that that didn't use to be the default. The default was much worse back in the day. Gotcha. So then I'm trying to make this, uh, this lock thing is effective, but it's a little bit annoying to have to put lock in your code everywhere.
So there is in fact, another feature in SQL Lite, which is called serialized mode. And the idea here is that it's not going to give you that busy error anymore. It's going to do another lock different from the database, lock a different lock, more like your lock, an application lock so that it will never try to execute more than one SQL statement at a time.
Therefore you'll never get that read. Right? Uh busy-ness and therefore your code will just continue on forever. So that's the serialized mode and a lot of people. Don't use that mode number mostly because libraries don't expose it always. And because it's kind of a new mode, but it's there and it's good because it's basically doing what you're doing in code with your lock statements.
James: [00:40:05] That's kind of cool. That's nice to know it just sort of, uh, go off and be out there. Yeah, I can. Can I do that with SQL ideation or not expose that? 
Frank: [00:40:15] It's there it's there. Yep. Um, so in SQL Lite dash net, it would be a part of the constructor arguments that I, oddly enough, called the connection string, the names aren't perfect in that library, but there are sequel light options.
I think that's what it's called. And one of them should be a serialized or synchronized. One of those words, there's a small problem, you know, not nothing's ever that easy in the world. There are conditions. In serialized mode that will still return Izzy. And that's why I still don't generally recommend it to people.
And that's why I don't use it as a default in SQL Lite dash net, because I basically designed the library to never give that busy signal because I found it so frustrating as a new user that I don't want to expose people to it. But, uh, so the probability of getting in as much lower, but still theoretically, you should still technically handle it.
And every single time you touch the database, you should have a catch for the busy exception, but no one wants to write code like that. That's terrible code to write. So I don't promote this very much, but it's there. 
James: [00:41:31] Yeah. That's a, that's a good point is, is I remember I was using. Some other library before that it was more of a problem when you're synchronizing files and it's like, Oh, you should also every single time handle, you know, a conflict between the servers we're in the Logan, uh, every single call that I've ever made to this database.
I mean, that's the right thing to do, but it's definitely not the thing I want to do. Um, so kind of funny, but yeah, it's kind of can be a little complicated though. The one last thing I want to talk about too, about SQL Lite. Maybe you have more things, but I guess the one thing I wanted to talk about is is that, um, it also does support.
In some ways, encryption with like SQL cipher and other ways too. I think sometimes people get a little concerned about, uh, well, is my data secure on the device? If it's just a file right on the desk, uh, w how was that going to work? Is it going to be secure? Well, most of the time, by the way, like your files that you put in the right places are going to be secure and your users aren't going to get to them.
Uh, but. There are like light DB also surprise of supports like this, but then there are some other database, like things out there like SQL cipher that also support database encryption with a key. However, I have to imagine that I I've never used them. I usually just do normal stuff. Cause it just app data to myself.
Have you ever thought about the different encryptions in your application and your data? 
Frank: [00:42:59] Yeah. Um, so for me personally, in my apps, whenever I have to put encrypted data down, it tends to be very small. It's like a password or an access token or something like that. So I tend to put those in the secure key chain, the secure storage.
I was trying to remember what Android called. It was called secured storage on windows. What's it called on? 
James: [00:43:23] Yeah. And essential as we call it secure storage on an Android. It's the key. Key store 
Frank: [00:43:28] key store, right? Yeah. Okay. 
James: [00:43:30] Key chains.  junk. 
Frank: [00:43:33] Yeah, but I will say that the encrypted version of SQL Lite there's a product out there called SQL Lite cipher, SQL cipher.
And that is not written by the SQL Lite people, but a third party, fortunately, a have a free version of it that you can use called SQL cipher. And I actually release a version of the sickle light dash mat that accesses this. This is. Pretty cool. What you're protecting against is the devices it's being compromised.
So if someone steals your device gets hardware access to it, they can get the files off of it. Uh, so now you want things to be encrypted. So this would be if you're exposing bank information, probably anything personally, identifiable would probably be validly put into one of these databases. Now, the weird thing is you actually have a lot of options here because.
Dot net has really nice encryption stuff already built into it. So you can very plausibly, um, maybe, uh, store the database on disk encrypted. Then whenever you want to use it, read it from disc unencrypted and memory, run the database and memory, and then whenever you want to save it, you could re encrypt it, rewrite it to disk.
So that is totally something you can write. And it's not even that bad in terms of lines of code, but it requires familiar familiarity with, uh, encryption and system security and all that kind of stuff. And it does have that little scary mode of you're writing to disk only once. So you better have a backup system and all of.
Problems with the file system apply. So that's where a product like SQL cypher steps in, and it handles all that nasty, low level encryption stuff for you. So it's. Become very popular. It's one of the larger, new get versions of SQL Lite dash net out. There are people using that version of it, but I wanted to put it out there that you're not forced into that.
You know, bytes are bytes. You can put them through any transformation you want, so you can write your own encryption or, uh, use something like SQL cipher. 
James: [00:45:54] Yeah. I use my own, um, I just, you know, encrypt like a string, put that in the database and then decrypted like for server. 
Frank: [00:46:02] Yeah. Yeah. Well, for passwords, that's kind of, yeah, that's definitely the recommended way to do it.
That's clever too. The trick is you gotta, you gotta put the key somewhere as with everything. So with SQL cipher, whenever you're opening the database connection, you have to give it up. Kind of a plain text key. So now you run into the problem. Well, where do I store the key, obviously into key chain, but you know, there there's a little chicken and an egg happening.
Uh, if you don't think through your encryption support completely, fortunately, um, It really is kind of that simple. If you're out there and you are using SQL Lite, you might've noticed in the last version 1.7, um, if you had created a database in 1.6 and tried to load it in 1.7, it gave you this nasty error, like cannot load database or database not found or something like that.
The people who made SQL cipher change the cipher algorithm. Talk about breaking changes, they just changed the default. So if I had created a database, one way, I can't load it with the defaults. Now I, I get really annoyed with people breaking API APIs. Technically they call the diversion too. You know, they did all the semantic version BS, but at the same time, they, and thus I broke a bunch of people's databases.
So I would say. Although I'm trying to make it a drop-in easy thing to use it. Still behooves you to learn a little bit about how encryption works and specifically how SQL cipher works and all the parameters to their encryption algorithm, because. They've proven that they don't really care about backwards compatibility.
James: [00:47:48] Yeah. That makes sense. Um, databases while they can be easy, can also be complicated. So it's good to know. Yeah. Well, 
Frank: [00:47:57] I, I even, I left off one part. I'm sorry. I, I just have to keep going with the multi-threaded stuff. I know you were trying to move on, but I left off one big part and it's really important.
Okay. So everything that we've been talking about up to now with the multi-threaded stuff was still just one time access to the database we weren't taking advantage of parallelism at all. And you aren't doing that with all your locks everywhere by definition. You can't have any parallelism. There is a new feature in SQL Lite and by new, I mean like five years old called, uh, the wall w a L right ahead logging.
And this is a very advanced feature from more of the server style, uh, database engines out there. And this is what finally allows multiple writers, multiple readers to be accessing the database simultaneously. Yeah. And that is a mode of operation that I do 100% support in secret by cash net, especially if you're using the asynch API.
So if you're using the asynch API and you did multiple reads, definitely multiple reads and potentially multiple rights can all happen simultaneously without your lock statements. So there are, uh, performance benefits to be had. All that said the memory system of the iPhone and the iPad is limited. So, you know, you'll only get what you get, but it's, uh, it's still better, at least in my head than, um, making everything single threaded, because at least it has the potential to be multi multitask multitask.
James: [00:49:30] That's kind of cool to say. Yeah, you did have a crazy amount of data that you were trying to figure out and trying to do and query that might be a good mode for you. Yeah. I'll often my applications are not that complicated. 
Frank: [00:49:42] Right, but you might just have that one big query and you don't want to stall the UI while that one big query is running.
So that would be a good place. 
James: [00:49:51] That makes sense. That makes sense. Anything else you want to talk about? 15 minutes into SQL Lite. 
Frank: [00:49:56] I could go for another two days. I can talk about coal light all night, but I will let you and the wonderful listeners go. 
James: [00:50:06] Yeah. I'd definitely be interested to know what our listeners are using and write into the show, merge conflict that I've found.
Like, what database have you used? Like, what are you finding successful? Do you use a light DB? Do you use something else or use SQL Lite dash net or using the audio version or using just monkey cash? That's not a database by the way. It's a cash player. Do not be confused. Like every time there's a thing that's like, can I do this?
And that's like, no, it's not a database. Do you use a database? Um, but you know, they want me to, yeah, let me know. Yeah. Cause I'd be, I'd be super interested in and if you have more questions, we'll, we'll try to try to answer them. We love our ELIZAs. This was inspired by Gary who wrote in before and uh, wrote in again.
But we love getting, uh, Emails are they're directly in our inbox or just hit us up on Twitter as well. Our E our Twitters and our emails and our discord are all on merge conflict out of em. But yeah. Frank, thank you so much for being the SQL Lite expert in the house. I appreciate it. 
Frank: [00:51:01] I can't believe this is what my career has led me to.
But thanks for letting me talk about SQL Lite, because oddly it seems like I can talk about it forever. I never realize that 
James: [00:51:11] it's how we met Frank. It's how we met. So 
Frank: [00:51:14] of course we can talk about it. Yeah. 
James: [00:51:16] It's it's we, we literally sat, uh, During a Xamarin meetup in Bellevue for two hours talking about settings and SQL lights.
So 
Frank: [00:51:25] that was not Frank. What nerds? Yes, 
James: [00:51:27] that's me and you. Best fans. All right. That's going to do it for this week's merge conflicts until next week. I'm James Monson Magnum 
Frank: [00:51:35] and I'm Frank Ruger. Thanks for this piece.
Um,