Welcome back! So far, we have relied heavily on the Odoo ORM (Object-Relational Mapping) to handle our data. The ORM is fantastic—it automatically creates tables, links foreign keys, and writes the SQL for you.

But what happens when your Odoo screen goes completely blank? What if a massive data import gets stuck, or a bug in your code locks up the system?

To be a true senior Odoo developer, you cannot be afraid of the database. You must know how to speak directly to PostgreSQL. Today, we are going to learn the essential database skills every Odoo developer needs to survive.

1. Entering the Matrix: The psql Console

When the web interface fails, the terminal is your best friend. PostgreSQL has a built-in command-line tool called psql.

To log into your database directly from your Ubuntu server, open your terminal and type:

# Log in as the postgres superuser and connect to your database
sudo -u postgres psql -d your_database_name

Once you are inside, your terminal prompt will change to your_database_name=#. You are now speaking directly to the database engine!

Essential Navigation Commands:

• \dt : Lists all the tables in your database.

• \d table_name : Shows the structure (columns, types, foreign keys) of a specific table.

• \q : Quits the console and returns you to Ubuntu.

2. The Golden Rule of Odoo Tables

Before you can query data, you need to know where it is. Odoo has a very strict, predictable rule for converting Python models into PostgreSQL tables: Dots become underscores.

• If your Python model is _name = 'res.partner', the SQL table is res_partner.

• If your model is _name = 'sale.order.line', the SQL table is sale_order_line.

3. Real-World SQL Debugging

Let's look at a few scenarios where knowing raw SQL will save your life.

Scenario A: You locked yourself out! Imagine you accidentally messed up your admin user permissions, and now you can't log into the Odoo web interface. You can fix it directly in the database:

/* Find the admin user (usually ID 1 or 2) */
SELECT id, login, active FROM res_users WHERE login = 'admin';

/* Force the user to be active again */
UPDATE res_users SET active = true WHERE login = 'admin';

Scenario B: Finding Hidden or Broken Data Sometimes, the Odoo web view has built-in filters that hide broken records from you. SQL hides nothing.

/* Find all invoices that have no customer assigned (which might be causing a crash) */
SELECT id, name, state FROM account_move WHERE partner_id IS NULL AND move_type = 'out_invoice';

4. Backing Up and Restoring (The Pro Way)

Odoo has a great built-in database manager (/web/database/manager) where you can click "Backup". But if your database is 50GB, that web page will time out and crash.

Professionals backup their databases directly from the terminal.

To Backup a Database: Use the pg_dump command. This creates a highly compressed custom file (.dump) containing all your data.

pg_dump -U your_db_user -F c -f /home/user/my_backup.dump your_database_name

To Restore a Database: First, create an empty database, then use pg_restore to fill it up.

# 1. Create a fresh, empty database
createdb -U your_db_user new_restored_database

# 2. Inject the data from your backup file
pg_restore -U your_db_user -d new_restored_database -1 /home/user/my_backup.dump

(The -1 flag is a lifesaver. It means "do this as a single transaction." If the restore fails halfway through, it cancels the whole thing so you aren't left with a corrupted database!)

5. Pro-Tips & Advice

💡 PRO TIP: Dropping a Stuck Database Sometimes, you try to delete a test database using the dropdb command, but PostgreSQL yells at you: "Cannot drop database because it is currently in use." This happens when an Odoo background worker is still connected.

To forcefully kick everyone out and drop the database, go into psql and run this magic query:

/* Terminate all connections to the database */
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'your_stuck_database'
  AND pid <> pg_backend_pid();

After running that, you can safely drop the database!

• Never DELETE, always UPDATE: If you are fixing data directly in SQL, try to avoid the DELETE command. Odoo tables are heavily linked. If you delete a customer, you might break 50 invoices. Instead, just archive them by setting UPDATE res_partner SET active = false WHERE id = 123;.

• Read-Only Mode: If you are debugging a live production server, type BEGIN READ ONLY; before you run any queries. This prevents you from accidentally typing UPDATE and destroying real client data!