How to Edit a SQLite Database in the Terminal

I usually would use an editor for SQLite called DB Browser (https://sqlitebrowser.org/), but sometimes if I am accessing a server with SSH, I do not want to deal with having to use a GUI program to edit my database.

To be able to modify your database, you may need to alter permissions on the database file, as well as the permissions on the folder it is in, or you will get an error like Runtime error: attempt to write a readonly database (8), meaning you would not have the proper permissions to edit the database. You will want to at least have read and write permissions for both of them.

chown user:user database_name.db
chown user:user /path/name
chmod 660 database_name.db
chmod 660 /path/name

Once this is done, install sqlite3 and start it up, then open your database file within sqlite3.

apt install sqlite3
sqlite3
.open database_name.db;

To test if everything is working, use the .tables command to list the names of all the tables in the database. If it lists any tables, you can view a table’s column info with PRAGMA table_info(). You can also view all the data in a table with a SELECT query.

# List all table names
.tables
# List column info for a table
PRAGMA table_info(table_name);
# Show all rows in a table
SELECT * FROM table_name;
# Show 10 rows from a table
SELECT * FROM table_name LIMIT 10;
# Show a row in a table with the matching ID
SELECT * FROM table_name WHERE id_name = value;

If you have a row you would like to change, you will need to know the name of the ID column, the ID value for that row, and the name of the column you want to change for that row. For example, to change the value of a String value in a row:

UPDATE table_name SET column_name = "Hello, world!" WHERE id_name = value;

If the user you are logged in as has the proper read/write permissions, this should work without a problem. To check if the change was successful, run a SELECT query for that row.

SELECT * FROM table_name WHERE id_name = value;