Development Environments: SQL Programming Setup with SQLite

Requirements

The requirements to start programming activities with Structured Query Language (SQL) are similar to those described for JavaScript, Python, and Lua: a text editor and a Database Management System (DBMS), or an Integrated Development Environment (IDE).

To avoid repeating the discussions from JavaScript and Python:

1. If you already have a text editor, you can install a DBMS to start programming using the language;
2. If you would rather use an IDE, the simplest option is choosing DB Browser for SQLite, which includes a text editor, DBMS and debugger among other features;
3. If you do now know how to install program, what is PATH or how to configure it, you can refer the topic regarding how to install programs.

Part of the contents of this page will be identical to the Python's page. Therefore, if certain paragraphs seem familiar, it will not an impression, but déjà vu. As a matter of fact, this will also occur in new entries about development environments for other programming languages. After all, the setup procedures are similar.

SQL as the First Programming Language

Structured Query Language (SQL) rarely is the first choice of people who want to learn programming. The language is not suitable for writing programs or applications, though it can be a good introductory option. The reason is that virtually every program has data; complex program can have large amounts of data. Therefore, it is usually a good option to work with structured data.

In other words, SQL is an excellent secondary programming language. It can also serve as the primary language for people who are interested in administering or modeling databases (like database administrators or DBAs).

About SQL

SQL is a programming language following the declarative paradigm. In declarative languages, programmers define what a machine should do, instead of instructing a computer about how it should perform a task. In other words, normally the choice of how the command will be performed is the responsibility of the DBMS instead of the programmer's.

Either as a primary language for database administrators and designers, or as a secondary language for software developers, SQL is a useful programming language. Complex applications for multiple domains use databases to store and retrieve data required for multiple sessions of use or for different users.

Databases deserve own topics for discussion, something that is not on the scope of this page.

For SQL documentation, ideally you will refer to the resources provided by your choice of DBMS. For instance:

• SQLite documentation;
• PostgreSQL documentation;
• MariaDB documentation;
• MySQL documentation.

Database Management System (DBMS)

The first step to start programming with databases is choosing a DBMS. The DBMS is the system that managers the database, and is responsible to define how to perform the operations defined in SQL. Traditional choices for DBMS include:

• SQLite;
• PostgreSQL;
• MariaDB;
• MySQL;
• Microsoft SQL Server;
• Oracle Database;
• IBM Db2.

SQLite is, perhaps, the simplest option for beginners, as it does not require configuring services. Databases created by SQLite are individual files, something that makes the DBMS an excellent choice to be embedded in other programming languages (for instance, in the C programming language or Python). For servers or complex databases, the other options can be more suitable.

Unfortunately, resources and even the syntax to program in SQL can vary among different DBMSs. Nevertheless, after you learn one, it is relatively simple to start programming on others. After all, the concepts are the same or similar.

Additional Remarks

The remainder of this text assumes the use of SQLite, though some examples may work with other options. To allow comparisons with other programming languages, the examples are the same adopted in previous languages.

Nevertheless, the examples are only illustrative. You should choose the most appropriate language to program applications (instead of using SQL). Databases and SQL are useful to store and retrieve data; SQL is not a good language to create programs.

DBMS and Text Editor

You should use root or administrator accounts only to install the DBMS. After the installation, use a regular user account, with lower privileges, to run the DBMS and program using it.

Linux

Linux's distributions commonly offer different DMBSs (such as SQLite, PostgreSQL, MariaDB and MySQL).

1. Arch Linux:

   pacman -S sqlite

2. Debian, Ubuntu or distributions based on the former ones:

   apt-get install sqlite3

3. Fedora:

   dnf install sqlite

4. Gentoo:

   emerge --ask dev-db/sqlite

macOS

On macOS environments, you can use brew to install SQLite.

Windows

To get started with SQLite on Windows, you should access the downloads page and choose one of the options from Precompiled Binaries for Windows. To use SQLite without embedding it another programming language, you must choose the file sqlite-tools-win32-x86-3360000.zip to get an executable for the DBMS. The numbers can vary according the version (3.36, when this page was written).

After download the .zip compressed file, extract the contents to a directory of your preference. Start a session of the cmd interpreter and browse to the directory with the extracted files. For instance, for files in C:\Users\Franco\Desktop\sqlite-tools-win32-x86-3360000, you should use the command cd C:\Users\Franco\Desktop\sqlite-tools-win32-x86-3360000 in cmd. Next, to start the DBMS, you should type sqlite3.exe. To create or open a database called database_name.db, you should use sqlite3 database_name.db.

Should you wish to add the DBMS to the PATH, refer to how to install programs.

Alternatives to install the DBMS on Windows include:

• Chocolatey or Scoop;
• An IDE with an integrated DBMS (for instance, DB Browser for SQLite).

Environment Test

You can start to program in SQL after installing the DBMS. To start the DBMS, with use the downloaded executable or type sqlite3 followed by enter in a command line interpreter (this requires PATH configuration for files manually downloaded).

sqlite3 sqlite.dbSQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite>

The parameter sqlite.db is the chosen name for the file that stores the database's data. You can modify it to your needs. Whenever you use a file with the same name (and path), the data saved on the database's tables will be persisted (saved for later use). If you omit a filename, no data will be stored on the disk after exiting the DBMS.

This section presents the same examples used in JavaScript and Python for purposes of testing and comparing the languages.

As in Python, you will type your code after the sequence (sqlite>).

For instance, you can write on the console with SELECT (documentation). Strictly speaking, SELECT can do much more than writing in the console, though it is enough to know about is writing ability for the moment. To write a message, type something like SELECT "Hello! My name is Franco"; after sqlite>, and press enter. The DBMS will write Hello! My name is Franco and will wait your next command.

sqlite3 sqlite.dbSQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite> SELECT "Hello! My name is Franco";Hello! My name is Franco
sqlite>

The text in the images are from the Portuguese version of this page.

From now on, you should follow the previous steps whenever you read a SQL source code block in this page and wish to manually run in the DBMS.

To exit the DBMS, type .quit (or .ext) followed by enter or press Ctrl d. On Windows, the shortcut can be Ctrl z followed by enter (or it may work not, requiring the use of .quit or .exit).

Like Python, SQL can be used in a Read, Eval, Print, Loop (REPL) mode.

SELECT 1 + 1;
SELECT "Hello! My name is Franco";

Like in other languages, it can be convenient to create the habit of using SELECT when you want to write in the console. For instance, create a file called script.sql (you can choose any name that you want) with the following contents:

SELECT "My First SQL Script";
SELECT "Hello! My name is Franco.";
SELECT "The DBMS can do Math.";
SELECT "Check it out: " || (1 + 1);
SELECT "Bye!";

The script introduces the concatenation operator || (documentation).

To run the script, open a command line interpreter (shell) in the directory that you have created the file and type sqlite3 sqlite.db -init script.sql ".quit" . The DBMS will run the file provided as a parameter (the value script.sql after sqlite3 sqlite.db -init) and will show results from SELECT calls.

sqlite3 sqlite.db -init script.sql ".quit"-- Loading resources from script.sql
My First SQL Script
Hello! My name is Franco.
The DBMS can do Math.
Check it out: 2
Bye!

If the current directory of command line interpreter (shell) is not the directory of the file, you can change directory to the file's folder, or you can write the absolute path of the file. Examples for absolute file paths can include C:\Users\Franco\Desktop\script.sql on Windows for a file in the desktop of a user account named Franco or ~/Desktop/script.sql for a file in the desktop of a Linux machine.

To avoid the message -- Loading resources from script.sql and use a shorter command, you can run the file using the < redirecting operator, to use the input file from the standard input (stdin). To do this, you can run the command as sqlite3 sqlite.db < script.sql:

sqlite3 sqlite.db < script.sqlMy First SQL Script
Hello! My name is Franco.
The DBMS can do Math.
Check it out: 2
Bye!

Finally, it is useful to know that you can consult part of the documentation using the REPL. To do so, use .help.

.help.archive ...             Manage SQL archives
.auth ON|OFF             Show authorizer callbacks
.backup ?DB? FILE        Backup DB (default "main") to FILE
.bail on|off             Stop after hitting an error.  Default OFF
.binary on|off           Turn binary output on or off.  Default OFF
.cd DIRECTORY            Change the working directory to DIRECTORY
(...)

It is enough to know how to use the REPL and run a SQL source code file for learning activities. In general, these two operations are also enough for most daily or professional uses of the programming language.

IDEs

The same considerations about the use of IDEs in Python apply to SQL. The convenience, especially for beginners, continues to be a benefit from using IDEs.

Remember shortcuts for text editors, such as Ctrl n (or File / New) to create new files and Ctrl o (or File / Open) to open existing files.

DB Browser for SQLite

Although it not exactly and IDE, DB Browser for SQLite (DB4S, previously called SQLite Database Browser or Database Browser for SQLite) is a simple option to get started with SQL and SQLite. The program can create, load, and modify SQLite databases.

Furthermore, the program can display and modify tables and values stored in the database.

SQLiteStudio

Another option for SQLite is SQLiteStudio.

The program performs the same operations as the DB Browser for SQLite; thus, the choice is a matter of preference.

Beekeeper Studio

Like DB Browser for SQLite and SQLiteStudio, Beekeeper Studio can manipulate SQLite databases.

Beekeeper Studio can also manage other DBMSs, such PostgreSQL, MariaDB, MySQL, SQL Server, and CockroachDB. Thus, if necessary, you can use a single program to work with multiple different DBMSs.

Other Options

Other options to use and manipulate SQLite database can include:

• HeidiSQL;
• SQuirreL SQL Client with SQLite JDBC Driver;
• DBeaver;
• DataGrip;
• TablePlus;

First Steps to Begin Programming in SQL

The following source code snippets illustrate some resources of the SQL programming language. At this time, it is not necessary to fully understand them; the purpose is showing resources and the syntax of the language.

There are three ways of using the code. You can:

1. Type (or copy and paste) the code directly into the language's interpreter;
2. Write and run the code in the IDE of your choice;
3. Write the code using a text editor and run the created file in the interpreter.

The examples are the same adopted for JavaScript. Therefore, you can compare languages, and notice similarities and differences between them.

Getting to Know SQLite Through Experimentation

1. Text writing:

   SELECT "Uma linha.";
   SELECT "Outra linha.";
   SELECT "Uma linha.
   Outra linha.";

   The line break should be part of the string, like in the previous example.

2. Comments (text that is ignored by the DBMS; documentation):

   SELECT "SGBD processes"; -- SGBD ignores.

   /* <- Here the comment starts.
   It can spawn multiple lines.
   Here it ends -> */

   /* It can also start and end in the same line. */

   -- Though, for single line comments, it is easier to use this comment style.

   It is also possible to create a comment with a single semicolon (;). Comments with a single one are aligned to the right.

3. Mathematics operations:

   • Sum, subtraction, multiplication and division (documentation):

     SELECT 1 + 1;
     SELECT 2 - 2;
     SELECT 3 * 3;
     SELECT 4 / 4; -- What does happen if you try to divide by 0? Try it!

   • Math expressions:

     SELECT 1 + 2 - 3 + (4 * 5) / 6.0;

   • Power (documentation):

     SELECT pow(5, 2);

   • Square root (documentation: sqrt()):

     SELECT sqrt(25);

   • Trigonometric functions, such as sin (documentation: sin() and pi()):

     SELECT sin(pi() / 2.0); -- Sin.

4. Comparisons (true or 1 means true; false or 0 means false):

   • Equality (documentation):

     SELECT 1 = 2; -- Equal.
     SELECT 1 <> 2; -- Different.

     SELECT "Franco" == "Franco";
     SELECT "Franco" <> "Franco";
     SELECT "Franco" <> "Seu Nome";
     -- SQL differs lower case characters from upper case ones (and vice-versa).
     SELECT "F" = "f";
     SELECT "F" <> "f";
     SELECT "Franco" = "franco";
     SELECT "Franco" <> "franco";

   • Other comparisons (documentation):

     SELECT 1 < 2; -- Less than.
     SELECT 1 > 2; -- Greater than.
     SELECT 1 <= 2; -- Less or equal than.
     SELECT 1 >= 2; -- Greater or equal than.

5. Variables and assignment (documentation: documentation):

   Variables are like boxes that store values put inside them. SQLite does not define variables, though it is possible to define constants using Common Table Expressions (CTEs; documentation). After the definition, the values cannot be changed.

   WITH var AS (SELECT 123 AS X)
   SELECT x
   FROM var;

   WITH v AS (SELECT 123.456 + 987.654 AS _result)
   SELECT _result
   FROM v;

   WITH _names AS (SELECT "Franco" AS name1, "Franco Garcia" AS name2)
   SELECT _names.name1, _names.name2
   FROM _names;

   WITH _names AS (SELECT "Franco" AS name1, "Seu Name" AS name2, "Outro Name" AS name3)
   SELECT _names.name1, _names.name2, _names.name3
   FROM _names;

   WITH logical_values AS (SELECT TRUE AS v1, FALSE AS v2, ((1 + 1) = 2) AS v3)
   SELECT logical_values.v1, logical_values.v2, logical_values.v3
   FROM logical_values;

6. Constants (documentation):

   WITH const AS (SELECT 3.14159 AS PI)
   SELECT const.PI
   FROM const;

   WITH const AS (SELECT 2.71828 AS E, 0 AS E) -- Error; the value of a constant cannot be changed once set.
   SELECT const.E
   FROM const;

7. Errors:

   SELECT Ooops!; -- Text should be between double quotes or single quotes.
   SELEC "Incorrect name for SELECT";
   SELECT 1 / 0;
   SELECT 0 / 0;

8. Strings for words and text (documentation):

   SELECT "Ok, this is a valid string";
   SELECT 'Ok, this is another valid string';
   SELECT "Ok, this is also a valid string";

   SELECT "If you want to 'mix' quotes, you have to use those different from the external ones.";
   SELECT "Otherwise, you will need to escape them with two quotes, like this: "". The additional use of the quotes is required.";

9. Logical operators (documentation):

   SELECT TRUE AND TRUE; -- This is a logical "and".
   SELECT TRUE OR FALSE; -- This is a logical "or".
   SELECT NOT TRUE; -- This is a logical "not".

10. Conditions (documentation):

    WITH v AS (SELECT "Firefox" AS browser)
    SELECT IIF(browser = "Firefox", "Mozilla Firefox.", NULL)
    FROM v;

    WITH v AS (SELECT "Your Browser" AS my_browser)
    SELECT IIF(my_browser = "Firefox", "Mozilla Firefox.", "You use another browser.")
    FROM v;

    WITH x AS (SELECT "X" AS i_use),
         y AS (SELECT lower(x.i_use) AS i_use
               FROM x)
    SELECT IIF(y.i_use = "firefox", "You use a browser by Mozilla.",
               IIF((y.i_use = "chrome") OR (y.i_use = "chromium"), "You use a browser by Google.",
                   IIF(y.i_use = "edge", "You use a browser by Microsoft.",
                       IIF(y.i_use = "safari", "You use a browser by Apple.",
                           IIF(y.i_use = "internet explorer", "You should use a more modern browser...", "You use another browser.")))))
    FROM y;

    Normally, you should use CASE (documentation) to write cleaner code.

11. Loops (documentation):

    SELECT i.value FROM generate_series(0, 4, 1) AS i;

    SELECT j.value FROM generate_series(0, 4, 1) AS j;

    SELECT 0;
    SELECT k.value FROM generate_series(1, 4, 1) AS k;

12. Functions (documentation):

    SQLite does not allow creating custom functions in SQL (only for embedded usage in the application language) Other DBMSs, such as PostgreSQL, can allow the creation of scripts in procedural languages (for PostgreSQL, the language is called PL/pgSQL).

    The following code snippet is an example of SQL function written for PostgreSQL. It will not work in SQLite, serving only as a curiosity.

    -- Code for *PostgreSQL*, not for SQLite.
    
    CREATE FUNCTION my_function(x INTEGER, y INTEGER) RETURNS INT AS
    $$
        SELECT x + y;
    $$ LANGUAGE SQL;
    
    -- A function is a block of code that performs arbitrary processing as defined
    -- by the programmer.
    -- After the definition, you can execute the function whenever wanted, using
    -- a function call.
    WITH v AS (SELECT my_function(12, -34) AS z) -- This is an example of a function call.
    SELECT v.z,
           my_function(1, 2) -- This is another example.
    FROM v;

13. Data types (documentation):

    SELECT 1 AS integer_number;
    SELECT -1 AS another_integer_number;
    SELECT 1.23 AS real_number;
    SELECT TRUE AS logic_value; -- or FALSE; it can either be TRUE or FALSE.
    SELECT "Text here. Line breaks use
    that is, this will be at
    the third line." AS string_for_text;

14. Input (documentation: CREATE TABLE; INSERT):

    SQL is a language to store and retrieve data. Thus, the language does not provide a feature for terminal input. Instead of improvising a solution, the following example creates a table to store values, insert an entry, and read the written data.

    CREATE TABLE person(
        _name TEXT,
        age INTEGER
    );
    
    -- SELECT "What is your name? ";
    -- SELECT "How old are you? ";
    INSERT INTO person(_name, age) VALUES ("Your Name", 123);
    
    SELECT _name FROM person WHERE (_name = "Your Name");
    SELECT age FROM person WHERE (_name = "Your Name");
    
    -- The operator || performs an operation called
    -- concatenation that combines the second string after the first.
    SELECT "Hello, " || person._name || "!", "You are " || person.age || " years old."
    FROM person;

Congratulations! You Are Already Able to Write Any Program in SQL

Although it is possible, you should not write whole programs using SQL (the exception are function or procedures in DBMSs that allow procedural scripts). As a tool, use SQL to store and retrieve data. Use another language alongside SQL to create your application.

However, the title of the section is technically correct.

Don't you believe it? You can find the explanation in the introduction to JavaScript

In short, it is not enough to learn the syntax of a language to solve problems using it. It is much more important learning programming logic and developing computational thinking skills than learning the syntax of a programming language. In fact, when you are in doubt about the syntax, you just have to consult the documentation. You do not have to memorize it.

After you learn a first programming language, it is relatively simple to learn other languages (that adopt similar programming paradigms to the first one). To verify this statement, you can open a new window in your browser and place it side by side with this one. Then, you can compare the source code blocks written in SQLite (in this window) and in Python (in the second window).

If you wish to perform comparisons with other programming languages, the following options are available:

• C;
• C++;
• GDScript (Godot Engine);
• Java;
• JavaScript;
• Lua;
• LISP;
• Python;
• Prolog;
• SQL (SQLite).

Next Steps

Once the development environment is configured, you can continue your system development journey.

To avoid repetitions, I recommend reading the page about configuring the JavaScript environment. Even if you are not interested at the language, many discussions are relevant to any programming language. Besides, you will understand a way to create Internet pages and will be able to create content for your browser.

Until GDScript (for Godot Engine), the topics describe develoment environment configurations for a few programming languages. After GDScript, the focus becomes basic concepts for learning programming.