Learn SQLPage
By: Nick Antonaccio (nick@com-pute.com)
This tutorial demonstrates how to use the SQLPage framework to create a variety of web applications. You'll learn to build several dozen full-stack apps, using nothing but SQL code, in just a few hours. No previous experience is required.
Developers, see https://learnsqlpage.com/sqlpage_quickstart.html
Contents:
1. How To View This Tutorial Best On Your Device2. What Is SQLPage? 101 Reasons To Check It Out
2.1 Usable by beginners, pros, and everyone in between
2.2 Instant installation
2.3 A complete full-featured framework
2.4 Connect to compliant database systems
2.5 Built to support standards and connectivity
2.6 SEO
2.7 Easy hosting
2.8 It's so productive
3. Getting Started
4. Creating User Interfaces
4.1 A first front-end UI example in SQLPage
4.2 A map
4.3 A chart
4.4 A table
4.5 Navigating with buttons
4.6 A form
4.7 More UI components
4.8 Icons
4.9 SQLPage Functions
5. Several Simple Front End App Examples
5.1 Web cam feed viewer - static values
5.2 99 bottles of beer - CTE loops
5.3 Coin flip simulator, conditional evaluations
5.4 Markdown viewer/editor - form submissions and markdown
5.5 Word counter - more about handling form submission values
6. Getting Started With Full-Stack SQLPage
6.1 Integrating UI components with the results of SQL database queries
6.2 Configuring a database schema
6.3 Another simple list example using the migrated table schema we've created
6.4 An example with a few more form fields, and corresponding database table columns
6.5 Adding a table UI component to display info from database tables
6.6 Displaying more columns in a table component
6.7 A form with more validation features and even more database table columns
6.8 Using graphical UI database admin apps to manage your tables (HeidiSQL, DBeaver)
7. App Examples Involving Database Queries
7.1 An updated webcam viewer, with a form to add new web cams
7.2 Todo list - a complete CRUD example, with query (URL) parameters
7.3 Map app to store geographical points of interest
7.4 Contacts app, with many typical CRUD features
7.5 Smallest full CRUD datagrid example, a cheatsheet in 8 lines
7.6 Public web forum - joined tables
7.7 Online File Storage System
7.8 Cash register and sales report generator
8. Authentication
8.1 Providing credentialed access to application features
8.2 Providing access to data associated with user accounts
8.3 Securing accounts with stored session tokens and cookies
9. Extending SQLPage With 3rd Party REST APIs
9.1 Some basic examples, and json data structures
9.2 Requests and responses
9.3 Display a random 200px image from picsum.photos API
9.4 Display a selected image size from picsum.photos API
9.5 Ophir's map search, parsing values from a json data structure
9.6 Display json code from an API
9.7 Parsing json records from an API using database functions, and displaying in a UI table
9.8 Json in Sqlite
9.9 Json in PostgreSQL
9.10 Json in MySQL
9.11 Json in MSSQL
9.12 Some additional json parsing examples
9.13 More complex API requests
10. Building And Integrating Your Own Custom REST APIs
10.1 Build an image API endpoint in SQLPage, to be called by other languages & tools
10.2 Use Python code to display photos served by your SQLPage API endpoint
10.3 Build a Python function to be served at a Flask API endpoint
10.4 Use SQLPage to display photos served by your Flask API endpoint
11. Custom REST API App Examples
11.1 Pig latin generator with a Python back end
11.2 Text encryption app with Python cryptography back end
12. Extending SQLPage Capabilities With Sqlpage.exec()
12.1 Integrating a local Python word count script in SQLPage
12.2 Retrieving json data from an API, via a local Python script
12.3 Display a markdown table, locally generated by the Python pandas library
12.4 Using a database table created by SQLAlchemy
13. Extending SQLPage's UI Capabilities By Building Custom Components
13.1 Getting started
13.2 Using built-in Tabler and Bootstrap styling
13.3 A custom URL list component
13.4 A more advanced animation example
13.5 Including images in the animation
13.6 Tweaking the animated image layout
14. Incorporating Iframes
14.1 Integrating apps which call SQLPage API endpoints, in SQLPage iframes
15. Compiling SQLPage From Source
16. Hosting Your SQLPage Apps
16.1 Delivering apps over a local network
16.2 Configuring SQLPage server settings
16.3 Using inexpensive VPS (Linux and Windows)
16.4 Using Docker, cloud hosting and other options
16.5 Host with datapage.app
17. Where To Go From Here
18. A Final Word - Some Perspective
19. About The Author
1. How To View This Tutorial Best On Your Device
In desktop browsers, press 'CTRL' and '+' on your keyboard to enlarge the size of text & images on this page. If you're reading on a small mobile screen, turn your device sideways lengthwise to rotate the view into landscape mode. That will provide the largest possible view of the document contents.
2. What Is SQLPage? 101 Reasons To Check It Out
SQLPage may be the easiest and most productive tool you'll ever find to create web applications. It uses a dialect of traditional SQL language to build both back-end functionality and front-end user interfaces. It's a lightweight, versatile, integrated full-stack solution which can be learned quickly.
The SQLPage web site is built entirely with SQLPage:
https://sql.ophir.dev by Ophir Lojkine, the creator of SQLPage Here's a taste of SQLPage code:
SELECT 'text' AS component, 'Markdown' AS title, 'Enter code:' AS contents; SELECT 'form' AS component; SELECT 'Code' AS name, 'textarea' AS type, :Code AS value; SELECT 'text' AS component, 'Output' AS title, :Code AS contents_md;Which produces this fully functional markdown viewer application:
markdown_simplest.sql Here's another short example which produces a full-stack application, complete with front-end UI and database read/write interactions:
CREATE TABLE IF NOT EXISTS things (items TEXT); SELECT 'form' AS component, 'Add item:' AS title; SELECT 'Item' AS name; INSERT INTO things(items) SELECT :Item WHERE :Item IS NOT NULL; SELECT 'list' AS component, 'Items:' AS title; SELECT items AS title FROM things;
items.sql The SQLPage code required to produce such functionality is dramatically simple compared to other development frameworks.
Here are some example apps from this tutorial, running live online:
todo.sql
map_points_of_interest.sql
custom_animated_image_list2.sql
web_cams_db.sql
shell1.sql
forum.sql
custom_smiley_message_grid.sql
upload_file.sql
iframe.sql
word_count.sql
login.sql (joeblow 12341234)
custom_animated_list.sql
cash_register.sql
cash_register_report.sql
coin_flip.sql
weather.sql
image_size.sql
form_with_table.sql
url_list_custom.sql
json_api_table.sqlAnd some additional basic examples from the tutorial:
99_bottles.sql
chart1.sql
fetch1.sql
random_image.sql
page1.sql
markdown_editor.sql
form_front_end.sql
table_example.sql
alert1.sql
echo_text_field.sql
echo_text_area.sql
index.sql
web_cams.sql
map1.sql
map2.sql
custom_smiley_list.sql
tiny_example.sql
agify_api.sql
items.sql
url_list.sql
crudtable1.sql
crudtable2.sql
2.1 Usable by beginners, pros, and everyone in between
SQLPage is an obvious platform choice for anyone who already knows some SQL, but it's also perfectly suited to absolute beginner developers. Professionals will appreciate SQLPage's simple alternative approach to mainstream language frameworks and their heavyweight tool dependencies.
Despite its ease of use, SQLPage enables a broad range of software development capabilities, with fast performance and modern features.
2.2 Instant installation
SQLPage combines all the benefits promised by no-code, low-code, and pro-code frameworks, in a lightweight free/open-source toolkit, which is a piece of cake to implement. The entire SQLPage server system can be installed immediately, without any dependencies, by copying a single small executable file to any Windows, Mac, Linux, Chromebook, Raspberry Pi machine, etc., or to your choice of inexpensive VPS hosting, AWS, or other cloud providers.
Client interfaces generated by SQLPage run instantly on any common mobile or desktop OS which has a web browser, including iOS, Android, Windows, Mac, Linux, Chromebook, and other proprietary platforms (no install needed, and SQLPage UIs run even in ancient browsers).
2.3 A complete full-featured framework
With SQLPage, developers simply write plain text .sql files to create every part of an application. There are no complicated build steps or any other tools required to deploy applications.
SQLPage includes everything needed to create multi-user software, with support for Postgres, MySQL, and MSSQL, as well as a built-in Sqlite database.
Common UI components such as forms, tables, datagrids, carousels, charts, markdown rendering, multi-step wizards, maps, more than 5000 graphic icons, navigation components, configurable card/hero/shell layouts that resize and rearrange responsively to any mobile/desktop screen size, etc., are all built-in, and are a breeze to use.
SQLPage also includes an authorization/login system, it can access and serve REST APIs, it provides top notch automated security, performance and configuration features, CSV downloads and RSS feeds which can be generated from database query results, database migrations, file uploads, HTTPS termination, etc. And if all those features aren't enough, the system is extensible with custom components, which anyone with HTML/CSS/JS knowledge can build.
2.4 Connect to compliant database systems
SQLPage fits naturally in environments where compliance obligations (HIPAA, PCI, etc.) call for the database servers most often specified by IT and security policy requirements.
Database administrators, analysts, scientists, statisticians, doctors, and other professionals who already use SQL in these environments can integrate SQLPage seamlessly with their existing schema, without having to contend with layers of foreign language and ORM infrastructure that exist in virtually every other popular web development framework.
2.5 Built to support standards and connectivity
Rest assured that work completed with SQLPage is portable to other development environments, and that SQLPage projects can easily connect with other tech tools.
JSON data structures and REST APIs are natively supported in SQLPage, so you can consume data in SQLPage from 3rd party API endpoints, or serve API data endpoints to 3rd party applications from SQLPage. Connect functions written in Python, delivered by Flask, Django, FastAPI, Bottle, etc., or any NodeJS, PHP, Java, C#, Ruby, Rust, Go, R, or other language framework. Collaborating on SQLPage apps with team members who have experience working in any well known ecosystem, is a piece of cake for everyone.
You can also interoperate with applications written in any other language, or any no-code/low-code toolkit, simply by connecting to the same database.
Display SQLPage UI output in iFrames created by 3rd party tools, or display 3rd party UIs in SQLPage iFrames. SQLPage also supports exporting data as CSV files, which can be opened directly as spreadsheets, or imported by other tools. Furthermore, extending SQLPage functionality with custom components requires only standard HTML/CSS/JS (although absolutely no HTML/CSS/JS is required to use any of SQLPage's main features).
Generative AI can be used to write standard SQL code for use in SQLPage. ChatGPT, Claude, Perplexity, Gemini, Llama, Deepseek, and others are all wizards at producing SQL. And if you want to move any of the functionality you've created in SQLPage to another framework, all that SQL code is one of the most time-tested standards in the industry, entirely portable to virtually any other environment.
Vendor lock-in is not a problem with SQLPage.
2.6 SEO
Compared to SPA (Single Page App) frameworks, SQLPage is more naturally suited to satisfy SEO (Search Engine Optimization) requirements. So, along with building data management applications, SQLPage is a great tool to publish articles and other static content, in a way that enables search engines to find and index your writing. SQLPage's traditional linked multi-page navigation system is as simple as possible for developers to construct.
2.7 Easy hosting
SQLPage can be set up instantly in any common hosting environment, but for users who prefer an even simpler deployment environment, https://datapage.app offers dedicated SQLPage hosting, for a totally hassle-free way to deliver SQLPage apps, without any knowledge of operating systems or server technology required.
2.8 It's so productive
Just a few lines of SQLPage code can produce results which require far more work in other languages, or complex interactions with complicated, expensive, and restrictive proprietary no-code systems. Give SQLPage a try for a week, and you'll likely accomplish more than would be possible in months using many other language frameworks and development tools.
Please note that the author of this article is in no way affiliated with SQLPage, and receives no reimbursement whatsoever from the creators of SQLPage, for any materials or opinions presented in this tutorial.
3. Getting Started
The easiest way to begin working with SQLPage is to install it on your own local computer. Go to the link below to download the version for your operating system (Windows, Linux, Mac):
https://github.com/lovasoa/SQLpage/releases
To get the SQLPage environment running:
- Unzip the package, to any accessible folder on your hard drive
- Run the sqlpage executable (sqlpage.exe on Windows, sqlpage.bin on Linux and Mac)
- Open your browser to http://localhost:8080
You'll see a confirmation page which shows that the SQLPage server is running:
4. Creating User Interfaces
The 'front end' of your SQLPage application will run in a web browser. It contains the 'UI' (User Interface) parts of the application, which users see and touch: buttons, dropdown selectors, text entry fields, links, menus, datagrids that display tables of information, etc.
4.1 A first front-end UI example in SQLPage
To write your first front-end UI in SQLPage, use any text editor (notepad, VSCode, etc.) to create a new plain text document. Paste the following code into the document, and save the file as 'alert1.sql', in the same folder where the SQLPage executable is found. Be aware that the SQLPage confirmation page above shows where that working directory is located on your hard drive (it should be wherever you unpacked the sqlpage zip file). Be sure to save this code file to that particular folder:
SELECT 'alert' AS component, 'Hello World!' AS title, 'That was easy :)' AS description, 'black' AS color, 'balloon' AS icon, TRUE AS dismissible;Now go to http://localhost:8080/alert1.sql in your browser. Notice that the path after the forward slash in the URL is the name of the file you just saved in your SQLPage folder (http://localhost:8080/alert1.sql):
UI components are displayed in SQLPage apps, by using a SELECT statement to first pick a component. Visual properties of the selected component are then set using AS clauses. As you can see, the page above displays:
- an alert component
- with the title text 'Hello World'
- and the description text 'That was easy :)'
- in black
- with a balloon icon
- and a button to dismiss the alert (click the X to make the alert component disappear)
Compare each of those 6 features to the 6 respective lines of SQL dialect code above. That comparison should make sense, even without much explanation. Each line of the 'SELECT' query code maps directly to one of the individual visual properties listed above, which you see displayed in the application front end. Notice that each line ends with a comma, and the entire query ends in a semicolon. Easy, right?
4.2 A map
Now create a new text document, paste the following code into it, and save the file as 'map1.sql', in your SQLPage working directory:
SELECT 'map' AS component; SELECT 'Statue of Liberty' AS title, 40.6892 AS latitude, -74.0445 AS longitude;With the SQLPage server application still running, go to http://localhost:8080/map1.sql in your browser, and you'll see the page below:
Again, the code which created the page above should make some intuitive sense without much explanation. It selects a visual map component, with 'Statue of Liberty' as the title, 40.6892 as the latitude value, and -74.0445 as the longitude value.
4.3 A chart
Next, create a new text document, paste the following code into it, and save the file as 'chart1.sql', in your SQLPage working directory:
SELECT 'chart' AS component, 'bar' AS type, 'Favorite pets' AS title; SELECT 'Cat' AS label, 35 AS value; SELECT 'Dog' AS label, 45 AS value; SELECT 'Fish' AS label, 20 AS value;Go to http://localhost:8080/chart1.sql in your browser, and you'll see the page below:
The code which created the page above should again make some intuitive sense without much explanation. It selects a visual bar chart component, with 'Favorite pets' as the title, and cat/dog/fish labels & values.
IMPORTANT:
The static data in the example above is hard-coded into SELECT statements - but that's not how tasks are normally accomplished in applications. Instead, you'll typically set up a database table to store the values to be displayed in a UI component. In this case we could, for example, create a table named 'pet_ratings', with 'animal' and 'score' columns, and then populate the chart output with rows from that table. The SELECT statement below sets the label and value properties of the chart component to be the animal and score data values FROM each row of the pet_ratings table:
SELECT 'chart' AS component, 'bar' AS type, 'Favorite pets' AS title; SELECT animal AS label, score AS value FROM pet_ratings;SQLPage uses this sort of general code pattern often (SELECT a component, then SELECT values to be displayed in the component), to populate UIs with data values returned by SQL queries.
4.4 A table
Next, save this code as 'table_example.sql' in your SQLPage working directory:
SELECT 'table' AS component, 'Static Data Table' AS title, TRUE AS sort, TRUE AS search; SELECT 'Alice' AS 'First Name', 'Smith' AS 'Last Name', 'alice@example.com' AS 'Email' SELECT 'Bob', 'Jones', 'bob@example.com';The code above selects a table component, with some static rows of data displayed in 'First Name', 'Last Name', and 'Email' columns, for Alice Smith and Bob Jones. Go to http://localhost:8080/table_example.sql in your browser, and you'll see the page below, where you can sort and search the rows of the table:
The table UI component will be used many times throughout this tutorial to display rows of data queried from database tables.
4.5 Navigating with buttons
Next, save this code as 'page1.sql':
SELECT 'title' AS component, 'This is page 1' AS contents; SELECT 'button' AS component; SELECT '/page2.sql' AS link, 'Go to page 2' AS title;And save this code as 'page2.sql'
SELECT 'title' AS component, 'This is page 2' AS contents; SELECT 'button' AS component; SELECT '/page1.sql' AS link, 'Go to page 1' AS title;Go to http://localhost:8080/page1.sql in your browser, and click the button:
You can click the button on both pages to navigate back and forth between http://localhost:8080/page1.sql and http://localhost:8080/page2.sql:
Again, the code in this example should be easy to decipher. Each page selects:
- A title component to display some text content.
- A button component which links to the other page when clicked. The second SELECT associated with each button, assigns 2 additional property values to the button: link and title.
Notice that the components are shown in a nice looking default page layout which requires no effort to set up.
4.6 A form
Now save this code as 'form_sender.sql':
SELECT 'form' AS component, 'form_receiver.sql' AS action; SELECT 'name' AS name; SELECT 'color' AS name;And this code as 'form_receiver.sql':
SELECT 'alert' AS component, 'You entered:' AS title, :name AS description, :color AS description, 'black' AS color, 'info' AS icon;Go to http://localhost:8080/form_sender.sql in your browser, and type in a name & color:
The code should again be straightforward to understand:
- The sender page displays a form with 'name' and 'color' fields. The values entered by the user are passed to the receiver page (form_receiver.sql), when submitted.
- The receiver page displays an alert in which the submitted :name and :color values are shown in the description. Note that the submitted form values are notated with a colon symbol prefix.
It's all just a few lines of code.
4.6.1 Submitting and processing form data using a single page of code
IMPORTANT:
The code to both send and receive form data can exist on a single .sql page. If the 'action' property isn't set for a form component (as in the example above), then the current page will be re-opened, and the values submitted by the form can be evaluated. For example, the single page of code below accomplishes the same goal as the two separate pages above. Note the use of the WHERE clause. It's used to check whether the page has been re-opened with a value submitted from a form, or whether the page has simply been opened by the user. The alert component will only be shown if the page has been opened with a value submitted from the name field on the form (i.e., when the :name value is not NULL):
SELECT 'form' AS component; SELECT 'name' AS name; SELECT 'color' AS name; SELECT 'alert' AS component, 'You entered:' AS title, :name AS description, :color AS description, 'black' AS color, 'info' AS icon WHERE :name IS NOT NULL;You'll see this single-page form pattern used again in SQLPage applications.
4.7 More UI components
The components you've seen so far are just the tip of the iceberg. sql.ophir.dev/documentation.sql has documentation and examples for all the UI components built into SQLPage.
You can begin to explore every bit of SQLPage's native UI capabilities simply by pasting the code examples on that page into files, and then running them, as you've done with the examples so far in this tutorial.
Try adjusting the examples to fit your own specifications. For example, here is a simplified version of a shell component example provided in the documentation. It's been adjusted to link the local example pages that we've created so far in this tutorial. Save this code as 'shell1.sql' and open your browser to http://localhost:8080/shell1.sql
SELECT 'shell' AS component, 'My page' AS title, 'home' AS icon, JSON('{"title":"Main","submenu":[ {"link":"/alert1.sql", "title":"Alert", "icon":"alert-square"}, {"link":"/map1.sql", "title":"Map", "icon":"map"}, {"link":"/chart1.sql", "title":"Chart", "icon":"chart-bar"}, {"link":"/page1.sql", "title":"Pages", "icon":"squares"}, {"link":"/form_front_end.sql", "title":"Forms", "icon":"forms"}, ]}') AS menu_item, JSON('{"title":"Other","submenu":[ {"link":"https://learnsqlpage.com", "title":"This tutorial", "icon":"book"}, {"link":"https://com-pute.com", "title":"Another web site", "icon":"world"}, ]}') AS menu_item, '[SQLPage](https://sql.ophir.dev)' as footer;
You'll see that the menu links actually open all the existing example pages we've saved so far. That's an easy way small pieces of functionality can be connected to form larger applications in SQLPage.
An enormous amount of progress learning SQLPage can be made simply by exploring all the UI component code examples provided in the SQLPage documentation - that's an important step which shouldn't be skipped. Just familiarize yourself with all the components, and all the properties available on each component. Get used to the general look of the code in each component example, and get comfortable editing some small details. To start out, try simply changing some text, icons, links, and other values in the examples, delete unnecessary components and properties, and combine manageable bits of the example code to customize the content and look of each example. Get your hands dirty adjusting the examples, and you'll see that each piece is malleable and simple to compose.
4.8 Icons
Notice the use of icons on almost every component in the previous example. Icons tend to not only improve the look of UI layouts, they also provide helpful clues as to what operation each component performs, so that the functionality of interfaces is more instantly understandable by users.
SQLPage comes packed with the 5450 free icons available at tabler.io/icons, all built in. You can go to that web page and search for any of the included images, to find one that fits the exact purpose of any UI component in your SQLPage apps.
You can also download all the icons for free, if you prefer to search them offline.
4.9 SQLPage Functions
Along with UI components, SQLPage includes a number of built in 'functions', which enable developers to manage data and functionality:
https://sql.ophir.dev/functions.sql
The 'fetch' function, for example, sends an HTTP request to a URL (typically a 3rd party 'API', which can be written in any other programming language), and returns the results as a string. Save the following code as 'fetch1.sql' and open http://localhost:8080/fetch1.sql in your browser:
SELECT 'code' AS component; SELECT 'API Results' AS title, sqlpage.fetch('https://jsonplaceholder.typicode.com/users') AS contents;
The rest of this tutorial will cover how to use all the SQLPage UI components and functions, in the context of learning to build increasingly complex full-stack applications. It's going to be surprisingly easy to build all sorts of useful software.
5. Several Simple Front End App Examples
The following examples provide some basic insight into how to perform common front-end tasks in SQLPage. You'll be able to re-use many of these techniques and component examples often, when creating software of all types.
5.1 Web cam feed viewer - static values
This app uses the SQLPage datagrid component to display a small thumbnail image from a number of live web cams, and allows the user to click each link to see the full live image. Save this code as 'web_cams.sql' and open your browser to http://localhost:8080/web_cams.sql:
SELECT 'datagrid' AS component, 'Live Web Cams' AS title; SELECT 'Eurekaway' AS title, 'Crosswalk' AS description, 'https://cwwp2.dot.ca.gov/data/d2/cctv/image/eurekaway/eurekaway.jpg' AS link, 'https://cwwp2.dot.ca.gov/data/d2/cctv/image/eurekaway/eurekaway.jpg' AS image_url; SELECT 'Perez' AS title, 'Wooded Road' AS description, 'https://cwwp2.dot.ca.gov/data/d2/cctv/image/perez/perez.jpg' AS link, 'https://cwwp2.dot.ca.gov/data/d2/cctv/image/perez/perez.jpg' AS image_url; SELECT 'Johnson Grade' AS title, 'Road Divider' AS description, 'https://cwwp2.dot.ca.gov/data/d2/cctv/image/johnsongrade/johnsongrade.jpg' AS link, 'https://cwwp2.dot.ca.gov/data/d2/cctv/image/johnsongrade/johnsongrade.jpg' AS image_url; SELECT 'East Riverside' AS title, 'Intersection' AS description, 'https://cwwp2.dot.ca.gov/data/d2/cctv/image/eastriverside/eastriverside.jpg' AS link, 'https://cwwp2.dot.ca.gov/data/d2/cctv/image/eastriverside/eastriverside.jpg' AS image_url; SELECT 'SBI5' AS title, 'Highway' AS description, 'https://cwwp2.dot.ca.gov/data/d10/cctv/image/sbi5sopeltierroad/sbi5sopeltierroad.jpg' AS link, 'https://cwwp2.dot.ca.gov/data/d10/cctv/image/sbi5sopeltierroad/sbi5sopeltierroad.jpg' AS image_url; SELECT 'SR70US395' AS title, 'Highway Signs' AS description, 'https://cwwp2.dot.ca.gov/data/d2/cctv/image/sr70us395/sr70us395.jpg' AS link, 'https://cwwp2.dot.ca.gov/data/d2/cctv/image/sr70us395/sr70us395.jpg' AS image_url; SELECT 'Garner Lane' AS title, '4 Way' AS description, 'https://cwwp2.dot.ca.gov/data/d3/cctv/image/hwy99atgarnerlane1/hwy99atgarnerlane1.jpg' AS link, 'https://cwwp2.dot.ca.gov/data/d3/cctv/image/hwy99atgarnerlane1/hwy99atgarnerlane1.jpg' AS image_url; SELECT 'Pit River Bridge' AS title, 'Bridge Over Water' AS description, 'https://cwwp2.dot.ca.gov/data/d2/cctv/image/pitriverbridge/pitriverbridge.jpg' AS link, 'https://cwwp2.dot.ca.gov/data/d2/cctv/image/pitriverbridge/pitriverbridge.jpg' AS image_url;
The layout automatically adjusts responsively to any size screen:
Note that the example above includes many repetitive lines of code, but only because all the titles, descriptions, and URLs are hard-coded directly in the script. When retrieving data from a database, the complete application code looks much simpler:
SELECT 'datagrid' AS component, 'Live Web Cams' AS title; SELECT title AS title, description AS description, url AS link, url AS image_url FROM webcams;SQLPage understands that you're pulling values from a database, whenever the select statement includes a FROM clause. Otherwise, it knows to use static/variable values directly written in the code. We'll cover much more about using database values later in the tutorial (that's of course where SQLPage really shines).
Pay attention to one final small but important detail in the code above: the datagrid component has a 'title' property, and then each of the selected items displayed in the grid also have a 'title' property. The big title value displayed at the top of the component is called a 'top level' parameter, and the title value which appears on each item, which typically changes for each row, is called a 'row level' parameter.
5.2 99 bottles of beer - CTE loops
This example prints out the lyrics to '99 Bottles of Beer':
WITH RECURSIVE numbers AS ( SELECT 99 AS num UNION ALL SELECT num - 1 FROM numbers WHERE num > 1 ) SELECT 'text' AS component, num || ' bottles of beer on the wall, ' || num || ' bottles of beer. Take one down and pass it around, ' || (num - 1) || ' bottles of beer on the wall.' AS contents FROM numbers ORDER BY num DESC;This code uses an SQL 'CTE' (Common Table Expression) to 'loop' through the numbers 99-1. For each successive number produced by the CTE, the static song text is 'concatenated' (joined) with the variable 'num' value. The || operator is used to perform that concatenation. In each iteration, an SQLPage text component is output to the user interface (the web page the user sees), with the contents of each text component set to that concatenated text. The result is the complete song lyrics printed on screen:
As with everything else in this tutorial, you can learn more about CTEs in SQL by searching online, or by simply pasting the code you see here into ChatGPT and asking for an explanation of how it works. We'll look much more at how to use AI, later in this tutorial - it's the best way to help you learn and to solve coding problems quickly and effectively.
5.3 Coin flip simulator, conditional evaluations
This example displays a random coin toss simulation each time the page is refreshed:
SELECT 'card' AS component; WITH random_choice AS ( SELECT RANDOM() % 2 AS coin_flip ) SELECT CASE WHEN coin_flip = 0 THEN 'Heads' ELSE 'Tails' END AS title, CASE WHEN coin_flip = 0 THEN 'You win this toss :)' ELSE 'You lose this toss :(' END AS description, CASE WHEN coin_flip = 0 THEN 'https://re-bol.com/heads.jpg' ELSE 'https://re-bol.com/tails.jpg' END AS top_image FROM random_choice;The code above generates a random value which is assigned to the variable label 'coin_flip'. A series of CASE evaluations are used to output alternate pieces of text to the user interface, based on WHEN and ELSE 'conditional evaluations'. In each CASE evaluation, one value (a chosen piece of text or image link) is output WHEN the random coin_flip variable equals 0, ELSE a different value is output:
5.4 Markdown viewer/editor - form submissions and markdown
SQLPage has the built-in ability to display markdown layouts on many components, so building a markdown viewer/editor is simple:
SELECT 'form' AS component, 'Enter Markdown Code:' AS title; SELECT 'Text' AS name, 'textarea' AS type, :Text AS value; SELECT 'text' AS component, :Text AS contents_md;The code above displays a form with a multi-line text entry area. When that text is submitted by the user, the variable :Text is assigned to the text in that textarea. When the page re-opens, that submitted text is re-displayed as the value in the text area, and it's also rendered as the 'contents_md' (the markdown contents) displayed in the text component:
Try pasting the following example markdown code into the app:
## This is your rendered markdown code: | SQLPage component | Description | Documentation link | | --- | --- | --- | | text | words | [Docs](https://sql.ophir.dev/documentation.sql?component=text) | | list | items | [Docs](https://sql.ophir.dev/documentation.sql?component=list) | > — [HackerNews](https://news.ycombinator.com/item?id=36194473#36209061)  --- > level 1 blockquote >> level 2 blockquote - [x] bread - [ ] milkThis app is actually useful, to help visualize markdown code that can be used in other applications. See https://www.markdownguide.org/cheat-sheet for a quick intro to markdown syntax.
5.5 Word counter - more about handling form submission values
Let's build a little app to count the words which users enter into a text area. We'll do this step by step.
First we need to create a form with a textarea that can accept input from the user. As in the markdown editor example, the following code displays a form with a text area, which is filled with the previously entered text submitted from that textarea (i.e., it echoes that submitted data back to the user, because :Text (the name given to the textarea) is set as the value displayed in the textarea component):
SELECT 'form' AS component, 'Enter your text:' AS title; SELECT 'Text' AS name, 'textarea' AS type, :Text AS value;
Great, now we can get text input from the user and re-display it each time the form is submitted. Now let's add some code to split the entered text into words that are separated by spaces:
SELECT 'form' AS component, 'Enter your text:' AS title; SELECT 'Text' AS name, 'textarea' AS type, :Text AS value; WITH entered_text AS ( SELECT :Text AS submitted_text ), word_split AS ( SELECT LENGTH( TRIM(submitted_text)) - LENGTH(REPLACE(TRIM(submitted_text), ' ', '') ) + 1 AS word_count FROM entered_text ) SELECT 'text' AS component, 'Word count: ' || word_count AS contents FROM word_split;
That sort of works, but there's a problem. Newlines and tabs aren't being treated as whitespace, and more than one space next to each other is being treated as separate countable works. The following code by Ophir Lojkine uses some more advanced SQL techniques to solve this parsing algorithm gracefully:
with recursive letters(n, l) as ( select 0, '' union all select n+1, substring(:Text, n+1, 1) from letters where n < length(:Text) ), is_word(n, l, is_word) as ( select n, l, l between 'a' and 'z' or l between 'A' and 'Z' from letters ), word_beginnings(is_beginning) as ( select is_word and not lag(is_word) over (order by n) from is_word ) select sum(is_beginning) from word_beginnings;And here's that SQL algorithm integrated into the SQLPage UI code:
SELECT 'form' AS component, 'Enter your text:' AS title; SELECT 'Text' AS name, 'textarea' AS type, :Text AS value; WITH RECURSIVE letters(n, l) AS ( SELECT 0, '' UNION ALL SELECT n+1, SUBSTRING(:Text, n+1, 1) FROM letters WHERE n < LENGTH(:Text) ), is_word(n, l, is_word) AS ( SELECT n, l, l BETWEEN 'a' AND 'z' OR l BETWEEN 'A' AND 'Z' FROM letters ), word_beginnings(is_beginning) AS ( SELECT is_word AND NOT LAG(is_word) OVER (ORDER BY n) FROM is_word ) SELECT 'text' AS component, 'Word count: ' || SUM(is_beginning::int) AS contents FROM word_beginnings;
The algorithm code above is complex if you're just starting with SQL. It's fine to ignore this complexity temporarily and move on. The important part, for the moment, is just to see that SQLPage can be used to get data submitted by a user, and perform SQL queries of any complexity, using that submitted data.
For more experienced SQL developers, Ophir's guidance to accompany the code above is: "When implementing more complicated algorithms in SQL, in general, I find it useful to build a recursive CTE where the columns are the variables I would use inside the loop of a traditional programming language". SQL CTEs will often be useful when building applications in SQLPage.
6. Getting Started With Full-Stack SQLPage
The real magic of SQLPage is its ability to easily populate UI components with data results from any traditional SQL query. All the common visual interfaces needed for users to input, output, and view data, as well as mechanisms to connect queries with other software applications, are tightly integrated. Wiring up fully functional data management apps becomes a snap when you see how to orchestrate SQLPage components and functions.
6.1 Integrating UI components with the results of SQL database queries
The following code creates a full stack SQLPage application, in which front-end UI components interact with back-end database queries. In this app, the user enters items into the database using a UI form field, and all the items in the database table are displayed in a UI list component:
CREATE TABLE IF NOT EXISTS things (items TEXT); SELECT 'form' AS component, 'Add item:' AS title; SELECT 'Item' AS name; INSERT INTO things(items) SELECT :Item WHERE :Item IS NOT NULL; SELECT 'list' AS component, 'Items:' AS title; SELECT items AS title FROM things;
http://server.py-thon.com:8008/items.sql Here's a breakdown of how each line works. The language is genuinely easy to decipher when you examine it piece by piece:
- A database table is created if it doesn't already exist. The table named 'things' has one column named 'items', which stores text values.
- A form component is shown in the app UI, with the title 'Add item:' and a single text field named 'Item'. Remember, when a value is submitted by the user, the page re-opens, with the variable :Item (the name of the form text field) containing the submitted value.
- When the app page opens, if an item has been submitted from the form (i.e., the :Item value is not null), then the submitted value is inserted into the 'items' column of the 'things' table.
- A list component is shown in the UI, with the top-level title 'Items:'. Rows from the 'items' column of the 'things' table are selected and displayed as the row-level title property of each visual row in the list.
The building blocks above form a foundation for creating useful data management apps with SQLPage. Note that you can use line breaks, indentation, and other white space to format code so it's easier to read. The following code is the exact same as the four lines above:
CREATE TABLE IF NOT EXISTS things ( items TEXT ); SELECT 'form' AS component, 'Add item:' AS title; SELECT 'Item' AS name; INSERT INTO things(items) SELECT :Item WHERE :Item IS NOT NULL; SELECT 'list' AS component, 'Items:' AS title; SELECT items AS title FROM things;Be aware that by default, SQLPage uses an embedded Sqlite engine to perform database operations. Connecting to any other supported database system is accomplished by simply editing the connection string stored in /sqlpage/sqlpage.json (more about that later).
6.2 Configuring a database schema
To store data in a database, you'll first need to configure a table 'schema'. The schema defines column names and data types that will be stored in specifically named tables of information. Along with data columns, most tables include a numeric 'key' column, which is used to identify and link each individual row of information (so, for example, if 2 rows were to ever contain the exact same text stored in every data column, each of those rows could still be uniquely identified by their id numbers).
The following schema definition code creates a table named 'food', with a column to hold the name of a person, a column to store their favorite fruit, and an id column to be used as the primary numeric key identifier for each row in the table. Save this code in a file named '0001_create_food_table.sql', in the '/sqlpage/migrations/' sub-folder of where the rest of your SQLPage code files have been saved:
CREATE TABLE food ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, fruit TEXT NOT NULL );Stop the SQLPage program from running (press CTRL C at the command line, or just close the console window where the server is running). Then restart the SQLPage server, and the table will be created:
You can change the schema of a table, using a sequentially numbered 'migration' file. Save this code in a file named '0002_edit_food_table.sql' in the '/sqlpage/migrations/' sub-folder:
-- This creates a new temporary table named food_temp CREATE TABLE food_temp ( id INTEGER PRIMARY KEY, fruit TEXT NOT NULL ); -- This copies any existing fruit and id column data to the temporary table INSERT into food_temp SELECT id, fruit from food; -- This deletes the original food table DROP table food; -- The following line renames the temporary food table, -- with its schema and copied data, to be the new food table ALTER table food_temp rename to food;Restart the SQLPage server application, and the table changes will be implemented (the changes above recreate the original table schema, but with the 'name' column removed):
Note that the portion of a migration file name following the unique sequential number can be any valid file name string which helps you remember what table operations are performed by that particular file.
Also be aware that migration files are simply a convenience built into SQLPage which help manage changes to database schema. You can also choose to define table schema directly in your application code, and use other tools to manage your database (more about that shortly).
6.3 Another simple list example using the migrated table schema we've created
Here's another simple example which enables users to enter rows of values into a UI form, store those values in the 'food' database table we've created, then retrieve and display the stored values in a UI list component. This code is almost exactly the same as the previous 4 line example, but with some small additions. Notice that the form component has its 'required' parameter set to TRUE. This requires the user to enter a value into the 'Fruit' field before the form can be entered. Also notice the 'CONCAT' function. This 'concatenates', or joins together, some variable (changing) text, with some static (unchanging) text (similar to how the || operator works, but preferred when working with MySQL, and for handling NULL values). The concatenation joins together the variable 'fruit', which represents a fruit value selected from the database, with the static text ' is a favorite fruit', in each row-level description parameter of the list component.
Save this code as 'fruit1.sql' and run it in your browser at http://localhost:8080/fruit1.sql:
INSERT INTO food(fruit) SELECT :Fruit WHERE :Fruit IS NOT NULL; SELECT 'form' AS component, 'Add a fruit' AS title; SELECT 'Fruit' AS name, TRUE AS required; SELECT 'list' AS component, 'Current fruits' AS title; SELECT fruit AS title, CONCAT(fruit, ' is a favorite fruit') AS description FROM food;
You've seen all this before, but to clarify each detail: the form sends the value entered by the user back to the same page (because there is no 'action' property specified in the form component), so the page is reloaded, and the submitted data is processed. The first SELECT operation gets the submitted :FRUIT value from the form, and inserts it into the 'food' database. Remember that the colon in front of the word FRUIT creates a variable label for the submitted value, which can then be used to refer to that value throughout the code. Notice also that the database INSERT operation is only performed if the submitted :FRUIT value is not null. This ensures that empty values aren't inserted into the database every time the page is opened without a submitted value (i.e., when the user first opens the page).
Note that for more complex form operations, it's often a better choice to create separate sender pages where users enter form data, and receiver pages where the submitted data is processed and results are displayed to the user.
6.4 An example with a few more form fields, and corresponding database table columns
All the logic in the application below is nearly identical to the simple 4 line example from earlier in the tutorial. This code simply displays more form fields and list values, which correspond to more columns in a database table:
CREATE TABLE IF NOT EXISTS myurls ( id INTEGER PRIMARY KEY AUTOINCREMENT, url TEXT NOT NULL, description TEXT NOT NULL ); INSERT INTO myurls ( url, description ) SELECT :URL, :Description WHERE :URL IS NOT NULL AND :Description IS NOT NULL; SELECT 'list' AS component, 'Links:' AS title; SELECT url AS title, description AS description, url AS link FROM myurls; SELECT 'form' AS component, 'Add link:' AS title; SELECT 'Description' AS name, TRUE AS required; SELECT 'URL' AS name, TRUE AS required;
http://server.py-thon.com:8008/url_list.sql Here's a breakdown of how each section works. Compare this, section by section, to the previous 4 line example:
- A database table is created if it doesn't already exist. The table named 'myurls' has 3 columns named 'id', 'url', and 'description'. We're most interested in the url and description columns, which store text values.
- When the app page opens, if values have been submitted from the form (i.e., the :URL and :Description values are not null), then the submitted values are inserted into the 'url' and 'description' columns of the 'myurls' table.
- A list component is shown in the UI, with the title 'Links:'. Rows from the 'url' column of the 'myurls' table are selected and displayed as the title property of each visual row in the list, and the 'description' columns are displayed as the description property of each visual row in the list.
- A form component is shown in the app UI, with the title 'Add link:' and 2 text fields named 'URL' and 'Description (both set as required). When those values are submitted by the user, the page re-opens, with the variables :URL and :Description (the names of the form text fields) containing the submitted values.
You can see that the logic is nearly identical to the previous 4 line example - this code simply handles 2 data values, instead of 1. Be aware that compared to the previous simple 4 line example, the positions of the form component and the list component have been swapped, so that the form now appears beneath the list, but that doesn't change the logic in each of the 4 sections. One new feature: the url column value is used as the 'link' property in the list component, which opens the specified URL link in the user's browser, when they click a row-level line in the list component.
Note that the id column in the database table is not strictly needed in this example, but it's good to get used to seeing id columns included in database table definitions. Also, to be clear, since the table schema code is included directly in this example, a separate migration file is not required.
The basic logic in this example is typical of any simple barebones application in SQLPage. To accomplish useful goals with SQLPage, you should really understand every detail of this code. If you have any questions, break out ChatGPT, copy, paste, and ask for explanations!
6.5 Adding a table UI component to display info from database tables
The code below extends the previous example incrementally by simply replacing the UI list component (used in the example above) with a table component (used in the example below). All the other logic and layout structure stays exactly the same:
CREATE TABLE IF NOT EXISTS myurls ( id INTEGER PRIMARY KEY AUTOINCREMENT, url TEXT NOT NULL, description TEXT NOT NULL ); INSERT INTO myurls ( url, description ) SELECT :URL, :Description WHERE :URL IS NOT NULL AND :Description IS NOT NULL; SELECT 'table' AS component, 'Links:' AS title, 'Link' AS markdown, TRUE AS sort, TRUE AS search; SELECT description AS Description, '[' || url || '](' || url || ')' AS Link FROM myurls; SELECT 'form' AS component, 'Add link:' AS title; SELECT 'Description' AS name, TRUE AS required; SELECT 'URL' AS name, TRUE AS required;
http://server.py-thon.com:8008/crudtable1.sql Notice that the 'Link' column in the table component above is signified to contain markdown content (via the line: 'Link' AS markdown). The displayed markdown content in each row is created by concatenating the url value from the database table with the appropriate markdown code needed to display a link:
'[' || url || '](' || url || ')' AS LinkTo be clear, that concatenation creates a link in the markdown format: [link text](link URL). In this case, the displayed link text is the same as the link URL that will open when the link is clicked. Markdown is simpler to use than HTML, and is implemented regularly in modern web frameworks. Be sure to Google a tutorial about how to use Markdown - it's easy to learn and you'll see it often, no matter what development tools you use to build web apps.
6.6 Displaying more columns in a table component
Tables are very useful in all sorts of data management apps because they enable users to see and interact with multi-column displays of data, and clearly, that's a fundamentally useful capability in apps which manage information. Adding another column of information to the display above is simple. Only one line of code needs to be added, for example, to also display the id column from the database table:
CREATE TABLE IF NOT EXISTS myurls ( id INTEGER PRIMARY KEY AUTOINCREMENT, url TEXT NOT NULL, description TEXT NOT NULL ); INSERT INTO myurls ( url, description ) SELECT :URL, :Description WHERE :URL IS NOT NULL AND :Description IS NOT NULL; SELECT 'table' AS component, 'Links:' AS title, 'Link' AS markdown, TRUE AS sort, TRUE AS search; SELECT description AS Description, '[' || url || '](' || url || ')' AS Link, id AS ID FROM myurls; SELECT 'form' AS component, 'Add link:' AS title; SELECT 'Description' AS name, TRUE AS required; SELECT 'URL' AS name, TRUE AS required;
http://server.py-thon.com:8008/crudtable2.sql Compare the code above with the previous example, and you'll see that only the single line 'id AS ID' has been added.
Be aware that SQLPage's table component automatically enables sorting columns of data by clicking column headers, and filtering values in the displayed data, simply by including the 'TRUE AS sort' and 'TRUE AS search' parameters. Those built-in features are so simple to implement, yet universally useful when dealing with columns of information in any data management application. We'll use the table component often in upcoming tutorial examples.
Finally, keep in mind that depending upon your style preferences, you can remove whitespace as desired. The code below is exactly the same as the code above, with most whitespace formatting eliminated:
CREATE TABLE IF NOT EXISTS myurls (id INTEGER PRIMARY KEY AUTOINCREMENT, url TEXT NOT NULL, description TEXT NOT NULL); INSERT INTO myurls (url, description) SELECT :URL, :Description WHERE :URL IS NOT NULL AND :Description IS NOT NULL; SELECT 'table' AS component, 'Links:'AS title, 'Link' AS markdown, TRUE AS sort, TRUE AS search; SELECT description AS Description, '[' || url || '](' || url || ')' AS Link, id AS ID FROM myurls; SELECT 'form' AS component,'Add link:' AS title; SELECT 'Description' AS name, TRUE AS required; SELECT 'URL' AS name, TRUE AS required;
6.7 A form with more validation features and even more database table columns
This example includes a bigger UI form layout, with many common validation properties (this form is taken directly from the SQLPage examples). When the form is submitted, the values entered by the user are saved to the database, and then all the rows of stored values are displayed in a UI table component. The rows displayed in the table can be sorted ascending/descending by clicking column headers.
First, we'll create a database table schema to store the data. Save this code in a file named '0003_create_demo_users_table.sql', in the '/sqlpage/migrations/' folder:
CREATE TABLE demo_users_table ( id INTEGER PRIMARY KEY AUTOINCREMENT, first_name TEXT NOT NULL, last_name TEXT NOT NULL, resume TEXT, birth_date DATE, password TEXT NOT NULL, terms BOOLEAN NOT NULL );Next, save this as 'form_new_demo_user_send.sql'. It will send submitted data to a separate page. Notice that various properties are set for each input field in the form (placeholders, initial field values, field types, max and min values, allowable input values, required fields, etc.):
SELECT 'form' AS component, 'User' AS title, 'Create new user' AS validate, 'form_new_demo_user_receive.sql' AS action; SELECT 'first_name' AS name, 'John' AS placeholder, TRUE AS required; SELECT 'last_name' AS name, TRUE AS required, 'We need your last name for legal purposes.' AS description; SELECT 'resume' AS name, 'textarea' AS type; SELECT 'birth_date' AS name, 'date' AS type, '2010-01-01' AS max, '1994-04-16' AS value; SELECT 'password' AS name, 'password' AS type, '^(?=.*[A-Za-z])(?=.*\d)[A-Za-z\d]{8,}$' AS pattern, TRUE AS required, 'Minimum eight characters, at least one letter and one number.' AS description; SELECT 'I accept the terms and conditions' AS label, 'terms' AS name, 'checkbox' AS type, TRUE AS required;
Next, save this code as 'form_new_demo_user_receive.sql':
INSERT INTO demo_users_table (first_name, last_name, resume, birth_date, password, terms) VALUES ( :first_name, :last_name, :resume, :birth_date, :password, CASE WHEN :terms IS NOT NULL THEN 1 ELSE 0 END ); SELECT 'text' AS component, 'New demo user info has been saved!' AS contents;You can see that this code receives the values entered into the previous form (:first_name :last_name :resume :birth_date :password), and inserts them into the associated database table columns.
6.7.1 Debugging
What if a problem emerges while you're working on any part of a process such as this (for example, if data values don't get saved as expected)? 'Debugging' applications by including code to temporarily inspect variable values, is a common technique developers use to gain insight into code logic and workflow issues. To display values submitted by the form above, for example, we can simply add some text components to display each submitted variable. Note that COALESCE is used below to display the text 'NULL' when a null value has been submitted:
SELECT 'text' AS component, 'First name: ' || COALESCE(:first_name, 'NULL') AS contents; SELECT 'text' AS component, 'Last name: ' || COALESCE(:last_name, 'NULL') AS contents; SELECT 'text' AS component, 'Resume: ' || COALESCE(:resume, 'NULL') AS contents; SELECT 'text' AS component, 'Birth date: ' || COALESCE(:birth_date, 'NULL') AS contents; SELECT 'text' AS component, 'Password: ' || COALESCE(:password, 'NULL') AS contents; SELECT 'text' AS component, 'Terms: ' || COALESCE(:terms, 'NULL') AS contents; INSERT INTO demo_users_table ( first_name, last_name, resume, birth_date, password, terms ) VALUES ( :first_name, :last_name, :resume, :birth_date, :password, CASE WHEN :terms IS NOT NULL THEN 1 ELSE 0 END ); SELECT 'text' AS component, 'New demo user info has been saved!' AS contents;Open http://localhost:8080/form_new_demo_user_send.sql in your browser. Fill out the form and submit it:
Now let's remove the debug lines above and add a table UI component to display all the submitted rows of data which have been saved in the database. The table schema code is also included directly in this example, so a separate migration file is not required. Additionally, the separate sender form is integrated with the receiver code, so that the entire application exists in a single code file. As you've seen in previous examples, by removing the form 'action' property, form values are submitted, and the page is re-opened with submitted values accessible as variables prefixed with a colon symbol.
Save this example as 'form_with_table.sql', and open it in your browser at http://localhost:8080/form_with_table.sql:
-- Create the demo_users_table if it does not exist CREATE TABLE IF NOT EXISTS demo_users_table ( id INTEGER PRIMARY KEY AUTOINCREMENT, first_name TEXT NOT NULL, last_name TEXT NOT NULL, resume TEXT, birth_date DATE, password TEXT NOT NULL, terms BOOLEAN NOT NULL ); -- Include the previously separate sender UI form component, -- for the user to enter new rows SELECT 'form' AS component, 'User' AS title, 'Create new user' AS validate; SELECT 'first_name' AS name, 'John' AS placeholder, TRUE AS required; SELECT 'last_name' AS name, TRUE AS required, 'We need your last name for legal purposes.' AS description; SELECT 'resume' AS name, 'textarea' AS type; SELECT 'birth_date' AS name, 'date' AS type, '2010-01-01' AS max, '1994-04-16' AS value; SELECT 'password' AS name, 'password' AS type, '^(?=.*[A-Za-z])(?=.*\d)[A-Za-z\d]{8,}$' AS pattern, TRUE AS required, 'Minimum eight characters, at least one letter and one number.' AS description; SELECT 'I accept the terms and conditions' AS label, 'terms' AS name, 'checkbox' AS type, TRUE AS required; -- Insert submitted form data into database table, if required values are not NULL INSERT INTO demo_users_table ( first_name, last_name, resume, birth_date, password, terms ) SELECT :first_name, :last_name, :resume, :birth_date, :password, CASE WHEN :terms IS NOT NULL THEN 1 ELSE 0 END WHERE :first_name IS NOT NULL AND :last_name IS NOT NULL AND :password IS NOT NULL; -- Display a confirmation message SELECT 'text' AS component, CASE WHEN :first_name IS NOT NULL AND :last_name IS NOT NULL AND :password IS NOT NULL THEN 'New user info has been saved!' ELSE 'No data submitted.' END AS contents; -- Display all rows from demo_users_table in a table SELECT 'table' AS component, 'Demo Users' AS title, TRUE AS sort, TRUE AS search; SELECT id AS 'ID', first_name AS 'First Name', last_name AS 'Last Name', resume AS 'Resume', birth_date AS 'Birth Date', -- password AS 'Password', CASE WHEN terms = 1 THEN 'Accepted' ELSE 'Not Accepted' END AS 'Terms Accepted' FROM demo_users_table;
There are a lot of lines to unpack in the code above, but at it's core this application still works basically the same way as the simple 4 line example explained earlier. There are just more form fields, with more validation properties set for each form field, and more variable values in this example.
This is a typical sort of form you'll see in 'CRUD' (Create Read Update Delete) interfaces which are common in all sorts of applications. You'll likely use the form validation properties again in many situations. For the moment, just absorb as much of this example as you can. You'll see most of these code patterns many times again in upcoming tutorial examples, where they'll become familiar.
6.8 Using graphical UI database admin apps to manage your tables (HeidiSQL, DBeaver)
HeidiSQL and DBeaver are just 2 of many free/open source applications which help to manage common database systems. They provide a simple point-and-click interface to easily edit table schema, and to edit data values stored in those tables. Both these applications support all the database systems which SQLPage can connect to.
HeidiSQL, for example, enables you to choose a database system (located on your local machine, or over a network connection):
Set login parameters:
Create and edit table schema:
And Create, Read, Update, Delete values in that schema:
There are many other tools which provide similar control of database configuration, as well as access to stored data. Becoming familiar with at least one such tool can replace the need to write migration scripts to create database schema, and can improve productivity by reducing the amount of code needed to manage back-end admin operations associated with handling database configuration.
7. App Examples Involving Database Queries
The following examples demonstrate how to perform common tasks which involve displaying database query results in front-end components, and manipulating values in the database with front-end UI components.
NOTE: If you want to continue with this tutorial, and you're new to SQL, it's a good idea to look up some additional learning resources about SQL in general. After all, the whole purpose of SQLPage is to provide a web application framework entirely based upon traditional SQL. It's beyond the scope of this tutorial to teach everything there is to know about SQL, but don't fret, SQL is one of the oldest, best established, and most used languages in all of the software development field, so there are endless free resources which can teach you everything there is to know about SQL, no matter your learning style.
Follow along the tutorial, and whenever you need help, be sure to paste code examples into ChatGPT, Claude, Perplexity, or any other favorite AI chatbot. Ask questions of your LLM chatbot friends - they can provide answers to anything you need to know!
7.1 An updated webcam viewer, with a form to add new web cams
The following example updates the webcam app shown earlier in the tutorial. Instead of hard-coding all the webcam info (name, description, url) in the application code, that info is now stored in a database table. The application also provides a form where the user can add new webcam records to the database.
First, save this migration code in the /sqlpage/migrations/ folder, to create a 'webcams' table schema with name, description, and url columns:
CREATE TABLE IF NOT EXISTS webcams ( id SERIAL PRIMARY KEY, title TEXT NOT NULL, description TEXT NOT NULL, url TEXT NOT NULL );Remember to name that file with a unique sequential number at the beginning of the file name. For example, if your previous migration file starts with '0007', call this one something descriptive such as '0008_create_webcam_table.sql'. Note that instead of creating tables with a migration file, you could otherwise choose to create tables with tools such as HeidiSQL, DBeaver, or any other database management application.
Now save this code as web_cams_db.sql:
-- Insert data into the webcams table only if the URL is not null INSERT INTO webcams (title, description, url) SELECT :title, :description, :url WHERE :url IS NOT NULL; -- Form definition for adding a webcam SELECT 'form' AS component, 'Add a webcam:' AS title; SELECT 'title' AS name, '(a short title)' AS description; SELECT 'description' AS name, 'describe the cam' AS placeholder, 'textarea' AS type; SELECT 'url' AS name, 'http://' AS value, TRUE AS required; -- Display a datagrid with the existing webcams SELECT 'datagrid' AS component, 'Live Web Cams' AS title; SELECT title AS title, description AS description, url AS link, url AS image_url FROM webcams;
You've already seen every technique used in this application, previously in the tutorial. When the app opens, it saves any submitted form values, to the database (or does nothing if the page has opened without submitted values). Next, it displays a form component where the user can enter values (with some placeholder, description, type, value, and required properties for each of the entry fields in the form). Finally, a datagrid component is displayed, which shows all the information for each web cam that is stored in the database.
That's a tremendous amount of functionality for only about 2 dozen lines of code. This app would be much more complicated to create, with just about any other software development framework.
7.2 Todo list - a complete CRUD example, with query (URL) parameters
So far in this tutorial, you've seen how to create and display rows of data in a database table using SQLPage UI components, but you haven't yet seen how to update and delete existing rows. The code below creates an editable Todo list, with full CRUD (Create Read Update Delete) capabilities.
In this example, a form is presented where the user can add a new Todo item, and then all Todo items are displayed in a table. The table is sortable by clicking column headers, and is searchable by typing any part of a desired filter text. The user can click the Complete, Edit, and Remove links on each row of the table, to update each row. This example includes the schema creation code (so a separate migration file is not required), and everything else needed to conveniently create, read, update and delete the ToDo items:
-- Create table to store ToDo items CREATE TABLE IF NOT EXISTS todos ( id INTEGER PRIMARY KEY, task TEXT NOT NULL, completed BOOLEAN DEFAULT FALSE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Display a form for adding or editing a ToDo item SELECT 'form' AS component, 'multipart/form-data' AS enctype; SELECT CASE WHEN $edit IS NOT NULL THEN (SELECT task FROM todos WHERE id = $edit) ELSE '' END AS value, 'task' AS name, 'text' AS type, 'Enter or edit your task' AS placeholder; -- Insert new task or update existing one INSERT INTO todos (task) SELECT :task WHERE :task IS NOT NULL AND $edit IS NULL; UPDATE todos SET task = :task WHERE id = $edit AND :task IS NOT NULL; -- Mark task as complete UPDATE todos SET completed = TRUE WHERE id = $complete; -- Delete a task DELETE FROM todos WHERE id = $delete; -- Display existing ToDo items in a table, along with links to perform database operations SELECT 'table' AS component, 'ToDo List' AS title, 'Completed' AS markdown, 'Complete_Action' AS markdown, 'Edit_Action' AS markdown, 'Remove_Action' AS markdown, TRUE AS sort, TRUE AS search; SELECT id AS ID_Number, task AS Task, CASE WHEN completed THEN 'Yes' ELSE 'No' END AS Completed, '[Complete](todo.sql?complete=' || id || ')' AS Complete_Action, '[Edit](todo.sql?edit=' || id || ')' AS Edit_Action, '[Remove](todo.sql?delete=' || id || ')' AS Remove_Action FROM todos;
Study the inline comments and prepare to re-use this code often.
7.2.1 Understanding query (URL) parameters
IMPORTANT:
In the ToDo example above, 'query parameter' values are submitted back to the app from clicked links. These values, in the form of '?somevariable=somevalue' at the end of URL links, are used to pass row ID numbers to perform Complete, Edit, and Delete actions upon selected rows.
The query parameter values submitted back to the app are constructed using the following concatenated links in the table component:
(todo.sql?complete=' || id || ')' (todo.sql?edit=' || id || ')' (todo.sql?delete=' || id || ')'Those submitted parameters (represented by ?somevariable=somevalue at the end of URL links), are handled in the receiving code by variable labels prefixed with the $ symbol (dollar sign):
$complete $edit $deleteThroughout the ToDo app logic, whenever one of those query variables has been submitted, their existence is used to conditionally execute SQL statements upon rows of the database table which have matching ID values. For example, if a $delete variable has been submitted, then a DELETE query is run on the todos table, for any row(s) that match the submitted ID:
DELETE FROM todos WHERE id = $delete;Similarly, if a $complete variable has been submitted, then an UPDATE query is run on the 'completed' column of the todos table, for any row(s) that match the submitted ID:
UPDATE todos SET completed = TRUE WHERE id = $complete;An important clarification to remember is that values submitted via SQLPage forms (using 'POST' method) are represented using the colon (:) prefix, and values submitted as query parameters in URL links (using 'GET' method) are represented using the dollar sign ($) prefix.
The ToDo example above may be the most important example in the tutorial so far, because it demonstrates how to perform some of the most common interactions with a database, using common UI components, all with very simple code constructs. For good measure, here's the full application without comments:
CREATE TABLE IF NOT EXISTS todos ( id INTEGER PRIMARY KEY, task TEXT NOT NULL, completed BOOLEAN DEFAULT FALSE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); SELECT 'form' AS component, 'multipart/form-data' AS enctype; SELECT CASE WHEN $edit IS NOT NULL THEN (SELECT task FROM todos WHERE id = $edit) ELSE '' END AS value, 'task' AS name, 'text' AS type, 'Enter or edit your task' AS placeholder; INSERT INTO todos (task) SELECT :task WHERE :task IS NOT NULL AND $edit IS NULL; UPDATE todos SET task = :task WHERE id = $edit AND :task IS NOT NULL; UPDATE todos SET completed = TRUE WHERE id = $complete; DELETE FROM todos WHERE id = $delete; SELECT 'table' AS component, 'ToDo List' AS title, 'Completed' AS markdown, 'Complete_Action' AS markdown, 'Edit_Action' AS markdown, 'Remove_Action' AS markdown, TRUE AS sort, TRUE AS search; SELECT id AS ID_Number, task AS Task, CASE WHEN completed THEN 'Yes' ELSE 'No' END AS Completed, '[Complete](todo.sql?complete=' || id || ')' AS Complete_Action, '[Edit](todo.sql?edit=' || id || ')' AS Edit_Action, '[Remove](todo.sql?delete=' || id || ')' AS Remove_Action FROM todos;That's only 37 lines of code, to create a complete CRUD web application, with a sortable/searchable table, and the database schema to boot - not too shabby SQLPage!
7.3 Map app to store geographical points of interest
At the beginning of this tutorial, this simple example was presented to demonstrate the map component:
SELECT 'map' AS component; SELECT 'Statue of Liberty' AS title, 40.6892 AS latitude, -74.0445 AS longitude;
Let's add some more functionality to that app, with a form that enables users to enter latitude and longitude values:
SELECT 'form' AS component; SELECT 'latitude' AS name, '40.6892' AS placeholder, :latitude AS value; SELECT 'longitude' AS name, '-74.0445' AS placeholder, :longitude AS value; SELECT 'map' AS component; SELECT 'Your pin: ' || :latitude || ',' || :longitude AS title, :latitude AS latitude, :longitude AS longitude;
The code above uses only a few basic techniques you've seen in several previous examples. The values entered into the form fields are represented by :latitude and :longitude values. When the form is submitted, those values are used by the map component to display a location.
Now let's create a database table to store any values the user enters, and a UI table component to display all the saved points of interest, with links to remove any selected row and to view any selected point on the map. Those links are passed back to the app URL as query parameters ($delete and $lat $long variables), where conditional evaluations perform specified database queries using the submitted ID value. You've seen all these techniques several times in previous examples, so every one of the code patterns should be familiar.
-- Uncomment this line to delete the database table and start fresh -- DROP table map_points_of_interest; CREATE TABLE IF NOT EXISTS map_points_of_interest ( id INTEGER PRIMARY KEY AUTOINCREMENT, datetime_added TIMESTAMP DEFAULT CURRENT_TIMESTAMP, latitude TEXT NOT NULL, longitude TEXT NOT NULL, location_name TEXT NOT NULL ); DELETE FROM map_points_of_interest WHERE id = $delete; SELECT 'redirect' AS component, 'map_points_of_interest.sql' AS link WHERE $lat IS NOT NULL; INSERT INTO map_points_of_interest (latitude, longitude, location_name) SELECT :latitude, :longitude, :location_name WHERE :latitude IS NOT NULL AND :longitude IS NOT NULL; SELECT 'table' AS component, 'Points of Interest' AS title, 'Remove' AS markdown, 'View Point' AS markdown, TRUE AS sort, TRUE AS search; SELECT latitude AS Latitude, longitude AS Longitude, location_name AS Name, '[Remove](map_points_of_interest.sql?delete=' || id || ')' AS Remove, '[Go](map_points_of_interest.sql?lat=' || latitude || '&long=' || longitude || ')' AS 'View Point' FROM map_points_of_interest; SELECT 'map' AS component; SELECT 'Your pin: ' || $lat || ',' || $long AS title, $lat AS latitude, $long AS longitude; SELECT 'form' AS component, 'Add a new point of interest:' AS title; SELECT 'latitude' AS name, '40.6892' AS placeholder, :latitude AS value; SELECT 'longitude' AS name, '-74.0445' AS placeholder, :longitude AS value; SELECT 'location_name' AS name, 'Liberty' AS placeholder, :location_name AS value;
The only new technique above is the inclusion of a commented DROP statement at the beginning of the example. If you uncomment that line (delete the 2 dashes temporarily), save and run the app, and then comment the line again, the database tables will be wiped completely, and you can start entering fresh new values. This is a simple and quick technique which can be used during the development of small in-house apps, for example, when you're entering lots of fake data to test app features. When putting bigger apps into production use, however, it's typically necessary to place schema definition code into sequential migration files so that table schema and data can be changed without erasing entire tables willy nilly. You can also choose to use 3rd party apps such as HeidiSQL/DBeaver/SSMS, as well as command line tools, to manage your production database tables. See https://sql.ophir.dev/your-first-sql-website/migrations.sql for some more information about SQLPage migrations.
If any part of the app above doesn't make sense yet, copy the code into ChatGPT (or any other AI chat bot), and ask for an explanation about how this SQLPage example works. You can dig deep with ChatGPT, ask it how to implement new features, ask it for tutorials about the specifics of any feature, ask it to help debug any code you write which produces errors, etc. Use those AI bots - they can improve your rate of learning and your productive capability manyfold!
7.4 Contacts app, with many typical CRUD features
This example follows code patterns and logic similar to the ToDo app, with form fields to create/update 'name', 'phone', & 'email' values, and a table to view/delete rows:
CREATE TABLE IF NOT EXISTS people ( id INTEGER PRIMARY KEY, name TEXT, phone TEXT, email TEXT ); -- Insert new record only if all fields are provided and no edit is in progress INSERT INTO people (name, phone, email) SELECT :Name, :Phone, :Email WHERE :Name IS NOT NULL AND $edit IS NULL; -- Update the record when editing UPDATE people SET name = :Name, phone = :Phone, email = :Email WHERE id = $edit AND :Name IS NOT NULL; -- Delete the record DELETE FROM people WHERE id = $delete; -- Conditionally show the form for editing or adding a new entry SELECT 'form' AS component; -- Populate form fields for both adding and editing SELECT (SELECT name FROM people WHERE id = $edit) AS value, 'Name' AS name; SELECT (SELECT phone FROM people WHERE id = $edit) AS value, 'Phone' AS name; SELECT (SELECT email FROM people WHERE id = $edit) AS value, 'Email' AS name; -- Add "Add New" button to set the $add parameter SELECT 'button' as component, 'center' as justify; SELECT '?add=1' as link, 'Add New' as title; -- Dynamic link for add new -- Display the table with actions SELECT 'table' AS component, 'Edit' AS markdown, 'Remove' AS markdown, TRUE AS sort, TRUE AS search; SELECT id AS ID, name AS Name, phone AS Phone, email AS Email, '[Edit](?edit=' || id || ')' AS Edit, -- Dynamic link for edit '[🗑️](?delete=' || id || ')' AS Remove -- Dynamic link for delete FROM people;
Even when displaying thousands of rows, this app performs so quickly that pagination is typically not required. It also performs very fast on low powered budget mobile devices. SQLPage's speed is impressive.
The version below adds a number of features to the Contacts app. Logic is added to show form fields only when needed (i.e., when adding or updating records). Pagination is added, to show only 100 rows per page, with home/first/next/previous/last links to navigate between pages. An extra form to perform server based filtering has been added, to compliment the table component's ability to search and sort front-end page row results. A confirmation dialogue has also been added to ensure users intend to actually delete selected rows, when they click the trashcan icon:
CREATE TABLE IF NOT EXISTS people ( id INTEGER PRIMARY KEY, name TEXT, phone TEXT, email TEXT ); -- Define pagination variables SET $records_per_page = 100; -- Number of records per page SET $page = COALESCE($page, 1); -- Current page number, defaults to 1 if not provided SET $offset = ($page - 1) * $records_per_page; -- Calculate the offset for pagination -- Insert new record only if all fields are provided and no edit is in progress INSERT INTO people (name, phone, email) SELECT :Name, :Phone, :Email WHERE :Name IS NOT NULL AND $edit IS NULL AND $add IS NOT NULL; -- Update the record when editing UPDATE people SET name = :Name, phone = :Phone, email = :Email WHERE id = $edit AND :Name IS NOT NULL; -- Delete the record only if confirmed DELETE FROM people WHERE id = $delete AND :confirm = 'Yes'; -- Redirect to clear form after insert, update, or deletion confirmation SELECT 'redirect' AS component, '?' AS link WHERE ($add IS NOT NULL AND :Name IS NOT NULL) -- Redirect after adding a new record OR ($edit IS NOT NULL AND :Name IS NOT NULL) -- Redirect after editing a record OR ($delete IS NOT NULL AND :confirm IS NOT NULL); -- Redirect after confirming deletion -- Conditionally show the form if editing or adding a new entry SELECT 'form' AS component WHERE $edit IS NOT NULL OR $add IS NOT NULL; -- Conditionally show the form for confirmation if a deletion is requested and not yet confirmed SELECT 'form' AS component WHERE $delete IS NOT NULL AND :confirm IS NULL; SELECT 'hidden' AS type, 'delete' AS name, $delete AS value WHERE $delete IS NOT NULL AND :confirm IS NULL; SELECT 'hidden' AS type, 'page' AS name, $page AS value WHERE $delete IS NOT NULL AND :confirm IS NULL; SELECT 'hidden' AS type, 'filter' AS name, $filter AS value WHERE $delete IS NOT NULL AND :confirm IS NULL; SELECT 'radio' AS type, 'Yes' AS value, 'confirm' AS name, 'Confirm Deletion' AS label WHERE $delete IS NOT NULL AND :confirm IS NULL; SELECT 'radio' AS type, 'No' AS value, 'confirm' AS name, 'Do Not Delete' AS label WHERE $delete IS NOT NULL AND :confirm IS NULL; -- Populate form fields for both adding and editing SELECT CASE WHEN $edit IS NOT NULL THEN (SELECT name FROM people WHERE id = $edit) ELSE '' END AS value, 'Name' AS name WHERE $edit IS NOT NULL OR $add IS NOT NULL; SELECT CASE WHEN $edit IS NOT NULL THEN (SELECT phone FROM people WHERE id = $edit) ELSE '' END AS value, 'Phone' AS name WHERE $edit IS NOT NULL OR $add IS NOT NULL; SELECT CASE WHEN $edit IS NOT NULL THEN (SELECT email FROM people WHERE id = $edit) ELSE '' END AS value, 'Email' AS name WHERE $edit IS NOT NULL OR $add IS NOT NULL; -- Display the filter form at the top of the page SELECT 'form' AS component; SELECT 'text' AS type, 'filter' AS name, 'Filter...' AS placeholder, $filter AS value; -- Filter form SELECT 'hidden' AS type, 'page' AS name, '1' AS value; -- Always reset page to 1 on new filter -- Handle empty filter and create a filter pattern for SQL LIKE SET $filter = COALESCE($filter, ''); -- Set filter to an empty string if not provided SET $filter_pattern = '%' || $filter || '%'; -- Create a filter pattern for SQL LIKE -- Add "Add New" button to set the $add parameter SELECT 'button' AS component, 'center' AS justify; SELECT '?add=1&page=' || $page AS link, 'Add New' AS title; -- Calculate the total number of pages SET $total_records = (SELECT COUNT(*) FROM people WHERE name LIKE $filter_pattern OR phone LIKE $filter_pattern OR email LIKE $filter_pattern); SET $total_pages = ($total_records + $records_per_page - 1) / $records_per_page; -- Calculate total pages using integer arithmetic -- Pagination controls: First, Previous, Next, and Last buttons SELECT 'text' AS component, '[Home](?) | ' || -- Home link always appears '[First](?page=1&filter=' || $filter || ') | ' || -- First link goes to the first page with filter CASE WHEN $page > 1 THEN '[Previous](?page=' || ($page - 1) || '&filter=' || $filter || ') | ' ELSE '' END || CASE WHEN (SELECT COUNT(*) FROM people WHERE name LIKE $filter_pattern OR phone LIKE $filter_pattern OR email LIKE $filter_pattern) > ($page * $records_per_page) THEN ' [Next](?page=' || ($page + 1) || '&filter=' || $filter || ') | ' ELSE '' END || '[Last](?page=' || CAST($total_pages AS INTEGER) || '&filter=' || $filter || ')' AS contents_md; -- Display the table with actions and apply pagination SELECT 'table' AS component, 'Edit' AS markdown, 'Remove' AS markdown, TRUE AS sort, TRUE AS search; SELECT id AS ID, name AS Name, phone AS Phone, email AS Email, '[Edit](?edit=' || id || '&page=' || $page || '&filter=' || $filter || ')' AS Edit, -- Dynamic link for edit with filter '[🗑️](?delete=' || id || '&page=' || $page || '&filter=' || $filter || '&confirm=no)' AS Remove -- Dynamic link for delete with confirmation FROM people WHERE name LIKE $filter_pattern OR phone LIKE $filter_pattern OR email LIKE $filter_pattern -- Filter records LIMIT $records_per_page OFFSET $offset; -- Apply pagination
This example implements many of the most common features needed in CRUD database interfaces. You can pick and choose between which features to incorporate, in applications of any sort. The online demo of this app uses SQLPage's embedded SQLite database, demonstrating how effective it can be out of the box. Performance can be improved when handling tremendously large data sets (terabytes), and large numbers of users, by connecting to database systems such as Postgres, and by load balancing servers with tools such as nginx.
7.5 Smallest full CRUD datagrid example, a cheatsheet in 8 lines
The following example works the same way as the simple contacts app, but all variables are shortened, extra text is eliminated, and white space formatting is removed. For some minds, this presentation may help simplify SQLPage CRUD code structures:
CREATE TABLE IF NOT EXISTS p (i INTEGER PRIMARY KEY, x TEXT, y TEXT); INSERT INTO p (x, y) SELECT :X, :Y WHERE $e IS NULL AND :X IS NOT NULL; UPDATE p SET x = :X, y = :Y WHERE i = $e AND :X IS NOT NULL; DELETE FROM p WHERE i = $d; SELECT 'button' AS component; SELECT '?' AS link, 'New' AS title; SELECT 'form' AS component; -- populate with existing values to edit SELECT (SELECT x FROM p WHERE i = $e) AS value, 'X' AS name; SELECT (SELECT y FROM p WHERE i = $e) AS value, 'Y' AS name; SELECT 'table' AS component, 'X' AS markdown, '' AS markdown, TRUE AS sort; SELECT i AS I, '['||x||'](?e='||i||')' AS X, y AS Y, '[X](?d='||i||')' AS '' FROM p;The following values are represented by the given variables: db-table p, db-fields i x y, form-values :X :Y, UI-columns I X Y '', ?query-params $e, $d
Here's an even smaller 8 line version which saves just a single column of values:
CREATE TABLE IF NOT EXISTS t (i INTEGER PRIMARY KEY, a TEXT); INSERT INTO t (a) SELECT :A WHERE $e IS NULL AND :A IS NOT NULL; UPDATE t SET a = :A WHERE i = $e AND :A IS NOT NULL; DELETE FROM t WHERE i = $d; SELECT 'button' AS component; SELECT '?' AS link, 'New' AS title; SELECT 'form' AS component; SELECT (SELECT a FROM t WHERE i = $e) AS value, 'A' AS name; SELECT 'table' AS component, '' AS markdown, 'X' AS markdown, TRUE AS sort; SELECT a AS A, i AS I, '[edit](?e='||i||')' AS '', '[x](?d='||i||')' AS X FROM t;That's cryptic, but it contains only the necessary code required for all CRUD interactions. It's a useful cheatsheet to keep on hand. Here's a slightly larger version with more readable variables:
7.6 Public web forum - joined tables
This example builds on the techniques you've seen so far, to create a simple public web forum, with data joined between 2 database tables.
The forum home page displays a paginated table of all existing topic titles, with the newest message date on each row. The home page also displays a form for users to add a new topic title, along with an initial message for that topic. When a topic title is clicked, the user is shown a page containing all the messages in that topic. The topic page displays a form which enables users to reply with a message to the topic. The form on the topic page includes a text entry field for the person's name, and a text area for the person's message. Each message on the topic page shows the name of the person who posted the message and the time the message was posted.
The user interface of this application is separated into 2 separate pages. This is the home page (forum.sql):
-- Table for forum topics CREATE TABLE IF NOT EXISTS topics ( id INTEGER PRIMARY KEY, title TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Table for forum messages CREATE TABLE IF NOT EXISTS messages ( id INTEGER PRIMARY KEY, topic_id INTEGER NOT NULL, author TEXT NOT NULL, message TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (topic_id) REFERENCES topics(id) ); -- Form for adding a new topic and initial message SELECT 'form' AS component, 'multipart/form-data' AS enctype; SELECT 'title' AS name, 'text' AS type, 'Enter topic title' AS placeholder; SELECT 'author' AS name, 'text' AS type, 'Your name' AS placeholder; SELECT 'message' AS name, 'textarea' AS type, 'Your message' AS placeholder; -- Insert new topic INSERT INTO topics (title) SELECT :title WHERE :title IS NOT NULL; -- Get the last inserted topic ID SET $topic_id = (SELECT MAX(id) FROM topics WHERE title = :title); -- Insert initial message if topic ID is valid INSERT INTO messages (topic_id, author, message) SELECT $topic_id, :author, :message WHERE :author IS NOT NULL AND :message IS NOT NULL; -- Display list of topics SELECT 'table' AS component, 'Forum Topics' AS title, 'View' AS markdown, TRUE AS sort, TRUE AS search; SELECT -- NOTE: the dots join values from the Topics and Messages tables t.id AS ID, t.title AS Topic, m.created_at AS 'Last Message', m.last_author AS 'Last Poster', '[View](topic.sql?topic_id=' || t.id || ')' AS View FROM topics t LEFT JOIN ( SELECT topic_id, MAX(created_at) AS created_at, MAX(author) AS last_author FROM messages GROUP BY topic_id ) m ON t.id = m.topic_id ORDER BY m.created_at DESC;This is the topic page (topic.sql):
-- Insert reply to the topic INSERT INTO messages (topic_id, author, message) SELECT $topic_id, :author, :message WHERE :author IS NOT NULL AND :message IS NOT NULL; -- Redirect to refresh the page after posting a reply SELECT 'redirect' AS component, 'topic.sql?topic_id=' || $topic_id AS link WHERE :author IS NOT NULL AND :message IS NOT NULL; -- Display messages for the selected topic SELECT 'table' AS component, 'Messages in Topic' AS title, TRUE AS sort, TRUE AS search; SELECT author AS Author, message AS Message, created_at AS 'Posted At' FROM messages WHERE topic_id = $topic_id ORDER BY created_at ASC; -- Form for replying to the topic SELECT 'form' AS component, 'multipart/form-data' AS enctype; SELECT 'author' AS name, 'text' AS type, 'Your name' AS placeholder; SELECT 'message' AS name, 'textarea' AS type, 'Your reply' AS placeholder; -- Link back to the forum home page SELECT 'text' AS component, '[Back to Forum](forum.sql)' AS contents_md;
Most of the techniques used in this app are similar to those used in the Todo app. A concatenated link is used on the home page to select the ID of a topic to be viewed. That value is passed to the topic.sql page, where conditional evaluations are performed upon the submitted query parameter. The form and table components you've seen used in previous tutorial examples, are again used to submit and view data.
The most important new feature in this application is the use of separate database tables to store topics and messages. Multiple pages are also used to separate UI functionalities, such as viewing and submitting new topics on the home page, and individual messages on the topics page.
If you're new to SQL, this is a perfect place to begin learning all about joining tables and using foreign keys. It's a good exercise to separate out the pieces of the application which are pure traditional SQL query code, and those which are SQLPage UI code. Simplifying the pieces of the application which create and query the database tables, and experimenting with them in a console, is a great way to clarify how that code is integrated into the SQLPage UI design.
Be sure to read the comments, and follow the logic piece by piece - there's a lot of reusable code in this example. And it can't be said enough - use those AI chat bots to analyze and explore any code which doesn't make sense. They are patient instructors and capable pair coding partners who never get tired of helping to write and debug applications. Get in the habit of bringing all your code questions and challenges to AI immediately, and your work will get completed more easily and quickly than is possible using any other tools. Of course your goal is to become proficient without the need for AI help, but even when you're fully capable of solving any problem on your own, you'll still find AI bots to be fantastic time-saving tools to generate common boilerplate code patterns.
7.7 Online File Storage System
This application enables users to upload binary files from a local device to the SQLPage server, where the files are stored in a database table. A UI table component displays all uploaded files, the dates they were uploaded, and a download link, which enables the users to download any selected file:
SELECT 'form' AS component, 'multipart/form-data' AS enctype; SELECT 'myfile' AS name, 'file' AS type, 'File' AS label; WITH uploaded_file AS ( SELECT sqlpage.uploaded_file_name('myfile') AS fname, sqlpage.read_file_as_data_url(sqlpage.uploaded_file_path('myfile')) AS content ) INSERT INTO uploaded_files (fname, content, uploaded) SELECT fname, content, CURRENT_TIMESTAMP FROM uploaded_file WHERE fname IS NOT NULL AND content IS NOT NULL; SELECT 'text' AS component, 'File uploaded!' AS contents WHERE EXISTS ( SELECT 1 FROM uploaded_files WHERE fname = sqlpage.uploaded_file_name('myfile') ); SELECT 'table' AS component, 'Download' AS markdown, TRUE AS sort, TRUE AS search; SELECT uploaded AS Timestamp, fname AS File_Name, '[download](upload_file.sql?filename=' || fname || ')' AS Download FROM uploaded_files; SELECT 'button' AS component; SELECT 'Download ' || $filename AS title, content AS link FROM uploaded_files WHERE $filename IS NOT NULL AND fname = $filename LIMIT 1;
You can use this code as a basic example of how to upload, download, and save files to the database in SQLPage. Note particularly the use of SQLPage functions sqlpage.uploaded_file_name(), sqlpage.read_file_as_data_url(), and sqlpage.uploaded_file_path(). This example also demonstrates how to use the table component to display links and other values in sortable, searchable columns.
7.8 Cash register and sales report generator
This application is a very simple implementation of a cash register (meant to be a UI demo code example, *not for production use (security and additional features need to be considered for production use)). In this app, users enter line item descriptions, along with a price per item and a quantity per line. All entered items are displayed in a table component, and any time a new line is entered, Subtotal, Tax & Total values are updated in the display below. Each line of the table includes a link to delete the selected row. A button is provided to save completed sales transactions to the database, along with the datetime of the purchase:
CREATE TABLE IF NOT EXISTS sales ( id INTEGER PRIMARY KEY AUTOINCREMENT, sale_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, subtotal DECIMAL(10, 2), tax DECIMAL(10, 2), total DECIMAL(10, 2) ); CREATE TABLE IF NOT EXISTS line_items ( id INTEGER PRIMARY KEY AUTOINCREMENT, sale_id INTEGER, description TEXT, price DECIMAL(10, 2), quantity INTEGER, total DECIMAL(10, 2), FOREIGN KEY (sale_id) REFERENCES sales(id) ); -- Remove a line item DELETE FROM line_items WHERE id = $delete; -- Redirect to refresh the page after deleting a line item SELECT 'redirect' AS component, 'cash_register.sql' AS link WHERE $delete IS NOT NULL; -- Form to add a new line item SELECT 'form' AS component, 'multipart/form-data' AS enctype; SELECT 'description' AS name, 'text' AS type, 'Item Description' AS placeholder; SELECT 'price' AS name, 'number' AS type, 'Item Price' AS placeholder, '0.00' AS value, 0.01 AS step; SELECT 'quantity' AS name, 'number' AS type, 'Quantity' AS placeholder, '1' AS value; -- Insert line item INSERT INTO line_items (description, price, quantity, total) SELECT :description, :price, :quantity, :price * :quantity WHERE :description IS NOT NULL AND :price IS NOT NULL AND :quantity IS NOT NULL; -- Display line items SELECT 'table' AS component, 'Line Items' AS title, 'Remove' AS markdown, TRUE AS sort, TRUE AS search; SELECT description AS Description, printf("%.2f", price) AS 'Price per Item', quantity AS Quantity, printf("%.2f", total) AS 'Total Cost', '[Remove](cash_register.sql?delete=' || id || ')' AS Remove FROM line_items; -- Calculate subtotal, tax, and total SELECT 'text' AS component, 'Subtotal: $' || printf("%.2f", SUM(total)) AS contents FROM line_items; SELECT 'text' AS component, 'Tax (10%): $' || printf("%.2f", SUM(total) * 0.1) AS contents FROM line_items; SELECT 'text' AS component, 'Total: $' || printf("%.2f", SUM(total) * 1.1) AS contents FROM line_items; -- Save the transaction INSERT INTO sales (subtotal, tax, total) SELECT SUM(total), SUM(total) * 0.1, SUM(total) * 1.1 FROM line_items WHERE $save IS NOT NULL; -- Clear line items after saving DELETE FROM line_items WHERE $save IS NOT NULL; -- Show a save button SELECT 'form' AS component; SELECT 'save' AS name, 'submit' AS type, 'Complete Sale' AS value;
This sales report app allows users to enter a start date and an end date. When the dates are submitted, the app displays all cash register sales which occurred between the 2 dates, along with a grand total tally of all the saved transactions:
-- Form to select date range SELECT 'form' AS component, 'multipart/form-data' AS enctype; SELECT 'start_date' AS name, 'date' AS type, 'Start Date' AS label; SELECT 'end_date' AS name, 'date' AS type, 'End Date' AS label; -- Display sales report SELECT 'table' AS component, 'Sales Report' AS title, TRUE AS sort, TRUE AS search; SELECT sale_datetime AS 'Sale Date', printf("%.2f", subtotal) AS 'Subtotal', printf("%.2f", tax) AS 'Tax', printf("%.2f", total) AS 'Total' FROM sales WHERE sale_datetime BETWEEN :start_date AND :end_date AND subtotal IS NOT NULL; -- Calculate total sales for the selected date range WITH total_sales AS ( SELECT SUM(total) AS total_sales FROM sales WHERE sale_datetime BETWEEN :start_date AND :end_date AND subtotal IS NOT NULL ) SELECT 'text' AS component, 'Total Sales: $' || printf("%.2f", total_sales) AS contents FROM total_sales;
Break out ChatGPT and get to work adding features!
8. Authentication
Web applications often need to provide user management controls which restrict access to credentialed, authorized, logged-in users. SQLPage provides a built-in authentication system which enables fine grained control over app features and data access permissions.
8.1 Providing credentialed access to application features
The documentation at https://sql.ophir.dev/documentation.sql?component=authentication#component provides a thorough explanation and examples which demonstrate how to use the SQLPage authentication component.
To use the auth system, you'll create password hashes, which store password values in a way that can be 'validated' (checked to be correct), but not decrypted (so if your database is ever breached, hackers still can't ever get to users' original plain-text password values). Here's a convenient demo which you can use to test the output of the sqlpage.hash_password() function: https://sql.ophir.dev/examples/hash_password.sql
The simplest possible way to password protect a page is to hard-code a hashed password directly in the application. The application runs the authentication component first, and only continues to display the rest of the page if proper credentials are entered into the authorization popup dialogue:
SELECT 'authentication' AS component, '$argon2i$v=19$m=8,t=1,p=1$YWFhYWFhYWE$oKBq5E8XFTHO2w' AS password_hash, sqlpage.basic_auth_password() AS password; -- enter 'password' SELECT 'alert' AS component, 'You are logged in' AS title, 'black' AS color;The sqlpage.hash_password() function is what's used to hash passwords so they can be saved safely in a database table. Here's an example which demonstrates how to use it:
CREATE TABLE IF NOT EXISTS users( id INTEGER PRIMARY KEY, email TEXT NOT NULL, username TEXT NOT NULL, password_hash TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); SELECT 'form' AS component, 'multipart/form-data' AS enctype; SELECT 'email' AS name, 'email' AS type, 'Enter your email' AS placeholder; SELECT 'username' AS name, 'text' AS type, 'Enter your username' AS placeholder; SELECT 'password' AS name, 'password' AS type, 'Enter your password' AS placeholder; INSERT INTO users (username, email, password_hash) SELECT :username, :email, sqlpage.hash_password(:password) -- NOTE THIS WHERE :username IS NOT NULL AND :password IS NOT NULL AND :email IS NOT NULL;
After user credentials have been entered via the form above, then you can provide a form for users to log in to protected pages:
SELECT 'form' AS component, 'Log in' AS title, 'authenticate.sql' AS action; SELECT 'username' AS name, 'text' AS type, 'joebloe' AS placeholder; SELECT 'password' AS name, 'password' AS type, '12341234' AS placeholder;
The authentication component can be placed at the top of any page which needs authorization. The form above logs in to this authenticate.sql page:
-- Authentication component must be at the top of the page SELECT 'authentication' AS component, (SELECT password_hash FROM users WHERE username = :username) AS password_hash, :password AS password; -- If authentication is successful SELECT 'alert' AS component, 'You are logged in' AS title;
You could also, for example, chose to redirect to a separate page upon successful login:
SELECT 'authentication' AS component, (SELECT password_hash FROM users WHERE username = :username) AS password_hash, :password AS password; SELECT 'redirect' AS component, 'welcome.sql' AS link
8.2 Providing access to data associated with user accounts
One of the most common uses for authentication is to provide access to private account data. Let's adjust the Favorite Fruits app from earlier in the tutorial so that users can only see their own private favorite fruits. We'll accomplish this first in the simplest way possible, and then we'll move on to using sessions and cookies to streamline and secure the process.
To separate data so that any row in a table is associated with a user account, we first need to create database schema to store user account information, and then we need a column in the data table to associate each row in the fruit table with a given user. The following admin app code (private_fruits_admin_no_session.sql) creates the required tables, and provides a form to enter new users for the Private Fruits app:
CREATE TABLE IF NOT EXISTS private_fruits_users( id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL, password_hash TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS private_food( id INTEGER PRIMARY KEY AUTOINCREMENT, fruit TEXT NOT NULL, user_id INTEGER, -- NOTE the line below links this user_id to the user table FOREIGN KEY (user_id) REFERENCES private_fruits_users(id) ); SELECT 'form' AS component, 'multipart/form-data' AS enctype; SELECT 'username' AS name, 'text' AS type; SELECT 'password' AS name, 'password' AS type; INSERT INTO private_fruits_users (username, password_hash) SELECT :username, sqlpage.hash_password(:password) WHERE :username IS NOT NULL AND :password IS NOT NULL;Next we need a login page with a form, where users can enter their login credentials (private_fruits_login_no_session.sql):
SELECT 'form' AS component, 'Log in' AS title, 'private_fruits_authenticate_no_session.sql' AS action; SELECT 'username' AS name, 'text' AS type; SELECT 'password' AS name, 'password' AS type;That form passes the submitted credentials to the following authenticate_private_fruits_no_session.sql page, where the user is validated and redirected to the private_fruits_no_session.sql page, if correct credentials are supplied:
SELECT 'authentication' AS component, ( SELECT password_hash FROM private_fruits_users WHERE username = :username ) AS password_hash, :password AS password; SELECT 'redirect' AS component, 'private_fruits_no_session.sql?user_id=' || ( SELECT id FROM private_fruits_users WHERE username = :username ) AS link;On the private_fruits_no_session.sql page, we can then show only records which are associated with the user_id (i.e., rows in the private_food table which have a matching ID in the user_id column):
-- Check if user_id is provided SELECT 'alert' AS component, 'Error: User ID not provided' AS title, 'red' AS color WHERE $user_id IS NULL; -- Form for adding a new fruit SELECT 'form' AS component, 'multipart/form-data' AS enctype; SELECT 'Fruit' AS name; -- Insert new fruit for the logged-in user INSERT INTO private_food(fruit, user_id) SELECT :Fruit, $user_id WHERE :Fruit IS NOT NULL; -- Display fruits for the logged-in user SELECT 'list' AS component, 'My fruit list:' AS title; SELECT fruit AS title FROM private_food WHERE user_id = $user_id;
8.3 Securing accounts with stored session tokens and cookies
The only problem with the example above is that it's entirely insecure. All a user needs to do to access another user's account info is to manually type any user_id number in their browser address bar (i.e., /private_fruits_no_session.sql?user_id=2), and they'll have full access to that other user's account info.
But our effort hasn't been wasted. To avoid exposing the user_id as a GET parameter in the browser address bar, we can instead use a 'session token' to store the user_id in a server-side session rather than passing it via a URL parameter. The updated code files using this approach are below.
A 'sessions' table is added to store session tokens, in private_fruits_admin.sql (be sure to open this page to create the new table):
CREATE TABLE IF NOT EXISTS private_fruits_users( id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL, password_hash TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS sessions ( session_token TEXT PRIMARY KEY, user_id INTEGER, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES private_fruits_users(id) ); CREATE TABLE IF NOT EXISTS private_food( id INTEGER PRIMARY KEY AUTOINCREMENT, fruit TEXT NOT NULL, user_id INTEGER, FOREIGN KEY (user_id) REFERENCES private_fruits_users(id) ); SELECT 'form' AS component, 'multipart/form-data' AS enctype; SELECT 'username' AS name, 'text' AS type; SELECT 'password' AS name, 'password' AS type; INSERT INTO private_fruits_users (username, password_hash) SELECT :username, sqlpage.hash_password(:password) WHERE :username IS NOT NULL AND :password IS NOT NULL;The only change needed in private_fruits_login.sql is a link to the new private_fruits_authenticate.sql URL:
SELECT 'form' AS component, 'Log in' AS title, 'private_fruits_authenticate.sql' AS action; SELECT 'username' AS name, 'text' AS type; SELECT 'password' AS name, 'password' AS type;The private_fruits_authenticate.sql code now generates a session token after successful authentication, and saves that random value in the 'sessions' database table. It also saves that same session token to a cookie in the user's browser:
SELECT 'authentication' AS component, ( SELECT password_hash FROM private_fruits_users WHERE username = :username ) AS password_hash, :password AS password; -- Generate a session token and cookie after successful authentication INSERT INTO sessions (session_token, user_id) VALUES (sqlpage.random_string(32), ( SELECT id FROM private_fruits_users WHERE username = :username )) RETURNING 'cookie' AS component, 'session_token' AS name, session_token AS value; SELECT 'redirect' AS component, 'private_fruits.sql' AS link;Finally, on the private_fruits.sql page, the session_token saved in the database is compared with the session_token stored in the user's browser. The key here is that the value stored in any user's browser cookie won't match any other other user's saved session token, unless they authenticate with the proper username and password. If the user isn't validated properly, then they're redirected to the login page. Otherwise, the rest of the page is displayed:
-- Validate session and get user_id WITH session_data AS ( SELECT user_id FROM sessions WHERE session_token = sqlpage.cookie('session_token') ) -- Redirect to login if session is invalid SELECT 'redirect' AS component, 'private_fruits_login.sql' AS link WHERE NOT EXISTS (SELECT 1 FROM session_data); -- Form for adding a new fruit SELECT 'form' AS component, 'multipart/form-data' AS enctype; SELECT 'Fruit' AS name; -- Insert new fruit for the logged-in user INSERT INTO private_food(fruit, user_id) SELECT :Fruit, ( SELECT user_id FROM sessions WHERE session_token = sqlpage.cookie('session_token') ) WHERE :Fruit IS NOT NULL; -- Display fruits for the logged-in user SELECT 'list' AS component, 'My fruit list:' AS title; SELECT fruit AS title FROM private_food WHERE user_id = ( SELECT user_id FROM sessions WHERE session_token = sqlpage.cookie('session_token') );The user can now open their personal private fruits page as many times as needed (including upon form submissions), and the cookie stored in their browser will validate them to access the page, every time.
Note that we can place this part of the code above, on any other page that requires validated access, and the same authorization requirements will be enforced, using the same stored cookie:
WITH session_data AS ( SELECT user_id FROM sessions WHERE session_token = sqlpage.cookie('session_token') ) SELECT 'redirect' AS component, 'private_fruits_login.sql' AS link WHERE NOT EXISTS (SELECT 1 FROM session_data);You can log a user out of the system above by removing the session token from the database table:
DELETE FROM sessions WHERE session_token = sqlpage.cookie('session_token'); SELECT 'redirect' AS component, 'private_fruits_login.sql' AS link;Although not strictly necessary, it's also a good idea to remove the session token from the browser cookie:
SELECT 'cookie' AS component, 'session_token' AS name, true AS remove;
9. Extending SQLPage With 3rd Party REST APIs
The terms 'Web API', 'REST', and 'HTTP Endpoint' all refer to a standardized methodology of delivering functionality at URL addresses, using the http:// or https:// protocol. Routing programming language functions through URL endpoints is the most common way web applications communicate and interact.
Web APIs typically send and receive data as 'json' payloads, or by using submitted form values. APIs typically look something like:
http://website.com:1234/function/var1/var2?param1=val1¶m2=val2,Note that the example above can be broken down into these parts:
- Domain
- Port number
- Paths (separated by forward slashes)
- Query parameters (starting with ?, and key=value pairs separated by ampersands).
Values can be included in the path values, as well as in query parameters. Attached data payloads aren't visible in the URL.
One key characteristic of Web APIs is that they're language agnostic. Web applications written using Python, NodeJS, PHP, Java, C#, Ruby, Rust, Go, R, or any other language can connect easily with one another via REST APIs. Any system which can request and receive data to-from a URL can interact with HTTP endpoint functions.
9.1 Some basic examples, and json data structures
Paste the following links in your browser:
https://picsum.photos/200 https://picsum.photos/400 https://picsum.photos/800Refresh each of those links a few times, and you'll see a different image every time. The API at that endpoint takes a variable number after the final forward-slash (/), and delivers a random image of the specified size:
Now paste the following links into your browser:
https://jsonplaceholder.typicode.com/users/1 https://jsonplaceholder.typicode.com/users/2 https://jsonplaceholder.typicode.com/users/3 https://jsonplaceholder.typicode.com/users/Refresh each of those links a few times, and you'll see different json data each time. The API at that endpoint takes a variable number after the final forward-slash (/), and delivers the specified record from a database record:
{ "id": 1, "name": "Leanne Graham", "username": "Bret", "email": "Sincere@april.biz", "address": { "street": "Kulas Light", "suite": "Apt. 556", "city": "Gwenborough", "zipcode": "92998-3874", "geo": { "lat": "-37.3159", "lng": "81.1496" } }, "phone": "1-770-736-8031 x56442", "website": "hildegard.org", "company": { "name": "Romaguera-Crona", "catchPhrase": "Multi-layered client-server neural-net", "bs": "harness real-time e-markets" } }The response to the API request above is delivered in json format. Json encloses 'lists' of data values in square brackets [], and 'dictionaries' of key-value pairs in the form {"key": "value"}. Dictionaries can be nested in lists, and vice-versa. Json values are typically surrounded by quotes. Images, sounds, videos, and other binary types of data are typically encoded using base64 or some other text representation in json.
9.2 Requests and responses
Interacting with a web API typically involves sending requests to an HTTP address, and receiving response data. Values are most often sent in one of these forms:
- A specified URL format: site.com/val1/val2
- Request parameters: site.com?x=val1&y=val2
- A json 'payload': a big blob of attached json data
In SQLpage, you can use the fetch function to send a request to an API:
sqlpage.fetch($url)
9.3 Display a random 200px image from picsum.photos API
Response data can be assigned a variable label, or used directly in an SQLPage component, as in this example:
SELECT 'card' AS component, 'A random image:' AS title; SELECT 'https://picsum.photos/200' AS top_image, 'star' AS icon;
You can try the code above at http://server.py-thon.com:8008/random_image.sql
9.4 Display a selected image size from picsum.photos API
The following example extends the idea above with buttons for the user to select image sizes. The selected image size is concatenated to the base API URL and the response is displayed as markdown in a text component:
SELECT 'button' AS component; SELECT '/image_size.sql?imgsize=200' AS link, '200' AS title; SELECT '/image_size.sql?imgsize=400' AS link, '400' AS title; SELECT '/image_size.sql?imgsize=800' AS link, '800' AS title; SELECT 'text' AS component, '' AS contents_md;
9.5 Ophir's map search, parsing values from a json data structure
The following example by Ophir Lojkine concatenates a base URL at openstreetmap.org, with a search value submitted in query parameter format. The API response to that request contains latitude and longitude values, which are displayed in an SQLPage map component:
set url = 'https://nominatim.openstreetmap.org/search?format=json&q=' || sqlpage.url_encode($user_search) set api_results = sqlpage.fetch($url); select 'map' as component; select $user_search as title, CAST($api_results->>0->>'lat' AS FLOAT) as latitude, CAST($api_results->>0->>'lon' AS FLOAT) as longitude;With the code above running at http://server.py-thon.com:8008/searchmap.sql, you could, for example, use the following request parameter to look up the latitude and longitude of the Eiffel Tower and display that location on the map: http://server.py-thon.com:8008/searchmap.sql?user_search=eiffel tower. Try pasting that URL in your browser, and replace 'eiffel tower' with other search values.
IMPORTANT:
Note particularly the CAST operation above, in which the 'lat' and 'lon' values are parsed from the json data returned from the remote API. The ->> operator enables picking from numbered items in list structures, and keys (labels) in dictionary structures.
Note also that JSON data is typically stored as text, even if it represents a number. The CAST function ensures that these values are treated as numeric types in SQL, allowing you to perform mathematical operations with them.
9.6 Display json code from an API
This example displays the response to an API request at https://jsonplaceholder.typicode.com/users , in an SQLPage code component. The code component formats the json response data neatly on screen:
select 'code' as component; select 'API Results' as title, sqlpage.fetch('https://jsonplaceholder.typicode.com/users') as contents;
We'll use data from that API in next few examples.
9.7 Parsing json records from an API using database functions, and displaying in a UI table
This section is a bit long-winded because it uses built-in functions in each separate database that SQLPage can connect to, and those features are slightly different for each database. In each of these examples, we'll download json containing 10 user records from https://jsonplaceholder.typicode.com/users (as above). Then we'll parse that data using database-specific functions, and store the values in separate columns of table. Finally, we'll query the data from that table, and display it in a UI table component.
9.8 Json in Sqlite
Sqlite has the built-in functions 'json_each' and 'json_extract', which can be used to parse downloaded json data. This code starts by creating a 'users_parsed' table with 'name', 'username', 'email', and 'company' columns, where select values from the parsed data are stored temporarily. The temporary data in this table is cleared each time the app loads. The json data is then downloaded using the sqlpage.fetch() function, and selected values are stored in the temporary database table. Finally, the database table is queried, and the results are displayed in a UI table component, with the sort and search properties set to TRUE:
-- Clear the table at the beginning of the script DELETE FROM users_parsed; -- Create table for parsed data CREATE TABLE IF NOT EXISTS users_parsed ( id INTEGER PRIMARY KEY, name TEXT, username TEXT, email TEXT, company TEXT ); -- Fetch and parse JSON data WITH raw_data AS ( SELECT sqlpage.fetch('https://jsonplaceholder.typicode.com/users/') AS json ), parsed AS ( SELECT json_extract(value, '$.id') AS id, json_extract(value, '$.name') AS name, json_extract(value, '$.username') AS username, json_extract(value, '$.email') AS email, json_extract(value, '$.company.name') AS company FROM raw_data, json_each(raw_data.json) ) INSERT INTO users_parsed (id, name, username, email, company) SELECT id, name, username, email, company FROM parsed; -- Display parsed data SELECT 'table' AS component, 'Parsed User Data' AS title, TRUE AS sort, TRUE AS search; SELECT name AS 'Name', username AS 'Username', email AS 'Email', company AS 'Company' FROM users_parsed;
9.9 Json in PostgreSQL
PostgreSQL has rich JSON support, including the functions jsonb_each_text and jsonb_extract_path_text:
-- Assuming you have already fetched the JSON into a variable 'json_data' WITH raw_data AS (SELECT :json_data::jsonb AS json), parsed AS ( SELECT (jsonb_each_text(json ->> 'company')).value AS company, json->>'id' AS id, json->>'name' AS name, json->>'username' AS username, json->>'email' AS email FROM raw_data, jsonb_array_elements(raw_data.json) ) INSERT INTO users_parsed (id, name, username, email, company) SELECT id, name, username, email, company FROM parsed ON CONFLICT (id) DO NOTHING;
9.10 Json in MySQL
MySQL supports the JSON functions JSON_UNQUOTE and JSON_EXTRACT:
-- Assuming you have already fetched the JSON into a variable 'json_data' SET @json = :json_data; INSERT INTO users_parsed (id, name, username, email, company) SELECT JSON_UNQUOTE(JSON_EXTRACT(user.value, '$.id')) AS id, JSON_UNQUOTE(JSON_EXTRACT(user.value, '$.name')) AS name, JSON_UNQUOTE(JSON_EXTRACT(user.value, '$.username')) AS username, JSON_UNQUOTE(JSON_EXTRACT(user.value, '$.email')) AS email, JSON_UNQUOTE(JSON_EXTRACT(user.value, '$.company.name')) AS company FROM JSON_TABLE(@json, '$[*]' COLUMNS ( value JSON PATH '$' )) AS user ON DUPLICATE KEY UPDATE id=id;
9.11 Json in MSSQL
MSSQL has the JSON functions JSON_VALUE and OPENJSON:
-- Assuming you have already fetched the JSON into a variable 'json_data' DECLARE @json NVARCHAR(MAX) = :json_data; INSERT INTO users_parsed (id, name, username, email, company) SELECT JSON_VALUE(user.value, '$.id') AS id, JSON_VALUE(user.value, '$.name') AS name, JSON_VALUE(user.value, '$.username') AS username, JSON_VALUE(user.value, '$.email') AS email, JSON_VALUE(user.value, '$.company.name') AS company FROM OPENJSON(@json) WITH ( value NVARCHAR(MAX) '$' ) AS user ON CONFLICT (id) DO NOTHING;Keep in mind that ChatGPT and other AI tools can be very helpful in converting specialized SQL code from one database system to another. If you're developing your app using the embedded version of sqlite, and then moving to Postgres in production, for example, it may be as simple as pasting your sqlite code into GPT and asking it to convert the functionality to Postgres code. Any time you're stuck with a challenge or lots of busy work, be sure to enlist the help of AI. Doing so will nearly universally reduce your workload and fatigue. Save your brain cells for the innovative work!
9.12 Some additional json parsing examples
9.12.1 Using the agify.io to guess age
Here's another example which uses the sqlite json_extract function to parse the $.age variable out of the json response from agify.io API:
-- Form to enter the name SELECT 'form' AS component, 'multipart/form-data' AS enctype; SELECT 'name' AS name, 'text' AS type, :name AS placeholder; -- Set a default value for the name if not provided SET $name = COALESCE(:name, ''); -- Check if the name is provided SELECT 'text' AS component, CASE WHEN $name != '' THEN 'Your age is predicted to be ' || json_extract( sqlpage.fetch('https://api.agify.io/?name=' || $name), '$.age' ) ELSE 'Please enter your name to predict your age.' END AS contents;
9.12.2 Weather app
This example provides a UI form for the user to enter a zip code. The API at openweathermap.org is then used to provide information about current weather conditions. The wind speed and temperature values picked from the json response data are displayed in a text component. Notice the use of the sqlpage.environment_variable() function, which can be used to keep secrets such as API tokens out of your code:
-- Form to enter the zip code SELECT 'form' AS component, 'multipart/form-data' AS enctype; SELECT 'zip_code' AS name, 'text' AS type, 'Enter your zip code' AS placeholder; -- Define the API key SET $api_key = sqlpage.environment_variable('MY_API_TOKEN'); -- Set a default value for the zip code if not provided SET $zip_code = COALESCE(:zip_code, '07006'); -- Fetch weather data from OpenWeatherMap API WITH weather_data AS ( SELECT sqlpage.fetch( CONCAT( 'https://api.openweathermap.org/data/2.5/weather?zip=', $zip_code, '&appid=', $api_key, '&units=imperial' ) ) AS json_data ) -- Extract wind speed and temperature SELECT 'text' AS component, 'Wind speed is ' || json_extract(json_data, '$.wind.speed') || ', and temperature is ' || json_extract(json_data, '$.main.temp') AS contents FROM weather_data;
To set an environment variable in Windows:
- Click the Windows button and type "environment variables"
- Select Edit the system environment variables
- Click Environment Variables
- Select New under User variables or System variables
- Enter the variable name and value
- Click OK
You can also set an environment variable by opening the Control Panel, selecting System, then Advanced system settings, and then clicking Environment Variables.
To set an environment variable at the Linux/macOS command line:
export VARIABLE_NAME=value
9.13 More complex API requests
So far, all of our API examples have made use of simple requests to HTTP endpoints. In each case, we've fetched the output from some URL. In cases where we needed to specify a value such as image size, a person's age, a zip code, etc., we've been able to concatenate static text and variable values together to compose the necessary URL.
For many API functionalities, however, we'll need to send more complex data along with the request, specify GET, POST, and other methods, and otherwise configure the request to get the info we need from the API. The following example from https://sql.ophir.dev/functions.sql?function=fetch#function demonstrates how to configure more complicated requests:
set request = json_object( 'method', 'POST' 'url', 'https://postman-echo.com/post', 'headers', json_object( 'Content-Type', 'application/json', 'Authorization', 'Bearer ' || sqlpage.environment_variable('MY_API_TOKEN') ), 'body', json_object( 'Hello', 'world', ), ); set api_results = sqlpage.fetch($request); select 'code' as component; select 'API call results' as title, 'json' as language, $api_results as contents;
10. Building And Integrating Your Own Custom REST APIs
You've seen how SQLPage can consume response data from 3rd party REST APIs. SQLPage can also serve data at an HTTP endpoint, to be consumed by other applications. Doing so requires using the json functions built into the particular database system you're using in SQLPage:
- json_group_array() in SQLite
- json_agg() in Postgres
- JSON_ARRAYAGG() in MySQL
- FOR JSON PATH in SQL Server
This example code from https://sql.ophir.dev/documentation.sql?component=json#component:
SELECT 'json' AS component, JSON_OBJECT( 'users', ( SELECT JSON_GROUP_ARRAY( JSON_OBJECT( 'username', username, 'userid', id ) ) FROM users ) ) AS contents;Returns a JSON response in this format:
{ "users" : [ { "username":"James", "userid":1 } ] }In the next few examples, we'll send out json data created by database functions, through API endpoints created by SQLPage.
10.1 Build an image API endpoint in SQLPage, to be called by other languages & tools
Earlier in the tutorial we covered the following file upload example, which enables users to upload and save files into an 'uploaded_files' database table:
SELECT 'form' AS component, 'multipart/form-data' AS enctype; SELECT 'myfile' AS name, 'file' AS type, 'File' AS label; WITH uploaded_file AS ( SELECT sqlpage.uploaded_file_name('myfile') AS fname, sqlpage.read_file_as_data_url(sqlpage.uploaded_file_path('myfile')) AS content ) INSERT INTO uploaded_files (fname, content, uploaded) SELECT fname, content, CURRENT_TIMESTAMP FROM uploaded_file WHERE fname IS NOT NULL AND content IS NOT NULL; SELECT 'text' AS component, 'File uploaded!' AS contents WHERE EXISTS ( SELECT 1 FROM uploaded_files WHERE fname = sqlpage.uploaded_file_name('myfile') ); SELECT 'table' AS component, 'Download' AS markdown, TRUE AS sort, TRUE AS search; SELECT uploaded AS Timestamp, fname AS File_Name, '[download](upload_file.sql?filename=' || fname || ')' AS Download FROM uploaded_files; SELECT 'button' AS component; SELECT 'Download ' || $filename AS title, content AS link FROM uploaded_files WHERE $filename IS NOT NULL AND fname = $filename LIMIT 1;The code above stores image data in the 'content' column of the uploaded_files table, in the following format (the image data is truncated here to save space):
data:image/jpeg;base64,/9j/4AAQSkZJRgABAQEASABIAAD/2wBDAAEBAQEBAQE [...]We're now going to create an HTTP API endpoint in SQLPage that will deliver a selected image from that database table. In this example, the file name can be specified in the HTTP request to the URL endpoint. Because each particular type of database system has different built-in functions to handle json data, we'll need to create separate json functions to return json objects from each type of database:
SQLite:
-- Define the API endpoint SELECT 'json' AS component, json_object( 'filename', uploaded_files.fname, 'image_data', uploaded_files.content ) AS contents FROM uploaded_files WHERE fname = $filename LIMIT 1; -- If no file was found, return an error message SELECT 'json' AS component, json_object( 'error', 'File not found' ) AS contents WHERE NOT EXISTS ( SELECT 1 FROM uploaded_files WHERE fname = $filename );PostgreSQL:
-- Define the API endpoint SELECT 'json' AS component, json_build_object( 'filename', uploaded_files.fname, 'image_data', uploaded_files.content ) AS contents FROM uploaded_files WHERE fname = $filename LIMIT 1; -- If no file was found, return an error message SELECT 'json' AS component, json_build_object( 'error', 'File not found' ) AS contents WHERE NOT EXISTS ( SELECT 1 FROM uploaded_files WHERE fname = $filename );MySQL:
-- Define the API endpoint SELECT 'json' AS component, JSON_OBJECT( 'filename', uploaded_files.fname, 'image_data', uploaded_files.content ) AS contents FROM uploaded_files WHERE fname = $filename LIMIT 1; -- If no file was found, return an error message SELECT 'json' AS component, JSON_OBJECT( 'error', 'File not found' ) AS contents WHERE NOT EXISTS ( SELECT 1 FROM uploaded_files WHERE fname = $filename );SQL Server:
-- Define the API endpoint SELECT 'json' AS component, (SELECT fname AS filename, content AS image_data FROM uploaded_files WHERE fname = $filename FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS contents; -- If no file was found, return an error message SELECT 'json' AS component, (SELECT 'File not found' AS error FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS contents WHERE NOT EXISTS ( SELECT 1 FROM uploaded_files WHERE fname = $filename );
- PostgreSQL: Uses json_build_object() to create a JSON object.
- SQLite: Uses json_object() to create the JSON object.
- MySQL: Uses JSON_OBJECT() to create the JSON object.
- SQL Server: Uses FOR JSON PATH, WITHOUT_ARRAY_WRAPPER to create the JSON object.
In each case, the query checks if the file exists and returns the file’s content if found, or an error message if not.
With the above code running at http://localhost:8008/image_api.sql, and an uploaded image named 'deb.jpg' saved in the data table, we can access the API to download that image at http://localhost:8008/image_api.sql?filename=deb.jpg:
10.2 Use Python code to display photos served by your SQLPage API endpoint
The following Python code can now connect to the API above and display the image:
import requests from PIL import Image from io import BytesIO import base64 # Define the URL to retrieve the image url = "http://localhost:8008/image_api.sql?filename=deb.jpg" # Send a GET request to the URL response = requests.get(url) # Check if the request was successful if response.status_code == 200: # Parse the JSON response data = response.json() # Extract the image data from the JSON response image_data = data.get("image_data") if image_data: # The image_data contains "data:image/jpeg;base64," prefix, so we need to remove it base64_str = image_data.split(',')[1] # Decode the Base64 string into bytes image_bytes = base64.b64decode(base64_str) # Create an image from the bytes and display it image = Image.open(BytesIO(image_bytes)) image.show() else: print("No image data found in the response.") else: print(f"Failed to retrieve data. HTTP Status Code: {response.status_code}")Run that code, and you'll see the reponse image data from the API endpoint, displayed on screen:
Of course, this same sort of functionality can be implemented using any other programming language. It's beyond the scope of this tutorial to cover all the myriad possibilities of how different languages connect with web APIs - the important takeaway is that you can use SQLPage to deliver useful data through an HTTP API endpoint which other tools can use. This simple capability enables connectivity with an enormous variety of other software development frameworks. Other developers will know how to connect to your endpoints, and use the response data. You just need to provide URL and data output examples which they can parse.
10.3 Build a Python function to be served at a Flask API endpoint
Let's work the other way around now, and build an HTTP API in Python which delivers an image at a URL endpoint. We can then connect to that endpoint in SQLPage and display the returned image using SQLPage UI components. The following code uses the Python Flask web framework to serve images from a local folder on the server, to http://localhost:5000/images/
: from flask import Flask, send_from_directory, abort app = Flask(__name__) # Define the path to the folder containing the images IMAGE_FOLDER = "/images" @app.route('/images/<filename>', methods=['GET']) def serve_image(filename): try: # Send the requested image from the IMAGE_FOLDER return send_from_directory(IMAGE_FOLDER, filename) except FileNotFoundError: # If the file is not found, return a 404 error abort(404, description="Image not found") if __name__ == '__main__': app.run(host='0.0.0.0', port=5000, debug=True)
Go to http://localhost:5000/images/filename.jpg in your browser, and you'll see an image returned:
Of course, any other programming language can be used to create a similar HTTP API endpoint.
10.4 Use SQLPage to display photos served by your Flask API endpoint
Now, given the Python Flask API endpoint above, the following SQLPage code can retrieve and display the image at http://localhost:5000/images/deb.jpg, on a card component, together with a specified star icon:
SELECT 'card' AS component, 'Deb:' AS title; SELECT 'http://localhost:5000/images/deb.jpg' AS top_image, 'heart' AS icon;
This example extends the idea by accepting a ?filename=someimage.jpg query parameter, and displaying the specified image:
SELECT 'card' AS component, 'Image:' AS title; SELECT 'http://localhost:5000/images/' || $filename AS top_image, 'star' AS icon WHERE $filename IS NOT NULL;
With these examples, you can see that SQLPage can be used to connect easily with the output of code written in other programming languages. That remote code can run on any accessible server computer, anywhere in the world. Code written in any other programming language can also connect with data output by SQLPage.
This web API architecture enables developers who have experience with just about any diverse language ecosystem, to collaborate on building various functionalities in any application. The power enabled by connecting APIs in both directions like this, leads to endless possibilities in how software can be created.
11. Custom REST API App Examples
This section contains some additional examples in which SQLPage code connects to REST API functions.
11.1 Pig latin generator with a Python back end
This Python Flask code accepts a text string query and returns that string as pig Latin:
from flask import Flask, request, jsonify app = Flask(__name__) def to_pig_latin(text): def convert_word(word): if word[0] in 'aeiouAEIOU': return word + 'yay' else: for i, letter in enumerate(word): if letter in 'aeiouAEIOU': return word[i:] + word[:i] + 'ay' return word + 'ay' # Fallback if no vowel is found return ' '.join(convert_word(word) for word in text.split()) @app.route('/convert_to_pig_latin', methods=['POST']) def convert_text(): data = request.json original_text = data.get('text', '') # Ensure that original_text is a string, default to an empty string if None if original_text is None: original_text = '' pig_latin_text = to_pig_latin(original_text) return jsonify({'pig_latin_text': pig_latin_text}) if __name__ == '__main__': app.run(host='0.0.0.0', port=5000, debug=True)
This SQLPage code uses the complex form of the sqlpage.fetch() function, to send a POST request to the Python API above, with a json object sent as body content. The json data sent as $request_body is extracted from text the user types into a form textarea UI component labeled 'text_input'. The returned text from the API response is labeled $pig_latin_text, and displayed in a text UI component:
-- Form to input text SELECT 'form' AS component, 'POST' AS method, 'http://localhost:8008/pig_latin.sql' AS action; -- Text area for user input SELECT 'text_input' AS name, 'textarea' AS type, '' AS value; -- Submit button SELECT 'button' AS component, 'Convert to Pig Latin' AS label; -- Extract the submitted text SET $original_text = json_extract(sqlpage.variables('POST'), '$.text_input'); -- Prepare the POST request SET $request_body = json_object( 'method', 'POST', 'url', 'http://localhost:5000/convert_to_pig_latin', 'headers', json_object( 'Content-Type', 'application/json' ), 'body', json_object('text', $original_text) ); -- Send the request and get the response SET $response = sqlpage.fetch($request_body); -- Extract the Pig Latin text from the response SET $pig_latin_text = json_extract($response, '$.pig_latin_text'); -- Display the Pig Latin text SELECT 'text' AS component, $pig_latin_text AS contents;
11.2 Text encryption app with Python cryptography back end
The following Python Flask code accepts 2 text strings in a query parameter, and returns the submitted text string in encrypted form. The submitted encryption key string can be used to later decrypt the string. To handle the Python encryption functions, be sure to pip install cryptography:
from flask import Flask, request, jsonify from cryptography.fernet import Fernet import base64 app = Flask(__name__) def generate_fernet_key(user_key): # Ensure the key is 32 bytes long by padding or truncating key_bytes = user_key.encode() # Convert the string to bytes key_bytes = key_bytes.ljust(32, b'\0')[:32] # Pad with null bytes or truncate # Encode to base64 to make it Fernet-compatible fernet_key = base64.urlsafe_b64encode(key_bytes) return fernet_key def encrypt_text(text, key): fernet = Fernet(key) encrypted_text = fernet.encrypt(text.encode()) return encrypted_text.decode() @app.route('/encrypt_text', methods=['POST']) def encrypt(): data = request.json original_text = data.get('text', '') user_key = data.get('key', '') print(f"Received text: {original_text}") print(f"Received key: {user_key}") if not original_text or not user_key: return jsonify({'error': 'Both text and key must be provided'}), 400 try: encryption_key = generate_fernet_key(user_key) encrypted_text = encrypt_text(original_text, encryption_key) return jsonify({'encrypted_text': encrypted_text}) except Exception as e: print(f"Encryption error: {str(e)}") return jsonify({'error': str(e)}), 500 if __name__ == '__main__': app.run(host='0.0.0.0', port=5000, debug=True)As in the previous pig Latin example, the following SQLPage code uses the complex form of the sqlpage.fetch() function, to send a POST request to the Python API above, with a json object sent as body content. The returned encrypted text from the API response is displayed in a text UI component:
-- Form to input text and encryption key SELECT 'form' AS component, 'POST' AS method, 'http://localhost:8008/encrypt_text.sql' AS action; -- Text area for user input SELECT 'text_input' AS name, 'textarea' AS type, '' AS value, 'Enter text to encrypt:' AS label; -- Text area for encryption key SELECT 'encryption_key' AS name, 'textarea' AS type, '' AS value, 'Enter encryption key:' AS label; -- Submit button SELECT 'button' AS component, 'Encrypt Text' AS label; -- Extract the submitted text and key SET $original_text = json_extract(sqlpage.variables('POST'), '$.text_input'); SET $encryption_key = json_extract(sqlpage.variables('POST'), '$.encryption_key'); -- Prepare the POST request as a JSON object SET $request_body = json_object( 'method', 'POST', 'url', 'http://localhost:5000/encrypt_text', 'headers', json_object( 'Content-Type', 'application/json' ), 'body', json_object('text', $original_text, 'key', $encryption_key) ); -- Send the request and get the response SET $response = sqlpage.fetch($request_body); -- Extract the encrypted text from the response SET $encrypted_text = json_extract($response, '$.encrypted_text'); -- Display the encrypted text SELECT 'text' AS component, $encrypted_text AS contents;
12. Extending SQLPage Capabilities With Sqlpage.exec()
Communicating between HTTP APIs can handle many sorts of data processing situations which require integrating 3rd party tools, no matter how disparate those tools are. Remote APIs can be written in any programming language, they can run on any operating system, across machines that are located anywhere in the world, etc.
Interacting with programs that run directly on your server computer, can be another powerful option to enable computing capabilities in SQLPage. The sqlpage.exec() function enables any executable program that can be run on your server to be integrated with SQLPage apps. Executable programs can include any compiled code and/or any code run by an interpreter of any programming language.
Because sqlpage.exec() can run any arbitrary code on your server, it needs to be treated with caution. To use this function, "allow_exec" : true must be set in the SQLPage configuration settings file sqlpage/sqlpage.json. You must restart the SQLPage server application for this change to take effect.
Be sure to read and understand the warnings at https://sql.ophir.dev/functions.sql?function=exec#function before using sqlpage.exec() in any production code.
12.1 Integrating a local Python word count script in SQLPage
Earlier in the tutorial, the word count example demonstrated how to use SQL to count words submitted in a SQLPage UI textarea. The SQL solution works great, but if you have experience with any other programming language, you could implement this same capability with the code of your choice, and send the results back to SQLPage. The following example uses sqlpage.exec() to run a Python script which performs the same functionality:
-- Form to input text SELECT 'form' AS component; SELECT 'text_input' AS name, 'textarea' AS type, :text_input AS value; -- Execute the Python script to count words SELECT 'text' AS component, 'Word Count: ' || sqlpage.exec( 'C:\\Users\\user\\AppData\\Local\\Programs\\Python\\Python311\\python.exe', 'word_count.py', :text_input ) AS contents;Here is the Python script which is called by the SQLPage code above:
import sys text = sys.argv[1] word_count = len(text.split()) print(word_count)In order for the Python script to run properly, the script must be placed in the SQLPage working directory (the folder where sqlpage.exe is located). You can determine that location with this SQLPage script:
SELECT 'text' AS component; SELECT 'Currently running from ' AS contents; SELECT sqlpage.current_working_directory() as contents, true as code;
12.2 Retrieving json data from an API, via a local Python script
This SQLPage app runs a Python script, which uses the Python 'requests' library to download a joke from the API at https://official-joke-api.appspot.com/random_joke:
SELECT 'text' AS component, 'Here''s a joke: ' AS title, sqlpage.exec( 'C:\\Users\\user\\AppData\\Local\\Programs\\Python\\Python311\\python.exe', 'get_joke.py' ) AS contents;Here's the Python code. Save it as get_joke.py in your SQLPage working directory:
import requests response = requests.get('https://official-joke-api.appspot.com/random_joke') joke_data = response.json() print(f"{joke_data['setup']} - {joke_data['punchline']}")
Remember that parsing json with SQL code works differently, depending upon the database system you're connected to in SQLPage. Note that this example works the same regardless of the database you're connected to, because it doesn't rely on the internal json parsing functions in each particular database system.
12.3 Display a markdown table, locally generated by the Python pandas library
This SQLPage script fetches and displays a markdown table generated by some Python pandas code:
SELECT 'text' AS component, sqlpage.exec( 'C:\\Users\\user\\AppData\\Local\\Programs\\Python\\Python311\\python.exe', 'generate_table.py' ) AS contents_md;Here's the Python code. Save it as generate_table.py in your SQLPage working directory:
import pandas as pd data = { "Name": ["Alice", "Bob", "Charlie"], "Age": [25, 30, 35], "City": ["New York", "Los Angeles", "Chicago"] } df = pd.DataFrame(data) markdown_table = df.to_markdown(index=False) print(markdown_table)
These are small examples, but the implications should be clear to anyone with some software development experience. The sqlpage.exec() function opens up the possibility to integrate code from just about any programming language, directly in your SQLPage applications. This adds tremendous potential capability and convenience to your SQLPage apps, because you can easily integrate the enormous ecosystems of just about any programming language - all the libraries and tools which can perform rich computing tasks of almost any sort, data analysis & manipulation, document generation, etc., all via a simple interface.
12.4 Using a database table created by SQLAlchemy
This example doesn't use sqlpage.exec(), but is included here because it accomplishes a useful task for projects which integrate Python. When creating a database table with the Python SQLAlchemy library like this:
from sqlalchemy import create_engine, Column, Integer, String, Date, Enum, ForeignKey from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import relationship connect_string="mssql+pyodbc://sa:pass@url.com/db?driver=ODBC+Driver+17+for+SQL+Server" engine = create_engine(connect_string, echo=False) Base = declarative_base() Session = sessionmaker(bind=engine) class Demog(Base): __tablename__ = 'demog' personID = Column(Integer, primary_key=True, autoincrement=True) regID = Column(Integer, ForeignKey('users.regID'), nullable=False) display = Column(Enum('yes', 'no'), default='no') LastName = Column(String(30)) FirstName = Column(String(30)) MiddleName = Column(String(25)) Gender = Column(Enum('Male', 'Female', 'Other')) DOB = Column(Date) SSN = Column(String(9), default='', nullable=True) notes = Column(String(255)) users = relationship("Users", back_populates="demogs")The following SQLPage example can be used to display a UI table and a form to interact with that database table:
INSERT INTO demog ( regID, LastName, FirstName, MiddleName, Sex, DOB, SSN, notes, display ) SELECT :regID, :LastName, :FirstName, :MiddleName, :Sex, :DOB, :SSN, :notes, :display WHERE :regID IS NOT NULL AND :DOB IS NOT NULL AND :LastName IS NOT NULL AND :FirstName IS NOT NULL; SELECT 'table' AS component, 'People:' AS title, TRUE AS sort, TRUE AS search; SELECT regID AS regID, LastName AS LastName, FirstName AS FirstName, MiddleName AS MiddleName, Sex AS Sex, DOB AS Date_Of_Birth, SSN AS SSN, notes AS notes, display AS display FROM demog; SELECT 'form' AS component, 'Add link:' AS title; SELECT 'regID' AS name, 'number' AS type, TRUE AS required; SELECT 'LastName' AS name, TRUE AS required; SELECT 'FirstName' AS name, TRUE AS required; SELECT 'MiddleName' AS name; SELECT 'Sex' AS name; SELECT 'SSN' AS name; SELECT 'notes' AS name; SELECT 'DOB' AS name, 'date' AS type, '2010-01-01' AS max, '1990-01-15' AS value; SELECT 'display' AS name, 'select' AS type, -- TRUE AS searchable, '[{"label": "no", "value": "no"}, {"label": "yes", "value": "yes"}]' AS options, 'no' AS value;Note that for the SQLAlchemy connection string:
connect_string="mssql+pyodbc://sa:pass@url.com/db?driver=ODBC+Driver+17+for+SQL+Server"The SQLPage connection string in ./sqlpage/sqlpage.json would be:
{"database_url": "mssql://sa:pass@url.com/db"}SQLAlchemy is tremendously popular, so being familiar with how it maps to SQL can be helpful when integrating with Python developers.
13. Extending SQLPage's UI Capabilities By Building Custom Components
The documentation at https://sql.datapage.app/custom_components.sql explains how new UI components can be created for use in SQLPage. This is a powerful feature which enables you to build any sort of specialized visual layout design and UI functionality which isn't included by default in SQLPage.
13.1 Getting started
Save the following code as 'smiley_list.handlebars' in the 'sqlpage/templates/' subfolder of your working directory (where the sqlpage.exe binary is located). Note that by default, the Windows SQLPage package comes with a separate templates subfolder in the working directory (this isn't the case for Linux packages). Just be sure to create a new templates subfolder within /sqlpage, and save this custom template file there, or you'll receive a 404 error when you try to use it:
<h1>{{big_text}}</h1> <ul> {{#each_row}} <li>😊 {{id}} - {{fruit}}</li> {{/each_row}} </ul>Notice that the template above has 3 properties included within double curly brackets {{}}:
- big_text
- id
- fruit
The template also includes a smiley icon, as well as a dash between the 'id' and 'fruit' properties. We'll use this component definition to display a list of fruits from the 'food' database table created earlier in the tutorial. Save this code as 'custom_smiley_list.sql' in the same folder where all your other normal SQLPage code application files are located:
SELECT 'smiley_list' AS component, 'My smiley fruit list' AS big_text; SELECT id AS id, fruit AS fruit FROM food;Note that the 'smiley_list' component we defined above can now be used like any other built-in component that comes with SQLPage. The only difference is that the properties we've defined in the template above, are now properties which can be used in the new smiley_list component. The 'big_text' property is used similarly to how a title property is used in other components - it's displayed between HTML 'h1' tags. The 'id' and 'fruit' properties are displayed within HTML 'li' tags (displayed rows) in the component. Each row of that list is preceded with a smiley icon, and there's a dash between the id and fruit values on each line:
13.2 Using built-in Tabler and Bootstrap styling
Now let's create a slightly more complex component. Save the following code as 'smiley_card_grid.handlebars' in the 'sqlpage/templates/' folder:
<h1>{{title}}</h1> <div class="row"> {{#each_row}} <div class="col-md-4"> <div class="card"> <div class="card-body"> <div class="card-icon">{{{icon_img "mood-smile"}}}</div> <h5 class="card-title">{{author}}</h5> <p class="card-text">{{message}}</p> </div> </div> </div> {{/each_row}} </div>Save this code as 'custom_smiley_card_grid.sql':
SELECT 'smiley_card_grid' AS component, 'Smiley Message Cards' AS title; SELECT message AS message, CONCAT('Author: ', author) AS author FROM messages;The example above queries the 'messages' database table, which was created for the public forum application earlier in the tutorial. Open the app above in your browser and you'll see this layout:
The values from each row of the query are displayed inside a card component. The card contains a body where the smiley icon, author name text (from the 'author' column in the messages database table), and message text (from the 'message' column in the messages database table) are displayed, and each card is displayed in a grid.
13.2.1 Understanding how custom components work
To understand how all this works, you need to know a bit about Bootstrap (the most popular front-end framework used on the Internet) and Tabler styling, which is built on top of Bootstrap.
The component definition above uses standard Bootstrap classes 'row' and 'col-md-4' to create a grid layout where each card occupies a third of the width on medium-sized screens and above. The 'card', 'card-body', 'card-title', and 'card-text' are Tabler classes which inherit from Bootstrap, but provide additional styling beyond what Bootstrap offers. Learning about Bootstrap classes and Tabler extensions will get you a long way towards creating custom SQLPage components.
If you want to style the cards or the grid above, beyond what Tabler/Bootstrap enables, you can include custom CSS. For example, you could define a custom class in your custom component or shell to adjust spacing, background colors, etc.
If all this front-end terminology is new, start by diving into Bootstrap. A Google search will provide endless Bootstrap tutorials. ChatGPT, Claude, Perplexity, or any other favorite AI bot can help you understand and generate working code. All the popular AI LLMs are particularly talented at writing complex Bootstrap, HTML, CSS, and JavaScript front-end code. With their help, the learning curve can be reduced to just a matter of hours before you're creating dazzling front-end custom components.
13.3 A custom URL list component
SQLPage's built-in list component has the ability to link to URLs when list items are clicked, but I wanted my own custom layout for a list component that displays URLs. So I created this simple handlebars definition, and saved it as '/sqlpage/templates/my_urls.handlebars':
<h1>{{title}}</h1> <ul> {{#each_row}} <li>{{description}}: <a href="{{url}}" target=_blank>{{url}}</a></li> {{/each_row}} </ul>Then I updated the URL list application from earlier in the tutorial to use the component above:
CREATE TABLE IF NOT EXISTS myurls ( id INTEGER PRIMARY KEY AUTOINCREMENT, url TEXT NOT NULL, description TEXT NOT NULL ); INSERT INTO myurls (url, description) SELECT :URL, :Description WHERE :URL IS NOT NULL AND :Description IS NOT NULL; SELECT 'my_urls' AS component, 'Links:' AS title; SELECT url AS url, description AS description FROM myurls; SELECT 'form' AS component, 'Add link:' AS title; SELECT 'Description' AS name, TRUE as required; SELECT 'URL' AS name, TRUE as required;
Easy!
13.4 A more advanced animation example
The following template animates a list of items, so that each item fades in as it appears. Save this code as 'sqlpage/templates/animated_list.handlebars':
<h1>{{title}}</h1> <ul class="animated-list"> {{#each_row}} <li style="animation-delay: {{@row_index}}s;">{{first_property}}: {{second_property}}</li> {{/each_row}} </ul> <style nonce="{{@csp_nonce}}"> .animated-list { list-style-type: none; padding: 0; } .animated-list li { opacity: 0; transform: translateY(20px); animation: fadeInUp 0.5s ease-out forwards; } @keyframes fadeInUp { to { opacity: 1; transform: translateY(0); } } </style>This template uses CSS animations to make each list item fade in sequentially. A key part of this component is the fadeInUp animation defined using @keyframes. It makes each list item start from a slightly lower position (translateY(20px)) with 0 opacity, and then fade in while moving up to its final position. The animation-delay property is dynamically set for each list item using {{@row_index}}s, so each item fades in with a slight delay based on its position in the list. The style tag includes a nonce="{{@csp_nonce}}" attribute, which is necessary for inline styles to work with the Content Security Policy that SQLPage enforces.
You can customize the animation timing in the template by adjusting the fadeInUp keyframes value (e.g., 0.5s) or the delay (e.g., {{@row_index}}s) to change the speed and sequence of the animation. The animation type can also be easily changed by altering the transform and opacity properties, or by using different CSS keyframes.
The SQLPage code below uses the component above to display values from the 'webcams' table defined earlier in the tutorial:
SELECT 'animated_list' AS component, 'Animated List' AS title; SELECT title AS first_property, description AS second_property FROM webcams;
For reference, here's the webcams table definition:
CREATE TABLE webcams ( id SERIAL PRIMARY KEY, title TEXT NOT NULL, description TEXT NOT NULL, url TEXT NOT NULL );
13.5 Including images in the animation
The code below adjusts the animated fade-in list component to include images from the url field in the webcams database table. Save the following template as 'sqlpage/templates/animated_image_list.handlebars'.
<h1>{{title}}</h1> <ul class="animated-list"> {{#each_row}} <li style="animation-delay: {{@row_index}}s;"> <img src="{{url}}" alt="{{title}}" class="webcam-image"> <h2>{{title}}</h2> <p>{{description}}</p> </li> {{/each_row}} </ul> <style nonce="{{@csp_nonce}}"> .animated-list { list-style-type: none; padding: 0; } .animated-list li { opacity: 0; transform: translateY(20px); animation: fadeInUp 0.5s ease-out forwards; margin-bottom: 20px; } .webcam-image { width: 100%; max-width: 400px; height: auto; border-radius: 8px; margin-bottom: 10px; } @keyframes fadeInUp { to { opacity: 1; transform: translateY(0); } } </style>This template displays each webcam's title, description, and image with a fade-in effect. The img tag is added within each list item, using the url field from the database to set the src attribute, so the images from each webcam's URL are displayed. The .webcam-image class ensures the images are responsive, with a maximum width of 400px, and adds a slight border-radius for rounded corners. The same fadeInUp animation effect is applied to the entire list item, which now includes the image, title, and description.
You can customize this template by adjusting the max-width and other properties in the .webcam-image class, as desired. As in the previous example, you can tweak the animation-delay, transform, and opacity properties to fine-tune the animation.
You can use this component in your SQLPage queries like this:
SELECT 'animated_image_list' AS component, 'Webcam Gallery' AS title; SELECT title, description, url FROM webcams;
13.6 Tweaking the animated image layout
The code below adjusts the animation layout above, so that images are smaller and appear next to each other across the screen. Each image, title, and description fades in smoothly. Save this template as 'sqlpage/templates/animated_webcam_grid2.handlebars':
<h1>{{title}}</h1> <div class="animated-grid"> {{#each_row}} <div class="grid-item" style="animation-delay: {{@row_index}}s;"> <img src="{{url}}" alt="{{title}}" class="webcam-image"> <h2>{{title}}</h2> <p>{{description}}</p> </div> {{/each_row}} </div> <style nonce="{{@csp_nonce}}"> .animated-grid { display: flex; flex-wrap: wrap; gap: 20px; justify-content: space-around; padding: 0; } .grid-item { width: 200px; opacity: 0; transform: translateY(20px); animation: fadeInUp 0.5s ease-out forwards; text-align: center; } .webcam-image { width: 100%; height: auto; border-radius: 8px; margin-bottom: 10px; } @keyframes fadeInUp { to { opacity: 1; transform: translateY(0); } } </style>The animated-grid class uses display: flex with flex-wrap: wrap to create a responsive grid layout. The gap property adds space between the grid items, and justify-content: space-around distributes the items evenly across the screen. Each grid item (.grid-item) is set to be 200px wide, making the images smaller. The items are centered using text-align: center. The images are set to take up the full width of each grid item (200px).
You can customize the template by adjusting the width property in .grid-item, to change the size of the images and the overall layout. The gap property in .animated-grid controls the spacing between the item. It can be increased or decreased as needed. The fadeInUp animation applies to the entire grid item. It can be adjusted as in the previous example.
Using this template in SQLPage code simply requires updating the component name to 'animated_image_list2':
SELECT 'animated_image_list2' AS component, 'Webcam Gallery' AS title; SELECT title, description, url FROM webcams;
Hopefully these custom component examples inspire some new layout ideas to help make your SQLPage applications pop with visually creative interest!
14. Incorporating Iframes
iFrames enable web applications running at a remote URL to be embedded in SQLPage apps. This is a powerful feature, because it provides an instant way for SQLPage apps to include functionalities which either can't be implemented in SQLPage, or which include technologies that are foreign to SQLPage, or which otherwise must run on 3rd party servers. Embedding remotely hosted software into your SQLPage app opens the door to integrating virtually any other software development tool stack, both on the front-end and on the back-end. iFrames are commonly used to integrate PCI compliant financial transaction services, and other hosted 3rd party componentry which can't be implemented on a given project server for any reason.
iFrames can be displayed in SQLPage card components. For example, this plain HTML code:
<h1>Hello World</h1><br> <a href="hello_world.html" target="_parent">hello world</a><br> <a href="hello_world_iframe.sql" target="_parent">hello world iframe</a>Can be displayed by this SQLPage code:
SELECT 'card' AS component, 'The Hello World HTML page running in an iFrame:' AS title, 1 AS columns; SELECT 'The section below could be ANY web app' AS title, 'http://server.py-thon.com:8008/hello_world.html' AS embed, 'iframe' AS embed_mode, '350' AS height;http://server.py-thon.com:8008/hello_world_iframe.sql
The following example embeds 4 separate applications built with a variety of other web based software tools. Notice that the iFrames are displayed in SQLPage card components:
SELECT 'card' AS component, 'Multiple remote applications running in iFrames' AS title, 2 AS columns; SELECT 'Message Board (Bootstrap, Plain JS, Bottle)' AS title, 'https://server.py-thon.com:8450' AS embed, 'iframe' AS embed_mode, '350' AS height; SELECT 'Embedded Image Uploader (Brython, Flask)' AS title, 'http://216.137.179.125:8023' AS embed, -- https://server.py-thon.com:8443/?sort_by=name&order=asc 'iframe' AS embed_mode, '350' AS height SELECT '3D Message (NiceGUI)' AS title, 'http://216.137.179.125:9090' AS embed, 'iframe' AS embed_mode, '350' AS height SELECT 'Editable Cards (Streamlit, Dataset, SQLAlchemy)' AS title, 'http://216.137.179.125:8501' AS embed, 'iframe' AS embed_mode, '350' AS height SELECT 'card' AS component, 1 AS columns; SELECT 'Contacts & Auth: joe@guitarz.org 12341234' AS title, 'http://server.py-thon.com:5001' AS embed, 'iframe' AS embed_mode, '500' AS height;
14.1 Integrating apps which call SQLPage API endpoints, in SQLPage iframes
Keep in mind that apps written in other programming languages can call APIs written in SQLPage, and vice-versa. This makes iframes a great way to enable easy collaboration with developers who choose to use web APIs in any other programming language ecosystem. For example, the SQLPage API below returns JSON data containing the author and title column values of all messages, from the Forum example app earlier in this tutorial:
SELECT 'json' AS component, JSON_OBJECT( 'messages', ( SELECT JSON_GROUP_ARRAY( JSON_OBJECT( 'author', author, 'message', message ) ) FROM messages ) ) AS contents;http://server.py-thon.com:8008/message_api.sql
The code below uses the JS Fetch API to request data from the API above, and Bootstrap to display those values in a table layout:
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet"> </head> <body> <div class="container" style="margin-top: 0;"> <table class="table table-striped"> <thead> <tr> <th scope="col">Author</th> <th scope="col">Message</th> </tr> </thead> <tbody id="message-table-body"> <!-- Rows will be inserted here dynamically --> </tbody> </table> </div> <script> // Fetch data from the API fetch('http://server.py-thon.com:8008/message_api.sql') .then(response => response.json()) .then(data => { const messages = data.messages; const tableBody = document.getElementById('message-table-body'); messages.forEach(msg => { const row = document.createElement('tr'); row.innerHTML = `<td>${msg.author}</td><td>${msg.message}</td>`; tableBody.appendChild(row); }); }) .catch(error => console.error('Error fetching the data:', error)); </script> </body> </html>http://server.py-thon.com:8008/bootstrap_message_table.html
Finally, this SQLPage code embeds the Bootstrap table layout above in a SQLPage card component:
SELECT 'card' AS component, 'Bootstrap table running in an iFrame:' AS title, 1 AS columns; SELECT 'Data from http://server.py-thon.com:8008/message_api.sql' AS title, 'http://server.py-thon.com:8008/bootstrap_message_table.html' AS embed, 'iframe' AS embed_mode, '350' AS height;http://server.py-thon.com:8008/bootstrap_message_table_iframe.sql
Those 3 pieces form a fully self-contained app, served entirely by SQLPage, with data returned solely by a SQLPage API. No other programming languages (Python, PHP, Java, etc.), ORMs (SQLAlchemy, pyDAL, Pony, etc.), or any other API server framework (Flask, FastAPI, etc.) are required on the back end. In this way, SQLPage can take the place of all those other complex architecture pieces, and integrate beautifully with any front-end tooling of your choice.
As you can see, if you already have any familiarity with other software development systems, iFrames provide a quick solution to leverage your skills. In many cases, it may be simpler to incorporate code and application functionality which you've already created for other projects. Or perhaps you prefer to build some kind of UI layout using a visual drag and drop builder. Or perhaps Bootstrap, jQuery, and other mature libraries make it easy to build interfaces such as tables/grids with inline editing features. Or perhaps you want to integrate a 3D library (see the NiceGUI example in the previous multi-example code above). IFrames provide an easy way to just drop those pre-built pieces into an SQLPage application.
Finally, it's important to understand that SQLPage apps can also be included in 3rd party front-ends, so the workflow discussed above can be entirely flipped. You could, for example, use SQLPage to connect to a 3rd party REST API and display the returned data in SQLPage UI components. And then that SQLPage could be included in an iFrame in a 3rd party app. And if you want to dive into iFrame inception, you could even choose to include 3rd party apps within your SQLPage apps, which are displayed in 3rd party apps, etc. Here's the contacts app from earlier in the tutorial, embedded in a Python Anvil app:
15. Compiling SQLPage From Source
SQLPage binaries are available for most common server operating systems. If you need it to run on another OS, the compile process is simple. SQLPage is written in Rust, which has an easy to use tool chain, and many platform targets. Information about cross-compiling is at https://rust-lang.github.io/rustup/cross-compilation.html.
It's often easier to install Rust on your target device, and compile there (instead of cross-compiling). For example, the following steps can be used to compile SQLPage directly on Android in Termux (this has been tested on Android 13, using the v0.28.0 source files directly from Github):
pkg install rust pkg install git git clone https://github.com/lovasoa/SQLpage.git cd SQLpage cargo build --release cd target/release ./sqlpageA full printout of that console interaction is available at https://com-pute.com/nick/compile-sqlpage-in-termux-all-details.txt. It doesn't get much easier than that - even if you've never touched the Rust toolchain.
Here are 2 compiled executables that run in Android/Termux & in 32 bit Windows 8 (the 32 bit executable also runs in newer Windows versions, tested up to Windows 11):
https://com-pute.com/nick/sqlpageandroid https://com-pute.com/nick/sqlpage32.exe
16. Hosting Your SQLPage Apps
So far, you've run all these tutorial applications on your local computer. So how do we publish them for multiple users to access?
16.1 Delivering apps over a local network
If you're building in-house apps for private use in a home or business, deploying code may be as simple as choosing a WIFI-connected computer on your network to use as a server. Client machines must be connected to the same local network, and client browsers must view the IP address of the server, the port which SQLPage is running on (default is 8080), and the name of the .sql file to run (i.e., something like http://192.168.1.112:8080/myapp.sql). You can obtain the IP address of a server machine by running 'ipconfig' on the command line of that computer.
Be sure that the server computer is on and that the SQLPage server application is running, whenever the app needs to be accessible. If you're using any database beside the embedded Sqlite option, make sure your database server software is also running and accessible. For low traffic in-house applications, sqlite may be fine for production use. Just be sure to back up your data often. With sqlite, this can be accomplished simply by copying the single .db file used by your app(s) to a backup drive. If you want to serve your application to many simultaneously connected users, it may be better to use a more powerful RDBMS, especially if many users will be writing to the database often.
16.2 Configuring SQLPage server settings
The following page lists all the available SQLPage server configuration settings:
https://github.com/lovasoa/SQLpage/blob/main/configuration.md#configuring-sqlpage
The easiest way to implement server settings is by saving a .json file in the /sqlpage subfolder. The following .json file demonstrates how to set the location of the sqlite database, the IP address and network port that the server listens on, as well as the folder where the SQLPage server looks to find source code .sql files. In this example, the files would be stored in sub-folder "./tutorial_text/sqlpage_tutorial_examples/", relative to where the SQLPage executable is located on the server hard drive. And with the configuration below, for a file saved as 'index.sql' in that sub-folder, a user would browse 'localhost:8008/index.sql'. This is actually the config file used to deliver the example apps linked at the beginning of this tutorial:
{ "database_url": "sqlite://./sqlpage/sqlpage.db?mode=rwc", "listen_on": "0.0.0.0:8008", "web_root": "./tutorial_text/sqlpage_tutorial_examples" }It's also possible to configure the server by saving setting values in environment variables (see the link above for instructions).
16.3 Using inexpensive VPS (Linux and Windows)
Although it is possible to set up your home Internet router to open ports and enable your PC to accept requests from the Internet, there are better ways to serve your applications to the world.
'Hosting' companies such as A2hosting, Contabo, and many others provide virtual machines you can rent for a just a few dollars a month, to host your applications.
When choosing a hosting type, you'll generally want to get started with inexpensive 'VPS' (Virtual Private Server) accounts. VPS hosting is very affordable, but extremely powerful and scalable. The down side with VPS hosting is that you shouldn't expect any help from your hosting company when it comes to setting up your software, but ChatGPT can be a huge help learning how to use a VPS system.
In general, VPS accounts simply provide a complete computer system, with an operating system installed, which you can use just like a computer at home. The big difference is that they're hosted on extremely fast server computers which are attached to super fast Internet connections.
You're going to need to choose the operating system that your application runs on. Linux OS is the most popular OS for running web applications, but there's definitely a learning curve to using Linux, which is beyond the scope of this tutorial. If you want to dive into Linux, you'll particularly want to learn how to use 'apt' to install requirements, 'tmux' to run multiple console sessions, 'pico' to edit text files, and 'SSH' to connect to the command line of your server computer, along with all the normal OS and file navigation command such as 'ls', 'cd', 'copy', 'rm', and others.
Learning Linux will enable faster and cheaper hosting options, as well as industry standard hosting practices with the widest variety of options, but if Linux is beyond your current possible scope of learning, you can rent a Windows VPS, to which you'll get screen share access. Shared desktop access on a VPS account provides an interface which is just like sitting in front of a local PC, where you can run your full-stack apps, and they'll be served to the world. Windows VPS is generally more expensive than Linux VPS. You can get Linux VPS from A2, starting at $2.99 monthly, and Window VPS from Contabo starting at about $15 for a usable setup.
If you're going to get deeper into building web apps, learning all about Linux is certainly a great next step to add to your to-do list!
16.4 Using Docker, cloud hosting and other options
SQLPage is available as a Docker image. Docker is a tool which is intended to simplify complicated server configuration steps. Since SQLPage is so simple to install, without any dependencies in most cases, using a heavy solution such as Docker may not be necessary. Some cloud hosting environments, however, make it easier and/or essential to install software with Docker. In those cases, you can get the docker image from https://hub.docker.com/r/lovasoa/sqlpage. You can find a docker compose yaml file at https://github.com/lovasoa/SQLpage/blob/main/docker-compose.yml. There's also a version specifically prepared for use on AWS Lambda at https://github.com/lovasoa/SQLpage/releases. Docker is useful for installing on ARM based Raspberry Pi machines. Other install methods include brew (useful for Mac OS), nix, scoop, and cargo. SQLPage makes it easy to deploy to almost any environment.
16.5 Host with datapage.app
To completely avoid the entire learning curve needed to host applications, just go to https://datapage.app. It's a turn key solution which will get your SQLPage apps running online right away.
17. Where To Go From Here
- Be sure to see Alexis Rouge Carrassat's video tutorials at https://www.youtube.com/playlist?list=PLTue_qIAHxAf9fEjBY2CN0N_5XOiffOk_
- Learn more about SQL
- Learn more about NodeJS, PHP, Java, C#, Ruby, Rust, Go, R and other back-end language ecosystems
- Learn more about HTML, CSS, JS, Bootstrap, and other front-end frameworks/tools
- Learn to use generative AI tools such as ChatGPT, Claude, Perplexity, Lllama, Deepseek, and others - they will supercharge your coding capabilities!
18. A Final Word - Some Perspective
Out of the box, there are many other complicated software development frameworks and enormous language ecosystems which can beat SQLPage in some capability (for example, in the areas of data visualization, document generation, 3D graphics, virtual reality & game development, the creation of AI models, etc.). There are also tools which can enable even greater development speed, or which offer a richer variety of default professional appearance options, etc. But to achieve such massive scope of use and/or specialized productive potential, those tools often require ridiculously complex integrations involving multi-gigabyte IDEs, plugins, visual builders and/or category-specific workflow editors, complicated compile steps, huge embedded components & massive frameworks to deploy in production, and/or expensive hosted services, etc.
SQLPage is just a super-light little tool that can, in the end, do everything needed to get a wide variety of in-house CRUD and data management/analysis work completed. SQLPage does actually rival the productivity of large tool-bound systems for many common project types, and soundly beats the 'simplicity' of no-code and low-code systems, without all the heavy infrastructure to deal with, without license fees, usage restrictions, etc. It exists as a single tiny server executable without dependencies, which takes only a few seconds to download & run in any mainstream environment. It replaces entire 3rd party tool chains with simple integrated code dialects, especially for what's typically handled by framework UI, ORM, Auth, and some file handling & REST endpoint features. It encapsulates the most common software development functionalities needed to build applications that support core business and organizational operation requirements, in a package that's instantly accessible, even for beginner developers.
There is a genuine, almost desperate, need for development tools which satisfy the space that SQLPage so neatly serves, and by placing the established capability of trusted RDBMSs at the heart of every development effort, SQLPage is competitively powerful, performant, and compliant out of the gate. Databases power every sector of our digital world, and SQLPage is based firmly on the rock solid foundation of utilizing all the native features of the most-used RDBMSs in the industry.
SQLPage can be easily augmented to incorporate other back-end language ecosystem tools, and everything about its front-end look/capability can be modified/extended as needed. It's a perfect solution for quick in-house projects, but it can flex to support a more challenging scope of requirements when needed. Its unique killer feature is that current SQL users don't need anything else to create a presentation layer which integrates gracefully with other solutions. It's a truly practical software development base for people who already use SQL but don't have the years needed to dive deep into full-stack development. It's a versatile toolkit which enables easy collaboration with other developers of almost any background (those who use all the other massive language ecosystem tools).
Using SQL to generate UIs and to form the basis of a full-stack development/collaboration framework is such a great idea, and SQLPage is a beautifully executed implementation of that idea. SQLPage's simplicity is a joy to work with, but despite the fun ergonomics, it's a pragmatic modern solution which can connect with the rest of the mainstream tech world and handle real world use cases... and if you want to integrate your favorite 3D JavaScript engine, or connect a Python AI model running on a supercomputer, SQLPage won't hold you back. For this author, it's been fantastic to see users empower themselves to achieve software development goals, and to build full-stack skills which had previously been unattainable. Thank you Ophir!
19. About The Author
Nick Antonaccio has been writing software code since 1979. His instructional texts are top Google results for topics such as 'Learn Anvil', 'Learn Streamlit', 'Learn SQLPage', 'Learn Livecode', 'Learn Rebol', 'Learn NS Basic', 'Learn RFO', 'JSLinb', 'Learn Red Programming', 'Learn Haxe', 'Etsy API Tutorial', 'Business Programming', and many more. Nick has completed hundreds of mission critical production software projects for corporations, government institutions, small businesses & individuals. He has been featured on the front page of the New York Times, and his industry-leading Merchants' Village POS Consignment Software has been used to sell tens of millions of products at busy retail locations around the world.
If you'd like a software product professionally developed, or to hire Nick for tutoring, please call or text 215-630-6759, or send a message to nick@com-pute.com
Copyright © 2024 Nick Antonaccio, All Rights Reserved