Okay, hi, I'm Nikolai, how many of you use CICOS already?
I think quite a few people, nice.
Yes, my talk is about building on top of CICOS.
Couple of words about me, I'm Senior Software Engineer based in Helsinki, Finland, and
past seven years, I used go for writing in production in Zalanda.
I offer a couple of projects, one of them was presented here at first in Lightning Talks,
PGX Outbox, and the second one I present today is CICOS C++, which is a playful name,
because I used to do some C in the past.
So the goal of this talk, the presentation, is about to create repositories on top of CICOS
Cgenerated queries.
So I'm trying to keep the strength of CICOS, type safety and compile time checks, but at the
same time, I don't want those generated records and queries to leak into a business logic
of my application.
So some kind of mapping would be required between generated structs and my domain models
and I tried to automate that one with AI.
So it's a second talk about AI today.
And what I mean by repository, it accepts and returns the main models.
I tied some CICOS and schema and query details, and it does the mapping if needed between
the main records and database records, which in our case is CICOS Cgenerated structs.
It can also do some transaction orchestration between multiple operations.
Yeah, let's introduce the main model.
So in Zalanda, it's about e-commerce, so I'll introduce a cart, where a user can add items
and then get the cart, see the items which are added, and then delete items from the cart.
So that's like a simple, our domain model cart.
So it can look like this, also quite simple.
There is an order for the cart, there are multiple items, and then each item has an
ID and a price created at times time, and so on.
That's how it can be stored in CICOS.
Yeah, quite straightforward, but it's a bit still different, because in cart we have
owner ID, but here we have owner ID in each cart item, so there is some, it's a bit
easy mismatch, but it's already some kind of mismatch we should try to address.
Yes, and now I would like to do some review of how we used to work with positives from
raw.
So when I joined the company since years ago, and in the same project we used to use a
standard API for talking to CICOS, and then at some point of time we started to
do a squirrel to build our queries in more dynamic way, and we started to use CICOS
to do the mapping to simplify the mapping scanning from the database results.
Then at some point of time we started to migrate to PGX driver and then to the API of
that, and now maybe a year ago we started to use CICOS and kind of a CICOS
and then to roll out our services, and our RMS out of scope for this talk.
So it started with standard driver API and the driver was LIPICU, because seven years ago
and we didn't use query builders that much, and we used like many of scanning, and this
is how it could look like in the code, someone still using that approach in production,
okay, some people, and then once we started to adopt a squirrel, we kept driver the same
and CICOS X, and then the code could look like this.
So squirrel, it provides the main start shot language to build the query, so it's not
CICOS anymore, but it's a goal which represents the CICOS, and then CICOS X, it's simplified
how we can do this tenant, but on the other hand we had to introduce additional structs,
which has DBTex and DBTex, and then at the end we would do the mapping back to the main
model, because we wanted to keep the main model the same.
And then we started to adopt PGX, which is more modern driver, yeah, we still kept a squirrel
for building the queries, but we started to use PGX, collect rows, function to do this
scanning, and then can be done directly into, I can be done directly into the main model,
it can simplify this scanning, yeah, so it counts, so if we migrate to API, if PGX, which
is not a standard library API anymore, then we can use some additional features supported
for Postgres, like Batching and Listen Notify, and Prepare Statesments and so on, it does
much better type handling, so it has more types which it can handle by default, and it has
advanced connection pool, and now comes CICOS X and let's see how it can help us, so in the
nutshell CICOS X, it's a static generator, so it's not, it's a deterministic generator
which would read our schema, our migrations, use it to read the queries, which we define
separately as CICOS, almost CICOS, in a separate file, and it reads our config, and then
it generates go queries, and it generates structs, which corresponds, which helps queries to
be executed, and then it use one-to-one table structs for our tables, which I don't usually
use at all, I use mostly go queries and structs, so if we adopt CICOS X, then we start to use
like pure CICOS, so it's like more CICOS first approach, not OREM, not building that as DSL,
but we use almost pure CICOS, yes, and struct mapping is automatic now, but it's automatic
to generate a track, so if we want to use the main models and now business logic we still
need to do the mapping, so this is how it could, could look like, so here is the query, which
I define in one of the CICOS files, and after generation, this is the go code, but I just
combine them together here in this file, so now we have like a building block, this get card,
it's a query in go, in go, which we can use as a building block in other places, in
repository layer, we can use that one, so what's the process CICOS, one of the main advantage
that's it does compile time, it can do the type safety, and it happens in compile time,
so it will be able to resolve the types of what we have in the database and accordingly
generate our strats, how it can do that, because under the hooded uses partial library from
post this itself, it's a before it used to be CICOS, but now they get rid of CICOS by my
guys and to this so I very go PG query, so and at you at leverages on app 163, which was
mentioned in one of the previous talks, so it also generates a lot of boilerplate code, which we
had to write previously before, and it does separation of CICOS and go code, so what is simply,
apps like simple apps like syntax 3, it's shown here, although it's a simplified version,
but it's not like just a text, it's CICOS query, but it's more like what's the semantic meaning
of that query, so it leverages on that, so under the hood what CICOS it does, it treats all
migrations and for each migration, it's builds on app 163, then it nudges them into CICOS C and
turn on catalog, and once it's wants to generate the query, it will to look up what's other types
in the catalog, so that's why it knows what types of return data we get, and then it can
do the safe mapping into go types, so there are certain challenges, if we start to use CICOS,
one of them that is kind of easy to leak generated records in our business logic, so to avoid that,
we need to do some mapping between records and the main models, so also many people would ask how
you would do with dynamic conditional queries, because now CICOS query is a static, I can
in the scope of this talk, I can say that it's solvable, but I don't have much time to go into that,
so about repository, as I mentioned before, repository should idea that it should work on
it with the main models, not the generated tracks, so in the simple case, we need to do some
delegation from the repository method to generate a query method by CICOS, and then do the
mapping of results and so on, so yeah, so repository, it's like more than the CICOS CG United query,
but the generated query can be used as the building block to implement repository, so here I would
like to introduce CICOS plus plus, which is a playful name, yeah, so the idea was that this kind of
glue code like the mapping and delegation is some boilerplate code to write, which we can ask
AI to help us to do that, so we would just provide the interface, so we still do the CICOS
preparation work, but for the repository layer, we provide the interface and then ask AI to generate
and do the mapping between generated previously, the terministic generator, so what we get,
so in the here, at top, what's been generated by CICOS, it's a ministically, we feel that
into AI based generator, and then we also provide repository interface, our domain models, and we
have some reference implementation, somewhere in GitHub, this explained to AI how we used to do that
delegation, how we write the tests, integration tests for our database access, where and so on,
so this is in the nutshell, so like I had this idea, a couple of times, some months ago,
and I started with them to go and some simple text and play, but in April, it was a rise of those
coding agents, I think Cloud Code was released, so I made this one migratory to custom commands,
and I think next generation is actually extract what we have in the reference implementation
and make a skew out of that, so we can leverage and skew, and skew is like, it's the next generation,
I think, for this project, so for the purpose of of for them, I found this crash,
crash is a coding agent, which I met some founders in conference and Florence, it's open source,
the license is a bit tricky, it's FSL, so it's multi-model, and it has LSP, which is a language
server protocol, I go police's implementation for this server protocol, so it has support for
that, so in February it would spend less tokens, and in support extensions like agents,
kills, and MCP and custom commands, so this is some example of custom command I can use for this project,
for example, so I just define some number of steps, and I say just first run,
SQL, C Generate, and then what you got as a result then collect that into some context,
the content of those files, and take the repository interface, I would like to implement,
and then do the generation of integration test first, it's not mentioned here, and then
a repository itself, and then I make sure everything passes and compiles and so on, so yeah,
and a couple of words about reference implementation of the project, so I use it,
I make it a bit more complex comparing to the demo, I intend to show, so it uses actually two
tables instead of one, so it has some transaction support, if we create an order, we have some
order metadata, and then we have order items, and we insert them in the transaction, and if we delete
some order, we also do that in transaction from multiple tables, and then it supports like
create operation, and on top of that some search by filter, solve delete and so on, and I try to
add a different few types for the gods, for them, for the posthus itself, so I try to show you the
demo, so first I need to run crash, because it would, it would take some time, so as you can see,
they have a goal and a zero protocol, which uses a go-police on my machine, and I have a goddalk
MCP, this is a different MCP server from Danielle show today, but it has only one tool,
it can read what the god documentation, but it does that in a bit more optimized way to save
the tokens, and now I'm going to run my custom command, I have the custom command, it's called
January 3, and I would do that for card, which I showed in the beginning, so it it read,
I read my custom command, and it starts, it made a plan, it has seven steps here, and it
doing some work right now, so use code so net, yes, and LSP, it also uses LSP, something is happening
under the hood, so ideally it will help us to use fast tokens, and while it's working, it takes a couple
of minutes, I will show you some source code of the demo project, what I have, so I have this
is CQC config first of all, so I have to specify where my migrations are, and where to generate
the queries, and what driver to use, and some additional parameters, and I can customize some types,
like if there is ambiguity, what type in god should be used, for example, you ID, and then I can
say, okay, which library I should use, and for time also, I want to use god time, but by default,
it would use PGX time, so what else like my CQC data is here in this file, so I showed, it has
free methods, so these free files, models, DB, they are result of generation, which happened just
now before the demo, they were not here, so my domain model is card, as I showed before,
money, some migrations, so yeah, this is important the card repository, so here I just provided
the interface, and I expect that AI can implement, so it's not yet here, I should generate the files
in repository, and for crash I use custom command, which I have to place here, also it supports
Qs, which I deleted for now, but I can need to customize if you use crash, it doesn't know about
Qs by default, but you need to to say where this Qs are, and here are permissions, which I use,
so I don't have to be asked each time while it's working, but I would say that granularity of
permissions is different from other coding agents, which I used before, so there is a room for them to
improve that, so yeah, still doing something, okay, maybe I can, while it's working, yeah, so
while it's working, I have a screenshot in case it wouldn't be working, so this is a recap for that,
so I added custom command, and I just say to generate by the interface implement repository and it
made the plan, it's working on the plan, yes, and it also would generate me integration tests, and for
the integration tests I use in the reference implementation, I use testing containers to run a
real puzzle list, to do like proper integration testing, and I use a suite package from
a structured testifying, so I pop your library for like a certain require, but suite is another
useful package, I think, because I can start the container, like it's good for a lifecycle container,
I can start that before all the tests I run, and then shut down, like then all the tests are done,
and the table tests for each repository method, and I use a couple useful libraries, go fake it,
to generate some data on the fly, and compare by Google, which is kind of an official library
to compare the structs for tests in a smart way, and this is the recap of the architecture of
SQL C++, so it takes what SQL C deterministically generated, and then that's some delegation,
and mapping by example, which is in the GitHub, and eventually we get repository, which only depends,
and the main models, and I should be like fully tested and use, because integration tests are
generated also, let's see what we have, still almost done, but not yet.
Okay, we actually have some files being generated here, and repository, they were not here before we
only had this interface, but now we have repository, which I got generated, you can see that
from the previous version, which I have committed, it's not, it's not 100% the same,
still a Y is involved, it doesn't generate exactly the same results, but functionally it's
more a bit of syntax difference, but functionally it should be the same, and here are the integration
tests, which use test containers, it starts like real posgres, and so on.
Yes, and I think actually, yeah, it's actually done, okay, it's done, actually yes, it's done,
yeah, so the code is done, and we have a report, and previously I used to have either code
okay, so now it's like fully tested, I almost 85% coverage for all integration tests,
and I'm running out of time, but I'm exactly at the end of my presentation, so take a
waste at the dobb sqc, and you can adopt crash if you really like open source, like you can
contribute to both projects, and I advise not to use sqc, it starts in the business mode,
I want you to because it creates a tightly capped coupling, so I advise to use repository,
pattern, but the boilerplate can be automated on this delegation and mapping, and augmented
generation, by the reference implementation, it provides more stable results than just like saying,
okay, you generate me data access layer, and thank you very much. Thank you!
If you still have questions, you will be available in our hallway track for any of your
SQL or AI questions. Thank you.