PostgreSQL for Odoo – What Every Developer Needs to Know

How Odoo Maps to PostgreSQL

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

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:

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

-- 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):

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

OR operator:

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

Relational field traversal (triggers a SQL JOIN):

[('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:

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:

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

Useful psql meta-commands:

\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:

-- 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.

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

• 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:

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

Check existing indexes and explain a slow query:

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:

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

PostgreSQL for Odoo Developers

Odoo runs only on PostgreSQL. You interact with data through the ORM 95% of the time, but understanding what Postgres is doing underneath helps you debug, optimize, and reason about your models.

-- what ORM code like self.search([('active','=',True)]) becomes:
SELECT id FROM library_book WHERE active = true;

The ORM writes the SQL for you: self.env['library.book'].search([...]) is translated by the ORM into a real SELECT against the library_book table. You almost never write SQL directly — but knowing the mapping (model → table, field → column, Many2one → foreign key) lets you read query logs, understand performance, and design better models. Why understanding Postgres pays off: when a view is slow, it's usually a database issue — a missing index on a searched field, or an N+1 query pattern. You fix these by adding index=True to fields, using the ORM's batching, or (rarely) inspecting the actual SQL. Odoo-specific tables: Postgres also stores Odoo's metadata — ir_model, ir_ui_view, ir_model_data (external IDs) — which is how the framework introspects itself. Practical tip: a GUI (pgAdmin, DBeaver) connected to your Odoo database is invaluable for inspecting data and debugging. Never modify Odoo's tables directly in production, though — go through the ORM so business logic, constraints, and caches stay consistent.

Interview Questions

• What database does Odoo use?
• How are Odoo models mapped to tables?
• Why avoid raw SQL writes in Odoo?
• How do you connect to the Odoo database?
• What manages the database schema in Odoo?

Related Topics