Database basics
Plain-language explanations of tables, rows, keys, SQL, and every word you will meet in Querly. No experience needed.
You do not need to know anything about databases to start using Querly. This page explains the few ideas that everything else is built on. Read it once, then come back whenever a word looks unfamiliar.
Think of a spreadsheet
A database is an organised place where an app keeps its information: customers, orders, products, messages. It looks a lot like a spreadsheet file with several sheets.
| id | name | country | |
|---|---|---|---|
| 1 | Mona | [email protected] | Egypt |
| 2 | Omar | [email protected] | Jordan |
| 3 | Lina | NULL | Egypt |
This small grid is a table called customers. Querly shows every table as a grid just like this one, and lets you search, sort, and edit it.
The words you will see
| Word | What it means | In the example |
|---|---|---|
| Database | A collection of tables for one app or project. | The whole file or server |
| Table | One list of similar things. | customers |
| Row (record) | One item in a table. | Mona's line |
| Column (field) | One kind of detail every row has. | email |
| Primary key | A column whose value is different for every row, so one row can always be told apart from the others. Usually called id. | id |
| Foreign key | A column that holds the id of a row in another table. It is how tables link together, such as an order pointing to its customer. | orders.customer_id |
| NULL | "No value". It is different from an empty text or a zero. | Lina's email |
| Index | A shortcut the database keeps so it can find rows quickly, like the index at the back of a book. | An index on email |
| Schema | The design of the database: which tables and columns exist and how they connect. | The list of tables |
| Query | A question or an instruction you send to the database, written in SQL. | See below |
| Connection | The saved details Querly needs to reach a database: where it is, and who you are. | The sidebar entry |
What a connection needs
- Host: the address of the computer that runs the database. Use
127.0.0.1(orlocalhost) when it runs on your own Mac. - Port: the numbered door on that computer. Each engine has a usual one: PostgreSQL
5432, MySQL and MariaDB3306, SQL Server1433, Redis6379. - Username and password: who you are. Ask whoever runs the database if you do not have them.
- Database name: which database on that server to open first.
- SQLite is different. A SQLite database is just one file on your Mac, so you only choose the file. There is no host, port, or password.
SQL in four sentences
SQL is the language used to talk to a database. You can use Querly for a long time without writing any, because the grid lets you click and edit. When you are ready, four statements cover most daily work.
SELECT name, email FROM customers WHERE country = 'Egypt';INSERT INTO customers (name, email, country) VALUES ('Sara', '[email protected]', 'Egypt');UPDATE customers SET country = 'Jordan' WHERE id = 3;DELETE FROM customers WHERE id = 3;SELECT only looks, so it is always safe. INSERT, UPDATE, and DELETE change data.
UPDATE or DELETE without a WHERE line changes or removes every row in the table. Querly warns you before it runs one, shows how many rows would really change, and can undo it afterwards. Still, make WHERE a habit. See Review and undo writes.
Saving changes
When you edit cells in the grid, nothing is written yet. Querly collects your changes in a pending-changes list. You review them, then press ⌘S to save them all at once. If anything goes wrong, none of the batch is written. See Browse and edit data.