Refactoring SQL Mapping Library: Duke Items Repository Implementation

Duncan and Lucas refactor SQL code, implementing items Repository in Duke, discussing transaction limits and comparing it with exposed.

### Refactoring SQL Code with Duke: Implementing Items Repository and Understanding Philosophy and Differences with Exposed
* 🛠 Lucas Ada helps Duncan refactor the code from last week into an implementation of the **items Repository** using Duke, resulting in versions **exposed in memory and on disk**.
* 🧠 Lucas provides great insights into the **difference in philosophy and implementation** between Duke and exposed, highlighting the **importance of writing tests**.
* 🐞 Duncan discovers an issue in the code where there are **no items in the database**, leading him to write a test to **handle that scenario**.
* 🔒 While implementing the interface, Duncan and Lucas discuss the concept of **transactions** and the challenges of **handling them**.
* ✅ Duncan successfully implements the Duke items, with the exception of transactions, which he **plans to work on**.
* 🔄 Comparing Duke and exposed implementations, Duncan and Lucas note that Duke's approach is more **SQL-centric**, aiming to **mimic SQL syntax closely**.
* 📊 Duke's ability to support a wide range of SQL features and dialects is seen as a **significant advantage** over other SQL libraries.
* 📅 Lucas mentions that he has been working on the Duke project since 2009, and the current version (**3.18**) supports around **30 dialects**, with the next version set to support **at least 32**.

hello, it's Duncan. Last week, Lucas Ada, the brains behind Duke, helped us understand the basics of his SQL mapping Library. This week, he helps me finish the job as we refactor the code from last week into an implementation of our items Repository. By the end, we have versions in Duke exposed in memory and on disk. Make sure you watch the end as Lucas has some great insights into the difference in philosophy and implementation between Duke and exposed. That's why we write tests. That's why we write tests so the tests can have bugs. So we've managed and factors that in one query. We've obviously got an issue here around what happened and we have no items in our database, right? It can't be that we're able to get a Max of items modified but we have no items to match it because that's a logical impossibility. But we may have no items at all. So I'm going to write a test for that. Yeah, if you don't have an insert, then it will fail. Yes, so let's say so it feels to me like in

fact, if I pulled this thing into a VAR, right, that this is, and it needs a DSL context in order to be separated out from here, yeah, so that DSL context is the thing that's bringing a database connection with it, is that right? Yeah, and we'll be transactional eventually. Splendid. Okay, so if I make that a parameter and I figure that I can pull this out of here, but no longer becomes tied to my tests, and then I don't know, but I kind of feel like I want to pretend that that's a method on DSL context, so that would be DSL context load gives me a stop list after that feel too horrible to you. Well, it's a matter of taste, I guess. It's just a notation, right? A dot b or B and with a as an argument, yeah, it is one thing that I miss in every other language, extension functions, yeah, they work so nicely and it so it should be also the same thing here, right? So I should be able to say this thing is save and that should

be my stock list which is the parameter in here and that wants to be a parameter yeah now you do a uh so that's my DSL context and then I say the same thing here. I say make that a receiver pick this thing and take it out of here and they feel like production code right and then I can inline that and I can say test load I think we had a rule so we don't save it we load it and it shouldn't look like an initial stock list it should look like a stock list it was last modified is instant Epoch yeah okay that's probably going to fail currently open fail okay but it hasn't filed in SQL which is good for us right yeah there's nothing wrong with query yeah so and it just returned an empty set then I'm going to say um that if that was if that was null if last modified equals null then this should be instant epoch else Mass modified ah all the types not correct so that wants to be let's just take that away we'll say Lift last modified is

because now that else last modified. First run those tests awesome. I don't like the fact. The IntelliJ is crashing every time I do that. So I wonder whether the fact that it's crashing is responsible for that. But that's a bit better, right? It's not really last modified. It's a pair of last modified. But that'll do, I think. We may have everything we need to implement our interface transactions as well. Yes. So before we go on, talk to me about transactions. What the limits a transaction here currently you're in autocommit mode, which is default in JDBC. So every query commits automatically by itself, which actually given that we are doing our load in one query is probably okay for a certain year, right? Yeah, I guess in our database version using exposed we were a bit embarrassed because we had to go and fetch in two queries that's here. So I had to do get last update and so I had to make sure I was running in a single transaction there actually we have no other transactions.

in the system. So in fact, this would be fine, but let's implement what we've got and then we'll add transactions on top of that because I think that's doable. So let's get rid of that and that, and we've got our Juke test, and we've got two nice little bits of code there. So let's remind ourselves what our items look like. So we have save, a stock list, and load. And so basically, we've already got those now, haven't we? So let's go to, we've got Duke here. So I've got Duke tests. I'm going to write new class, and we're going to call this Duke items tests. How do you capitalize Duke in Java? Yeah, I didn't think about this. It's almost impossible. How do you camel case or possible case sequel, for instance, SQL? Yeah, I mean the rotating element URL and URI, didn't it? Yeah, yeah, but they don't agree. That's the JDK, and it's always different. XML is the

same thing. I'm going to copy that and put it in here. This is my sort of template. So, the contract here, it manages what should happen. Like it says, So you see, we've got returns an empty stock list for any table. We've just written that, right? And, Returns the last eight stock list. We've effectively written that. So, this manages being the same test for all of our implementations, and it requires an implementation of items to work with and then the individual tests themselves. So, our database items tests are responsible for resetting the DB in order to empty between tests. So, if we were to take something like this and put it into my Duke items tests, I think this is what sort of TDD goes wrong, isn't it? Because all these tests have to pass. I need to now move everything over into this abstraction that exists already. But how do we reset the database? So, we had a DSL context and we did clear DB. So, I'm just going to copy those and in my Duke items tests and test data source, I should just be

able to import from that place right. We'll share it for now. So that's our Duke version of clearing the DB. And then this wants to be our Duke items, which doesn't yet exist. And that was on the test database, which is exposed. That's not what I want. So that's on my DSL context speak. So for now, at least, I'm just going to pull that out of this test and worry about that life cycle later because that's probably going to be a sort of Singleton shed between all the tests. I guess is normal, isn't it? Yeah, most people use when you use spring, you just inject that, yeah, or whatever brings solve so many problems for us, doesn't it? It creates a new ones, but, uh, okay. So I'm gonna give myself a Duke items, and I'm going to put it in, you know what? I'm going to leave it in here for now, okay? So I need a DSL context, okay? And it's and items, and so we need to implement some members in transactions, so on, and this thing is our transaction type.

and somewhere I was cheeky and had sort of the idea of no transactions, and I can do that for now. So we've got the TX context, and they are like no transaction. So what I'm going to say is until we do it, we don't need a transaction. And that means that that needs email transaction. Okay, getting there, getting there. Okay. And now our test is also doesn't have a transaction. Okay, so we just don't have any code, but that's quite all right. So let's look at Duke items and say we haven't don't have a transaction. So this thing just needs to return the result of running that block with the input since the no transaction. That's that sort of way of getting to how. How do we run transactions intelligence formatter still really doesn't understand context enough to they think that. So you can read them, but I think we've got this already, right? So we can go to our Duke tests and make this public. Uh, well, in fact, of course, this is Kotlin said that's just like the truth that's.

still private and we had save and we'll make that that and ignoring the issues with a result type and whether we get errors which I think we should for now this thing is return success of our ESL context save a stock list okay results stockness loading error found ah okay so now there's a small wrinkle here which is that I have save return the thing that it just saved in order in order that you're forced to go and check the Air Attack basically but let's that's easy enough we can say we can just take that out of here for now and we can say return success of the stock list here we have the same sort of problem well the same sort of solution model's point we can say return s of our DSL context so okay I'm wondering why that says it's not that where is that oh that's interesting have I typed those differently ah that needs to be a ow that's the one I get oh okay cannot be cast to expose transaction context okay so I just messed up

one of the things in my contract that zone transaction that's no transaction ah here so that should be that we we don't have a transaction type for now. See I don't think that should compile okay we're very close so we've got one working oh Precision problem no so we're actually getting a last modified returns an empty stock list so we're getting oh turning up deleting stuff we're not deleting stuff are we no picked up. I think what's happening is that I am running the test in parallel and so let's and because the Juke ones aren't transactional you can reach the the other transaction and you're deleting stuff only at the beginning of a test yeah instead of at the end there we go oddly enough. One of the things one of the things on my list talked to you about was how to run tests in parallel with you yeah well each each thread could have its own database or you have to be very sure of your transactions. I mean in the end in the end you want to test

transactional code. There you have a bug, right? I've written test code again Amazon SimpleDB, for example, and in those cases, yes, effectively always just used to have a table name appended with some random things. Yeah, so Juke has a schema mapping feature where you can map your items table to items whatever so you can add runtime decide what the actual items table will be that Duke writes to or reads from. That would be perfect for this kind of multi-tenancy that you want to do. So I think fantastically we are where we wanted to be, are we, except for transactions. So the lesson we've learned is parallel tests with persistence is a problem, and I think it's a problem we solve in the rest of the code base because we set up temp directories to save our tab-separated file, so that's not an issue at the moment, at least. The simple thing is to just not run a test in parallel, and I'm sure there's some way of telling JUnit that I want to run these tests in parallel or not these.

so on, so I will fix that all by myself. But I think we have managed to get Duke items working, with the exception of transactions. And I think at the moment, certainly it is safe to not have transactions. But I will plug away myself and have a look and see whether I can work out how to do that. So we've managed to implement our contract we had with all of our other implementations, which is fantastic. We've got Duke items. Let's compare that to database items, which is the exposed version. So we'll ignore the transaction bids we have here saved. So we have two saves and two loads. And isn't it actually, it's impressive how the shape is the same, isn't it? Yeah, it's a very simple application so far, right? So there's no magic so far. No, I wish I could have shown you multi-set and not some more advanced stuff, but I guess the first step of refactoring, it's gonna be very simple. Yes, yes, we haven't, we have no joins so I

there will be a time when gilded Rose is such a world-class organization that so comparing these two. We're doing for each in both cases. In fact, we're saying stopless items for each stock. That's three same thing insert into items here though. We're saying we've got this items table insert. And I suppose to me that's the fundamental difference is that this DSL context gets involved and carries the database connection with it. Where exposed has magic a transaction now to thin air. Yes, that was the thing I certainly didn't like. But this items table insert into oh. So there's a difference here. I suppose the items table is a type that we are calling things onto. And here we're sending insert into items. And then I can do the other style as well with Duke right. You can have an items record and then add to its properties. And then insert call insert on it. I suppose though this is this is because you're starting for SQL really isn't it. You're far closer to the SQL syntax here well.

if you want to do SQL, and I mean more advanced SQL, then we have to support the syntax as well for insert statements. And you can run insert select. You can insert on duplicate key update. You can run a merge statement, yes, but for the really simple crad. So, for this particular example, we could have mimicked exposed syntax by just creating a new items record, setting all its properties, and then call store on it, right? So, in this particular case, if you're looping anyway, that would be an option, yes. And actually, in some ways, think that might be good for me because that feels super slightly more reusable. This mapping here between whatever this insert statement is and an item is a single thing. It's in one place, whereas here, we sort of split it between here and now. Yeah. So, you can mimic that syntax as well even with SQL. So, after insert into, you can type set, and then you have the exact same map, right? Oh, brilliant. I will play with that. Load load is where we, I think, we have a

major advantage because in the case of exposed having to do things in two goes. We're having to go and get the last modified and there's this slice which I never really understood to be honest with you. It's not SQL and every every time I used Xposed I had to look this stuff up whereas a sub select. I think I mainly tag this myself tonight. The nice thing is that we're running this in one go so we've got one trip to database as opposed to two trips. I saw who've got the impression there was some way of doing that sort of thing in exposed but largely you're on your own. I found if stack Overflow doesn't answer your question you can't go to what's equal you would have written whereas this way I feel that I can at least go to SQL to the extent that I understand SQL. Yeah, the philosophy of joke is really you you want to write SQL and you want to for some reason have it embedded in your cuddling code maybe even Dynamic.

then that's the only difference so you should really think in terms of SQL and you have physical syntax in your mind and then you write exactly that. One thing I think they do share so I've sort of used hibernating to Something jdo in the past. Wow and those things are very much sort of based on building types and having those types populated for you as opposed to building queries and having those queries Marshall for you and the exposed both. I guess I guess neither are orms are they they're not doing relational mapping what they're doing is is query mapping. Yeah that's how people often distinguish between your things so with an orm you want to have an object graph that you want to be able to to persist so the focus is also on persistence when you write which is probably a bit more difficult if you have a complex tree of data with items and item child children and maybe parent entities and you want to move everything around and with SQL directly or with Juke or exposed it's a bit harder than

with an orm but once you query your data, it's the query's complexity that needs to be modeled, and the mapping maybe and we didn't get much into the mapping of Duke but these kind of things work really nicely with Duke so for instance when you want to project a really complicated nested collections you can help you here in a typesafe way so you can you can have arbitrary levels of nesting and fetch everything in a single query I guess with a single table this isn't going to be very impressive in your case but once you have other tables and instead of joining them you can also decide to just Nest them and get some sort of hierarchy of data in a single query and this is going to be really nice especially with kotlin at one time I'd like to hibernate but I came back to a project it was it was a spring project uh sorry spring it was a swing project so it wasn't a natural home for hibernate but established that it's because of the orm nature of it this

project effectively loaded the entire database into local memory. Yeah, it's hard to tune correctly, right? So the defaults are often wrong. You either fetch everything and then you get too much data, or you lazy fetch everything and then you get too many queries. Yes. And, of course, you can write the queries manually, but it's always there's always a risk of accidentally forgetting something in a query, and it's going to be fetched automatically in tons of extra queries. I think certainly the thing that I've learned over the years is that relying on your database to offload as much work into queries as you can, bringing back only the columns that you need to answer the question you have right now is a far, far nicer way of working than bringing back everything about a table in order to answer a simple question. Yeah, it's going to scale better. Yeah. Ah, Lucas, thank you very much. I plan to go and look at transactions and not waste your time with that. I think the really interesting cases where I can help again is when you

have more than one table so we can look into some Advanced we can do another session maybe look at some Advanced use cases. I guess it doesn't make sense with only one table right now but um the nice thing about starting the beginning yeah is there is there anything else you would you would like to say about duke or exposed. How have you found exposed by the way I'd be interested. I mean I don't have any opinion about it it's just I I've seen tons of these apis come and go in the past. Exposed follows a similar model as a Scala slick so they uh kind of invented their own query API for some reason not mimicking the SQL syntax so I I guess they wanted to make it look more like a kotlin but in my opinion this quickly breaks down once you want to have some Advanced syntax mapping. So like for instance in SQL you have lateral joints or window functions or grouping sets and how are we going to represent that in a kotlin style it's almost impossible. They're gonna invent

new API. And these kind of APIs are really hard to learn. So is the user going to invest into this API? I don't think so. So this was the strategy with Duke: to not add anything that people have to learn. So there's nothing Duke specific except for setting up the code generator. But once you have set that up, it feels like SQL. So it feels like you're writing a store procedure, right? So it should be different. There shouldn't be any hurdle, any challenge to learning the API. So I think this is the main difference. They could have done the exact same thing, but weirdly they didn't. I don't understand why. Other than that, I mean it's as you said, it doesn't have too many features. I don't know why there's no limit to how many things that should be implemented. It's just a matter of how much time you invest. I think there is that investment, isn't there? Yeah, yeah. And it gets really hard. I mean, it looks so

simple at first right select from where but once you start adding unions and limits and all these kind of things so limit looks really simple but it's extremely hard to to implement correctly for all dialects right so some dialects like MySQL and postgres just have limit and offset and it's very easy but if you want to support all the dialects you maybe have to Nest your query using window functions and derive tables and that gets really really messy and everyone who's tried even including me many years ago make the same mistake of thinking that it's uh it's simple but it's really extremely complex so this is kind of the point there's always people who think they're going to be limited by the DSL so the actual language the SQL query language is is obviously even more powerful than any DSL can ever mimic including Juke so there has to be a lot of value in that so and this this value can only be achieved uh by offering an extreme amount of SQL features right so if if every every time you want to

have some substring function. You have to kind of shoehorn that into your query with some work around. The tweak people are going to hate the DSL because it starts being limiting. So, it should be enabling. And that means you have to invest a lot of time into features. I mean SQL feature support, for instance, Juke also supports spatial features by now, and tons of vendor-specific stuff, and JSON. We didn't touch on that or XML in the database. So, Juke supports all of that because if you have the problem and you want to use the feature, you can use it with Juke. And I think if you want to implement a DSL, it should be as close as possible to SQL. It should be very, very big to DSL. So, it should support almost all SQL features. I personally also think it should use a code generator because if Juke's users maybe have databases with thousands of tables, and there's no way you're gonna handwrite those table definitions again in Kotlin, right? So, you need some sort of code generation.

at least as an optional feature. Yeah, I just don't. I don't have any experience with any with uh exposed so I can't really be specific about it. I mean I understand, for instance, the syntax to create an object like that object table is kind of a compelling for simple applications and people have done that with Duke as well. It's possible to do exactly this with you instead of generating the table. You can write yourself using internal API at your own risk of course, but I think you're still not going to get all of the features out of it. For instance, if you use aliasing, we didn't do that, but you can write items as you can try it really quick. If you write items dot as and then use an alias, it has to be right after items. Yep, so you have to quote that in Kotlin, unfortunately. Ah, yes, yeah, but now this object, you pass a string, an alias, and you can still de-reference columns from it. So if you type dot ID after banana, I mean after the parenthesis.

outside yeah there you still get all the type safety and everything so it's really hard to see how this could be possible without code generation. I think actually that just that idea that SQL is big and you don't want to be limited by the supporter SQL in your mapping layer I think is a very persuasive one. You either have to do the very very minimum so I mean I know people who quite happy basically with intellij's embedded SQL in string support and you can write little methods yourself and it's all good you are untyped safe but that's okay largely that's one end of it. Otherwise you pretty much have to support all of SQL in in your DSL in some sort of way and that's a big job that's a big job just with SQL and it's uh it's an even bigger job when you consider that you then have to make things work across databases so you've got those two things how long have you been working on the project I'm fascinated uh 2009 so 14 years now because it's

quite old by now. So right now, uh, Juke 318 supports, I think, 30 dialects, and the next version will support at least 32. I've never seen as many dialects being supported by another SQL Library. It's really, uh, no one, I somehow, no one actually goes to the end and does that, right? So let's go because a lot of SQL language features, plus a lot of SQL database products, that means the test Matrix gets enormous. Yes, I can imagine. Well, Lucas, thank you so much. People can find you on the internet at duke.org, yes, and follow you on Twitter, I suppose. Are you still on Twitter these days? Yeah, yeah, until it goes down. I have enjoyed this so much, as I say, it's been lovely, lovely to work with you. I'm sorry for taking up so much of your time. Let's hope I managed to edit this down into something that takes up the slightliness of the viewers' time, but I think I'm convinced, you know, I think Guilby Rose is going to adopt you.

Yeah, I hope so. There's no turning back. [Laughter]

jOOQ vs Exposed - Kotlin database access with Lukas Eder

Advanced Kotlin with Extreme Programming (XP), covering Java to Kotlin, Test Driven Development (TDD), Refactoring, and Object Oriented (OOP) and Functional Programming (FP).

Co-author (with Nat Pryce) of the O'Reilly book Java to Kotlin - A Refactoring Guidebook, Duncan has been programming computers since 1981, but still consistently fails do things properly the first time. Luckily he is much better at refactoring than he is typing, so can you can watch him fix things on this channel.

Eventually this channel will make Duncan enough money to return to the first person, but in the meantime you could show your appreciation though the medium of PayPal - https://www.paypal.com/paypalme/DuncanMcGregor



Refactoring to Kotlin

Refactoring Code with Lucas Ada: Implementing Items Repository in Duke

Lucas Ada helps Duncan refactor the code in Duke to implement the items Repository, with versions exposed in memory and on disk.

They discuss the difference in philosophy and implementation between Duke and Exposed, emphasizing the importance of writing tests.

The conversation also touches on the transaction limits in autocommit mode and the challenges faced in the database version using Exposed.

Refactoring and Implementing Transactions in Duke Items

The focus is on refactoring the code and implementing transactions in Duke Items tests.

The TDD approach is being discussed, ensuring that all tests pass and the database is reset between tests.

The process involves creating a new Duke Items class, dealing with the DSL context, and implementing transaction types.

The goal is to run the block with the input since there is no transaction, while also addressing the issue of the formatter not fully understanding the context.

The speaker is discussing a problem with their code, specifically related to saving and returning a stock list.

They are encountering errors with the transaction context and parallel test execution, leading to unintended data deletion.

The speaker considers the possibility of running tests in parallel and discusses multi-tenancy and schema mapping as potential solutions.

Implementing Duke Items and Database Items

The implementation of Duke items is now complete, following the same structure as database items.

The fundamental difference lies in the involvement of the DSL context and the database connection.

Mimicking the syntax of the Exposed framework is possible even with SQL, offering a potentially more reusable approach.

The Advantage of Using Exposed for Database Operations

Exposed allows for performing operations in a single trip to the database, unlike the two trips required in SQL.

It provides the ability to write SQL queries directly, offering a more familiar approach for those comfortable with SQL.

Exposed focuses on query mapping rather than object-relational mapping (ORM), allowing for complex nested collections to be fetched in a single query, which is especially beneficial for Kotlin projects.

Efficient Database Querying and API Usage

Efficient database querying involves bringing back only the necessary data to answer current questions, improving scalability.

The use of a simple and familiar API, like Duke, reduces the learning curve for users compared to more complex query APIs.

Duke's approach focuses on minimizing the need for users to invest time in learning the API, offering a SQL-like experience without unnecessary challenges.

The Complexity of Implementing SQL Features in DSLs

Implementing SQL features in DSLs can be much more complex than anticipated, especially when trying to support multiple dialects.

The goal should be to offer extensive SQL feature support to avoid limiting users and instead empower them.

Using a code generator for DSLs can be crucial, especially for databases with a large number of tables.

The Challenges of Supporting Multiple SQL Dialects

Supporting multiple SQL dialects in a mapping layer requires extensive code generation and a deep understanding of SQL language features.

Juke 318 is a unique SQL library that supports an unprecedented number of dialects, posing significant challenges in testing and compatibility across various database products.

The interviewee has been working on the project since 2009 and continues to expand its support for different SQL dialects.