PostgreSQL for Odoo – Database Concepts Every Odoo Developer Needs

How Odoo Maps to PostgreSQL

Every Odoo model is a PostgreSQL table. Here is the complete mapping:

Odoo Concept	PostgreSQL Equivalent
`models.Model` subclass	Table
`fields.Char` / `fields.Integer` / `fields.Float`	Column (VARCHAR, INTEGER, NUMERIC)
`fields.Many2one('res.partner')`	Foreign key column (INTEGER referencing res_partner.id)
`fields.One2many`	No column — reverse FK lookup on the related model
`fields.Many2many`	Junction table (e.g., `sale_order_tag_rel`)
`fields.Binary`	BYTEA column (or ir.attachment record)
`active = fields.Boolean`	Column + automatically added to WHERE active=true

Model name → table name: dots become underscores. sale.order → sale_order, res.partner → res_partner.

Here is the same model shown in both Python (Odoo) and the SQL table Odoo creates automatically:

Python

# Odoo
class SaleOrder(models.Model):
    _name = 'sale.order'
    name = fields.Char(required=True)
    partner_id = fields.Many2one('res.partner')
    amount_total = fields.Float()

SQL

-- PostgreSQL (what Odoo creates)
CREATE TABLE sale_order (
    id SERIAL PRIMARY KEY,
    name VARCHAR NOT NULL,
    partner_id INTEGER REFERENCES res_partner(id),
    amount_total NUMERIC,
    active BOOLEAN DEFAULT TRUE,
    create_date TIMESTAMP,
    write_date TIMESTAMP,
    create_uid INTEGER REFERENCES res_users(id),
    write_uid INTEGER REFERENCES res_users(id)
);

Notice that Odoo automatically adds id, active, create_date, write_date, create_uid, and write_uid columns to every model — you never define these yourself.

Domain Filters → SQL WHERE Clauses

Odoo domains are Python lists that the ORM compiles into SQL WHERE clauses automatically. Understanding this mapping helps you write efficient domains and debug performance problems.

Simple domain (AND by default):

Python

# Odoo domain
[('state', '=', 'sale'), ('amount_total', '>', 1000)]
# Compiles to:
# WHERE state = 'sale' AND amount_total > 1000

OR operator:

Python

['|', ('state', '=', 'sale'), ('state', '=', 'done')]
# WHERE state = 'sale' OR state = 'done'

Relational field traversal (triggers a SQL JOIN):

Python

[('partner_id.country_id.code', '=', 'US')]
# JOIN res_partner ON sale_order.partner_id = res_partner.id
# JOIN res_country ON res_partner.country_id = res_country.id
# WHERE res_country.code = 'US'

Domain operators reference:

Operator	SQL	Example
`=`	`=`	`('state', '=', 'sale')`
`!=`	`!=`	`('active', '!=', False)`
`>`, `<`, `>=`, `<=`	same	`('amount', '>', 100)`
`like`	`LIKE '%x%'`	`('name', 'like', 'Acme')`
`ilike`	`ILIKE '%x%'`	case-insensitive match
`in`	`IN (...)`	`('state', 'in', ['sale', 'done'])`
`not in`	`NOT IN`	`('state', 'not in', ['cancel'])`
`child_of`	recursive CTE	`('category_id', 'child_of', parent_id)`

Essential PostgreSQL Commands

As an Odoo developer, you will often need to inspect the database directly to debug issues, check table structures, or verify that records were created correctly.

Connecting to the Odoo database:

Bash

psql -d your_odoo_database_name
# or as postgres user:
sudo -u postgres psql -d your_odoo_database_name

Useful psql meta-commands:

SQL

\dt                          -- list all tables
\d sale_order                -- describe table structure
\d+ res_partner              -- detailed structure with comments
\l                           -- list databases
\c database_name             -- connect to another database
\q                           -- quit

Common queries for Odoo development:

SQL

-- Find all Odoo models and their tables
SELECT model, name FROM ir_model ORDER BY model;

-- Find all fields of a model
SELECT name, field_description, ttype
FROM ir_model_fields
WHERE model = 'sale.order'
ORDER BY name;

-- Check active users
SELECT id, login, active FROM res_users WHERE active = true;

-- Find records without using the ORM
SELECT id, name, state, amount_total
FROM sale_order
WHERE state = 'sale'
ORDER BY create_date DESC
LIMIT 10;

Running Raw SQL in Odoo

Sometimes the ORM is not the right tool — bulk operations, complex analytics, or performance-critical searches may require raw SQL. Odoo exposes the database cursor via self.env.cr.

Python

def get_top_customers(self):
    query = """
        SELECT p.name, SUM(so.amount_total) AS total_sales
        FROM sale_order so
        JOIN res_partner p ON so.partner_id = p.id
        WHERE so.state = 'sale'
          AND so.company_id = %s
        GROUP BY p.id, p.name
        ORDER BY total_sales DESC
        LIMIT 10
    """
    self.env.cr.execute(query, (self.env.company.id,))
    results = self.env.cr.fetchall()
    return results

⚠️

Always use parameterized queries

Use %s placeholders and pass values as the second argument to execute(). Never format SQL strings with Python string interpolation or f-strings — this opens your code to SQL injection attacks.

fetchall() returns a list of tuples
dictfetchall() (Odoo helper) returns a list of dicts — easier to work with by column name
Raw SQL bypasses Odoo's access control — you must check permissions manually when using raw queries for write operations

Indexes and Performance

PostgreSQL uses indexes to speed up queries. Odoo auto-creates indexes on:

id column (primary key) on every table
Many2one FK columns (automatically indexed)
Fields declared with index=True

Adding an index to a field in Python:

Python

partner_id = fields.Many2one('res.partner', index=True)  # Odoo creates index
reference = fields.Char(index=True)

Check existing indexes and explain a slow query:

SQL

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'sale_order';

EXPLAIN ANALYZE
SELECT * FROM sale_order WHERE partner_id = 42;
-- Look for: Seq Scan (bad on large tables), Index Scan (good)

Transactions in Odoo

Every Odoo HTTP request runs inside a single PostgreSQL transaction:

If the request succeeds → COMMIT (all changes persisted)
If an exception is raised → ROLLBACK (all changes discarded)

You can use savepoints for partial rollback within a request:

Python

with self.env.cr.savepoint():
    # If anything here raises an exception,
    # only this block is rolled back, not the whole request
    risky_operation()

⚠️

Never call self.env.cr.commit() in a module method

Odoo manages the transaction lifecycle automatically. Manually committing inside a model method can cause inconsistent state, break the rollback mechanism, and lead to data integrity issues. Leave transaction management to Odoo.

📋 Key Points

Odoo model name → PostgreSQL table name: replace dots with underscores (sale.order → sale_order).
Odoo automatically adds id, active, create_date, write_date, create_uid, and write_uid to every table.
Domain filters compile to SQL WHERE clauses. Dot-traversal (partner_id.country_id.code) generates SQL JOINs.
Use self.env.cr.execute(query, params) for raw SQL — always with %s parameterized values to prevent SQL injection.
Add index=True to frequently searched fields to speed up queries on large tables.
Every request is a transaction. Use savepoints for partial rollbacks. Never call cr.commit() manually.

FAQ

Can I use MySQL with Odoo? +

No. Odoo only supports PostgreSQL. The ORM uses PostgreSQL-specific features including the RETURNING clause, jsonb columns, recursive CTEs, and advisory locks. These are not available in MySQL or SQLite. PostgreSQL support is a hard architectural requirement, not a preference.

How do I find which PostgreSQL table corresponds to an Odoo model? +

Replace dots with underscores in the model name: sale.order → sale_order, res.partner → res_partner. You can also confirm by querying the Odoo meta-model: SELECT model FROM ir_model WHERE model = 'sale.order'; — or from Python: self.env['sale.order']._table returns the table name.

Is it safe to write to the Odoo database directly with SQL? +

Direct SQL writes bypass the ORM's access rights checking, computed field recomputation, constraint validation, and chatter/audit trail. For read-only queries it is generally safe. For writes, always use the ORM unless you have a specific, well-understood performance reason — and when you do bypass the ORM, handle permissions, recomputation, and any dependent logic manually.

What is self.env.cr? +

It is the database cursor — a psycopg2 cursor object connected to the current PostgreSQL transaction. Use self.env.cr.execute() to run raw SQL queries, self.env.cr.fetchall() to retrieve results as tuples, and self.env.cr.dictfetchall() (Odoo helper) to retrieve results as dicts keyed by column name.