Support for Cassandra/ScyllaDB

[steebchen]

This feature request serves as a central place to discuss development and progress for Cassandra support.

[laquereric]

I am working on a large enterprise-embeddd application where Scylla already has a presence. This feature would really help promote Prisma in that application.

[samrith-s]

Is there any comment on this from the Prisma team? For our project, we've had to move from Postgres to Cassandra, and would like to continue using Prisma.

[ezsper]

I would really appreciate some initial documentation on how to contribute to add support to other databases, I'm specially interested on Cassandra support and I would like to start this. Is there a documentation on how to start a new data provider?

[ryanarmstrong]

Same here, if this needs to be in the community, would be great to have some documentation on how it could be provided by the community.

[PeterCorless]

Chiming in my support for this from the ScyllaDB community. I've shared with our Pythonistas. The Rustaceans are already ahead in this race. q.v., Catalytic, the Rust ORM for Cassandra/ScyllaDB: https://www.scylladb.com/2022/02/15/introducing-catalytic-an-orm-designed-for-scylladb-and-cassandra-written-in-rust/

[janpio]

That there already is a working Rust driver for Cassandra/ScyllaDB is of course great - as that is what we would need in Prisma to be able to create a connector.

[namdien177]

Bump!!

Hope this issue is still alive! Really need Prisma to support Cassandra.

[virus2016]

Bump

[AvIsBeastMC]

Cassandra PLEASE!

[Creative-Difficulty]

@timsuchanek Please add this for TS and JS too, Cassandra is just too good to not use!

[danielhe4rt]

Let's see what we can do about it. :p

[MaximKing1]

Can we expect to see this anytime soon? This would be an excellent feature.

[bojank]

Would love to see this happen soon, Cassandras performance makes the world go round.

[janpio]

Currently, we have no plans to add support for Cassandra to Prisma ORM.

What could generally be useful here, is your idea how Prisma would work with Cassandra. I only know that CQL is close-ish to SQL, but still very different.

• What does a common schema look like?
• What would the Prisma schema version of that look like?
• What kind of queries would you expect Prisma to offer?
• Would that map to how we currently support relational databases and especially MongoDB?
• How do migrations work?

[nima2142]

you can see express-cassandra for prisma requirement
@janpio
https://express-cassandra.readthedocs.io/en/latest/usage/

var ExpressCassandra = require('express-cassandra');
var models = ExpressCassandra.createClient({
    clientOptions: {
        contactPoints: ['127.0.0.1'],
        localDataCenter: 'datacenter1',
        protocolOptions: { port: 9042 },
        keyspace: 'mykeyspace',
        queryOptions: {consistency: ExpressCassandra.consistencies.one},
        socketOptions: { readTimeout: 60000 },
    },
    ormOptions: {
        defaultReplicationStrategy : {
            class: 'SimpleStrategy',
            replication_factor: 1
        },
        migration: 'safe',
    }
});

var MyModel = models.loadSchema('Person', {
    fields:{
        name    : "text",
        surname : "text",
        age     : "int",
        created : "timestamp"
    },
    key:["name"]
});

// MyModel or models.instance.Person can now be used as the model instance
console.log(models.instance.Person === MyModel);

// sync the schema definition with the cassandra database table
// if the schema has not changed, the callback will fire immediately
// otherwise express-cassandra will try to migrate the schema and fire the callback afterwards
MyModel.syncDB(function(err, result) {
    if (err) throw err;
    // result == true if any database schema was updated
    // result == false if no schema change was detected in your models
});

find query

var query = {
    // equal query stays for name='john', also could be written as name: { $eq: 'John' }
    name: 'John',
    // range query stays for age>10 and age<=20. You can use $gt (>), $gte (>=), $lt (<), $lte (<=)
    age : { '$gt':10, '$lte':20 },
    // IN clause, means surname should either be Doe or Smith
    surname : { '$in': ['Doe','Smith'] },
    // like query supported by sasi indexes, complete_name must have an SASI index defined in custom_indexes
    complete_name: { '$like': 'J%' },
    // order results by age in ascending order.
    // also allowed $desc and complex order like $orderby: {'$asc' : ['k1','k2'] }
    $orderby: { '$asc' :'age' },
    // group results by a certain field or list of fields
    $groupby: [ 'age' ],
    //limit the result set to 10 rows, $per_partition_limit is also supported
    $limit: 10
}

models.instance.Person.find(query, {raw: true}, function(err, people){
    //people is an array of plain objects satisfying the query conditions above
});

select coll

models.instance.Person.find({name: 'John'}, { select: ['name as username','age'] }, function(err, people){
    //people is an array of plain objects with only name and age
});

[Cliftonz]

@janpio How would one go about implementing this from the open-source side of things?
Where would be the best place to start and what would be the list of requirements to have everything "production ready"?

[danielhe4rt]

Hey guys! Dev Advocate from ScyllaDB here!

CQL is suppose to have an easier implementation comparing to SQL since you don't have "Join" statements. It doesn't change so much from SQL schema tbh.

There's a few keywords and properties that will need to work on related to grammar, like:

• Create Table statements
• Create Keyspace statements
• Select statements with TTL and so on.

I can help with that if possible! I've been studying this type of implementation in a different project (PHP Eloquent with CQL).

[Cliftonz]

@danielhe4rt Great to hear your experience on implementing things like this.
If we are able to get a list of things we need to do to make this happen, I am able to start contributing.
If you want we can even tag team this.

[janpio]

We at Prisma are currently not that interested in adding support for Cassandra/ScyllaDB in our codebase, as we are super busy with our existing bugs and feature requests for existing databases. We just can't further increase our surface area.

Hence the best approach would be to fork our prisma-engines repository and add support for Cassandra/ScyllaDB in your fork. The repository is pretty self-contained. You can then replace/redirect the Engines your Prisma CLI and Prisma Client will use via custom engine location environment variables to your own, enhanced builds.

You will probably have to touch roughly all the components of our engines to add support for a new provider, its types, the validations, migrations, introspection - and most importantly of course the generation of queries and handling of the requests to the database and the data returned. (You could skip migrations and introspection technically if you are ok with managing the actual database by hand, and making sure the Prisma schema matches the database schema as well.)

[Cliftonz]

@janpio I would like to ask where Cassandra comes in to play now that you guys are working on Neon and Planetscale database drivers.

This is a different direction than the sentiment that you explain above (no more databases) and would like to better understand where Prisma's mind is about supporting this technology.

Thanks!

[janpio]

Neon and PlanetScale use PostgreSQL and MySQL dialects, and are already supported by Prisma via direct TCP connections. This new release adds support for their serverless drivers, which communicate via HTTP and Websockets.

Technically, we could now also build a driver adapter that talks to a Node Cassandra client/library - but we could only send it SQL queries we generate for one of our supported database SQL dialects (PostgreSQL, MySQL, SQL Server, SQLite). As far as I know, that is unfortunately not an option that would work for Cassandra. Correct?

[Cliftonz]

@janpio Not total true but ultimately joins specifically will be the issue as cassandra does not support them.
https://www.scylladb.com/glossary/cassandra-query-language-cql/

Other then that CQL and SQL are very similar as CQL is a superset of SLQ.
https://www.webcomand.com/docs/language/cql/cql_vs_sql/index.html

The biggest difference is in creating the database and joins as mentioned above.

I would love to see this technology supported as would all of these people in the Cassandra community.

The modern development landscape is rapidly evolving, yet the support for Cassandra hasn't kept pace, creating a significant gap in the ecosystem.

Currently, developers are cornered into using Stargate and Mongoose, which although functional, is a very cumbersome workflow especially when compared with the experience offered by Prisma. This unfortunately makes it a non-option for many founders and developers.

I firmly believe that with Prisma's support, Cassandra's adoption would become a viable option amongst developers, leading to a bettor, more robust ecosystem.

I would at least like to request that it be considered for future developments.

[janpio]

We hear you. But adding another dialect of SQL (for relational databases) or commands (for MongoDB) is a significant investment from our side. It touches all areas of our tooling, so also requires people from all teams to get involved - hence currently it is not a priority for us. We have too much work in making our existing users, or potential users, for our existing databases happy - see all the other issues around here.

(We're continuing to invest in approaches like Client Extensions or Driver Adapters that potentially will open the route for user contributions to add support for databases in the future, in a simpler way than it is already possible via prisma-engines (which we understand is not an option for many)).

[janpio]

That being said: https://www.webcomand.com/docs/language/cql/cql_vs_sql/index.html is indeed a super interesting link. One could imagine a driver adapter that gets the normal SQL, and then has a light translation layer to adopt to cQL, and then uses a native driver to execute these cQL queries. I am unclear after reading the limitations on https://www.scylladb.com/glossary/cassandra-query-language-cql/ though if that is actually possible.

[Cliftonz]

@janpio Thank you for at least listening and letting us know that you hear us.

I could also see doing a light transitions layer that would convert SQL to CQL, but it would be a "hack" around fully supporting it.