Sequelize not playing nice? Your sequences are to blame.

I wrote a web scraping utility in order to import recipes into my personal cookbook/kitchen management application. After a database restore/refresh using Sequelize seeds of previously exported JSON files (using DBeaver) my script had issues with UniqueViolation and primary keys.

In hindsight, and knowing that a data refresh created this issue, it is easy to determine that the sequences are set tostart at 0, rather than the MAX(id) of the primary key column in your tables.

This problem is described in the PostgreSQL Documentation and they provide a very neat solution to it. The following script will generate the script required to reset all your sequences to produce valid primary keys according to the primary key values already in use.

Script Generator Script

SELECT 'SELECT SETVAL(' ||
       quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
       ', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' ||
       quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
FROM pg_class AS S,
     pg_depend AS D,
     pg_class AS T,
     pg_attribute AS C,
     pg_tables AS PGT
WHERE S.relkind = 'S'
    AND S.oid = D.objid
    AND D.refobjid = T.oid
    AND D.refobjid = C.attrelid
    AND D.refobjsubid = C.attnum
    AND T.relname = PGT.tablename
ORDER BY S.relname;

For me, this resulted in the following output. Using DBeaver, you can select the SQL only as it default to text column selection mode making working with Databases much easier.

Output (snippet)

?column?                                                                                                |
--------------------------------------------------------------------------------------------------------|
SELECT SETVAL('public.barcode_id_seq', COALESCE(MAX(id), 1) ) FROM public.barcode;                      |
SELECT SETVAL('public.chore_history_id_seq', COALESCE(MAX(id), 1) ) FROM public.chore_history;          |
SELECT SETVAL('public.chores_id_seq', COALESCE(MAX(id), 1) ) FROM public.chores;                        |
SELECT SETVAL('public.ingredients_id_seq', COALESCE(MAX(id), 1) ) FROM public.ingredients;              |

Executing those commands will fix up all your sequences. The full logging output of the error trace is included below.

Full logging output for SEO

<class 'models.Ingredient'>
{'raw': '750g stewing steak, such as chuck, cut into strips'}
{'name': 'steak'}
Traceback (most recent call last):
  File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\engine\base.py", line 1244, in _execute_context
    cursor, statement, parameters, context
  File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\engine\default.py", line 552, in do_execute    
    cursor.execute(statement, parameters)
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "units_pkey"
DETAIL:  Key (id)=(3) already exists.


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File ".\scrape.py", line 69, in <module>
    unprocessableIngredients = process_ingredients(session,recipe, scraper.ingredients())
  File "C:\Users\danie\Documents\repos\recipe_scraper\common.py", line 136, in process_ingredients
    ingredient = get_or_create(session=session, model=Ingredient, defaults={'raw': i}, name=ii['name'].lower())
  File "C:\Users\danie\Documents\repos\recipe_scraper\common.py", line 37, in get_or_create
    instance = session.query(model).filter_by(**kwargs).first()
  File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\orm\query.py", line 3228, in first
    ret = list(self[0:1])
  File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\orm\query.py", line 3018, in __getitem__
    return list(res)
  File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\orm\query.py", line 3329, in __iter__
    self.session._autoflush()
  File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\orm\session.py", line 1588, in _autoflush
    util.raise_from_cause(e)
  File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\util\compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\util\compat.py", line 153, in reraise
    raise value
  File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\orm\session.py", line 1577, in _autoflush
    self.flush()
  File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\orm\session.py", line 2459, in flush
    self._flush(objects)
  File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\orm\session.py", line 2597, in _flush
    transaction.rollback(_capture_exception=True)
  File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\util\langhelpers.py", line 68, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\util\compat.py", line 153, in reraise
    raise value
  File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\orm\session.py", line 2557, in _flush
    flush_context.execute()
  File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 422, in execute
    rec.execute(self)
  File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 589, in execute
    uow,
  File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\orm\persistence.py", line 245, in save_obj
    insert,
  File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\orm\persistence.py", line 1138, in _emit_insert_statements
    statement, params
  File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\engine\base.py", line 988, in execute
    return meth(self, multiparams, params)
  File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\sql\elements.py", line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\engine\base.py", line 1107, in _execute_clauseelement
    distilled_params,
  File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\engine\base.py", line 1248, in _execute_context
    e, statement, parameters, cursor, context
  File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\engine\base.py", line 1466, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\util\compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\util\compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\engine\base.py", line 1244, in _execute_context
    cursor, statement, parameters, context
  File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\engine\default.py", line 552, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) 
(psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "units_pkey"
DETAIL:  Key (id)=(3) already exists.

[SQL: INSERT INTO units (name, plural, "otherNames") VALUES (%(name)s, %(plural)s, %(otherNames)s) RETURNING units.id]
[parameters: {'name': 'stewing', 'plural': None, 'otherNames': None}]
(Background on this error at: http://sqlalche.me/e/gkpj)

Happy days.