D1: parâmetros e SQL multi-instrução excluem-se

O Cloudflare D1 responde "params with multiple statements is not supported". Os parâmetros ligados e o SQL de várias instruções excluem-se mutuamente, por isso não consegues envolver várias escritas parametrizadas num BEGIN e num COMMIT. Confirmei isto contra a base de dados real em vez de o deduzir, e muda a ordem de todos os caminhos de escrita.

Em que consiste a restrição

Podes enviar várias instruções numa cadeia. Podes enviar uma instrução com parâmetros ligados. Não podes fazer as duas coisas. Assim que um envio leva ao mesmo tempo um ponto e vírgula a separar instruções e uma lista de parâmetros, o D1 rejeita-o com essa mensagem exata.

O remendo óbvio é pior do que a restrição. Interpolar valores no SQL para conseguir várias instruções troca uma transação por uma superfície de injeção, numa base de dados alcançada por HTTP, e nenhuma transação vale isso.

O que isso custa

Significa que um helper de batch sobre a API REST é sequencial e não atómico. O meu emite as instruções umas atrás das outras. Se a ligação cair a meio, metade das escritas entrou e a outra metade não, e nada faz rollback.

Quem chama tem de estar ordenado para que uma falha a meio deixe um estado recuperável.

Essa frase é toda a regra de desenho. Não é uma limitação para contornar, é uma restrição contra a qual se escreve código, e o código não fica mais difícil. Fica ordenado de propósito em vez de por acaso.

Ordenar uma escrita para que a falha seja sobrevivível

O calendário de disponibilidade é o caso mais claro que tenho. Substituir uma semana de horários é um delete e um insert. Se apagas primeiro, uma interrupção deixa o calendário vazio, o que corta todas as marcações em silêncio e parece que a funcionalidade avariou. Se inseres primeiro, uma interrupção deixa duplicados, que o caminho de leitura já colapsa porque agrupa por dia e por hora.

// Not atomic. So the order is the recovery strategy: insert the new rows
// first, delete the old ids afterwards. An interruption between the two
// leaves duplicates, which the read path collapses, rather than an empty
// calendar, which nobody can recover from.
await db.batch(rows.map((r) => ({
  sql: 'INSERT INTO availability (id, weekday, start_minute, end_minute) VALUES (?, ?, ?, ?)',
  params: [r.id, r.weekday, r.start, r.end],
})));
await db.batch(oldIds.map((id) => ({
  sql: 'DELETE FROM availability WHERE id = ?',
  params: [id],
})));

As mesmas duas operações, a mesma ausência de transação, e a diferença entre uma tarde má e ninguém dar por nada está em qual delas corre primeiro.

A armadilha das datas, da mesma família

Já que estás a escrever defaults em SQL, usa a forma ISO com o T: strftime('%Y-%m-%dT%H:%M:%fZ', 'now'). A variante com espaço parece equivalente e ordena de outra maneira, por isso as comparações de cadeias contra um toISOString de JavaScript deixam de bater certo, em silêncio, nas linhas escritas pela base de dados e não pelo teu código.

A regra que eu te daria

• Parte do princípio de que não há transações. Ordena cada caminho de várias escritas para que uma leitura sobreviva ao estado intermédio.
• Prefere duplicados a ausências. Uma leitura que desduplica é barata; um utilizador cujos dados desapareceram não é.
• Nunca interpoles valores para conseguir várias instruções.
• Escreve os defaults do esquema na forma ISO com T e em mais nenhuma.