Getting SQLite Syntax Error when saving model in Sequelize.js v7

Question

So, I'm trying to learn full stack by making a list app, and I'm using Sequelize.js for the database. So far I've got User and List models. I recently switched to Sequelize v7.0.0-alpha.47. I can't quite remember, but I believe it worked for a bit using v7; however, now I'm getting this strange error:

Error: SQLITE_ERROR: near "RETURNING": syntax error
    at new BaseError (/home/projects/listless-qzftb29s/node_modules/@sequelize/core/src/errors/base-error.ts:35:5)
    at new DatabaseError (/home/projects/listless-qzftb29s/node_modules/@sequelize/core/src/errors/database-error.ts:32:5)
    at SqliteQuery.formatError (/home/projects/listless-qzftb29s/node_modules/@sequelize/sqlite3/src/query.js:303:16)
    at executeSql (/home/projects/listless-qzftb29s/node_modules/@sequelize/sqlite3/src/query.js:197:20)
    at eval (/home/projects/listless-qzftb29s/node_modules/@sequelize/core/src/sequelize.js:380:16)

Here is the database main code. I believe the error is occurring from saving the model:

import { sequelize } from "./models";
import User from "./models/user.model";

await sequelize.sync();

const maxCodes = (
  await User.findOrCreate({ // <<< error here
    where: { username: "maxCodes" },
  })
)[0];

I also tried it like this:

import { sequelize } from "./models";
import User from "./models/user.model";

await sequelize.sync();

const [maxCodes, built] = (
  await User.findOrBuild({
    where: { username: "maxCodes" },
  })
);
if (built) maxCodes.save(); // async error w/out await, so it must occur at saving

I'm using @sequelize/[email protected] and @sequelize/[email protected].

Here's my user model:

import { Model, DataTypes } from "@sequelize/core";
import type {
  HasManyAddAssociationMixin,
  HasManyCountAssociationsMixin,
  HasManyCreateAssociationMixin,
  HasManyGetAssociationsMixin,
  HasManyHasAssociationMixin,
  HasManySetAssociationsMixin,
  HasManyAddAssociationsMixin,
  HasManyHasAssociationsMixin,
  HasManyRemoveAssociationMixin,
  HasManyRemoveAssociationsMixin,
  NonAttribute,
  CreationOptional,
  InferAttributes,
  InferCreationAttributes,
} from "@sequelize/core";
import {
  Attribute,
  PrimaryKey,
  AutoIncrement,
  NotNull,
  HasMany,
} from "@sequelize/core/decorators-legacy";

import List from "./list.model";

class User extends Model<InferAttributes<User>, InferCreationAttributes<User>> {
  @Attribute(DataTypes.INTEGER)
  @PrimaryKey
  @AutoIncrement
  id!: CreationOptional<number>;

  @Attribute(DataTypes.STRING)
  @NotNull
  username!: string;

  @HasMany(() => List, {
    foreignKey: "userId",
    inverse: {
      as: "user",
    },
  })
  lists?: NonAttribute<List[]>;

  declare getLists: HasManyGetAssociationsMixin<List>; // Note the null assertions!
  declare addList: HasManyAddAssociationMixin<List, number>;
  declare addLists: HasManyAddAssociationsMixin<List, number>;
  declare setLists: HasManySetAssociationsMixin<List, number>;
  declare removeList: HasManyRemoveAssociationMixin<List, number>;
  declare removeLists: HasManyRemoveAssociationsMixin<List, number>;
  declare hasList: HasManyHasAssociationMixin<List, number>;
  declare hasLists: HasManyHasAssociationsMixin<List, number>;
  declare countLists: HasManyCountAssociationsMixin<List>;
  declare createList: HasManyCreateAssociationMixin<List, "userId">;

  declare createdAt: CreationOptional<Date>;
  declare updatedAt: CreationOptional<Date>;
}

export default User;

Here is my List model code:

import { Model, DataTypes } from "@sequelize/core";
import type {
  NonAttribute,
  ForeignKey,
  CreationOptional,
  InferAttributes,
  InferCreationAttributes,
  BelongsToGetAssociationMixin,
  BelongsToCreateAssociationMixin,
  BelongsToSetAssociationMixin,
} from "@sequelize/core";

import {
  Attribute,
  PrimaryKey,
  AutoIncrement,
  NotNull,
  BelongsTo,
} from "@sequelize/core/decorators-legacy";

import User from "./user.model";

class List extends Model<InferAttributes<List>, InferCreationAttributes<List>> {
  @Attribute(DataTypes.INTEGER)
  @PrimaryKey
  @AutoIncrement
  id!: CreationOptional<number>;

  @Attribute(DataTypes.STRING)
  @NotNull
  name!: string;

  @BelongsTo(() => User, {
    foreignKey: "userId",
    inverse: {
      as: "lists",
      type: "hasMany",
    },
  })
  user!: NonAttribute<User>;

  @Attribute(DataTypes.INTEGER)
  @NotNull
  userId!: ForeignKey<User["id"]>;

  // timestamps!
  // createdAt can be undefined during creation
  declare createdAt: CreationOptional<Date>;
  // updatedAt can be undefined during creation
  declare updatedAt: CreationOptional<Date>;

  declare getUser: BelongsToGetAssociationMixin<User>;
  declare createUser: BelongsToCreateAssociationMixin<User>;
  declare setUser: BelongsToSetAssociationMixin<User, User["id"]>;
}

export default List;

And finally, my Sequelize config:

import { Sequelize } from "@sequelize/core";
import { SqliteDialect } from "@sequelize/sqlite3";

import User from "./user.model";
import List from "./list.model";

const sequelize = new Sequelize({
  dialect: SqliteDialect,
  storage: "data/database.sqlite",
  models: [User, List],
});

export { sequelize, Sequelize };

I honestly have no idea what is happening. Is this an error on my part, or an error in Sequelize itself? Why am I even getting this error? (Like what code is causing the error?)

If you want to see the full code (note: much more than just server-side), you can check out the live demo. If you think that the error is coming from elsewhere in the code, instead of what I've shown here, please comment and I'll review my question and code.

"what code is causing the error?" can only be answered if you either provide the SQL that Sequelize generated or reduce your code to a minimal reproducible example (you're basically asking how to debug Sequelize). — user4157124
– user4157124, Commented Nov 5 at 2:51
@user4157124 thanks for the guidance, this is my first question on SO. I'll create a new demo that is just an MRE. (Not sure how to provide generated SQL, and I pretty much have no experience w/ SQL) — MaxCodes
– MaxCodes, Commented Nov 5 at 21:10

MikeT · Accepted Answer · 2025-11-05 05:57:11Z

1

The Cause and why

The message

SQLITE_ERROR: near "RETURNING": syntax error

likely (almost certainly) indicates that RETURNING is invalid where found in the SQL that is being parsed (aka prepared) for execution.

A likely cause is that the SQLite version being used is one that does not recognise the RETURNING clause.

by likely, it would only be unlikely, if for whatever reason, that some non SQLite code were mimicking the message.
that is SQLITE_ERROR is the generic error code issued when a more specific error code is not available. Frequently such an error code will contain additional information.
In the case of this question the additional information provided with the code is that there is a syntax error near RETURNING. In short SQLite doesn't know how to handle RETURNING that the parser has come accross.
Therefore assuming that whatever generated the SQL being parsed is doing so correctly (no apparent issues found in the sequelise issues for V7), AND as RETURNING is a newly introduced SQLite keyword, the it is highly likely that the sqlite version is one that does not handle the keyword.
As such a sensible/logical net step is to look to see if there is a version issue. Furthermore, if the highly likely wrong version of SQLite, is the issue then looking elsewhere for an error would probably be futile (although, as per the potential alternative fixes, could circumvent the version issue. However, as can be seen by the inclusion of the alternatives, looking elsewhere would be a waste if it only resulted in one of the alternatives being used)
- If you wish to delve more into the depths of SQLite Error Codes, results then here's a link https://www.sqlite.org/rescode.html#error

The versions of SQLite that support RETURNING are those from and greater than 3.35.0 as per:-

The RETURNING syntax has been supported by SQLite since version 3.35.0 (2021-03-12).

an extract from https://sqlite.org/lang_returning.html

Fixing the issue

You either

need to utilise a version of SQLite that supports the RETURNING clause,
or utilise a version of Sequelize that does not utilise the RETURNING clause,
or if possible resort to code that does not utilise the RETURNING clause (perhaps utilising Raw SQL https://sequelize.org/docs/v7/querying/raw-queries/)
- the first is suggested/recommended as the easiest/best approach.

Note the caveats and limitations, as per section 3 of the link to the RETURNING clause and been assumed to not apply.

Additional

You could use the RAW SQL feature of sequelise (link above) to invoke the sqlite_version in built function by executing the SQL SELECT sqlite_version();, examine the output to confirm or not that this issue is in fact the sqlite version.

see https://sqlite.org/lang_corefunc.html#sqlite_version
e.g.
- note just selected an easy to use SQlite tool to demonstrate. That tool has a much out of date (10/10/2019) version of SQLite (3.30.1).
- the date of the version obtained from https://www.sqlite.org/changes.html (which is the official SQLite list of versions)

edited Nov 5 at 5:57

answered Nov 4 at 19:08

MikeT

57.9k16 gold badges61 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

MaxCodes Nov 4 at 19:49

So this is an error in Sequelize itself?

MikeT Nov 4 at 22:28

Not really, it appears to be an environmental incompatibility with the underlying SQLite I believe due to the node js setup. To quote an AI response to the question "how to specify what version of sqlite to use in node js", the answer includes "Specifying the SQLite version used in a Node.js application primarily depends on how SQLite is integrated into the project.". Search results have a link that may be of interest as per stackoverflow.com/questions/14355004/… (it is way out of date though).

MikeT Nov 4 at 23:49

After doing a little hunting I think you need the SQLite3 npm (or equivalent for other package managers) package that is 5.0.3 or higher. 5.0.2 Bundles SQLite3 3.32.3 as a fallback if the installing system doesn't include SQLite, whilst 5.0.3 Bundles SQLite v3.38.2, or you can build using a local SQLite (different matter if using local SQLite). See npmjs.com/package/sqlite3/v/5.0.3

MaxCodes Nov 5 at 0:31

Hmm, @sequelize/sqlite3 uses sqlite3 v. 5.1.7, though...

MikeT Nov 5 at 3:50

Re uses 5.1.7 I suspect that what you have seen is the version of SQLite that it expects and thus generates code for. Searching on what version of sqlite does sequelize sqlite3 use comes back with Sequelize, when configured to use SQLite, relies on sqlite3 npm package for connector library. SQLite version used by sequelize-sqlite3 is the version of the npm package installed in project.. Hence the above re npm etc. However, never used node js and also therefore sequelise. Just going by the most likely cause and looking into that (would only have commented but too much hence answer)

user4157124 Nov 5 at 2:52

@MikeT Vote to close for requiring a minimal reproducible example rather than posting speculative comments as answers please (it also reads like you've just slightly reworded what a generative AI suggested; that's not allowed). Besides, syntax errors of the format "near X" complain about what's after X, which indicates SQLite recognises RETURNING is not used correctly.

Collectives™ on Stack Overflow

Getting SQLite Syntax Error when saving model in Sequelize.js v7

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related