How do I check SQLite3 syntax?

Question

Is there a way to check the syntax of a SQLite3 script without running it?

Basically, I'm looking for the SQLite3 equivalent of ruby -c script.rb, perl -c script.pl, php --syntax-check script.php, etc.

I've thought of using explain, but most of the scripts I'd like to check are kept around for reference purposes (and don't necessarily have an associated database). Using explain would also make it hard to use with something like Syntastic. (That is, I'm only wanting to check syntax, not semantics.)

Update:

I'm confused. Let's say I want to syntax check this:

select * from foo;

I could do something like:

echo 'explain select * from foo;' | sqlite3

But then I get:

SQL error near line 1: no such table: foo

All I'd expect to see is "Syntax OK" (or something similar). Is that possible?

Mirella

This is close to what I'm thinking of: developer.mimer.se/validator/parser200x/index.tml#parser – Mirella

That link is dead. The question in the OP seems important, as SQLite will not inform the user of lots of kinds of errors. Misspelled PRAGMAs, for instance, are simply ignored, which seems insane to me. – Heriot

Answer 1

A syntax checker that works well for me so far is the Ruby gem sqlint

It checks ANSI SQL: it is not specific to the sqlite dialect.

It came out in 2015, 5 years after the question was asked

It needs Ruby and a C compiler (to build the pg_query native extension.)

Here is an example of output: $ sqlint ex7a.sql ex7a.sql:81:10:ERROR syntax error at or near "*"

In the true Unix tradition, if the syntax is correct, sqlint produces no output. As the questioner asked, it doesn't check if tables exist.

Zerk

Thanks for sharing! I became aware of sqlint a couple of weeks ago, but your answer got me to actually install it and try it out. – Mirella

Answer 2

As you mentioned, you can use the EXPLAIN keyword. It will return information about how the statement would be executed had you omitted the preceding EXPLAIN keyword.

Lyndalynde

Answer 3

You could probably use EXPLAIN against an in memory database. With sqlite3, you can get an in memory database by passing ":memory:" as the filename to sqlite3_open_v2().

Myongmyopia