Serverpod makes it easy to communicate with your database using strictly typed objects without a single SQL line. But, if you need to do more complex tasks, you can always do direct SQL calls. You define your database mappings right in the protocol yaml files.
It's possible to map serializable classes straight to tables in your database. To do this, add the
table key to your yaml file:
serverpod generate, the database schema will be saved in the
generated/tables.pgsql file. You can use this to create the corresponding database tables.
In some cases, you want to save a field to the database, but it should never be sent to the server. You can exclude it from the protocol by adding the
database flag to the type.
password: String?, database
Likewise, if you only want a field to be accessible in the protocol but not stored in the server, you can add the
api flag. By default, a field is accessible to both the API and the database.
If you use the
api options the field must be nullable.
For performance reasons, you may want to add indexes to your database tables. You add these in the yaml-files defining the serializable objects.
fields key holds a comma-separated list of column names. In addition, it's possible to add a type key (default is
btree), and a
unique key (default is
For the communication to work, you need to have generated serializable classes with the
table key set, and the corresponding table must have been created in the database.
Inserting a table row
Insert a new row in the database by calling the insert method of the
db field in your
var myRow = Company(name: 'Serverpod corp.', employees: );
await Company.insert(session, myRow);
After the object has been inserted, it's
id field is set from its row in the database.
Finding a single row
You can find a single row, either by its
id or using an expression. You need to pass a reference to the a session in the call. Tables are accessible through generated serializable classes.
var myCompany = await Company.findById(session, companyId);
If no matching row is found,
null is returned. You can also search for rows using expressions with the
where parameter. The
where parameter is a typed expression builder. The builder's parameter,
t, contains a description of the table which gives access to the table's columns.
var myCompany = await Company.findSingleRow(
where: (t) => t.name.equals('My Company'),
Finding multiple rows
To find multiple rows, use the same principle as for finding a single row. Returned will be a
var companies = await Company.find(
where: (t) => t.id < 100,
Updating a row
To update a row, use the
update method. The object that you update must have its
id set to a non
var myCompany = await session.db.findById(tCompany, companyId) as Company?;
myCompany.name = 'New name';
Deleting a single row works similarly to the
update method, but you can also delete rows using the where parameter.
// Delete a single row
await Company.deleteRow(session, myCompany);
// Delete all rows where the company name ends with 'Ltd'
where: (t) => t.name.like('%Ltd'),
To find or delete specific rows, most often, expressions are needed. Serverpod makes it easy to build expressions that are statically type-checked. Columns are referenced using the global table descriptor objects. The table descriptors,
t are passed to the expression builder function. The
| operators are overridden to make it easier to work with column values. When using the operators, it's a good practice to place them within a set of parentheses as the precedence rules are not always what would be expected. These are some examples of expressions.
// The name column of the Company table equals 'My company')
// Companies founded at or after 2020
t.foundedDate >= DateTime.utc(2020)
// Companies with number of employees between 10 and 100
(t.numEmployees > 10) & (t.numEmployees <= 100)
// Companies that has the founded date set
Executing raw queries
Sometimes more advanced tasks need to be performed on the database. For those occasions, it's possible to run raw SQL queries on the database. Use the
query method. A
List<List<dynamic>> will be returned with rows and columns.
var result = await session.db.query('SELECT * FROM mytable WHERE ...');