importing large .sql files (sqlite)

[c-nv-s]

Describe the bug

Source data is the Sakila dataset: https://github.com/jOOQ/sakila/blob/main/sqlite-sakila-db/sqlite-sakila-schema.sql

To Reproduce

# sqlitr2 --create ./data.db
# sq add ./data.db
# cat ./sqlite-sakila-schema.sql  | sq sql --src=@data  - 
sq: unable to detect type of stdin: use flag --driver

Expected behavior

the sqlite database should get populated with all schema tables and data seeded from the .sql file
maybe it might be useful to allow a --file flag to specify a file which contains the sql statements to execute

sq version

sq v0.48.3

[neilotoole]

Hi @c-nv-s,

There isn't currently a good way to execute arbitrary SQL files against a source. Well, I should say that it's a weakness I'm aware of, but it's not been fully implemented yet.

There's actually a hidden beta version implemented for postgres, but I'm not entirely happy with it, and it's not been implemented for SQLite or the other drivers yet.

$ sq db exec --help
Execute SQL script or command using the db-native tool.

If no source is specified, the active source is used.

If --file is specified, the SQL is read from that file; otherwise if --command
is specified, that command string is used; otherwise the SQL commands are
read from stdin.

If --print or --print-long are specified, the SQL is not executed, but instead
the db-native command is printed to stdout. Note that the output will include DB
credentials.

Usage:
  sq db exec [@src] [--f SCRIPT.sql] [-c 'SQL'] [--print]

Examples:
  # Execute query.sql on @sakila_pg
  $ sq db exec @sakila_pg -f query.sql

  # Same as above, but use stdin
  $ sq db exec @sakila_pg < query.sql

  # Execute a command string against the active source
  $ sq db exec -c 'SELECT 777'
  777

  # Print the db-native command, but don't execute it
  $ sq db exec -f query.sql --print
  psql -d 'postgres://alice:[email protected]:5432/sales' -f query.sql

  # Execute against an alternative catalog or schema
  $ sq db exec @sakila_pg --schema inventory.public -f query.sql

There's an entire family of these sq db commands that I'm playing with, e.g. sq db dump, sq db restore etc. As currently being experimented with, these db commands call out to the native tools (pg_dump, pg_restore, psql) to do the heavy lifting. That gets away from sq's goal of being entirely platform-independent, so I'm struggling with the correct path forward.

Anyway, this sq db exec command (using the db native tool) would have significant overlap with sq sql.

The complexity with sq sql is that, for each DB dialect, sq needs to know how to parse a big SQL script into individual commands/statements and execute them, either as a query, or an exec statement. It's not insurmountable work, but it's a lot more work than executing a single statement (as sq sql can currently do).

So... it's on the agenda, and it's def a weakness.

_n

[neilotoole]

BTW, to create an empty SQLite DB, you don't need to use sqlitr. This should work:

$ sq add --driver=sqlite3 ./new_sqlite_datafile.db

[c-nv-s]

ok thanks for the explanation...
obviously I'm eternally grateful for this tool... I just keep trying to use it as my daily driver and report anything I assume would work but understand there will be limitations