There are two options for creating and updating databases.

One is to create a database externally, then place it in the assets folder of the project and then copy the entire database from there. This is much quicker if the database has a lot of tables and other components. Upgrades are triggered by changing the database version number in the res/values/strings.xml file. Upgrades would then be accomplished by creating a new database externally, replacing the old database in the assets folder with the new database, saving the old database in internal storage under another name, copying the new database from the assets folder into internal storage, transferring all of the data from the old database (that was renamed earlier) into the new database and finally deleting the old database. You can create a database originally by using the SQLite Manager FireFox plugin to execute your creation sql statements.

The other option is to create a database internally from a sql file. This is not as quick but the delay would probably be unnoticeable to the users if the database has only a few tables. Upgrades are triggered by changing the database version number in the res/values/strings.xml file. Upgrades would then be accomplished by processing an upgrade sql file. The data in the database will remain unchanged except when its container is removed, for example dropping a table.

The example below demonstrates how to use either method.

Here is a sample create_database.sql file. It is to be placed in the assets folder of the project for the internal method or copied into the "Execute SQL' of SQLite Manager to create the database for the external method. (NOTE: Notice the comment about the table required by Android.)

--Android requires a table named 'android_metadata' with a 'locale' columnCREATE TABLE "android_metadata" ("locale" TEXT DEFAULT 'en_US');INSERT INTO "android_metadata" VALUES ('en_US');CREATE TABLE "kitchen_table";CREATE TABLE "coffee_table";CREATE TABLE "pool_table";CREATE TABLE "dining_room_table";CREATE TABLE "card_table"; 

Here is a sample update_database.sql file. It is to be placed in the assets folder of the project for the internal method or copied into the "Execute SQL' of SQLite Manager to create the database for the external method. (NOTE: Notice that all three types of SQL comments will be ignored by the sql parser that is included in this example.)

--CREATE TABLE "kitchen_table";  This is one type of comment in sql.  It is ignored by parseSql./* * CREATE TABLE "coffee_table"; This is a second type of comment in sql.  It is ignored by parseSql. */{CREATE TABLE "pool_table";  This is a third type of comment in sql.  It is ignored by parseSql.}/* CREATE TABLE "dining_room_table"; This is a second type of comment in sql.  It is ignored by parseSql. */{ CREATE TABLE "card_table"; This is a third type of comment in sql.  It is ignored by parseSql. }--DROP TABLE "picnic_table"; Uncomment this if picnic table was previously created and now is being replaced.CREATE TABLE "picnic_table" ("plates" TEXT);INSERT INTO "picnic_table" VALUES ('paper');

Here is an entry to add to the /res/values/strings.xml file for the database version number.

<item type="string" name="databaseVersion" format="integer">1</item>

Here is an activity that accesses the database and then uses it. (Note: You might want to run the database code in a separate thread if it uses a lot of resources.)

package android.example;import android.app.Activity;import android.database.sqlite.SQLiteDatabase;import android.os.Bundle;/** * @author Danny Remington - MacroSolve *  *         Activity for demonstrating how to use a sqlite database. */public class Database extends Activity {     /** Called when the activity is first created. */     @Override    public void onCreate(Bundle savedInstanceState) {        super.onCreate(savedInstanceState);        setContentView(R.layout.main);        DatabaseHelper myDbHelper;        SQLiteDatabase myDb = null;        myDbHelper = new DatabaseHelper(this);        /*         * Database must be initialized before it can be used. This will ensure         * that the database exists and is the current version.         */         myDbHelper.initializeDataBase();         try {            // A reference to the database can be obtained after initialization.            myDb = myDbHelper.getWritableDatabase();            /*             * Place code to use database here.             */         } catch (Exception ex) {            ex.printStackTrace();         } finally {            try {                myDbHelper.close();            } catch (Exception ex) {                ex.printStackTrace();            } finally {                myDb.close();            }        }    }}

Here is the database helper class where the database is created or updated if necessary. (NOTE: Android requires that you create a class that extends SQLiteOpenHelper in order to work with a Sqlite database.)

package android.example;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;/** * @author Danny Remington - MacroSolve *  *         Helper class for sqlite database. */public class DatabaseHelper extends SQLiteOpenHelper {    /*     * The Android's default system path of the application database in internal     * storage. The package of the application is part of the path of the     * directory.     */    private static String DB_DIR = "/data/data/android.example/databases/";    private static String DB_NAME = "database.sqlite";    private static String DB_PATH = DB_DIR + DB_NAME;    private static String OLD_DB_PATH = DB_DIR + "old_" + DB_NAME;    private final Context myContext;    private boolean createDatabase = false;    private boolean upgradeDatabase = false;    /**     * Constructor Takes and keeps a reference of the passed context in order to     * access to the application assets and resources.     *      * @param context     */    public DatabaseHelper(Context context) {        super(context, DB_NAME, null, context.getResources().getInteger(                R.string.databaseVersion));        myContext = context;        // Get the path of the database that is based on the context.        DB_PATH = myContext.getDatabasePath(DB_NAME).getAbsolutePath();    }    /**     * Upgrade the database in internal storage if it exists but is not current.      * Create a new empty database in internal storage if it does not exist.     */    public void initializeDataBase() {        /*         * Creates or updates the database in internal storage if it is needed         * before opening the database. In all cases opening the database copies         * the database in internal storage to the cache.         */        getWritableDatabase();        if (createDatabase) {            /*             * If the database is created by the copy method, then the creation             * code needs to go here. This method consists of copying the new             * database from assets into internal storage and then caching it.             */            try {                /*                 * Write over the empty data that was created in internal                 * storage with the one in assets and then cache it.                 */                copyDataBase();            } catch (IOException e) {                throw new Error("Error copying database");            }        } else if (upgradeDatabase) {            /*             * If the database is upgraded by the copy and reload method, then             * the upgrade code needs to go here. This method consists of             * renaming the old database in internal storage, create an empty             * new database in internal storage, copying the database from             * assets to the new database in internal storage, caching the new             * database from internal storage, loading the data from the old             * database into the new database in the cache and then deleting the             * old database from internal storage.             */            try {                FileHelper.copyFile(DB_PATH, OLD_DB_PATH);                copyDataBase();                SQLiteDatabase old_db = SQLiteDatabase.openDatabase(OLD_DB_PATH, null, SQLiteDatabase.OPEN_READWRITE);                SQLiteDatabase new_db = SQLiteDatabase.openDatabase(DB_PATH,null, SQLiteDatabase.OPEN_READWRITE);                /*                 * Add code to load data into the new database from the old                 * database and then delete the old database from internal                 * storage after all data has been transferred.                 */            } catch (IOException e) {                throw new Error("Error copying database");            }        }    }    /**     * Copies your database from your local assets-folder to the just created     * empty database in the system folder, from where it can be accessed and     * handled. This is done by transfering bytestream.     * */    private void copyDataBase() throws IOException {        /*         * Close SQLiteOpenHelper so it will commit the created empty database         * to internal storage.         */        close();        /*         * Open the database in the assets folder as the input stream.         */        InputStream myInput = myContext.getAssets().open(DB_NAME);        /*         * Open the empty db in interal storage as the output stream.         */        OutputStream myOutput = new FileOutputStream(DB_PATH);        /*         * Copy over the empty db in internal storage with the database in the         * assets folder.         */        FileHelper.copyFile(myInput, myOutput);        /*         * Access the copied database so SQLiteHelper will cache it and mark it         * as created.         */        getWritableDatabase().close();    }    /*     * This is where the creation of tables and the initial population of the     * tables should happen, if a database is being created from scratch instead     * of being copied from the application package assets. Copying a database     * from the application package assets to internal storage inside this     * method will result in a corrupted database.     * <P>     * NOTE: This method is normally only called when a database has not already     * been created. When the database has been copied, then this method is     * called the first time a reference to the database is retrieved after the     * database is copied since the database last cached by SQLiteOpenHelper is     * different than the database in internal storage.     */    @Override    public void onCreate(SQLiteDatabase db) {        /*         * Signal that a new database needs to be copied. The copy process must         * be performed after the database in the cache has been closed causing         * it to be committed to internal storage. Otherwise the database in         * internal storage will not have the same creation timestamp as the one         * in the cache causing the database in internal storage to be marked as         * corrupted.         */        createDatabase = true;        /*         * This will create by reading a sql file and executing the commands in         * it.         */            // try {            // InputStream is = myContext.getResources().getAssets().open(            // "create_database.sql");            //            // String[] statements = FileHelper.parseSqlFile(is);            //            // for (String statement : statements) {            // db.execSQL(statement);            // }            // } catch (Exception ex) {            // ex.printStackTrace();            // }    }    /**     * Called only if version number was changed and the database has already     * been created. Copying a database from the application package assets to     * the internal data system inside this method will result in a corrupted     * database in the internal data system.     */    @Override    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {        /*         * Signal that the database needs to be upgraded for the copy method of         * creation. The copy process must be performed after the database has         * been opened or the database will be corrupted.         */        upgradeDatabase = true;        /*         * Code to update the database via execution of sql statements goes         * here.         */        /*         * This will upgrade by reading a sql file and executing the commands in         * it.         */        // try {        // InputStream is = myContext.getResources().getAssets().open(        // "upgrade_database.sql");        //        // String[] statements = FileHelper.parseSqlFile(is);        //        // for (String statement : statements) {        // db.execSQL(statement);        // }        // } catch (Exception ex) {        // ex.printStackTrace();        // }    }    /**     * Called everytime the database is opened by getReadableDatabase or     * getWritableDatabase. This is called after onCreate or onUpgrade is     * called.     */    @Override    public void onOpen(SQLiteDatabase db) {        super.onOpen(db);    }    /*     * Add your public helper methods to access and get content from the     * database. You could return cursors by doing     * "return myDataBase.query(....)" so it'd be easy to you to create adapters     * for your views.     */}

Here's the FileHelper class that contains methods for byte stream copying files and parsing sql files.

package android.example;import java.io.BufferedReader;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.FileReader;import java.io.IOException;import java.io.InputStream;import java.io.InputStreamReader;import java.io.OutputStream;import java.io.Reader;import java.nio.channels.FileChannel;/** * @author Danny Remington - MacroSolve *  *         Helper class for common tasks using files. *  */public class FileHelper {    /**     * Creates the specified <i><b>toFile</b></i> that is a byte for byte a copy     * of <i><b>fromFile</b></i>. If <i><b>toFile</b></i> already existed, then     * it will be replaced with a copy of <i><b>fromFile</b></i>. The name and     * path of <i><b>toFile</b></i> will be that of <i><b>toFile</b></i>. Both     * <i><b>fromFile</b></i> and <i><b>toFile</b></i> will be closed by this     * operation.     *      * @param fromFile     *            - InputStream for the file to copy from.     * @param toFile     *            - InputStream for the file to copy to.     */    public static void copyFile(InputStream fromFile, OutputStream toFile) throws IOException {        // transfer bytes from the inputfile to the outputfile        byte[] buffer = new byte[1024];        int length;        try {            while ((length = fromFile.read(buffer)) > 0) {                toFile.write(buffer, 0, length);            }        }        // Close the streams        finally {            try {                if (toFile != null) {                    try {                        toFile.flush();                    } finally {                        toFile.close();                    }            }            } finally {                if (fromFile != null) {                    fromFile.close();                }            }        }    }    /**     * Creates the specified <i><b>toFile</b></i> that is a byte for byte a copy     * of <i><b>fromFile</b></i>. If <i><b>toFile</b></i> already existed, then     * it will be replaced with a copy of <i><b>fromFile</b></i>. The name and     * path of <i><b>toFile</b></i> will be that of <i><b>toFile</b></i>. Both     * <i><b>fromFile</b></i> and <i><b>toFile</b></i> will be closed by this     * operation.     *      * @param fromFile     *            - String specifying the path of the file to copy from.     * @param toFile     *            - String specifying the path of the file to copy to.     */    public static void copyFile(String fromFile, String toFile) throws IOException {        copyFile(new FileInputStream(fromFile), new FileOutputStream(toFile));    }    /**     * Creates the specified <i><b>toFile</b></i> that is a byte for byte a copy     * of <i><b>fromFile</b></i>. If <i><b>toFile</b></i> already existed, then     * it will be replaced with a copy of <i><b>fromFile</b></i>. The name and     * path of <i><b>toFile</b></i> will be that of <i><b>toFile</b></i>. Both     * <i><b>fromFile</b></i> and <i><b>toFile</b></i> will be closed by this     * operation.     *      * @param fromFile     *            - File for the file to copy from.     * @param toFile     *            - File for the file to copy to.     */    public static void copyFile(File fromFile, File toFile) throws IOException {        copyFile(new FileInputStream(fromFile), new FileOutputStream(toFile));    }    /**     * Creates the specified <i><b>toFile</b></i> that is a byte for byte a copy     * of <i><b>fromFile</b></i>. If <i><b>toFile</b></i> already existed, then     * it will be replaced with a copy of <i><b>fromFile</b></i>. The name and     * path of <i><b>toFile</b></i> will be that of <i><b>toFile</b></i>. Both     * <i><b>fromFile</b></i> and <i><b>toFile</b></i> will be closed by this     * operation.     *      * @param fromFile     *            - FileInputStream for the file to copy from.     * @param toFile     *            - FileInputStream for the file to copy to.     */    public static void copyFile(FileInputStream fromFile, FileOutputStream toFile) throws IOException {        FileChannel fromChannel = fromFile.getChannel();        FileChannel toChannel = toFile.getChannel();        try {            fromChannel.transferTo(0, fromChannel.size(), toChannel);        } finally {            try {                if (fromChannel != null) {                    fromChannel.close();                }            } finally {                if (toChannel != null) {                    toChannel.close();                }            }        }    }    /**     * Parses a file containing sql statements into a String array that contains     * only the sql statements. Comments and white spaces in the file are not     * parsed into the String array. Note the file must not contained malformed     * comments and all sql statements must end with a semi-colon ";" in order     * for the file to be parsed correctly. The sql statements in the String     * array will not end with a semi-colon ";".     *      * @param sqlFile     *            - String containing the path for the file that contains sql     *            statements.     *      * @return String array containing the sql statements.     */    public static String[] parseSqlFile(String sqlFile) throws IOException {        return parseSqlFile(new BufferedReader(new FileReader(sqlFile)));    }    /**     * Parses a file containing sql statements into a String array that contains     * only the sql statements. Comments and white spaces in the file are not     * parsed into the String array. Note the file must not contained malformed     * comments and all sql statements must end with a semi-colon ";" in order     * for the file to be parsed correctly. The sql statements in the String     * array will not end with a semi-colon ";".     *      * @param sqlFile     *            - InputStream for the file that contains sql statements.     *      * @return String array containing the sql statements.     */    public static String[] parseSqlFile(InputStream sqlFile) throws IOException {        return parseSqlFile(new BufferedReader(new InputStreamReader(sqlFile)));    }    /**     * Parses a file containing sql statements into a String array that contains     * only the sql statements. Comments and white spaces in the file are not     * parsed into the String array. Note the file must not contained malformed     * comments and all sql statements must end with a semi-colon ";" in order     * for the file to be parsed correctly. The sql statements in the String     * array will not end with a semi-colon ";".     *      * @param sqlFile     *            - Reader for the file that contains sql statements.     *      * @return String array containing the sql statements.     */    public static String[] parseSqlFile(Reader sqlFile) throws IOException {        return parseSqlFile(new BufferedReader(sqlFile));    }    /**     * Parses a file containing sql statements into a String array that contains     * only the sql statements. Comments and white spaces in the file are not     * parsed into the String array. Note the file must not contained malformed     * comments and all sql statements must end with a semi-colon ";" in order     * for the file to be parsed correctly. The sql statements in the String     * array will not end with a semi-colon ";".     *      * @param sqlFile     *            - BufferedReader for the file that contains sql statements.     *      * @return String array containing the sql statements.     */    public static String[] parseSqlFile(BufferedReader sqlFile) throws IOException {        String line;        StringBuilder sql = new StringBuilder();        String multiLineComment = null;        while ((line = sqlFile.readLine()) != null) {            line = line.trim();            // Check for start of multi-line comment            if (multiLineComment == null) {                // Check for first multi-line comment type                if (line.startsWith("/*")) {                    if (!line.endsWith("}")) {                        multiLineComment = "/*";                    }                // Check for second multi-line comment type                } else if (line.startsWith("{")) {                    if (!line.endsWith("}")) {                        multiLineComment = "{";                }                // Append line if line is not empty or a single line comment                } else if (!line.startsWith("--") && !line.equals("")) {                    sql.append(line);                } // Check for matching end comment            } else if (multiLineComment.equals("/*")) {                if (line.endsWith("*/")) {                    multiLineComment = null;                }            // Check for matching end comment            } else if (multiLineComment.equals("{")) {                if (line.endsWith("}")) {                    multiLineComment = null;                }            }        }        sqlFile.close();        return sql.toString().split(";");    }}

The SQLiteAssetHelper library makes this task really simple.

It's easy to add as a gradle dependency (but a Jar is also available for Ant/Eclipse), and together with the documentation it can be found at:
https://github.com/jgilfelt/android-sqlite-asset-helper

Note: This project is no longer maintained as stated on above Github link.

As explained in documentation:

1. Add the dependency to your module's gradle build file:

   dependencies {    compile 'com.readystatesoftware.sqliteasset:sqliteassethelper:+'}

2. Copy the database into the assets directory, in a subdirectory called assets/databases. For instance:
   assets/databases/my_database.db

   (Optionally, you may compress the database in a zip file such as assets/databases/my_database.zip. This isn't needed, since the APK is compressed as a whole already.)

3. Create a class, for example:

   public class MyDatabase extends SQLiteAssetHelper {    private static final String DATABASE_NAME = "my_database.db";    private static final int DATABASE_VERSION = 1;    public MyDatabase(Context context) {        super(context, DATABASE_NAME, null, DATABASE_VERSION);    }}

Shipping the app with a database file, in Android Studio 3.0

Shipping the app with a database file is a good idea for me. The advantage is that you don't need to do a complex initialization, which sometimes costs lots of time, if your data set is huge.

Step 1: Prepare database file

Have your database file ready. It can be either a .db file or a .sqlite file. If you use a .sqlite file, all you need to do is to change file extension names. The steps are the same.

In this example, I prepared a file called testDB.db. It has one table and some sample data in it like this

Step 2: Import the file into your project

Create the assets folder if you haven't had one. Then copy and paste the database file into this folder

Step 3: Copy the file to the app's data folder

You need to copy the database file to the app's data folder in order to do further interaction with it. This is a one time action (initialization) to copy the database file. If you call this code multiple times, the database file in data folder will be overwritten by the one in assets folder. This overwrite process is useful when you want to update the database in future during the app update.

Note that during app update, this database file will not be changed in the app's data folder. Only uninstall will delete it.

The database file needs to be copied to /databases folder. Open Device File Explorer. Enter data/data/<YourAppName>/ location. This is the app's default data folder mentioned above. And by default, the database file will be place in another folder called databases under this directory

Now, the copy file process is pretty much like the what Java is doing. Use the following code to do the copy paste. This is the initiation code. It can also be used to update(by overwriting) the database file in future.

//get context by calling "this" in activity or getActivity() in fragment//call this if API level is lower than 17  String appDataPath = "/data/data/" + context.getPackageName() + "/databases/"String appDataPath = context.getApplicationInfo().dataDir;File dbFolder = new File(appDataPath + "/databases");//Make sure the /databases folder existsdbFolder.mkdir();//This can be called multiple times.File dbFilePath = new File(appDataPath + "/databases/testDB.db");try {    InputStream inputStream = context.getAssets().open("testDB.db");    OutputStream outputStream = new FileOutputStream(dbFilePath);    byte[] buffer = new byte[1024];    int length;    while ((length = inputStream.read(buffer))>0)    {        outputStream.write(buffer, 0, length);    }    outputStream.flush();    outputStream.close();    inputStream.close();} catch (IOException e){    //handle}

Then refresh the folder to verify the copy process

Step 4: Create database open helper

Create a subclass for SQLiteOpenHelper, with connect, close, path, etc. I named it DatabaseOpenHelper

import android.content.Context;import android.database.SQLException;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;public class DatabaseOpenHelper extends SQLiteOpenHelper {    public static final String DB_NAME = "testDB.db";    public static final String DB_SUB_PATH = "/databases/" + DB_NAME;    private static String APP_DATA_PATH = "";    private SQLiteDatabase dataBase;    private final Context context;    public DatabaseOpenHelper(Context context){        super(context, DB_NAME, null, 1);        APP_DATA_PATH = context.getApplicationInfo().dataDir;        this.context = context;    }    public boolean openDataBase() throws SQLException{        String mPath = APP_DATA_PATH + DB_SUB_PATH;        //Note that this method assumes that the db file is already copied in place        dataBase = SQLiteDatabase.openDatabase(mPath, null, SQLiteDatabase.OPEN_READWRITE);        return dataBase != null;    }    @Override    public synchronized void close(){        if(dataBase != null) {dataBase.close();}        super.close();    }    @Override    public void onCreate(SQLiteDatabase db) {    }    @Override    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {    }}

Step 5: Create top level class to interact with the database

This will be the class that read & write your database file. Also there is a sample query to print out the value in the database.

import android.content.Context;import android.database.Cursor;import android.database.SQLException;import android.database.sqlite.SQLiteDatabase;import android.util.Log;public class Database {    private final Context context;    private SQLiteDatabase database;    private DatabaseOpenHelper dbHelper;    public Database(Context context){        this.context = context;        dbHelper = new DatabaseOpenHelper(context);    }    public Database open() throws SQLException    {        dbHelper.openDataBase();        dbHelper.close();        database = dbHelper.getReadableDatabase();        return this;    }    public void close()    {        dbHelper.close();    }    public void test(){        try{            String query ="SELECT value FROM test1";            Cursor cursor = database.rawQuery(query, null);            if (cursor.moveToFirst()){                do{                    String value = cursor.getString(0);                    Log.d("db", value);                }while (cursor.moveToNext());            }            cursor.close();        } catch (SQLException e) {            //handle        }    }}

Step 6: Test running

Test the code by running the following lines of codes.

Database db = new Database(context);db.open();db.test();db.close();

Hit the run button and cheer!