How to create a database in MATLAB and what the different options are

Hi everyone. I have a project in mind for a database and wish to use MATLAB for it.

I am struggling to figure out what the different options are in terms of SQL etc. And how to create, manage and query a database in MATLAB.

I have looked through the other MATLAB answers and they didn't give much detail. They suggest the Database toolbox and provide a link to the documentation, and they also suggest the Database Explorer app, but don't give any more detail than that.

I have looked at the Database toolbox link and it doesn't contain a how-to guide. I wouldn't be able to create a database from scratch using that.

I have looked at the Database explorer and again, it's not straightforwards or intuitive, and I found no getting started guide.

I also had a look through the Mathworks training options and didn't find anything targetting working with databases.

I would first like to get my head around the options, tools, apps etc before I decide which option is best for my project. So a bit of help on the syntax or process for creating and then querying a very simple database would be great, and an overview of the different database options.

I also dont understand how to connect the Database Explorer app to a database

Answer 1

[Strider]

I understand the learning curve. I work with a SQL Server and SQLite files regularly now and the best thing that got me over the hump was perusing resources outside of MATLAB for SQL sytnax. Try W3 schools as an approachable first.

Also reccomended is to make yourself some easy, repeatable unit tests to manage database connections (open / close) and deleting old files so you do not step over previous sqlite files / connections.

Along the way here is a simple way to create and work with a database in MATLAB persuming you have the Database Toolbox.

clearvars
dbfile = "tst.db";
if isfile(dbfile) delete(dbfile); end
conn = sqlite(dbfile,"create"); % makes a sqlite file for you

% get an interesting table
LastName = {'Sanchez';'Johnson';'Li';'Diaz';'Brown'};
Age = [38;43;38;40;49];
Smoker = logical([1;0;1;0;1]);
Height = [71;69;64;67;64];
Weight = [176;163;131;133;119];
BloodPressure1 = [124; 109 ; 125 ; 117 ; 122 ];
BloodPressure2 = [93; 77; 83; 75; 80];
T = table(LastName,Age,Smoker,Height,Weight,BloodPressure1, BloodPressure2)

T = 5x7 table
     LastName      Age    Smoker    Height    Weight    BloodPressure1    BloodPressure2
    ___________    ___    ______    ______    ______    ______________    ______________

    {'Sanchez'}    38     true        71       176           124                93      
    {'Johnson'}    43     false       69       163           109                77      
    {'Li'     }    38     true        64       131           125                83      
    {'Diaz'   }    40     false       67       133           117                75      
    {'Brown'  }    49     true        64       119           122                80      

% write it to the database
tabname = "Data";
sqlwrite(conn,tabname,T);

% read it back
Tread = sqlread(conn,tabname)

Tread = 5x7 table
    LastName     Age    Smoker    Height    Weight    BloodPressure1    BloodPressure2
    _________    ___    ______    ______    ______    ______________    ______________

    "Sanchez"    38       1         71       176           124                93      
    "Johnson"    43       0         69       163           109                77      
    "Li"         38       1         64       131           125                83      
    "Diaz"       40       0         67       133           117                75      
    "Brown"      49       1         64       119           122                80      

% read with a row filter
rf = rowfilter('LastName');
rf2 = rf.LastName == "Sanchez";
TSanchez = sqlread(conn,tabname,"RowFilter",rf2)

TSanchez = 1x7 table
    LastName     Age    Smoker    Height    Weight    BloodPressure1    BloodPressure2
    _________    ___    ______    ______    ______    ______________    ______________

    "Sanchez"    38       1         71       176           124                93      

% a more advanced gem when you are ready
sq = sprintf("SELECT * FROM %s",tabname);
sqlopts = databaseImportOptions(conn,sq);

% preview the data only (first 8 rows)
preview = sqlopts.preview

preview = 5x7 table
    LastName     Age    Smoker    Height    Weight    BloodPressure1    BloodPressure2
    _________    ___    ______    ______    ______    ______________    ______________

    "Sanchez"    38       1         71       176           124                93      
    "Johnson"    43       0         69       163           109                77      
    "Li"         38       1         64       131           125                83      
    "Diaz"       40       0         67       133           117                75      
    "Brown"      49       1         64       119           122                80