SQLite Importer will overwrite my database when I load my application?

Issue

I have an Ionic App using SQLite. I don't have any problems with implementation. The issue is that I need to import an SQL file using SQLitePorter to populate the database with configuration info. But also, on the same database I have user info, so my question is:

Everytime I start the app, it will import the sql file, fill the database and probably overwrite my user data too? Since it is all on the same base?

Solution

I assume that you can always init your table using string queries inside your code. The problem is not that you are importing a .sql file. Right? According to https://www.sqlitetutorial.net/sqlite-create-table/ it is obvious that you always create a table with [IF NOT EXISTS] switch. Writing a query like :

CREATE TABLE [IF NOT EXISTS] [schema_name].table_name (
    column_1 data_type PRIMARY KEY);

you let sqlite to decide if it's going to create a table with the risk to overwrite an existing table. It is supposed that you can trust that sqlite is smart enough, not to overwrite any information especially if you use 'BEGIN TRANSACTION' - 'COMMIT' procedure.

I give my answer assuming that you have imported data and user data in distinct tables, so you can manipulate what you populate and what you don't. Is that right?

What I usually do, is to have a sql file like this:

DROP TABLE configutation_a;
DROP TABLE configutation_b;

CREATE TABLE configutation_a;
INSERT INTO configutation_a (...);
CREATE TABLE configutation_b;
INSERT INTO configutation_b (...);

CREATE TABLE IF NOT EXIST user_data (...);

This means that every time the app starts, I am updating with the configuration data I have at that time (that's is why we use http.get to get any configuration file from a remote repo in the future) and create user data only if user_data table is not there (hopefully initial start).

Conclusion: It's always a good practice, in my opinion, to trust a database product 100% and abstractly let it do any transaction that might give you some risk if you implemented your self in your code; since it gives a tool for that.For example, the keyword [if not exists], is always safer than implementing a table checker your self.

I hope that helps.

PS: In case you refer in create database procedure, SQLite, connects to a database file and it doesn't exist, it creates it. For someone comfortable in sqlite command line, when you type

sqlite3 /home/user/db/configuration.db will connect you with this db and if the file is not there, it will create it.

Answered By - Tsakiroglou Fotis