Accessing a SQLite Database on a Raspberry Pi

On this page we provide instructions for setting up SQLite on a Raspberry Pi, preparing a test database and creating a Lazarus application for displaying and editing selected data in a DBGrid. Buttons on the DBNavigator enable the user to edit existing data, add new records, delete records and to refresh (view the latest contents of the database on disk). The action of closing the form saves the changes, so refresh before saving to preserve the unchanged contents of the database.

At the LXTerminal use the command sudo apt-get install sqlite3 to install SQLite for use from the console. Create a new database with the command sqlite3 Contributions.sqlite. Create and populate the table Programmer with the statements on the preceding page. (You can copy a block of statements into the terminal). Use the command .exit to return from the SQLite prompt.

In order to use SQLite in an application you need to issue the command sudo apt-get install libsqlite3-dev. You can then start a new Lazarus form-based application and drop onto it a DBGrid, DBNavigator, and the four other components shown in this design view of the form.

Design View of Form

Design View of Form

You will see the data when you have linked up the components with the help of the text view of the form below. The only Pascal code to copy from below is the block of code to save the changed data to the database. Double click in the edit box for the OnClose event property of the form to create the empty procedure in the editor then copy the code. Close Lazarus and run the executable directly to prevent an error message about the database being locked.

This is very quick to set up when you know what you are doing. We have used the default settings for the grid columns. See our DBGrid example if you need help with customising columns.

Pascal Code of Unit

unit Unit1; 

{$mode objfpc}

interface

uses
  Classes, SysUtils, db, sqlite3conn, sqldb, FileUtil, Forms, Controls,
  Graphics, Dialogs, DbCtrls, DBGrids;

type
  TForm1 = class(TForm)
    Datasource1: TDatasource;
    DBGrid1: TDBGrid;
    DBNavigator1: TDBNavigator;
    SQLite3Connection1: TSQLite3Connection;
    SQLQuery1: TSQLQuery;
    SQLTransaction1: TSQLTransaction;
    procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
  end;
var
  Form1: TForm1; 

implementation

{$R *.lfm}

procedure TForm1.FormClose(Sender: TObject; var CloseAction: TCloseAction);
begin
  SQLQuery1.Edit;
  SQLQuery1.UpdateMode := UpWhereChanged;
  SQLQuery1.ApplyUpdates;
  SQLTransaction1.Commit;
  SQLQuery1.Close;
  SQLQuery1.Open;
end;

end.
    

Text View of Form

object Form1: TForm1
  Left = 493
  Height = 172
  Top = 38
  Width = 514
  Caption = 'Programmers'
  ClientHeight = 172
  ClientWidth = 514
  OnClose = FormClose
  LCLVersion = '0.9.30.4'
  object DBNavigator1: TDBNavigator
    Left = 136
    Height = 25
    Top = 120
    Width = 241
    BevelOuter = bvNone
    ChildSizing.EnlargeHorizontal = crsScaleChilds
    ChildSizing.EnlargeVertical = crsScaleChilds
    ChildSizing.ShrinkHorizontal = crsScaleChilds
    ChildSizing.ShrinkVertical = crsScaleChilds
    ChildSizing.Layout = cclLeftToRightThenTopToBottom
    ChildSizing.ControlsPerLine = 100
    ClientHeight = 25
    ClientWidth = 241
    DataSource = Datasource1
    TabOrder = 0
  end
  object DBGrid1: TDBGrid
    Left = 8
    Height = 100
    Top = 8
    Width = 500
    AutoFillColumns = True
    Color = clWindow
    Columns = <>
    DataSource = Datasource1
    TabOrder = 1
  end
  object Datasource1: TDatasource
    DataSet = SQLQuery1
    left = 28
    top = 107
  end
  object SQLite3Connection1: TSQLite3Connection
    Connected = True
    LoginPrompt = False
    DatabaseName = '/home/pi/Contributions.sqlite'
    KeepConnection = False
    Transaction = SQLTransaction1
    LogEvents = []
    Options = []
    left = 91
    top = 120
  end
  object SQLQuery1: TSQLQuery
    IndexName = 'DEFAULT_ORDER'
    FieldDefs = <    
      item
        Name = 'ID'
        DataType = ftInteger
        Precision = -1
        Size = 0
      end    
      item
        Name = 'Forename'
        DataType = ftString
        Precision = -1
        Size = 15
      end    
      item
        Name = 'Surname'
        DataType = ftString
        Precision = -1
        Size = 15
      end    
      item
        Name = 'YearGroup'
        DataType = ftString
        Precision = -1
        Size = 3
      end    
      item
        Name = 'Age'
        DataType = ftInteger
        Precision = -1
        Size = 0
      end>
    Active = True
    Database = SQLite3Connection1
    Transaction = SQLTransaction1
    SQL.Strings = (
      'SELECT * FROM Programmer ORDER BY ID'
    )
    Params = <>
    left = 381
    top = 120
  end
  object SQLTransaction1: TSQLTransaction
    Active = True
    Action = caNone
    Database = SQLite3Connection1
    left = 447
    top = 109
  end
end    
Programming - a skill for life!

How to display and edit selected contents of SQLite databases using Lazarus