If you have data as JSON, you can use ``sqlite-utils insert tablename`` to insert it into a database. The table will be created with the correct (automatically detected) columns if it does not already exist.
You can pass in a single JSON object or a list of JSON objects, either as a filename or piped directly to standard-in (by using ``-`` as the filename).
You can also import newline-delimited JSON using the ``--nl`` option. Since `Datasette <https://datasette.readthedocs.io/>`__ can export newline-delimited JSON, you can combine the two tools like so::
This also means you pipe ``sqlite-utils`` together to easily create a new SQLite database file containing the results of a SQL query against another database::
$ sqlite-utils json sf-trees.db \
"select TreeID, qAddress, Latitude, Longitude from Street_Tree_List" --nl \
| sqlite-utils insert saved.db trees - --nl
# This creates saved.db with a single table called trees:
$ sqlite-utils csv saved.db "select * from trees limit 5"
Insert-replacing works exactly like inserting, with the exception that if your data has a primary key that matches an already existing record that record will be replaced with the new data.
Upserting is update-or-insert. If a row exists with the specified primary key the provided columns will be updated. If no row exists that row will be created.
Unlike ``insert --replace``, an upsert will ignore any column values that exist but are not present in the upsert document.
For example::
$ echo '{"id": 2, "age": 4}' | \
sqlite-utils upsert dogs.db dogs - --pk=id
This will update the dog with id=2 to have an age of 4, creating a new record (with a null name) if one does not exist. If a row DOES exist the name will be left as-is.
The command will fail if you reference columns that do not exist on the table. To automatically create missing columns, use the ``--alter`` option.
..note::
``upsert`` in sqlite-utils 1.x worked like ``insert ... --replace`` does in 2.x. See `issue #66 <https://github.com/simonw/sqlite-utils/issues/66>`__ for details of this change.
The last argument here is the type of the column to be created. You can use one of ``text``, ``integer``, ``float`` or ``blob``. If you leave it off, ``text`` will be used.
The ``add-foreign-key`` command can be used to add new foreign key references to an existing table - something which SQLite's ``ALTER TABLE`` command does not support.
To add a foreign key constraint pointing the ``books.author_id`` column to ``authors.id`` in another table, do this::
$ sqlite-utils add-foreign-key books.db books author_id authors id
If you omit the other table and other column references ``sqlite-utils`` will attempt to guess them - so the above example could instead look like this::
You can use the ``--not-null`` and ``--default`` options (to both ``insert`` and ``upsert``) to specify columns that should be ``NOT NULL`` or to set database defaults for one or more specific columns::
You can enable SQLite full-text search on a table and a set of columns like this::
$ sqlite-utils enable-fts mydb.db documents title summary
This will use SQLite's FTS5 module by default. Use ``--fts4`` if you want to use FTS4::
$ sqlite-utils enable-fts mydb.db documents title summary --fts4
The ``enable-fts`` command will populate the new index with all existing documents. If you later add more documents you will need to use ``populate-fts`` to cause them to be indexed as well::
A better solution here is to use database triggers. You can set up database triggers to automatically update the full-text index using the ``--create-triggers`` option when you first run ``enable-fts``::
$ sqlite-utils enable-fts mydb.db documents title summary --create-triggers
The optimize command can dramatically reduce the size of your database if you are using SQLite full-text search. It runs OPTIMIZE against all of our FTS4 and FTS5 tables, then runs VACUUM.
If you just want to run OPTIMIZE without the VACUUM, use the ``--no-vacuum`` flag.
