Autoincrement Issue with my sqlite3 db

System Information
  • Strapi Version: 4.16.2
  • Operating System: Windows
  • Database: sqlite3
  • Node Version: v18.17.0
  • NPM Version: 9.6.7
  • Yarn Version: 1.22.19

when I try to deploy locally using sqlite3 I get this error:

│   SqliteError: create table `public`.`strapi_migrations` (`id` integer not null primary key autoincrement, `name` varchar(255), `time` datetime) - unknown database `public`   │
│       at Database.prepare (C:\Proyectos\wheelsup-strapi\node_modules\better-sqlite3\lib\methods\wrappers.js:5:21)                                                              │
│       at Client_BetterSQLite3._query (C:\Proyectos\wheelsup-strapi\node_modules\knex\lib\dialects\better-sqlite3\index.js:35:34)                                               │
│       at executeQuery (C:\Proyectos\wheelsup-strapi\node_modules\knex\lib\execution\internal\query-executioner.js:37:17)                                                       │
│       at Client_BetterSQLite3.query (C:\Proyectos\wheelsup-strapi\node_modules\knex\lib\client.js:154:12)                                                                      │
│       at Runner.query (C:\Proyectos\wheelsup-strapi\node_modules\knex\lib\execution\runner.js:141:36)                                                                          │
│       at Runner.queryArray (C:\Proyectos\wheelsup-strapi\node_modules\knex\lib\execution\runner.js:235:21)                                                                     │
│       at ensureConnectionCallback (C:\Proyectos\wheelsup-strapi\node_modules\knex\lib\execution\internal\ensure-connection-callback.js:11:19)                                  │
│       at Runner.ensureConnection (C:\Proyectos\wheelsup-strapi\node_modules\knex\lib\execution\runner.js:318:20)                                                               │
│       at async Runner.run (C:\Proyectos\wheelsup-strapi\node_modules\knex\lib\execution\runner.js:30:19)                                                                       │
│       at async Object.executed (C:\Proyectos\wheelsup-strapi\node_modules\@strapi\database\dist\index.js:5792:9)    

But when I connect to an empty PG it is work propertly

My config for local is

const path = require('path');

module.exports = ({ env }) => ({
  connection: {
    client: 'sqlite',
    connection: {
      filename: path.join(__dirname, '..', env('DATABASE_FILENAME', '.tmp/data.db')),
    },
    useNullAsDefault: true,
  },
});

My config for prod is

module.exports = ({ env }) => ({
  connection: {
    client: 'postgres',
    connection: {
      host: env('DATABASE_HOST', '127.0.0.1'),
      port: env.int('DATABASE_PORT', 5432),
      database: env('DATABASE_NAME', 'strapi'),
      user: env('DATABASE_USERNAME', 'strapi'),
      password: env('DATABASE_PASSWORD', 'strapi'),
      schema: env('DATABASE_SCHEMA', 'public'), // Not required
      ssl: {
        rejectUnauthorized: env.bool('DATABASE_SSL_SELF', false), // For self-signed certificates
      },
    },
    debug: false,
  },
});
1 Like

Thank you! I’m running into exact same issue. Trying to run sqlite3 for unit testing

I figured it out!

You just have to comment out the schema line from your config/database.js file. It breaks sqlite.