SQL statement gives me a syntax error while importing to a sqlite3 database

So I tried importing this into sqlite

-- Create the users table
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username text NOT NULL,
    email text NOT NULL,
    bio text NOT NULL,
    website text NOT NULL,
    password text NOT NULL,    
    karma text NOT NULL,
    upvotes text NOT NULL,
    comments text NOT NULL,
    role text NOT NULL
);
-- Create the posts table
CREATE TABLE posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    userid text NOT NULL,
    date text NOT NULL,
    lastEdited text NOT NULL,
    title text NOT NULL,
    body text NOT NULL,
    url text NOT NULL,
    tags text NOT NULL,
    locked text NOT NULL,
    hidden text NOT NULL,
    upvotes text NOT NULL,
);
-- Create the comments table
CREATE TABLE comments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    inReplyTo text NOT NULL,
    userid text NOT NULL,
    date text NOT NULL,
    lastEdited text NOT NULL,
    body text NOT NULL,
    upvotes text NOT NULL,
);
-- Settings
CREATE TABLE settings (
    key text NOT NULL,
    value text NOT NULL
);

Running cat init.sql | sqlite3 database.sqlite shows this error:

Error: near line 15: near ")": syntax error
Error: near line 29: near ")": syntax error
Error: near line 50: no such table: posts

Using mysql2sqlite gives me this error:

Error: near line 4: near ",": syntax error
Error: near line 45: no such table: users
Error: near line 46: NOT NULL constraint failed: posts.lastEdited

What mistake did I make in the SQL statements?

I think it doesn’t support those trailing commas that you have, e.g.

CREATE TABLE posts (
    ...
    upvotes text NOT NULL,
);

dunno if that’s all tho

No luck:

Error: near line 2: near "username": syntax error
Error: near line 15: table posts already exists
Error: near line 29: table comments already exists
Error: near line 39: table settings already exists
Error: near line 49: no such table: users
Error: near line 50: NOT NULL constraint failed: posts.lastEdited
-- Create the users table
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT
    username text NOT NULL
    email text NOT NULL
    bio text NOT NULL
    website text NOT NULL
    password text NOT NULL  
    karma text NOT NULL
    upvotes text NOT NULL
    comments text NOT NULL
    role text NOT NULL
);
-- Create the posts table
CREATE TABLE posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT
    userid text NOT NULL
    date text NOT NULL
    lastEdited text NOT NULL
    title text NOT NULL
    body text NOT NULL
    url text NOT NULL
    tags text NOT NULL
    locked text NOT NULL
    hidden text NOT NULL
    upvotes text NOT NULL
);
-- Create the comments table
CREATE TABLE comments (
    id INTEGER PRIMARY KEY AUTOINCREMENT
    inReplyTo text NOT NULL
    userid text NOT NULL
    date text NOT NULL
    lastEdited text NOT NULL
    body text NOT NULL
    upvotes text NOT NULL
);
-- Settings
CREATE TABLE settings (
    key text NOT NULL
    value text NOT NULL
);

didn’t meant to say you should remove all commas :speak_no_evil:

5 Likes

wh0 is right, you need to trim the trailing commas from lines 26 and 36 only. Final sql:

-- Create the users table
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username text NOT NULL,
    email text NOT NULL,
    bio text NOT NULL,
    website text NOT NULL,
    password text NOT NULL,    
    karma text NOT NULL,
    upvotes text NOT NULL,
    comments text NOT NULL,
    role text NOT NULL
);
-- Create the posts table
CREATE TABLE posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    userid text NOT NULL,
    date text NOT NULL,
    lastEdited text NOT NULL,
    title text NOT NULL,
    body text NOT NULL,
    url text NOT NULL,
    tags text NOT NULL,
    locked text NOT NULL,
    hidden text NOT NULL,
    upvotes text NOT NULL
);
-- Create the comments table
CREATE TABLE comments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    inReplyTo text NOT NULL,
    userid text NOT NULL,
    date text NOT NULL,
    lastEdited text NOT NULL,
    body text NOT NULL,
    upvotes text NOT NULL
);
-- Settings
CREATE TABLE settings (
    key text NOT NULL,
    value text NOT NULL
);

Running your original sql, you can introspect the database with .tables (in sqlite3’s interactive mode) and it will prove that both users and settings have been created. The ones that haven’t were the ones with trailing commas.

4 Likes

My brain turned into mush for a moment, thanks!