fails if table don't have ID column

[pantchox]

I dont have "id" column in my table, so as the title say, the server exited and the error is

[forest] 🌳🌳🌳  Unexpected error: column tweets.id does not exist
SequelizeDatabaseError: column tweets.id does not exist
    at Query.formatError (/root/node-projects/postgres-admin/node_modules/sequelize/lib/dialects/postgres/query.js:351:16)
    at query.catch.err (/root/node-projects/postgres-admin/node_modules/sequelize/lib/dialects/postgres/query.js:86:18)
    at tryCatcher (/root/node-projects/postgres-admin/node_modules/sequelize/node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (/root/node-projects/postgres-admin/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:512:31)
    at Promise._settlePromise (/root/node-projects/postgres-admin/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:569:18)
    at Promise._settlePromise0 (/root/node-projects/postgres-admin/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:614:10)
    at Promise._settlePromises (/root/node-projects/postgres-admin/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:689:18)
    at Async._drainQueue (/root/node-projects/postgres-admin/node_modules/sequelize/node_modules/bluebird/js/release/async.js:133:16)
    at Async._drainQueues (/root/node-projects/postgres-admin/node_modules/sequelize/node_modules/bluebird/js/release/async.js:143:10)
    at Immediate.Async.drainQueues (/root/node-projects/postgres-admin/node_modules/sequelize/node_modules/bluebird/js/release/async.js:17:14)
    at runCallback (timers.js:810:20)
    at tryOnImmediate (timers.js:768:5)
    at processImmediate [as _immediateCallback] (timers.js:745:5)

[SeyZ]

Hey @pantchox - can you share your table schema to reproduce your issue on my side? I'd like to check if the table has another primary key set and if the lumber generated model is well configured accordingly.

[pantchox]

--
-- PostgreSQL database dump
--

-- Dumped from database version 9.6.8
-- Dumped by pg_dump version 9.6.8

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: timescaledb; Type: EXTENSION; Schema: -; Owner:
--

CREATE EXTENSION IF NOT EXISTS timescaledb WITH SCHEMA public;


--
-- Name: EXTENSION timescaledb; Type: COMMENT; Schema: -; Owner:
--

COMMENT ON EXTENSION timescaledb IS 'Enables scalable inserts and complex queries for time-series data';


--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';


SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: tweets; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.tweets (
    "time" timestamp with time zone DEFAULT now() NOT NULL,
    entity text NOT NULL,
    tweet text NOT NULL,
    tweet_id text NOT NULL,
    tweet_time text NOT NULL,
    tweet_lang character varying(8) NOT NULL,
    retweet boolean DEFAULT false,
    user_id text NOT NULL,
    user_name text NOT NULL,
    user_lang character varying(8) NOT NULL
);


ALTER TABLE public.tweets OWNER TO postgres;

--
-- Name: _hyper_1_1_chunk; Type: TABLE; Schema: _timescaledb_internal; Owner: postgres
--

CREATE TABLE _timescaledb_internal._hyper_1_1_chunk (
    CONSTRAINT constraint_1 CHECK ((("time" >= '2018-05-19 00:00:00+00'::timestamp with time zone) AND ("time" < '2018-06-18 00:00:00+00'::timestamp with time zone)))
)
INHERITS (public.tweets);


ALTER TABLE _timescaledb_internal._hyper_1_1_chunk OWNER TO postgres;

--
-- Name: _hyper_1_2_chunk; Type: TABLE; Schema: _timescaledb_internal; Owner: postgres
--

CREATE TABLE _timescaledb_internal._hyper_1_2_chunk (
    CONSTRAINT constraint_2 CHECK ((("time" >= '2018-06-18 00:00:00+00'::timestamp with time zone) AND ("time" < '2018-07-18 00:00:00+00'::timestamp with time zone)))
)
INHERITS (public.tweets);


ALTER TABLE _timescaledb_internal._hyper_1_2_chunk OWNER TO postgres;

--
-- Name: _hyper_1_1_chunk time; Type: DEFAULT; Schema: _timescaledb_internal; Owner: postgres
--

ALTER TABLE ONLY _timescaledb_internal._hyper_1_1_chunk ALTER COLUMN "time" SET DEFAULT now();


--
-- Name: _hyper_1_1_chunk retweet; Type: DEFAULT; Schema: _timescaledb_internal; Owner: postgres
--

ALTER TABLE ONLY _timescaledb_internal._hyper_1_1_chunk ALTER COLUMN retweet SET DEFAULT false;


--
-- Name: _hyper_1_2_chunk time; Type: DEFAULT; Schema: _timescaledb_internal; Owner: postgres
--

ALTER TABLE ONLY _timescaledb_internal._hyper_1_2_chunk ALTER COLUMN "time" SET DEFAULT now();


--
-- Name: _hyper_1_2_chunk retweet; Type: DEFAULT; Schema: _timescaledb_internal; Owner: postgres
--

ALTER TABLE ONLY _timescaledb_internal._hyper_1_2_chunk ALTER COLUMN retweet SET DEFAULT false;


--
-- Name: _hyper_1_1_chunk_tweets_time_idx; Type: INDEX; Schema: _timescaledb_internal; Owner: postgres
--

CREATE INDEX _hyper_1_1_chunk_tweets_time_idx ON _timescaledb_internal._hyper_1_1_chunk USING btree ("time" DESC);


--
-- Name: _hyper_1_2_chunk_tweets_time_idx; Type: INDEX; Schema: _timescaledb_internal; Owner: postgres
--

CREATE INDEX _hyper_1_2_chunk_tweets_time_idx ON _timescaledb_internal._hyper_1_2_chunk USING btree ("time" DESC);


--
-- Name: tweets_time_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX tweets_time_idx ON public.tweets USING btree ("time" DESC);


--
-- PostgreSQL database dump complete
--

[pantchox]

note: i am using timescale db (extension to postgres) but it should not effect in my opinion at all.

[SeyZ]

Thanks @pantchox - I've successfully reproduced your bug. But your table does not seem to have any primary key at all. Am I right?

fix140=# \d tweets;
                         Table "public.tweets"
   Column   |           Type           | Collation | Nullable | Default 
------------+--------------------------+-----------+----------+---------
 time       | timestamp with time zone |           | not null | now()
 entity     | text                     |           | not null | 
 tweet      | text                     |           | not null | 
 tweet_id   | text                     |           | not null | 
 tweet_time | text                     |           | not null | 
 tweet_lang | character varying(8)     |           | not null | 
 retweet    | boolean                  |           |          | false
 user_id    | text                     |           | not null | 
 user_name  | text                     |           | not null | 
 user_lang  | character varying(8)     |           | not null | 
Indexes:
    "tweets_time_idx" btree ("time" DESC)
Number of child tables: 2 (Use \d+ to list them.)

[VincentMolinie]

@SeyZ this is normal. It's a time based db so most of the time the id will just be the timestamp (it's certainly hidding an id under the hood). This is not really the table saved in DB, it's an hypertable so it's actually not represented like that in DB but that's not the issue. We should consider the timestamp as primary key may be if there is no Primary Key to be found

[pantchox]

@VincentMolinie 👍

[oz]

Any progess on this issue?

[adarah]

Just hit this issue myself. Are there any workarounds for tables without primary keys?

[baptisteArno]

Same here with tables generated with Prisma that don't have the @id param.

How can this be fixed?