Errors with push mode / SQLite in development

[mkrn]

Describe the Bug

During development whenever fields are changed in collections I intermittently get errors such as:

 ⨯ [Error [LibsqlError]: SQL_INPUT_ERROR: SQLite input error: table `payload_locked_documents_rels` already exists (at offset 13)] {
  code: 'SQL_INPUT_ERROR',
  rawCode: undefined,
  digest: '3770172395',
  [cause]: [Error [ResponseError]: SQLite input error: table `payload_locked_documents_rels` already exists (at offset 13)] {
    code: 'SQL_INPUT_ERROR',
    proto: {
      message: 'SQLite input error: table `payload_locked_documents_rels` already exists (at offset 13)',
      code: 'SQL_INPUT_ERROR'
    }
  }
}

OR

 ⨯ [Error [LibsqlError]: SQLITE_UNKNOWN: SQLite error: index payload_locked_documents_rels_order_idx already exists] {
  code: 'SQLITE_UNKNOWN',
  rawCode: undefined,
  payloadInitError: true,
  digest: '1406407794',
  [cause]: [Error [ResponseError]: SQLite error: index payload_locked_documents_rels_order_idx already exists] {
    code: 'SQLITE_UNKNOWN',
    proto: {
      message: 'SQLite error: index payload_locked_documents_rels_order_idx already exists',
      code: 'SQLITE_UNKNOWN'
    }
  }
}
 ⨯ [Error [LibsqlError]: SQLITE_UNKNOWN: SQLite error: index payload_locked_documents_rels_order_idx already exists] {
  code: 'SQLITE_UNKNOWN',
  rawCode: undefined,
  payloadInitError: true,
  digest: '1406407794',
  [cause]: [Error [ResponseError]: SQLite error: index payload_locked_documents_rels_order_idx already exists] {
    code: 'SQLITE_UNKNOWN',
    proto: {
      message: 'SQLite error: index payload_locked_documents_rels_order_idx already exists',
      code: 'SQLITE_UNKNOWN'
    }
  }
}
 ⨯ [Error [LibsqlError]: SQLITE_UNKNOWN: SQLite error: index payload_locked_documents_rels_order_idx already exists] {
  code: 'SQLITE_UNKNOWN',
  rawCode: undefined,
  payloadInitError: true,
  digest: '1406407794',
  [cause]: [Error [ResponseError]: SQLite error: index payload_locked_documents_rels_order_idx already exists] {
    code: 'SQLITE_UNKNOWN',
    proto: {
      message: 'SQLite error: index payload_locked_documents_rels_order_idx already exists',
      code: 'SQLITE_UNKNOWN'
    }
  }
}

requiring me to reload the project and waste a lot of time

Link to the code that reproduces this issue

https://github.com/mkrn/payloadcms_reproduction

Reproduction Steps

• Start in dev mode, init db and login to admin
• Add an array field or change an array field in Pages collection
• See admin (Pages collection page)
• See errors in console

Which area(s) are affected? (Select all that apply)

db-sqlite

Environment Info

Binaries:
  Node: 20.9.0
  npm: 10.1.0
  Yarn: 4.5.3
  pnpm: 9.15.1
Relevant Packages:
  payload: 3.31.0
  next: 15.2.3
  @payloadcms/email-nodemailer: 3.31.0
  @payloadcms/graphql: 3.31.0
  @payloadcms/next/utilities: 3.31.0
  @payloadcms/payload-cloud: 3.31.0
  @payloadcms/richtext-lexical: 3.31.0
  @payloadcms/translations: 3.31.0
  @payloadcms/ui/shared: 3.31.0
  react: 19.0.0
  react-dom: 19.0.0
Operating System:
  Platform: darwin
  Arch: arm64
  Version: Darwin Kernel Version 23.6.0: Sun Jun 30 19:41:22 PDT 2024; root:xnu-10063.140.33~20/RELEASE_ARM64_T6000
  Available memory (MB): 16384
  Available CPU cores: 10

[mkrn]

Just some thoughts: noticed severe problems with automatic database schema update with SQLite especially when array fields are involved. This makes it very difficult to add collections, making development experience a guesswork. Happy to provide more details. On actual prod project getting many more errors such as:

 ⨯ [Error [LibsqlError]: SQLITE_CONSTRAINT: SQLite error: FOREIGN KEY constraint failed] {
  code: 'SQLITE_CONSTRAINT',
  rawCode: undefined,
  payloadInitError: true,
  digest: '3252118422',
  [cause]: [Error [ResponseError]: SQLite error: FOREIGN KEY constraint failed] {
    code: 'SQLITE_CONSTRAINT',
    proto: {
      message: 'SQLite error: FOREIGN KEY constraint failed',
      code: 'SQLITE_CONSTRAINT'
    }
  }
}

 ⨯ TypeError: Cannot read properties of undefined (reading 'length')
    at Array.find (<anonymous>) {
  digest: '2384508779'
}

Errors sometimes lack specificity on where they come from, making it hard to troubleshoot. My suspicion is the database schema reload process is not done in correct order.

Database is SQLite Turso on AWS.

[nathanclevenger]

we're seeing this as well ... is this being worked on at all by the payload team?

[aashahin]

+1

[joshdavenport]

Seeing this almost straight away in a fresh instance, just adding a collection. I think I restarted and it went away. Just saw it after removing a field. Seems to be fairly easy to trigger the issue.

[nickthegit]

+1

[j-mir-prazak]

maybe I am totally off, but would not be easiest to either just add "CREATE INDEX IF NOT EXISTS" or preemptively do "DROP INDEX IF EXISTS"?

DROP INDEX INDEX IF EXISTS is already used sometimes; but I haven't seen migration generated with create index if not exists. I assume, that it could lead to mismatch between assumed index and created index.

But having any CREATE INDEX have to follow DROP INDEX INDEX IF EXISTS, could solve some of those issues. Though existing tables may be more complicated issues.

But it is very inconsistent and time consuming to debug. And deployments get messed up sometimes for me too.

Is using postgres better or more robust? or is it basically the same code generated?

[nunomalta]

Any update on this? Seeing this using Turso

[itaysarfaty]

+1

[ArchiCroc]

I'm getting this too. I created a new project with the sqlite adapter and tried to add a new collection.

[netimen]

same here. When adding fields to a collection, which already contains items

[baranelitez]

I too confirm

[hunterashaw-swiftotter]

+1