How to create SQLite databases?

SQLite databases are a type of lightweight database that allows you to store, manage, and retrieve information using a command language with a syntax very similar to MySQL. The main advantage of SQLite is that few resources are required to use it. The database consists of a file with a .db extension, in which all the information that we wish to store is stored.

To create a database in SQLite, I recommend using a program called DB Browser for SQLite, which is available for free on Windows. This software can be downloaded at the following link: https://sqlitebrowser.org/dl/. Once downloaded and installed, we open it and we will have a simple and easy-to-use interface.

To create a database, just click on “New Database”. A window will open in which we are asked to select a location and a name. This will be the location and name of the database, which is ultimately a single file. In my case I called my database “test.db”.

After that a window appears in which we must specify the name of a table. Here I am assuming that I am going to create a table to store temperature data.

SQL-based databases have tables, which are internal structures for storing data. It is like having an Excel file, where there can be multiple worksheets in one file. The file is equivalent to the database and the spreadsheets are equivalent to the tables.

Each table has a name and several data fields. Data fields are like columns in a spreadsheet. These tables are where the records are stored, which is the equivalent of the rows on a spreadsheet.

In my case I have created a table called “Temperatura” (see image). This table will have the following data fields: id, temp, and time. In id the identification of each record is stored, something that is customary in databases. This field will have the following characteristics: PK (primary key), AI (auto increment), U (unique).

Below I proceed to describe these characteristics:

• Primary key (PK): The primary key is something that is required in every table. Usually the id field is used as a key.
• Auto increment (AI): This will cause the field value to automatically increase on each record. In other words, each time a piece of data is inserted, the system will assign an automatic value to any column to which this characteristic is assigned.
• Unique (U): this characteristic will limit the values that can be stored in a column, since they cannot be repeated in the whole table. That is, a single column will not be able to store records with repeating values.
• Not Null (NN): selecting Not Null forces a value to be inserted in each record. If this option is not used, one could insert empty (null) records.

I have set the 3 columns that I will handle to be Not Null, as I don’t want any of the 3 values that are needed in each record to be empty.

Another characteristic that must be established in the columns is the data type. These can be INTEGER, TEXT, BLOB, REAL, NUMERIC. I proceed to explain this type of data:

• INTEGER: whole numbers. They do not accept numbers with a decimal point.
• TEXT: alphanumeric data, including letters, numbers and special characters.
• BLOB: unspecified data type.
• REAL: numeric data, including numbers with a decimal point.
• NUMERIC: can be used to store Boolean data and dates (date type data).

In my case (see image) I set id as integer, temp as real (temperature stored as decimal), time as text (date stored as text). Maybe it could save time and time (time) as numeric, but I prefer to do it as text.

The program also generates a “query” which is basically code that allows you to build the database.

If this code is executed in a console of an operating system that has SQLite installed, you will be able to create the same table that I have created with DB Browser. The difference is that here I do it visually and when using the query it is done by code.

Once the table is created we can insert data to do a test. Hundreds of thousands of data can be stored in these tables in an orderly manner, without taking up much space.

When inserting the data, click on the “Write Changes” button so that they are stored in the file.

There can be many tables in the same database. The good thing about these databases is that they allow you to store information through queries, that is, programmatically. But that’s material from another post. For now, I hope you liked this contribution. For any questions, please let me know through the comment box.