Generating table structure in code

So I'm not the biggest fan of generating the table structs from SQL but I also didn't like the macro based solution sqlpp11 had.
Instead I like to have my types as simple structs, that I can easily read and which allow me to use designated initializers.

Which is why I've had templated classes to create those table structs for sqlpp11 already and now I've also tried to do something similar, quick and dirty for sqlpp23.

So, at the moment this allows me to define my tables like this:

namespace identifier{
	SQLPP_MAKE_IDENTIFIER(user);
	SQLPP_MAKE_IDENTIFIER(id);
	SQLPP_MAKE_IDENTIFIER(name);
	SQLPP_MAKE_IDENTIFIER(display_name);
}

struct user{
	std::uint64_t id;
	std::string name;
	std::optional<std::string> display_name;

	using table = sqlpp::table_t<sqlpp::tablegen::table<
		user, identifier::user_t, "user", sqlpp::tablegen::fields<
			sqlpp::tablegen::field<&user::id, identifier::id_t, "id", sqlpp::tablegen::field_options::required>,
			sqlpp::tablegen::field<&user::name, identifier::name_t, "name">,
			sqlpp::tablegen::field<&user::display_name, identifier::display_name_t, "display_name">
		>
	>>;
};

inline constexpr user::table user{};

int main(){
	// ...
	db(sqlpp::insert_into(user).set(user.id = 3, user.name = "abc", user.display_name = std::nullopt));
}

having the identifiers separate is also a benefit for me, as you're often using the same identifiers multiple times (e.g. id for multiple types) or want to use them for aliases (though I haven't looked into those for sqlpp23 yet).

You could also go even further with this and maybe create mappings between those types and the query result fields (like a query_result.merge_into(my_obj) to update a "cached" object) or return this aggregate type in case of a SELECT * FROM .... But this might be a bit intrusive.

I haven't really tested this yet, it's basically "it compiles" at the moment, as it was just trying it out and I plan to change it up still.

Downsides would probably be, the error messages being worse than with "hard-coded" structures and also more to maintain of course (so far the code is quite managable I think).

So my question now is, if this would be something desirable for sqlpp23 itself, or if I should keep it in my own fork/repo, which is totally fine with me as well.

-Jonas

@vaijns

Thanks for sharing, @vaijns !

I have a couple of questions / comments:

Is the multiplication of identifiers intentional? I see the ids being in the field template three times?
How do you make use of the references in the fields, e.g. &user::id?
I wonder what you really gain from using entries in the identifiers namespace multiple times?
sqlpp::detail::make_type_set_if might replace your conditional_type_set.
That said, I think it would be rather easy to move the calculation of _required_insert_columns into sqlpp::table_t.
It might make sense to re-arrange macros in include/sqlpp23/core/name/create_name_tag.h so that you would not have to duplicate stuff.
What is the benefit of your inplace_string vs. constexpr string_view?
Today I learned about std::bool_constant :-)
Looking at generated code, e.g. in tests/include/sqlpp23/tests/core/tables.h, it seems to me that it should be radically simpler to achieve what you are doing? But then again, I might not understand all your use cases.

Looking forward to the next iteration!

Cheers,
Roland

Let me begin by saying: tbf, practically I don't have much of a use case, that couldn't be solved by the ddl2cpp generated code at the moment.
My intentions are mostly based on the idealistic desire to have my C++ code as my single source of truth for the table structure (well... Besides the actual database that can be out of sync) instead of the create scripts.
So in an ideal world, all I'd have to write would be:

[[=sqlpp::sql_name("user")]]
struct user{
    [[=sqlpp::sql_name("id")]]
    std::uint64_t id;
    [[=sqlpp::sql_name("name")]]
    std::string name;
    [[=sqlpp::sql_name("displayName")]]
    std::optional<std::string> display_name;
};

Basically all data that I need in the simplest form possible.
But unfortunately reflection will take some time to arrive as a standardized feature in our compilers and annotations might take longer. :D

And I really wanna keep macro use to a minimum.
So the next best thing for me is, writing the "reflections" myself and that's pretty much what that is for me.

So, to your questions:

As you can see by the code I'd prefer, it's not quite intentional but I'm not sure how I could do this while keeping my type an aggregate (I want designated Initializers but you can't have them for base class fields) and still provide them for sqlpp. Probably macros?
The pointers to members are used to get the type of the struct members (see field_ptr_type_t) and I think they could theoretically be used to map between sqlpp fields and those of my type.
I haven't checked if aliases are still a thing in sqlpp23 but that's what I could use the identifiers for (select(user.id, group.id.as(identifier::group{})).from(...) or something like that)
Didn't look into the code enough to find it but yes, make_type_set_if seems like what I need
Yes, moving classes/macros/files around (and following the general coding style of sqlpp a bit more) would be a good idea indeed
inplace_string is structural, so it's allowed as non-tyle template argument! So I can do my_type_taking_inplace_string<"Hello world!">{} but for the static constexpr field I could make it a string_view, doesn't matter much there
I also found out about bool_constant by surprise while doing this! :D
Yeah, I've pretty much looked at the generated code to see what code I should generate from my templated classes, so the benefits are limited I have to admit. But some of them might be:
- Aggregates! I can create instances with designated Initializers, like this: user obj{ .id = 3, .name = "abc", .display_name = std::nullopt } with my types. Tbf I could change ddl2cpp to generate those too
- More control over the field types
- Could be used as general non-sqlpp "reflectable" types and sqlpp just building on it but same here, could ofc be done with ddl2cpp
- I don't need to write the create statements separately
- Could generate the create statements, maybe with extra versioning information for migrations... But this would be quite some work I guess

All of this is very subjective and just what I myself prefer, which is why I'm also not sure if the "core" of sqlpp would even be the right place for this or if I should keep it outside of it in its own repo.

-Jonas

Thanks for the explanations! I can imagine situations in which having those aggregates might be useful. And yes, I wish we had reflection already!

I think you should be able to drop the string parameter, e.g. "id" as the identifier already contains the string?

That said, I don't think this should be added to the library, at least for now. But is certainly is good food for thought!

In this case the string wouldn't really be necessary, yes. But this would be the name of the actual SQL column, which I thought would still be good to have in case it differs.

Alright, I will work on it separately then, once I've even got proper time for it, thanks for the feedback!

And great work on the library update, has some very good improvements from what I've seen!

Good luck and thanks for the flowers!

Navigation Menu

Repository navigation

Generating table structure in code #11

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Generating table structure in code #11

Description

Activity

rbock commented on Apr 17, 2025

vaijns commented on Apr 17, 2025

rbock commented on Apr 17, 2025

vaijns commented on Apr 17, 2025

rbock commented on Apr 17, 2025

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions