SQLite - Run multi-line SQL script from file?

Question

I have the following SQL in a file, user.sql:

CREATE TABLE user
(
  user_id INTEGER PRIMARY KEY,
  username varchar(255),
  password varchar(255)
);

However, when the following command is executed:

sqlite3 my.db < user.sql 

The following error is generated:

Error: near line 1: near ")": syntax error

I would prefer to keep the SQL as-is, as the file will be checked into source control and will be more maintainable and readable as it is now. Can the SQL span multiple lines like this, or do I need to put it all on the same line?

Answer 1

Multiple lines aren't a problem. There might be a platform issue, because I am able to run this example successfully using SQLite3 3.6.22 on OS X 10.5.8.

Answer 2

I realize that this is not a direct answer to your question. As Brian mentions, this could be a silly platform issue.

If you interface with SQLite through Python, you will probably avoid most platform-specific issues and you get to have fun things like datetime columns :-)

Something like this should work fine:

import sqlite3

qry = open('create_table_user.sql', 'r').read()
conn = sqlite3.connect('/path/to/db')
c = conn.cursor()
c.execute(qry)
conn.commit()
c.close()
conn.close()