It depends on what you're trying to implement.
sql-sqlite is a function that you would use if you want to interactively send commands to a sqlite process. This is what you want if you're writing some code that uses SQL and you want a REPL where you can test your queries.
On the other hand if you're writing an emacs mode that stores data in a database then this isn't what you want. You want the
emacsql package, or possibly
emacsql lets you execute arbitrary queries using an s-expression syntax. Some examples from the documentation:
(emacsql db [:create-table people ([name (id integer :unique) salary])])
(emacsql db [:insert :into people
:values (["Jeff" 1000 60000.0] ["Susan" 1001 64000.0])])
closql is a higher-level ORM that stores class instances in database tables for you.
Since you didn't say what you're actually doing, we can only guess which is more appropriate.
Ah, since you do want a REPL,
sql-sqlite is what you want. You may already know that you can tell it which database to open by setting the
sql-database variable before calling it (or by adding a dynamic binding):
(let ((sql-database "~/temp/test.sql"))
However, I've just checked and it still prompts for the database file name. It uses the value you set as the default value for the prompt, which will save you time, but a quick look through the code shows that there's no easy way to disable the prompt. If it were a different kind of database it would also prompt you for other details such as username and password. You could add advice to the function
sql-get-login-ext so that it skips the prompt if there already is a value, but that might not be a good idea.