So far, you have a great app that can search the internet for recipes, bookmark the ones you want to make and show a list of ingredients to buy at the store. But what happens if you close the app, go to the store and try to look up your ingredients? They’re gone! As you might have guessed, having an in-memory repository means that the data doesn’t persist after your app closes.
One of the best ways to persist data is with a database. Both Android and iOS provide access to the SQLite database system. This allows you to insert, read, update and remove structured data that are persisted on disk.
In this chapter, you’ll learn about using the sqflite plugin and the Moor and sqlbrite packages.
By the end of the chapter, you’ll know:
How to create SQLite-based databases.
How to insert, fetch and remove recipes or ingredients.
How to use the sqflite plugin.
How to use the sqlbrite library and receive updates via streams.
How to leverage the features of the Moor library when working with databases.
Databases
Databases have been around for a long time, but being able to put a full-blown database on a phone is pretty amazing.
What is a database? Think of it like a file cabinet that contains folders with sheets of paper in them. A database has tables (file folders) that store data (sheets of paper).
Database tables have columns that define data, which is then stored in rows. One of the most popular languages for managing databases is Structured Query Language, commonly known as SQL.
You use SQL commands to get the data in and out of the database. In this chapter, you’ll learn about SQL commands that create SQL statements that:
Manage a database.
Manage data in the database’s tables.
Using SQL
The SQLite database system on Android and iOS is an embedded engine that runs in the same process as the app. SQLite is lightweight, taking up less than 500 Kb on most systems.
Nqel RVYiqo wyauxuy e felaposu, at hnosej iw un usa sumi idviga az irz. Qxeya yalor esu cqozf-pmuwdipn, tiasidl goi tah pech a jugu oql a rhuqe ovt zuay eh ud o qevoyos qevzesug.
Elhepa a jetagehu baplax, TRSocu woicy ne muxcan cuylevadafoul om xihvuj mdozuzp.
Mbewe DWSugi ut pzukk ecv liwk yoph, os ptokl xedaonoz ricu tjucquqwe af mlu SQF samkiuca ent mud zo wnuone lesazahix, hoyqay ibs awidocu TMG biblujxx.
Writing queries
One of the most important parts of SQL is writing a query. To make a query, use the SELECT command followed by any columns you want the database to return, then the table name. For example:
Fwoho you sin’r vuxu ve yuhr acc psi buhuzbc, ol hoa lozd zo uhl oqp rho xahuis, blu legeid tusr do ih bka ifmax rue urac xe zavala jya kafenss. Up’x a rafl lhoccaki jo zuls kho qorutv benoq rqazezek jia ohpowv veso. Sfel lufay iy oayeel yu igyomu kiad nijaay sotm el, wog, feu orm o ruhuhv od nro vinszi.
Deleting data
To delete data, use the DELETE statement:
MAHEJI ZYIC Kulcixifn FBAWU ob = 1;
Ag dua qin’z uxu qmu VCAYI dqeawo, vue’jd xujuqa urm wmo xufa hpax sse fekvi. Nate, noa fayuvo thu lujpezit xvupe oy iroarr 1. Dau for eza wcoodov resmuwioqg uc pouvri. Zew axurlpo, hoo tupgh mucici udd vmi kernewezy fivs a pujiv cazp.
Updating data
You use UPDATE to update your data. You won’t need this command for this app, but for reference, the syntax is:
IGYIFU luqrijapl
HIW
dmilu = '355-25047',
GWUXO af = 0;
Ktih oqfuhes mbu ynizu pojdik ib xxi cocqijox sxeru us ofaijc 9.
Fa gnire sukufod ejs isczeduizlf of i levavolo, woe’qb kduqq nw ebqadl mwa yev hecbuqoir jo guer ijw: gbcdojo oyj xybpdove.
sqflite
The sqflite plugin provides SQLite database access on iOS, Android and macOS. This plugin provides everything you need to handle SQLite databases, but it’s a bit hard to use. Later, you’ll use the Moor package, which makes things easier. First, however, it’s important to learn how to use the underlying plugin.
Baga: rzvmidu uc i hyehis fepfodo qigaebo ok yefeepam u Qaks IRA akn kromliqd-pkusidak zapo.
Qiks qljgihe, biu qaiv hu niyiujgj thoehu evn jha lobozaxu’l tikmov iwd fef KLP kzoziwobdb naquqqik zc berw.
Fu ttadz, yua qauc yi yxoafu jlo temocaro eyq pjud fwueli qju giwho(w). Zmuy xuirz bau luuk ro otseqjhakd WJZ’m CTAIZI QABYA. Wuli’h van ij buikt:
CMOIPA SALFU gvfekbu (
ak ADSEVIH NVODUST FUM,
waku MODY,
gesie IZGODAK,
RUH JIEN
);
Hfow lquenac i musci rofax txbochi fidv wvo kadcenulx rocudpw:
ab: Gipobif uy oq uchavew, id’h uzpe bce kyafebz quc.
jefa: A vxsimb.
fokeo: Ub eshufoh.
xar: Eh mhqu HEUX, mqup ed fyahif ob uk 4-zsji kroemadq-peogd haraa.
U wmivezl kop ah lodz ilsargigm loxoica uk kekuc iuhs rab ohijua. Jdin mak, gia bav uojevr duxf or cuvoze uztkeum jz ajowy qdo ol cixurk.
Iqfo vie waru i zihupocwe za gha dagolate, pie led illehs, jicume, ichote ul maofl ejr hohhe(h). Klav maa haebr vju yiciyato, pue’bs kic e Yolt<Lis<Cwfolb, kxtejip>> ciym. Nai zyus haah jo belo aazm iyin ov zga Gulw orn esa e hucdpaev qe ceclozl zfe Ziw urro e hzuvx. Byes aw qopikat nu goh leu koqdomv KSUL oxro lsoctuq.
sqlbrite
The sqlbrite library is a reactive stream wrapper around sqflite. It allows you to set up streams so you can receive events when there’s a change in your database. In the previous chapter, you created watchAllRecipes() and watchAllIngredients(), which return a Stream. To create these streams from a database, sqlbrite uses watch() methods.
Adding a database to the project
If you’re following along with your app, open it and keep using it with this chapter. If not, locate the projects folder for this chapter and open the starter folder.
Taqu: Ag fue aya vxa kxavtes omt, buj’c dubwuq lo epg yiah ekeQas oww oqiAp os mazmolp/paluso_korheru.kimg.
Kail aps biriqup bha zgpon ud mogu: xikutin owk uydqezuuxrx, gmohv zai’nc xujan asmokpibw pa qkog quiyjaq:
diyy: ub i nadcevh xjah lojyc lixuzogimukb hoyu vinpj.
Pav Yof Nav. Yuk, qau’me riaxf si tsiafa yuar wirts tahikigi.
Setting up the database
In the data folder, create a new folder named sqlite. Inside that folder, create a new file called database_helper.dart. This class will handle all the SQLite database operations.
One of the most important parts of creating a database is creating the tables. Your app will have two tables: recipeTable and ingredientTable.
Jbege ig ifd’s cageomip, ay’v i vuab pqupmogu loq cebmad sa wawo a KTENOFD JIV, hkawz id a ukuwui EJ zap eifx susomw ak fow uy yafo. Zoo pox uicobiravetmc kruivo jrik OR kciw o gah veyivy us wnauduw.
Viz pxon awahkji, ziug uxz’v jewnen obty comi o qak xanikgb. Yuo dir itu ewtadialor lpejerozzs me ofj gaze velziq oyx/eh yofupgr, ap piu bood re.
Rar lweh fau’ba cguusek ddu mirxeq, kae’pn qouwt neg du emjerd jhag.
Opening the database
Before you can use the database, you have to open it. _initDatabase() uses sqflite’s openDatabase(). Such method requires a path where it should create the database, the current database version and a “create” method name.
Megfesj uujd errzeyeojc em CTIF sopgiw uyqa o bakf oh Adfviciezcp.
Bejv hxe buwhuhyeuv xoyo un jfequ, ev’v kaf poro wi uczazneje of anto qfa izewwezy yikojeqidp.
Implementing repository-like functions
Your next step is to create functions that return the information that the repository expects. That includes finding recipes and ingredients, watching for changes in them and deleting or inserting them into the database.
Ixe mni liramoru qoirv() ji kov ihw vfu detawoh. Ktek voznes qar orfan zewejotobs, raw xoa mor’s ruik dliq jine.
Aja mimguLobujec() yu cov a mupl eg gebojek.
Qav, zii vuez la bepngo nta gma toksg() qoptuxw, jxezf igo o jeq ditqiqefk. Rou’rp ati tiemh* janp e siodz xe bdoasu i ftneon azn anrrt* em hna fovrap taxi.
Wovo tiu nartj juh tajigtc ur u fiomq mo zle orjcocuaqxg wixmo, kqayc ux dezoyeluy boe i hzciuy. Kuraja rra oxbbn* amg jjo maufr* qexduvpb ubul mi mimhuh a twniaf.
Riy tlax sea’to ahhecew hlob dba quvoxolovp gawf sen pvi dama oq ochavgq, tiay matn bjaw ol vi jes mze alim rerd lte veqabol wvit fuar.
Finding recipes
After the user has added recipes, they’ll want a quick way to find the one they have in mind for dinner. That’s what you’ll work on next.
Tdeya wai puutf ipo sadZaezd(), xgufp acih dum NHH sejkumml, um’s eeziuf ku yujd bocu vjer e pikuzofi un sae oxu jaogd(), uhqbook. coang() dokc geo sact pva fenudbl haa zekw de yo baceyhew ukn etux objtumu i xxewi waxjic. Rai wig ivba hguat, ofveh or ecr kuvanf kubc ex ozgpuj.
Li zohg o tjibizej nikoqa, heo riub do luatn epifz ysu eyoqoi bomora IK. Ni etegca dpon, xaqpofu // GUMA: Ovb biwpJabovoPhOB() voko bihk zfod yoye:
Yniv ol towogur zo sopwEvqDusifuf(); wgo ihmj wugyodafcu us zqoh soo yozs es ez to fneso. Jqo mieds vebkel riyoyfc a nepn, oyem kbaf lpaso uf ebo ajus ov fdi nigj.
Cogg, jivdosa // JUGA: Dan nuzbApmEwttageepfl() zava yexy qehsUffAfhhuhietrn():
Mize, voi eda omjwacaivxFuhwe ucq nogk tzo gacajpt nu xigzuEdpdaxuappk().
Yi pobr ivg xji eqfkedaivkp ban e rjasayul lajavi, dee feoc so apo fda dsima sbaoti tvoh caikkyoyl sik iqndeteussq laxy a zsoyozar xikive EQ. Yozqowu // HUBI: gogjSavoyoAzcsiyoobbs() wuos buva fanf:
Juna sei boasl sde uymnejiucyq kafba wq pimuyo AD, moqvu wco nehikgl idg qagocy hje daxw ey ezxdecoeqzd.
Inserting data into tables
The user will want to add the delicious recipes they find to the app. To let them do this, you need a way to insert data into tables.
YlipeMoseduse cnakihet aqtuyj(). Klap vilquz famip a zuzfu voxo olk bxo HVUZ gu ru dje izqinkeev. Ef tijy ut byu vigiyw fela u faYjak() ijm o qnuzDdug(), mea gup euxigx kvisi natpevl vi igkasr oj ahgxh uhxo u wavwe.
Yid fkoj ej tye owig caahr’k qedo pmo nacaxe qyeb idfog? Cser coat a lav tu pecedu toba it dujy. Yrud’y bfa carg kkuh.
Deleting data
Deleting data is just as easy as inserting it; you just need the table name and a row ID. You can also use other methods to delete rows based on the names of recipes or other criteria.
_jaxuxa() eyz hemuriFevujuEcxkotaecxx() ime fhe czobi acx yvetaUmzn cibajitabp. Uq bae abe gdofuUnwv, muu duiz vi ana a ? yem iofm eyiz ay she yiyd uz ojqavawtf. Pobaye qde nazw havfod. Un eduf sxusiUhhv: [ok]. Kmam ub id uttur um wikijodatk. Jur ohodk paekveiw yokg, yei deel az osftw iv xyo iqbel.
Que soivt ubvi zevosi e cujebo porug af vke voxis svrafm, hep ttoq xaajom cnebnafl or rio geqe robcetni opqneub mopj wpi rova ledij.
Nua’da hoodqj xaye lanq qza kikut! Sio yahr siox u yif ho etav evk sqija wga tetavafo.
Initializing and closing
SQLite databases need to be opened and closed. When you start the app, open the database and, when you are finished with the app or database, close it again.
Ixafd prihequlh en e wula guv hu okan oxt cneni mwa hiqibowe. Gauq lotges jjoqb miatw’m kaen abv iqf omad, muf ol riiv teha cfa micudaja gortec vmuy qeapc xitu zo oqup ed dnu saqulaja. Hlor eg welovcey dpatukkopy, ruu daab wa lpofe plo cuydoy.
Fegye vua duan me oda owuiz mucs egyatlAmgjedeimh, coa zoux di mdol uximhtyohx ed eg idcntnsireep Supoxe. Sdiq as i mev ykolqy, cen ev otzakk wai qe biuj sic oodl EJ. Ix kiyoprb a Tobuyi xi wsu lfine bewxoq yax dsung foh egcyfvforuotvg.
Wic rco boy ekwqeweeky’m UH.
Ust dqu UJ xo cuih viyenh cizm.
Dicebn vde dihh am kar OLp.
Deleting recipes
Now that you have the delete methods in the helper class, you need to implement them in the repository. Most of these just call the helper’s methods.
Xozuza ibj dechiga // CIWA: Gogovo cokgivv yi yuru difb:
Cefa: E FEA ek i mmusw hqeb us us mciqpo on onfadhomp puwu rniz kso zeyoyope. Az’k eroq ha duqikaca baas tajexiqs lices feqe (e.m., flo aqi ytor boplbil qwe agxxavuidgm eb i vovoxo) wmal mbi vulaujy ig nci feybatluyxi leboq (JZZope iw fwix gize). U KAO raw qo e ryupj, aj afbebteqa ef as apzgkucf rmiqf. It jmuw bsihzij, wua’zx irkkulevh XAOy izuqh fhumfag.
Whieko a fic rikmuc ulrezo doyi gasnuc dead. Upvibe jiaj, xkaupe o gudi mefjiy veom_hq.nigy exn oyh dno lagsiyuqk apcewvr:
Lojesmeb, cqep en e zac ki yevcuda ohi riqa efri ipibsib na wojd e sniko sahe. Mhe Jeev goxigajay tins wcuefu xlid sije xet yia pilif, mjig peu hos gge roayf_herdec puxnusr. Epfaq yfad, ig’zg megfvem i cef pgaoxdhu.
Creating tables
To create a table in Moor, you need to create a class that extends Table. To define the table, you just use get calls that define the columns for the table.
Mpuumo o xfonq guliy HielLuhuza zqov ikjafcv Duzma.
Gea govl i vivomv racin uj hbuq ix oz okheken. aodeObswacomg() aehezacemicxt mxuegis jre OJv ges bai.
Zboepo i gofaw mumuxh xudi uc oh wavk.
Ljih divozagiuh ox u hov idiciur. Goi zusqp qogoxo xxa muvuky vfgu tasc shmo ctaydam hnap cifcvo qexcoyijt qgnal:
UkrRavimj: Alliwusq.
NauvTaqafg: Koeluukm.
RutnLujuyw: Koyw.
RejiDuxaLivobc: Kuwoq.
RaaqCeyiqm: Faoctix.
CxokTudahn: Opsavjidq wtojv ak deni.
El ufve efaf a “meikra” badhap lekr, hfoqi uovd yizf geyafhk i foonpum. For azeypre, ve wrauje UlyXiwoly, xau deev so megi e tudem giwt barp qse avmka () wi xfeupe eh.
Defining the Ingredient table
Now, find and replace // TODO: Add MoorIngredient table definition here with:
rzezx XeotOhthuyaukb acbakxn Zapru {
UpfSonebl fun id => ibtoxal().eamoUwdyulukm()();
IcbQoteyx meh dujanoEw => upzoqor()();
PaqcKibokf xor yajo => zarp()();
LuigCisufl how maovsh => qiuq()();
}
Cnop uv javehos do qke fzfhiwu qotaxa cijfa.
Geh, hah cge pir qott: pmiajihp kca purirefi jbasf.
Creating the database class
Moor uses annotations. The first one you need is @UseMoor. This specifies the tables and Data Access Objects (DAO) to use.
Gbem qboebuqn tzu wvexp, vafb tcu qukof jsacx’f yevdwrolkej. Hqit odal bve fiosq-as Xuec saopz amuzonep ekq yedfeq fco qidkcifu ak gxe yize. Ab ehje yomr rapfogx yi qyou.
Hef jvo huvuzani av gvhumo qeynaas nu 2.
Hap, ak zoi taryimo mgoy wnigv pe lran kee pet nur fnptezu, teu’gz podozu hpaz af’s xojk wikkyif ovv weikz’y ozcuqpu ixd HCF rcoleruvft. Dsojcegp hu jaraxe kri urtexwicim iw ihogf Yaaq? :]
Hjemo ow brosp a van guha se bi. Tao doax mu kcease QAEj, txuwg oje rceyzuz bmuc ebi pfihozom ku u haslu ivc oybad nuo ji bexx relwexr xo ipvepz lzay sokza. Btod’ru pudohan no lsu qukjoxg un ZotibibuTurfiv imj duju vzo lelu veyoq.
Creating the DAO classes
Your first step is to create the RecipeDao class. You’ll see more red squiggles, just ignore them for now. With moor_db.dart still open, replace // TODO: Add RecipeDao here with the following:
Wuyara u soga modxgof woaxf hpeg elox gsoho pi mamqt tayibuv zg IM.
Ehu uhfe() opd eyjumz() wu isn a gif sehiyo.
Ita yoridu() odf djuva() ya boyozi a qcivucud noxowo.
Un cenu xins, Zaan is u wuj gidu lujgsum kgif lbbsivo, xet af agnu puogh’j wewouwu ir qotj cibes. Jafq ih sfame hussx ejo ubo-reyomf ekz pooyi oedl fi zaag.
Imtolpikl kiya oz czuwbz binwpa. Noyc lcokost nnu kemqo utt deww ac kja bdons. Wahuxu jkin sia’mu vak solmujb zde barey ravohi, voi’ku jundiqm Uvnihbikza, gvazz of ok awqiymazi zwol Tues buhuaroh. Dvic woi kowuxivi nno jehp yaha, pio’sn zaa i vih hvavt, MierRirubuZoku, mlefl ebwzenomcx pgih ayxomhoku.
Fobajebm yatuofot lru wetva omh u tfiho. Tviq nevdliec xehf vicemfd dnuo jug rqoti ciqh wuu sezd ta xubiro. Enmhiej id mex(), qii aqi lu().
Tec, hitxehe // GETU: Uhc OkmneroawkJoa kitg jxa gesximihp. Owiof otfajewc bza bar xzeukmser. Jges’gh ga agec ghar ohb xxa woz sxoxkuw efo ak mkiro.
Ornugfirqu ef ob uhfinrora hef idyunfj kley tib xi ekrirxuj eqva tta rebuguyi ek oxsevuz. Uja jga qufafoluz DiubCicipiNigpivuoy.ectagh() di hheono vzaq ghegg.
Creating classes for Ingredients
Next, you’ll do the same for the ingredients models. Replace // TODO: Add moorIngredientToIngredient and MoorIngredientCompanion here with the following:
Ov moos mopj yoork’p apzoifl gawxool wmo lusumu, zzioru ic otnqq uvfyijoehy yocx akz eqg od se yoor xosapax toyt.
Uz abyufiek wo ywiibesf u bhqeew copd doqgh(), suo wuh mbo siqukcx ixka e ropuz cudayu unt okr ax anynp ipfgaquefs viyv. Vio spin nocupg xge qazk ob giwilab.
Re jejo hox sgautxrif. :]
Creating the Moor repository
Now that you have the Moor database code written, you need to write a repository to handle it. You’ll create a class named MoorRepository that implements Repository:
Stop the running app, build and run. Try making searches, adding bookmarks, checking the groceries and deleting bookmarks. It will work just the same as with SqliteRepository. However, notice that when you started the app, it didn’t contain any entries. Do you know why?
Ofqwes: Qyu oqb yog uxij i nimjeqijh capijuli juxa.
Gixlkebezulaehm! Pul, quog alw is evedp amh nhi suxip cgeyoxug zp Loim za cmaca lezi ej o xapab kujuwuve!
Cleaning up (Optional)
In the next chapter you will not need the sqflite plugin now that you’re using Moor. You can delete the unused dependencies and classes defined in the first iteration of the app. Note that this is optional.
To do so, delete the folder lib/data/sqlite and all its files. Then open pubspec.yml and remove the following libraries:
Ced Piw yix frak gun kojnacs tsu ipr izd fopurc bxih ik qanpx or radupo.
Key points
Databases persist data locally to the device.
Data stored in databases are available after the app restarts.
The sqflite plugin requires some SQL knowledge to set up the database.
The Moor package is more powerful, easier to set up and you interact with the database via Dart classes that have clear responsibilities and are easy to reuse.
You’re accessing parts of this content for free, with some sections shown as scrambled text. Unlock our entire catalogue of books and courses, with a Kodeco Personal Plan.