Cassandra(or ScyllaDB) support

[rpstw]

trafficstars

What do you want to change?

We plan to migrate some Postgres workload to ScyllaDB in the next months due to scaling and cost reasons. We heavily use sqlc in the past for Postgres workload, so naturally some Cassandra support in sqlc would be great.

Being a sqlc user for a lot time, I'm not certain about the overall idea. Does the Cassandra support also in the roadmap of the sqlc developers and users? How many code need to be written for the support?

Opinions and advice would be really helpful, also, if Cassandra is really a can-have, I could devote some time implementing it.

What database engines need to be changed?

No response

What programming language backends need to be changed?

Go

[kyleconroy]

There are no short term plans to add database engines beyond MySQL, PostgreSQL and SQLite. The reason is that those three engines are due for a large rewrite in how they work. I want to move sqlc to directly use databases for query analysis, which should make it much easier to support newer engines in the future.

That said if you'd like to see Cassandra / ScyllaDB support, there's some background research that needs to be done. To add a new database engine, we need to have access to a high-quality parser that can both parse and render a SQL AST. The engine itself needs to support analyzing queries.

I've never used Cassandra / ScyllaDB before. Do you think it has these capabilities?

[rpstw]

Thanks for the direction. Since Cassandra CQL is relative simple compared to Postgres, I'll try to make some demo and paste any further meaningful progress later.

[ericraio]

@rpstw any updates to a demo?

[paulschroeder-tomtom]

@kyleconroy

I am not a SQL expert, quite the opposite actually, but how much does the database driver depend on the exact engine flavor?

Since cassandra speaks SQL as well, couldnt we get away with the default postgres and only use basic SQL statements and nothing exotic (only available to the corresponding falvor)?

@rpstw did you figure something out?

[rpstw]

After several months of studying. Here's some conclusion I can share

1. compared to PostgreSQL or MYSQL, CQL is relatively simple in practice. In almost all the circumstances I observed , CQL are always like SELECT a,b,c FROM WHERE or INSERT INTO |UPDATE , slightly advance functionalities like GROUP BY, WITH are either unsupported or unpopular. With that being said, using a tool like sqlc seem fit for a fully functional SQL but overkill for CQL.
2. There lacks a CQL parser in golang like https://pkg.go.dev/github.com/pganalyze/pg_query_go/v5. One solution would to maintain a go library using Apache Cassandra's Java parser, which seems unfeasible since Go and Java does not fit really well and to use this kind of library we require users to install Java. Another solution is to use Scylla's C++ parser. As of my understanding after reading the code of Scylladb, their parser is not a traditional ast-semantic-execution architecture, it is not as easy as Postgres to use only the parser part to form a Golang library.Also Scylla changed to a readable but not usable code license so we can not just use the code as a separate library.

I created this question because I want to write CQL code in golang as efficient as SQL with sqlc. I think the problem still exists, but based on the conclusion above I don't think there would be an easy method to have CQL with sqlc. I still hope there could be one but as for now I'll close the issue.