Quickstart

0. Connect


dodil data bucket create shop -d "SQL quickstart"
dodil data connect shop -o psql     # prints a ready-to-use connection URL

The URL has the shape postgresql://<key-id>:<secret>@pg.uk-lon-1.dodil.io:5432/shop?sslmode=require. Any of your credentials work as user/password — an API key (dk_… id and its secret), a service account, or a bearer JWT as the password. Details on Connect.

psql


psql "$(dodil data connect shop -o psql)"

Python (psycopg)


import os, psycopg
 
conn = psycopg.connect(os.environ["DATABASE_URL"], autocommit=True)   # the URL from `dodil data connect`
cur = conn.cursor()

Prisma


// prisma/schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")   // the URL from `dodil data connect`
}
generator client {
  provider = "prisma-client-js"
}

1. Create a table

psql


CREATE TABLE orders (
  id          BIGINT PRIMARY KEY,
  customer    VARCHAR(64)   NOT NULL,
  total       NUMERIC(18,2) NOT NULL CHECK (total >= 0),
  status      VARCHAR(16)   NOT NULL DEFAULT 'open',
  created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Python (psycopg)


cur.execute("""
CREATE TABLE orders (
  id          BIGINT PRIMARY KEY,
  customer    VARCHAR(64)   NOT NULL,
  total       NUMERIC(18,2) NOT NULL CHECK (total >= 0),
  status      VARCHAR(16)   NOT NULL DEFAULT 'open',
  created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
)""")

Prisma


model Order {
  id        BigInt   @id
  customer  String   @db.VarChar(64)
  total     Decimal  @db.Decimal(18, 2)
  status    String   @default("open") @db.VarChar(16)
  createdAt DateTime @default(now()) @map("created_at")
  @@map("orders")
}


npx prisma db push

What you declared is what you get. NUMERIC(18,2) is exact, VARCHAR(64) refuses a longer value with 22001, the CHECK is enforced with 23514, and the primary key is enforced with 23505. TIMESTAMP is stored in UTC. The full type table is on Data types.


DESCRIBE orders;


column      type        nullable  pk     default
id          long        false     true
customer    string(64)  false     false
total       decimal(18,2) false   false
status      string(16)  false     false  'open'
created_at  timestamp   false     false  CURRENT_TIMESTAMP

2. Insert rows

psql


INSERT INTO orders (id, customer, total) VALUES
  (1, 'ada',   120.00),
  (2, 'grace',  19.99),
  (3, 'ada',    42.50);

Python (psycopg)


cur.executemany(
    "INSERT INTO orders (id, customer, total) VALUES (%s, %s, %s)",
    [(1, "ada", "120.00"), (2, "grace", "19.99"), (3, "ada", "42.50")],
)

Prisma


await prisma.order.createMany({ data: [
  { id: 1, customer: "ada",   total: "120.00" },
  { id: 2, customer: "grace", total: "19.99"  },
  { id: 3, customer: "ada",   total: "42.50"  },
]});

A plain INSERT of an existing key fails with 23505; it is never a silent upsert. For insert-or-update use INSERT … ON CONFLICT (id) DO UPDATE SET ….

3. Query


SELECT customer, COUNT(*) AS orders, SUM(total) AS spent
FROM orders
GROUP BY customer
ORDER BY spent DESC;


 customer | orders | spent
----------+--------+--------
 ada      |      2 | 162.50
 grace    |      1 |  19.99

You see the rows you just inserted. Reads always include every committed write; there is no freshness setting and no replica lag to reason about. Joins, CTEs, window functions and subqueries all work.

4. A transaction with a guarded read

The pattern for a read-then-write that must not lose an update:

psql


BEGIN;
SELECT total FROM orders WHERE id = 1 FOR UPDATE;      -- guard this row
UPDATE orders SET total = total + 5.00 WHERE id = 1;
COMMIT;                                                 -- 40001 here → run the block again

Nothing blocks. If another transaction committed a change to a row you read FOR UPDATE or wrote, your COMMIT fails with 40001 and you run the block again. Inside the transaction you see your own staged writes. The complete contract is on Transactions & concurrency.

5. Change the schema


ALTER TABLE orders ADD COLUMN note VARCHAR(200);
SELECT id, note FROM orders WHERE id = 1;    -- note is NULL, immediately, on every existing row

ADD COLUMN is online: the column is readable the moment the statement returns since 0.1.42. DROP COLUMN and widening ALTER COLUMN … SET DATA TYPE are metadata-only too. What is and is not supported is on Schema changes.

6. Clean up


DROP TABLE orders;


dodil data bucket delete shop      # only if you created it for this walkthrough

Next: the ledger recipe builds a double-entry ledger with the transaction pattern above, end to end.