Most all of the Android examples and tutorials out there assume you want to create and populate your database at runtime and not to use and access an independent, preloaded database with your Android application.

This tutorial shows, you can take your own SQLite database file from the “assets” folder and copies into the system database path of your application so the SQLiteDatabase API can open and access it normally.

1. Preparing the SQLite database file.

Assuming you already have your SQLite database created, we need to do some modifications to it.

If you don’t have a SQLite Manager We recommend you to download the opensource SQLite Database Browser available for Win/Linux/Mac.

Open your database and add a new table called “android_metadata”, you can execute the following SQL statement to do it:

CREATE TABLE "android_metadata" ("locale" TEXT DEFAULT 'en_US')

Now insert a single row with the text ‘en_US’ in the “android_metadata” table:

INSERT INTO "android_metadata" VALUES ('en_US')

Then, it is necessary to rename the primary id field of your tables to “_id” so Android will know where to bind the id field of your tables.

You can easily do this with SQLite Database Browser by pressing the edit table button Edit Table, then selecting the table you want to edit and finally selecting the field you want to rename.

After renaming the id field of all your data tables to “_id” and adding the “android_metadata” table, your database it’s ready to be used in your Android application.

Note: in this image we see the tables “EMP_TABLE” with the id field renamed to “_id” and the just added table “android_metadata“.

2. Copying, opening and accessing your database in your Android application.

Create project named “com.mobisys.android.External_to_application” with the activity “CopyDbActivity".
"main.xml" will contain:

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"

<Button android:id="@+id/button01"
     android:text="Copy Database">


Now just put your database file in the "assets" folder of your project and create a DatabaseHelper class by extending the SQLiteOpenHelper class.

DatabaseHelper class contains:

package com.mobisys.android.External_to_application;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper{
    //The Android's default system path of your application database.
    String DB_PATH =null;
    private static String DB_NAME = "extenalDB";
    private SQLiteDatabase myDataBase; 
    private final Context myContext;
     * 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, 1);
        this.myContext = context;
     * Creates a empty database on the system and rewrites it with your own database.
     * */
    public void createDataBase() throws IOException{
    	boolean dbExist = checkDataBase();
    		//do nothing - database already exist
    		//By calling this method and empty database will be created into the default system path
               //of your application so we are gonna be able to overwrite that database with our database.
        	try {
 <div style="display:none"></div> 
    		} catch (IOException e) {
        		throw new Error("Error copying database");
     * Check if the database already exist to avoid re-copying the file each time you open the application.
     * @return true if it exists, false if it doesn't
    private boolean checkDataBase(){
    	SQLiteDatabase checkDB = null;
    		String myPath = DB_PATH + DB_NAME;
    		checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
    	}catch(SQLiteException e){
    		//database does't exist yet.
    	if(checkDB != null){
    	return checkDB != null ? true : false;
     * 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{
    	//Open your local db as the input stream
    	InputStream myInput = myContext.getAssets().open(DB_NAME);
    	// Path to the just created empty db
    	String outFileName = DB_PATH + DB_NAME;
    	//Open the empty db as the output stream
    	OutputStream myOutput = new FileOutputStream(outFileName);
    	//transfer bytes from the inputfile to the outputfile
    	byte[] buffer = new byte[];
    	int length;
    	while ((length = myInput.read(buffer))>0){
    		myOutput.write(buffer, 0, length);
    	//Close the streams
    public void openDataBase() throws SQLException{
    	//Open the database
        String myPath = DB_PATH + DB_NAME;
    	myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
	public synchronized void close() {
    	    if(myDataBase != null)
	public void onCreate(SQLiteDatabase db) {
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 //return cursor
	public Cursor query(String table,String[] columns, String selection,String[] selectionArgs,String groupBy,String having,String orderBy){
		return myDataBase.query("EMP_TABLE", null, null, null, null, null, null);

Now you can create a new instance of this DataBaseHelper class in CopyDbActivity class and call the createDataBase() and openDataBase() methods. Remember to change the "YOUR_PACKAGE" to your application package namespace (i.e: com.mobisys.android.External_to_application) in the DB_PATH string or you can get package name using method context.getPackageName().

CopyDbActivity class contains:

package com.mobisys.android.External_to_application;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import android.app.Activity;
import android.content.Context;
import android.content.Intent;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.Toast;

public class CopyDbActivity extends Activity {
    /** Called when the activity is first created. */
	Cursor c=null;
    public void onCreate(Bundle savedInstanceState) {
        ((Button)findViewById(R.id.button01)).setOnClickListener(new View.OnClickListener(){
			public void onClick(View v) {
		         DatabaseHelper myDbHelper = new DatabaseHelper(CopyDbActivity.this);
		         try {
		  	} catch (IOException ioe) {
		  		throw new Error("Unable to create database");
		  	try {
		  	}catch(SQLException sqle){
		  		throw sqle;
		  	Toast.makeText(CopyDbActivity.this, "Success", Toast.LENGTH_SHORT).show();
		  	c=myDbHelper.query("EMP_TABLE", null, null, null, null,null, null);
                do {
                            "_id: " + c.getString(0) + "n" +
                            "E_NAME: " + c.getString(1) + "n" +
                            "E_AGE: " + c.getString(2) + "n" +
                            "E_DEPT:  " + c.getString(3),
                } while (c.moveToNext());

Run the Application.

Download the Source Code

Click here to download sourcecode.

AUTHOR: Vikas Hiran
  • maryam

    hi,it was so useful for me,Thank you so much

    July 5, 2012
  • swarup gosavi

    If i use multiple table in assets folder in android

    December 14, 2012
    • Well, didn’t got your question. We can have as many tables as we want into sqlite database which is in assets folder. If you wanted to ask if we can have multiple database in assets folder, then also, it is possible. But you will need to copy each database first time when you launch the application.

      December 14, 2012
  • goutam

    The above code work very good with fetching that data with table but can you show how to insert data with the same database and table.

    December 29, 2012
  • jayanth

    hi,thanks for the tutorial…by the way can you tell me how to store an image in the above sqlite database browser…let me elaborate a bit more….say for instance i add an extra field “img” of type “BLOB”…..and say i need to store an image in this column….and my image is present in my hard drive….say Desktop….how do i store it…???…can u pls explain it…nd also how the query should be wen i need to insert a blob object….thanks in advance….

    January 13, 2013
  • dinesh

    My android application crashes when I click on Copy Database if i use my own database. However if I use database created by you, it works perfectly fine! Can you tell where’s the problem ?

    April 14, 2013
  • shank

    this ain’t working bro ! cannot open .
    error says cannot open file .
    Tag : SQLLiteDataBase – Failed to open database .
    Any suggestions ?

    May 7, 2013
  • pravind

    if i want to show only the person with id=3 what changes i have to made here .
    please reply as soon as possible.

    June 19, 2013
  • pravind

    @vikashiran its not working correctly it shows only the value with id 2 not three

    June 20, 2013
  • Just an addition:

    in createDataBase() there is the following check;


    This checks if there is already a database with the provided name and if not creates an empty database such that it can be overwritten with the one in the assets folder. On newer devices this works flawlessly but there are some devices on which this doesn’t work. Mainly older devices. I do not know exactly why, but it seems like the getReadableDatabase() function not only gets the database but also opens it. If you then copy the database from the assets folder over it, it still has the pointer to an empty database and you will get table does not exist errors.

    So in order to make it work on all devices you should modify it to the following lines:

    SQLiteDatabase db = this.getReadableDatabase();
    if (db.isOpen()){

    Even if the database is opened in the check, it is closed thereafter and it will not give you any more trouble.

    July 20, 2013
    • waleed

      I tried your solution but didn`t work on android 4.1.2 ,it crash with “no such table”… Any idea please, thanks

      December 16, 2013
  • Kira

    Hi, thank you so much for your tutorial. Can you help me or give me advice. I have file wich contain all sqlite scripts(create tables, insert in tables a lot of information). How I might work with it? 1) I create a database 2) read information from file . How I put all sripts programmaticaly? Results have to contain all tables with information from file. Thank you

    October 17, 2013
  • amit sharma

    this code is very use full for me…………….

    thanks a looooooot

    October 18, 2013
  • Mubashir Meddekar

    Thanks for the information. But it didn’t worked for me, “it crashes by saying there is no such table”.. Please help me ASAP.

    November 25, 2013
  • waleed

    This doesn`t work on Android v4.1.2 , it works on 4.3 and up.

    I tried many things but the app crash with “no such table”

    tried adding

    SQLiteDatabase db = this.getReadableDatabase();
    if (db.isOpen()){

    but didn`t work.

    Any help?

    December 16, 2013
  • Alberto R Gaytan

    Terrific!! It works great!!!

    February 5, 2014
  • isaac

    thanks this is what I needed

    June 15, 2014
  • madhuri

    The above code work very good with fetching that data with table but can you show how to insert data with the same database and table.
    is it possible to insert rows into table in already existing db.

    August 24, 2014
  • Martin

    Code works great
    Thanks a lot
    After so many hours i found the problem…

    August 26, 2015
  • Ajit Gaikwad

    This code working fine
    I want to show all records in listview using Simple Cursor adapter

    September 5, 2015
  • Duy

    How to make ExternalDB file. I cant define the its type. Plz help!

    November 6, 2015
  • Chamalides

    Thank you..you help a lot

    November 13, 2015
  • ronit

    Hi, Thanks! This work perfectly, but now issue is I have published my app on google playstore and and then I changed my sqlite db schema. I have added new tables from sqlite manger and copied new db into asset folder. I have also increased my sqlite db version to 2. Then I put new update on playstore, but then new datatables doesn’t get copied and my app crashes. Do you have any solution for this?

    April 8, 2016

Leave a Comment

Your email address will not be published.