ORM Support and Usage

Object-Relational Mapping (ORM) frameworks provide a high-level abstraction for interacting with databases. CockroachDB supports popular ORMs across multiple programming languages.

Why Use an ORM?

ORMs provide several benefits for application development:

Type safety - Map database tables to programming language types
Productivity - Write less boilerplate code for common operations
Maintainability - Centralize database logic and schema definitions
Database abstraction - Switch between databases with minimal code changes
Migration management - Track and version schema changes

Supported ORMs by Language

JavaScript/TypeScript

Prisma
Sequelize
TypeORM
Knex.js

Support level: FullPrisma is a modern, type-safe ORM with first-class CockroachDB support.

npm install prisma @prisma/client
npx prisma init

Features:

Full TypeScript support with generated types
Intuitive data modeling
Automatic migrations
Built-in connection pooling

Support level: FullSequelize is a promise-based ORM for Node.js. Use the sequelize-cockroachdb adapter.

npm install sequelize sequelize-cockroachdb

Use the sequelize-cockroachdb package instead of plain Sequelize to handle CockroachDB-specific differences.

Support level: FullTypeORM provides TypeScript decorators for elegant data modeling.

import { Entity, PrimaryGeneratedColumn, Column } from 'typeorm';

@Entity()
export class Account {
  @PrimaryGeneratedColumn('uuid')
  id: string;
  
  @Column('int')
  balance: number;
}

Support level: FullKnex.js is a SQL query builder that works as a lighter alternative to full ORMs.

const knex = require('knex')({
  client: 'pg',
  connection: process.env.DATABASE_URL
});

await knex('accounts').insert({ balance: 1000 });
const accounts = await knex('accounts').where('balance', '>', 500);

Python

SQLAlchemy
Django

Support level: FullSQLAlchemy is Python’s most popular ORM. Use the sqlalchemy-cockroachdb dialect for best compatibility.

pip install sqlalchemy sqlalchemy-cockroachdb psycopg2

Critical: You must replace postgresql:// with cockroachdb:// in your connection string for SQLAlchemy to use the CockroachDB dialect.

Best Practices

Use the run_transaction() helper functionThe run_transaction() function handles transaction retries automatically:

from sqlalchemy_cockroachdb import run_transaction

def transfer_funds(session, from_id, to_id, amount):
    from_account = session.query(Account).filter_by(id=from_id).one()
    to_account = session.query(Account).filter_by(id=to_id).one()
    
    from_account.balance -= amount
    to_account.balance += amount

run_transaction(SessionMaker, lambda s: transfer_funds(s, id1, id2, 100))

Avoid transaction state mutationsDo NOT call these methods inside run_transaction():

session.commit() - Handled automatically
session.rollback() - Handled automatically
session.flush() - Not supported due to lack of nested transactions

Break up large transactionsIf you see “transaction is too large to complete”, split your work into smaller chunks:

chunk_size = 100
for i in range(0, len(records), chunk_size):
    chunk = records[i:i + chunk_size]
    run_transaction(SessionMaker, lambda s: insert_chunk(s, chunk))

Support level: FullDjango 3.1+ is fully supported with the django-cockroachdb backend.

pip install django==3.1.*
pip install psycopg2-binary
pip install django-cockroachdb==3.1.*

The major version of django-cockroachdb must match your Django version (e.g., 3.1.x backend for Django 3.1.x).

Go

GORM
upper/db

Support level: FullGORM is Go’s feature-rich ORM library.

go get -u github.com/lib/pq
go get -u gorm.io/gorm
go get -u gorm.io/driver/postgres

Support level: Fullupper/db provides a clean API for database operations.

import "github.com/upper/db/v4/adapter/postgresql"

sess, err := postgresql.Open(settings)
defer sess.Close()

accountsCol := sess.Collection("accounts")

Java

Hibernate
jOOQ
Spring Data JPA

Support level: FullHibernate 5.4.19+ includes native CockroachDB dialect support.

implementation 'org.hibernate:hibernate-core:6.2.0.Final'
implementation 'org.postgresql:postgresql:42.6.0'

Specify the CockroachDB dialect in your Hibernate configuration for optimal compatibility.

Support level: FulljOOQ provides type-safe SQL queries with code generation.

DSLContext create = DSL.using(connection, SQLDialect.POSTGRES);

create.insertInto(ACCOUNTS)
      .columns(ACCOUNTS.ID, ACCOUNTS.BALANCE)
      .values(UUID.randomUUID(), 1000)
      .execute();

Result<Record> result = create.select()
    .from(ACCOUNTS)
    .where(ACCOUNTS.BALANCE.gt(500))
    .fetch();

Support level: FullSpring Data JPA builds on Hibernate and provides repository abstractions.

@Entity
public class Account {
    @Id
    @GeneratedValue
    private UUID id;
    private Integer balance;
}

public interface AccountRepository extends JpaRepository<Account, UUID> {
    List<Account> findByBalanceGreaterThan(Integer balance);
}

Ruby

Active Record

Support level: FullActive Record is Ruby on Rails’ ORM framework.

gem install activerecord pg activerecord-cockroachdb-adapter

The activerecord-cockroachdb-adapter version must match your Active Record major version.

Common ORM Patterns

Handling Transactions

Most ORMs provide transaction support. Always use transactions for operations that modify multiple rows:

TypeScript (Prisma)
Python (SQLAlchemy)
Go (GORM)

await prisma.$transaction(async (tx) => {
  await tx.account.update({
    where: { id: fromId },
    data: { balance: { decrement: 100 } }
  })
  
  await tx.account.update({
    where: { id: toId },
    data: { balance: { increment: 100 } }
  })
})

from sqlalchemy_cockroachdb import run_transaction

def transfer(session, from_id, to_id, amount):
    from_acct = session.query(Account).get(from_id)
    to_acct = session.query(Account).get(to_id)
    from_acct.balance -= amount
    to_acct.balance += amount

run_transaction(SessionMaker, lambda s: transfer(s, id1, id2, 100))

db.Transaction(func(tx *gorm.DB) error {
    if err := tx.Model(&Account{ID: fromID}).Update("balance", gorm.Expr("balance - ?", 100)).Error; err != nil {
        return err
    }
    if err := tx.Model(&Account{ID: toID}).Update("balance", gorm.Expr("balance + ?", 100)).Error; err != nil {
        return err
    }
    return nil
})

Managing Schema Migrations

Use your ORM’s migration tools to version control your schema:

npx prisma migrate dev --name init
npx prisma migrate deploy

Connection Pooling

Configure connection pools appropriately for your workload:

Prisma
SQLAlchemy

datasource db {
  provider = "cockroachdb"
  url      = env("DATABASE_URL")
}

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["metrics"]
}

const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL + '?pool_timeout=0&connection_limit=10'
    }
  }
})

engine = create_engine(
    db_uri,
    pool_size=10,
    max_overflow=20,
    pool_pre_ping=True
)

ORM Performance Tips

Use Eager Loading

Avoid N+1 queries by loading related data upfront

// Good: One query
const users = await prisma.user.findMany({
  include: { posts: true }
})

// Bad: N+1 queries
const users = await prisma.user.findMany()
for (const user of users) {
  const posts = await prisma.post.findMany({
    where: { userId: user.id }
  })
}

Batch Operations

Use bulk inserts/updates instead of individual operations

# Good: Bulk insert
session.bulk_insert_mappings(
    Account,
    [{"balance": i * 100} for i in range(1000)]
)

# Bad: Individual inserts
for i in range(1000):
    session.add(Account(balance=i * 100))

Select Specific Fields

Only query the columns you need

// Good
const accounts = await prisma.account.findMany({
  select: { id: true, balance: true }
})

// Wasteful
const accounts = await prisma.account.findMany()

Use Indexes

Add indexes for frequently queried fields

model Account {
  id      String @id
  balance Int
  userId  String
  
  @@index([userId])
  @@index([balance])
}

Next Steps

Best Practices

Learn development best practices for CockroachDB

Client Drivers

View all supported client drivers

Transaction Handling

Deep dive into transaction retry logic

Performance Optimization

Optimize your ORM queries for production

Get Started

Architecture

Core Concepts

Deployment

SQL Reference

Operations

Migration

Developer Guide

ORM Support and Usage

Why Use an ORM?

Supported ORMs by Language

JavaScript/TypeScript

Python

Best Practices

Go

Java

Ruby

Common ORM Patterns

Handling Transactions

Managing Schema Migrations

Connection Pooling

ORM Performance Tips

Use Eager Loading

Batch Operations

Select Specific Fields

Use Indexes

Next Steps

Best Practices

Client Drivers

Transaction Handling

Performance Optimization

Build docs developers (and LLMs) love

Get Started

Architecture

Core Concepts

Deployment

SQL Reference

Operations

Migration

Developer Guide

​Why Use an ORM?

​Supported ORMs by Language

​JavaScript/TypeScript

​Python

​Best Practices

​Go

​Java

​Ruby

​Common ORM Patterns

​Handling Transactions

​Managing Schema Migrations

​Connection Pooling

​ORM Performance Tips

Use Eager Loading

Batch Operations

Select Specific Fields

Use Indexes

​Next Steps

Best Practices

Client Drivers

Transaction Handling

Performance Optimization

Build docs developers (and LLMs) love

Why Use an ORM?

Supported ORMs by Language

JavaScript/TypeScript

Python

Best Practices

Go

Java

Ruby

Common ORM Patterns

Handling Transactions

Managing Schema Migrations

Connection Pooling

ORM Performance Tips

Next Steps