How to properly create a schema file for a new SQLite database?

Question

I'm trying to create a new SQLite database from scratch by writing the schema for my new tables (only one so far though), and the INSERT statements for that table in one file.

Then I go into sqlite3 and try to create the database as follows:

$ sqlite3 newdatabase.db
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> .read ./schema.sql
SQL error near line 16: near "s": syntax error

Line 16 of my file looks something like this:

INSERT INTO table_name (field1, field2) VALUES (123, 'Hello there\'s');

So the problem there is the escape character for a single quote, but I cannot figure out why this is not working. I already tried double escaping the single quote (using \' instead of \'), but that didn't work either.

What am I doing wrong here?

Answer 1

I am not familiar with SQLite, but did you try doubling up the single quotes? many databases expect it that way. So it would be

...'Hello there''s');

Answer 2

I believe you'd want to escape by doubling the single quote:

INSERT INTO table_name (field1, field2) VALUES (123, 'Hello there''s');